VBA select query with inserted value

[Earplug]

Hi there!

With the following VBA code, I'm trying to select the text value from a textbox and fill that in a query to select the code from a person. (a password only login). However, I'm getting a run time error 3078 at the '' Set rs = db.OpenRecordset("strSQL")'' line.

Private Sub Tekst6_AfterUpdate()
   Dim strSQL As String
   Dim strBarcode As String
   
   Set db = CurrentDb()
   'strBarcode = Me.Tekst6
   'MsgBox strBarcode
   'MsgBox Me.Tekst6.Value
   
   strSQL = "SELECT * FROM Personeel WHERE Barcode = Me.Tekst6.Value"
   Set rs = db.OpenRecordset("strSQL")
   If rs.RecordCount > 0 Then
   
   'If Me.Tekst6 = "1" Then
   
   DoCmd.OpenForm "frmNavigation"
   
   Else
       MsgBox "Barcode is wrong!", vbOKOnly

   End If

End Sub

Does anybody know why this is not working properly?

Thank you!

 

[StarGrabber]

Hi Earplug, welcome to the forum!

Try this:

strSQL = "SELECT * FROM Personeel WHERE Barcode = '" & Me.Tekst6.Value & "';"

 

[Earplug]

Thank you for the quick reply!

I replaced the line, but unfortunately I get the same error.

 

[StarGrabber]

Sorry!

Now I got it:

   strSQL = "SELECT * FROM Personeel WHERE Barcode = '" & Me.Tekst6.Value & "';"
   Set rs = db.OpenRecordset(strSQL)

 

[Earplug]

Thank you very much for the quick reply again.

I replaced those 2 lines but get a different error this time:

error 3464 - datatype mismatch in expression.

Thanks for the quick reply! My apologizes if I myself don't reply quick because it's really late at the moment in my country and I have to wake up too early. I could fall asleep at any time so my apologies if I'm not responding within 12 hours.

 

[StarGrabber]

Ah, o.k., so I assume your "Barcode" field is of numeric type. If so, put the line this way:

strSQL = "SELECT * FROM Personeel WHERE Barcode = " & Me.Tekst6.Value & ";"

 

[Earplug]

StarGrabber thank you soooooooooooo much!!!!!!!!!!! We were all trying to fix this today it took us 3 hours with no result!

Thank you very much!!!!!!!

 

[StarGrabber]

Have pleasant dreams - but hopefully not of VBA!

 

[Earplug]

Have pleasant dreams - but hopefully not of VBA!

Hahahaha thank you!!!!