help with query in a form

[automaticbaby]

I have an SQL statement which I know works. When I type the code into the form, though, I get an error.

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)= " & Me.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

 

[LittleSmudge]

I don't think a textBox will run a SQL query like that at all. - Least ways, I've never come across one doing that.

And even if it did, you are asking the SQL query to return TWO values, yet there is only one place to put it

Solution 1
Have another control called txtIndivNo
txtIndivNo.ControlSource = DLookup("IndividualNo","UserId","UserId.UserId = Me.RPID &quot

and then

RPFirstName.ControlSource = DLookup("FirstName","Individual","IndividualNo = txtIndivNo&quot

Make sure that txtIndivNo is higher up ( Lower Number ) the Tab order than RPFirstName and that
txtIndivNo.Visible = False


Solution 2
In an appropriate event proc do the work in a recordset ( this will be quicker to execute )

Dim rst As Recordset
etc.

rst.Open "SELECT Individual.FirstName, UserID.UserID FROM UserID INNER JOIN Individual ON UserID.IndividualNo = Individual.IndividualNo WHERE (((UserID.UserID)= " & Me.RPID & &quot);"

RPFirstName = rst!FirstName
rst.Close

etc..




'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[automaticbaby]

D'oh! I copied the original query into the question. The actual query should be this:

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

So, now, I have it asking for only one value to be returned. When I run this with your second solution I get an error message: Object variable or With block variable not set.

 

[LittleSmudge]

What is the data TYPE of RPID ?



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[LittleSmudge]

And another question.. ..

If you COMPILE before running the code do you get the error at Compile time or only at run time ?




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[automaticbaby]

I'm not sure I understand your first question. RPID is a the name of the textbox from where I'm getting what I want to search for. I want the textbox to accept alphanumeric characters, but these are text data type in the table they reference.

I can't COMPILE because it's greyed out, so the error only pops up when I actually run the code.

 

[automaticbaby]

I was able to compile it. I get no error messages when I compile the code.

 

[LittleSmudge]

Your answer
I want the textbox to accept alphanumeric characters, but these are text data type in the table they reference.

gives me the information I needs ( suspected )

Use
"SELECT [Individual].[First Name] FROM [UserID] INNER JOIN [Individual] ON [UserID].[IndividualNo] = [Individual].[IndividualNo] WHERE (([UserID].[UserID])= '" & RPID & "'));"

Because RPID is of type TEXT it needs single quote marks around it.
The single quote marks need to be inside the textstring part of the SQL string, as shown above.


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[automaticbaby]

Unfortunately, I still get the same error message: Object variable or With block variable not set at the rst.open line in the code. The code compiles with no problems, though.

 

[LittleSmudge]

Delete the whole of the Where clause and run it again, you'll get a lot of records back - but the issue is seeing if the SQL runs okay rather than the value of the result you get.


Next,
Add a line to the code

MsgBox "SELECT [Individual].[First Name] FROM [UserID] INNER JOIN [Individual] ON [UserID].[IndividualNo] = [Individual].[IndividualNo] WHERE (([UserID].[UserID])= '" & RPID & "'));"

And see what you get back in the MsgBox window.
Read it carefully for any clues





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[automaticbaby]

I deleted the WHERE clause. Same error message.

I created the msgbox. The message box displays the entire SQL statement. (I had to comment out the rst.open statement to do this.)

I simplified the query so that it stays in the same table and doesn't jump from table to table trying to find a record. Same error message.

I created a string variable and had the rst.open statement reference it. Same error message.

Could there be something that I'm missing something from the Reference Library?

 

[automaticbaby]

I'm not sure what I did, but for some reason, now I'm not getting any error messages. But, now, no data is popping into the textbox.

This is what I currently have:

Dim rst As Recordset

rst.Open "SELECT [Individual].[First Name] FROM [UserID] INNER JOIN [Individual] ON [UserID].[IndividualNo] = [Individual].[IndividualNo] WHERE (([UserID].[UserID])= '" & RPID & "'));"

RPFirstName = rst![First Name]

rst.Close


Is is possible that rst![First Name] is causing the problem because of the space in the name? I know if there wasn't a space in the field name, it shouldn't have brackets. Unfortunately, that's the field name and I can't change it.

 

[automaticbaby]

I've changed the code some, but I still can't get data. When I run the following code I get "No current record." When I create a new query and use the SQL below, I get data. Am I trying to extract First Name the wrong way?


Dim rst As DAO.Recordset
Dim db As Database

Set db = CurrentDb

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

Me.RPFirstName.Text = rst("First Name&quot

rst.Close

 

[LittleSmudge]

You seem to be 'extracting' [First Name] the right way.

In your last post you have suddenly flipped to DAO when you've been using ADO all along - why ?


The NoCurrentData is interesting.
Are you sure that your schema is correct. Do you have an existing record that links both tables correctly ?


Try this:
Copy the following

SELECT [Individual].[First Name] 
FROM [UserID] INNER JOIN [Individual] 
ON [UserID].[IndividualNo] = [Individual].[IndividualNo]
WHERE [UserID].[UserID]= XXYYZZ ;

And paste it into a New Query in SQL view.
And replace XXYYZZ with a known RPId
Then run the query directly and see what you get.
Look at it in design view and see if it looks right.

Copy any changes that you make back into the code and try again.



'ope-that-'elps.



G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[automaticbaby]

I switched to DAO because I wasn't getting anywhere with ADO. I've got lines of code in both ADO and DAO that I've commented out trying to get something to work. DAO has at least gotten me a little further.

I didn't know how to write SQL, so I cheated. I first created it as a new query and copied it over to the form code. I've tried the statement several times in the query and it works. I get do a First Name.

So, if the query works, why am I not getting any records?

 

[LittleSmudge]

For the job you are doing here there is no benefit in flitting from ADO back to DAO & v.v. You will just get different error messages that could give the appearance of progress - but not a solution.

Stick to ADO and make sure you have the right references libraries links and ( only those necessary Ie. unlink DAO 3.6 )



OKAY - I've just re-read the thread and I think I have a clue.
It could be in the preparation of the rst Not in the line you are working on.

Make sure ALL of the following lines appear

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

and then the
rst.Open etc.. .. ..



Were any other the above missing ?






G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

 

[automaticbaby]

Still not working. I get the error "The value you entered isn't valid for this field."

Here's what I have:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection

rst.Open "SELECT [Individual].[First Name] FROM [UserID] INNER JOIN [Individual] ON [UserID].[IndividualNo] = [Individual].[IndividualNo] WHERE ((([UserID].[UserID])= '" & RPID & "'));"

RPFirstName = rst("First Name&quot

rst.Close

Also, should I have anything else besides Microsoft ActiveX Data Objects 2.5 Library, as far as ADO is concerned?

 

[LittleSmudge]

The usual 2 are

Microsoft ActiveX Data Objects Recordset 2.5 Library

Microsoft ActiveX Data Objects 2.1 Library


If you have later version of these two thats fine. But I'd expect to see them both referenced.


Graham

 

[Quda]

I may be inexperienced but if you already have a relationship created in Access, then all you have to do is, just create a form via the wizard include the field from the other table ([Individual].[First Name]), and the rest from the first table. Then just remove the field [ID], and create a combo box using the wizard, you can select it to look up records and that would populate the [Individual].[First Name] field.
Now if you need to change the data, why not just create a simple Update query. I read a lot of the questions and answers, and sometimes think that the simplest answers may not be looked at. Can’t see the forest through the trees.

Just my two cents, I hope it helps.

 

[automaticbaby]

Okay, it works, but now I have a new problem. The reason I'm not using the wizard, as suggested, is that I have to enter multiple IDs (up to 7). When I update 1 ID, the name appears in 1 name field. When I update the 2nd ID, that name replaces the name in the 1st name field.

I thought by doing a query it would fix that since I'm making 1 textbox for one ID and another for a 2nd ID. What's happening, though, is the opposite of what happens with the wizard -- the 1st name is replacing the 2nd name.

Is there a way to clear or reset the query?