VB help with sql statement

[blackie1982]

Hi All,
I 'm having a problem with an sql statement. I need to select values from a table depending on the value in a textbox in a form. I keep getting a syntax error when I run the code. Here it is:

Dim sqlrecords As String
Dim tempRS As Integer
Dim tempString As String
Dim codeString As String

codeString = Forms![Calibration Record]!

sqlrecords = "SELECT COUNT (CalibrationRecord.Code) FROM CalibrationRecord WHERE Code = " & [Code]
Set DB2 = Application.CurrentDb
Set RSRecord2 = Nothing
Set RSRecord2 = DB2.OpenRecordSet(sqlrecords)
With RSRecord2
.MoveFirst
tempRS = RSRecord2.Fields(0)
.Close
End With
tempString = tempRS
Me.lblRecord.Caption = tempString + " Calibration Records"
'Me.lblRecord.Caption = codeString
Set RSRecord2 = Nothing

End Sub

Thank You

 

[Mile-O]

Just use a query.

 

[blackie1982]

How do you use a query in a label, I just want to display the number of records related to a specific record. How do you use a value from a textfield in a form in an sql statement?

 

[Rich]

DCount or Sum(Iif(

 

[cinders]

I have th same issue, but I do not know how to use a query to do this. Can some elaborate?

Thanks

 

[Mile-O]

How do you use a query in a label, I just want to display the number of records related to a specific record. How do you use a value from a textfield in a form in an sql statement?

With code:

Me.MyLabel.Caption = DCount("MyField", "MyQuery")

Or, Disable a textbox and set the calculation as its ControlSource.