Thread: SQL Search in VBA

Greetings:

I'm trying to conduct a search for a variable using a form in vbscript. I am trying to use this code but it dosen't seem to work out

Code:

Dim SQLstr As String

Item.Value = txtlvar.Value
Label.Value = txtlvarl.Value


SQLstr = SELECT tblLT.[ItemNumber], tblLabelTable.[LabelNumber] FROM tblLT 
  WHERE (((tblLT.[ItemNumber])=[txtvar]) AND ((tblLT.[LabelNumber])=[txtvarl]));

CurrentDb.Execute (SQLstr)

If Item.Value = [itemnumber] Then
MsgBox "Item found"
End If

If Label.Value = [labelnumber] Then
MsgBox "Item Found"
End If

Item.value and Label.Value are textbox in the form that the user can enter for a search. I get an error when trying to run this and I really don't know much about vba and mysql commands so I'm kinda lost there.

Any help is much appreciated.

You have quite a few errors in your VBA code (it's not VBScript, that's a different variation of the language).

First, when you define a string variable, the literal must be enclosed in quotation marks.

Then you are using the wrong command. The Execute method is not appropriate for searching a table for a value. You could use OpenRecordset, but you have to define the database and recordset objects first.

I'm confused by your saying that you have a text box named "Label". I do hope that's not the case, that would be extremely misleading, since a label is a control that the user cannot use, it's for displaying a label.

So let's assume that you just have a text box named "Item". Your code should be:

Code:

Dim SQLstr As String
Dim db As DAO.Database   ' This could also be done with ADO, different syntax
Dim rs As DAO.Recordset
Set db = CurrentDB

SQLstr = "SELECT [ItemNumber] FROM tblLT WHERE ItemNumber = Me!Item"

Set rs = db.OpenRecordset(SQLstr)

rs.MoveLast

If rs.RecordCount > 0 Then
   MsgBox "Item found"
End If

End If

At least, that's the way I would probably code it. You really need to study the fundamentals of VBA programming.

A good place to start would be Google. Use search terms MS access basic tutorial.

Originally Posted by Colmag

Thanks I'll try that.

Yes I agree I don't know much about it. I just started taking this class and it's a project that's due, and they haven't gone over coding very much yet, so I'm kinda clueless. If you know of any good places to learn more about vb I'd be happy to try and learn more for myself,

Another good VBA tutorial is:

2007 VBA

2003 VBA

Originally Posted by Colmag

Hi,

Tried the code, but it keeps giving me an error message

Run-time Error '3061'

Too few parameters. Expected 1.

I tried switching things around, chaning some names, nothing works I just get this error. Thanks

Have you debugged? What does this return:

Code:

SQLstr = "SELECT [ItemNumber] FROM tblLT WHERE ItemNumber = Me!Item"
MsgBox SQLstr

More than likely, you need to replace Me!Item with your value.

I owe you an apology, I was too fast on the trigger. The "Me!Item" reference cannot be inside the quotation marks, because it is not a literal value, what you want is the value represented by that expression. So make this change:

Originally Posted by don94403

Code:

Dim SQLstr As String
Dim db As DAO.Database   ' This could also be done with ADO, different syntax
Dim rs As DAO.Recordset
Set db = CurrentDB

SQLstr = "SELECT [ItemNumber] FROM tblLT WHERE ItemNumber = " & Me!Item

Set rs = db.OpenRecordset(SQLstr)

rs.MoveLast

If rs.RecordCount > 0 Then
   MsgBox "Item found"
End If

End If

That should work if "ItemNumber" is really a number. If it is a text field, that line must read:

SQLstr = "SELECT [ItemNumber] FROM tblLT WHERE ItemNumber = '" & Me!Item & "'"

where what looks like 3 quotes is actually a single quote followed by a double quote, and what looks like 5 quotes is actually a single quote surrounded by double quotes.