How to pass a variable in a text box to a query

[BigKahuna]

Hi Everyone,

I am not a programmer so my question might sound a bit dumb to all the professional people here but I'd really appreciate ya'lls input. Thanks in advance!!!
We have a table called Student which has a primary key "ID". I have this value show up in a textbox in one of the forms. When this ID is double clicked another form is supposed to open up with the funding information corresponding to that particular student ID. The data for the funding information comes from a linked table where the ID field goes by a different name "SFCRefNo". How can I pass the variable "ID" to the query which fetches the funding information.

 

[timgill]

Set the criteria for SFCRefNo in the second query to the following:

forms!form1name.ID

This will work assuming you don't use the query for anything other than viewing the second form and that the initial form stays open while viewing the first form, and that the first form is only displaying one record.

Alternately (and more cleanly) you can use the following in the OnDblClick event for the ID field:

DoCmd.OpenForm "frmSecondForm", acNormal, , "[SFCRefNo] = " & Me.ID

Tim Gill
Gill Consulting

http://www.dagills.com

 

[BigKahuna]

Tim,

Thanks a lot for taking the time to answer my question. I made the suggested changes to the Double Click event of the first form. But when I double click the TextBox which contains the ID, a small window pops up with this in the title bar "Enter Parameter Value" and it has a text box for entering the ID with a caption which is none other than the "ID" in the form. After I enter the ID in this pop up box the second form opens up with the appropriate info.
I was wondering if there is any way to not enter the ID again since it already exists in the form.
Please advise!
Here is the code for the Double Click event:
--------------------------------------------
Private Sub Text105_DblClick(Cancel As Integer)
Dim stDocName As String

stDocName = "SFCinfo"

DoCmd.OpenForm stDocName, acNormal, , "[SFCRefNum] = " & Me.ID

End Sub



And just to let you know, I dont have anything in the criteria field of the query.

Kash

 

[timgill]

Do you have any parameters set in the query? I understand you have nothing for the criteria, but if you have defined parameters for it, it will ask for them.

Tim Gill
Gill Consulting

http://www.dagills.com

 

[BigKahuna]

I dont understand what you mean by parameters. The query is based on 2 linked tables which are relational. Apart from that they dont have anything in the Criteria or the Sort fields.

Thanks,
Kash

 

[timgill]

Wait, I see it now. Your ID field (on the form) is not called ID, it is called "Text105" according to your code. Change the line to be:

DoCmd.OpenForm stDocName, acNormal, , "[SFCRefNum] = " & Me.Text105

That should do it.

Tim Gill
Gill Consulting

http://www.dagills.com

 

[BigKahuna]

I made the change you suggested but its still doing the same thing. The command line now reads:

DoCmd.OpenForm stDocName, acNormal, , "[SFCRefNum] = " & Me.Text105

I designed the 2nd form using the Form Design Wizard which does nothing but shows the information. When I tried to view the code there was nothing in it. Do you think that might be the problem?

Thanks for your help, Tim!

kash

 

[timgill]

Any chance you could send me the database? What you have should work, so I must be missing something. If you send it to me, I'll take a look and try to help. Tim Gill
Gill Consulting

http://www.dagills.com

 

[BigKahuna]

I tried to send the database to tim@dagills.com but it bounced back. Can you give me your alternate email address Tim.
Thanks a lot!
Kash

 

[timgill]

tim@dagills.com should have worked.

Try tim@amstat.org, that is my work address.

Tim Gill
Gill Consulting

http://www.dagills.com

 

[gwinn7]

Just a thought. Have you tried: Me![text105] instead of Me.[text105] ?

My vague memory seems to recall that this fixed a similar issue. I know that suggestion seems kinda silly.

Gary
gwinn7