'Converting VBA code into Control Source for form TextBox

I assume this is an easy answer but I am just not good at SQL statements and subqueries (if that's what I need). I have a button that takes a value from a record in a continuous form (me.ProdNum) and gets a recordset. It displays the results of that record set in a MsgBox (code removed for simplicity).

Objective: I'd like to report "totalsupp.recordset" on each record in a textbox.

I assume I need a SELECT COUNT(subquery), but I can't get the syntax correct. [PNUM] and ProdNum are strings

Private Sub MultSupp_Click()
Dim db As DAO.Database
Dim totalsupp As DAO.Recordset
Set db = CurrentDb

Set totalsupp = db.OpenRecordset("SELECT * FROM qryKANBAN_Suppliers WHERE [PNUM] = '" & Me.ProdNum & "'")

Debug.Print totalsupp.RecordCount

Set totalsup = Nothing

End Sub
sqlms-accessselectcountsubquery


Solution 1:[1]

Thanks to the help in the comments about using DCount. I get so confused about all the double quotes and single quotes...

=DCount("*","qryKANBAN_Suppliers","PNUM='" & [ProdNum] & "'")

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 RiverChawk

Related Questions