VBA Syntax Error

Question

I am trying to use an unbound textbox and button to search my ID column (Barcode) and list the record in a form.

The issue I'm having is that according to many examples it looks correct, but I'm new to VBA.

Private Sub searchbutton_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.OpenForm "Update"

Set rst = Forms!Update.Recordset.Clone

rst.FindFirst (Barcode) =  & Me.searchtext
Forms!Update.Bookmark = rst.Bookmark

DoCmd.Close acForm, Me.Name

End Sub

The issue is with line rst.FindFirst (Barcode) = & Me.searchtext and throws a syntax error, but no specifics.

I've also tried:

rst.FindFirst [Barcode] = Me.searchtext
rst.FindFirst "[Barcode] = " Me.searchtext
rst.FindFirst (Barcode) = " Me.searchtext

The [Barcode] lines throw:

Run-time error '3464' Data type mismatch in criteria expression.

the Barcode field is ShortText as it needs to support my barcodes which as "000001".....

Same issue, barcode is shorttext as i needed to keep the begining 000's which are on the barcodes, sorry I forgot to add that — Sauced Apples
– Sauced Apples, Commented Jun 22, 2016 at 15:12
I left out a needed & character. Should be this: rst.FindFirst "[Barcode] = '" & Me.searchtext & "'" — HansUp
– HansUp, Commented Jun 22, 2016 at 15:16
And thats because the script is telling it to close, yep working great thank you. pop it as an answer and ill accept — Sauced Apples
– Sauced Apples, Commented Jun 22, 2016 at 15:19

HansUp · Accepted Answer · 2016-06-22 15:25:33Z

1

Give FindFirst a string expression ...

'rst.FindFirst (Barcode) =  & Me.searchtext
rst.FindFirst "[Barcode] = '" & Me.searchtext & "'"

However, I'm not sure why the original code triggered a runtime error. Giving FindFirst this ... (Barcode) = & Me.searchtext ... should trigger a compile error. I don't understand why you didn't get a compile error. Make sure you have Option Explicit in the Declarations section of your code module.

edited Jun 22, 2016 at 15:25

answered Jun 22, 2016 at 15:22

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Syntax Error

1 Answer 1

Comments

Hot Network Questions