convert sql from query to sql in vb

[automaticbaby]

I'm not that familiar with writing an sql statement, so I cheated. I created a query and then copied the statement, with some changes, to VB in the form I want need the code. The sql works in the query, but it doesn't work in the form.

Here's the code from the query:
SELECT Individual.[First Name], UserID.UserID
FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo
WHERE (((UserID.UserID)="xxxxxxxxxxxx&quot);

This the code in the form:
RPFirstName = "SELECT Individual.First Name, UserID.UserID FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= " & Forms![Error Form]!RPID & &quot);"

The textbox RPFirstName is always showing what is written between the quotation marks. How can I get the result from this query to pop up in the textbox?

Thanks

 

[petermeachem]

It's not a query. You need to create a recordset using your sql and then copy the result into the text box.

http://www.therockalltimes.co.uk

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[automaticbaby]

petermeachem,

Would you be able to guide me in the right direction? I've been playing with dao recordsets for the past few days and it still get errors. I'm not sure I'm doing it right. Should I be using dao? All I really want to do is query 3 tables (which the code above accomplishes) and output the data to a textbox.

Thanks

 

[petermeachem]

What code do you have so far? Andwhat database are you using?

http://www.therockalltimes.co.uk

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[automaticbaby]

I've tried 2 variations on the following code:

Dim rs As DAO.Recordset

Set rs = "SELECT Individual.[First Name] FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= " & Me.RPID & &quot);"

Me.RPFirstName = rs

I've also set rs = CurrentDb.OpenRecordset(sql_code), but with no luck either.

The first statement gives me a "Too few parameters. Expected 1." error message and the second give me a "Object required" error message.

 

[petermeachem]

You want to do something more like

Dim rs As Recordset

Set rs = db.OpenRecordset("SELECT wt FROM SwitchBox &quot
If Not (rs.EOF And rs.BOF) Then
me.txtOne = rs("wt&quot
Else
me.txtOne = "--"
End If
rs.Close

where db is the database and is opened like
Set db = OpenDatabase(app.path & "\valves.MDB", False, False)

Define db as
Global db As Database

dao is perfectly fine.

http://www.therockalltimes.co.uk

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[automaticbaby]

Okay, I made some changes with your suggestions. Here's what I've got:

Dim rs As Recordset
Dim drs As DAO.Recordset
Dim db As Database

Set db = CurrentDb

Set rs = db.OpenRecordset("SELECT Individual.[First Name] FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= " & Me.RPID & &quot);&quot

If Not (rs.EOF And rs.BOF) Then
Me.RPFirstName = rs("Individual.[First Name]&quot
Else
Me.RPFirstName = "--"
End If

rs.Close

I still get the same error as before (Too few parameters...). If I change the set statement to the following:

Set rs = db.OpenRecordset("SELECT Individual.[First Name] FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= " & Me.RPID & &quot);", dbOpenTable)

...I get the following error:

The Microsoft Jet database engine could not find the object 'sql_statement'. Make sure the obect exists and that you spell its name and the path name correctly.

Any ideas?

Thanks

 

[petermeachem]

Make sure you have a reference to a suitable dao library, 3.51 does for access 97.

Open the database like this
Set db = OpenDatabase("d:\db4.MDB", False, False)

Set rs = db.OpenRecordset("SELECT Individual.[First Name] FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= " & Me.RPID & &quot);&quot

If Not (rs.EOF And rs.BOF) Then
me.RPFirstName = rs("[First Name]&quot
Else
me.RPFirstName = "--"
End If

rs.Close

You only need to put DAO.Recordset if you have, for instance, ado recordset referenced as well.
The above works, I've just tried it. Use F8 to single step through and you will see where the problem is.

http://www.therockalltimes.co.uk

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[automaticbaby]

DAO 3.6 object library is checked, so that shouldn't be the problem.

As far as opening the database, I've imported the tables I need. Do I have to open it the way you described? Should I not be importing it? Either way, I still get the same error message.

ADO recordset was not checked, so I checked it and I still get the same error message.

I get an error message "Too few parameters. Expected 1." at the set rs = db.OpenRecordset...

 

[petermeachem]

Imported into what? How does the vb programme know where the data is supposed to be?

You aren't writing this in Access are you? If so, try the Access forum.

http://www.therockalltimes.co.uk

http://www.therockalltimes.co.uk/2003/03/24/geneva-convention.html

 

[automaticbaby]

Sorry to make you go through all this trouble. Yes, I'm writing this in Access. I thought this was the right forum since it is in Visual Basic and it is a Microsoft database.

 

[CCLINT]

>I get an error message "Too few parameters. Expected 1." at the set rs = db.OpenRecordset...

Then it is obvious that you have mis-spelled field names, or those field(s) do not exist.

With-out seeing the DB table structure, it is hard from our side to help further.

Copy the names directly to the sql statement.

It is also possible that the error is due to a mis-type in the sql statement which is being interpeted to a wrong field name as oppossed to a sytax error.

Also, make sure you enclose all table and field names with brackets as in:

"SELECT [Individual].[First Name] FROM [UserID] INNER JOIN [Individual] ON [UserID].[IndividualNo] = [Individual].[IndividualNo] WHERE [UserID].[UserID]= " & Me.RPID

If your sql statement in the debug/immediate window produces exactly this result:
SELECT Individual.[First Name] FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= 123));

where I have substituted
" & Me.RPID & " with the value 123

and you still receive the same error (Too few parameters...) then you have one or more mis-spelled field names.

Therefore, because this error is only produce as a result of what I mentioned above, we would be spinning our wheels here to pursue the matter further...

Verify the field names. Copy them from the db table. Sometimes we may think we are seeing a piece of text correctly spellt, but indeed it is mis-spellt.


>I thought this was the right forum since it is in Visual Basic and it is a Microsoft database.

If you are using VB5 or VB6 then it is the right forum, even if you are using an ACCESS database as the source for your data.
If you are writing this an an ACCESS module using VBA, then the ACCESS forum is where you should be as petermeachem as pointed out.