Three parameter SQL in VBA

[Brambojr]

Well that sounds a bit more ambitious than I think I really am.

I have written some code and looked at past posting trying to riddle this away . . to no avail, alas and alak!!

Someone please help here is the code, and yes the table is named "tbl" and form is named "frm" - it is an experimental db til I figure this out. . .


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

Dim Name As String
Dim ID As String
Dim Information As String
Dim strSQL As String

Name = Nz([Forms]![frm]![txtName])
ID = Nz([Forms]![frm]![txtID])
Information = Nz([Forms]![frm]![txtInformation])

strSQL = "SELECT * FROM tbl"
strSQL = strSQL & " WHERE (([Name] = " & Name & &quot"
strSQL = strSQL & " AND ([ID] = " & ID & &quot"
strSQL = strSQL & " AND ([Information] = " & Information & &quot);"

MsgBox strSQL, vbInformation

'to open up the record set
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL) Brambojr

 

[Brambojr]

btw -

I DID find the extra "s" after "db" in two line. But I still get a missing operator message.

Help!!! Brambojr

 

[UncleJack]

Gosh, why don't you just design a query with the form references as criteria and then open the query in code? You're making this into much more of a task than it need be.

In the QBE design view, drag down the Name, ID, etc. fields onto the grid below. On the criteria line place:

Forms!Frm!TxtName for the Name field and corresponding values for the other fields. The query will look to the open form and substitute the values there for the expressions on the criteria line.

After designing the query if you absolutely insist on using SQL just click the View button on the menubar, select SQL View and copy the SQL that Access has created for you.

The interesting thing about your code is that it goes nowhere. You end up opening a recordset, but then you don't do anything with it. (Opening a recordset is the beginning of a process, not the end.) I understand you're doing this as a learning exercise, which is fine, but what is the purpose of your routine?

Uncle Jack

 

[tlbroadbent]

You need to provide some quotes around the string or text parameters. I assume [ID] is numeric thus requires no quotes.

strSQL = "SELECT * FROM tbl"
strSQL = strSQL & " WHERE (([Name] = '" & Name & "')"
strSQL = strSQL & " AND ([ID] = " & ID & &quot"
strSQL = strSQL & " AND ([Information] = '" & Information & "'));"
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Brambojr]

Thank you both for helping. Terry, that was exactly what I needed.

Uncle Jack, I tried that approachand it would give me an error stating that I had more parameters than expected. I guess if you want to add parameters that link to a form it must be done in VBA code like what I had only with Terry's modification. ([ID] was a string)
Wow, I spent some time trying to get that done!

The reason I was putting it together and the continuation of the code is that I am making many forms with unbound controls. I want to ensure that no one enters a value twice (ie. logging the same error). I am putting this together both to learn and in the hopes it will lead me to that end.
It now appears I need to build in a specific Error handler and I may be done. . . . geez. . . . at least after this I will have the understanding I need to whiz through any others. A template, if you will.

Thanks again both of you for your help. Brambojr