Move Record in form based on Query-by-form result 1

[hceonetman]

I've looked at several threads and can't find the exact code to finish this. How do I move to a record on a form based on a value in a second form? The forms are not related, e.g. form/subform. There is a key field on the main form which also exists in the second form, but the records in the second form are only a subset of those in the main form. I seem to run into problems working between DAO and ADO.

Thanks,
HCEONETMAN

 

[traingamer]

You can reference the control on the other form using Forms!FormName.ControlName then use .FindFirst or whatever method to find the record on the current form.

Are you having trouble referencing the control or moving to the record?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[hceonetman]

Traingamer,
The problem is in moving to the correct record on the form. A query-by-form calls another form showing the hits. The user should scroll to the desired record and click the Exit button. Following is the code from the exit button which is supposed to return you to the original form with the selected record displayed:

Private Sub Btn_Select_and_Exit_Click()
Dim str_DESIGNATN As String
str_DESIGNATN = Forms.Frm_Show_Criteria_Results.DESIGNATN
Dim rst As Dao.Recordset
Set rst = Forms.Frm_Benchmarks.RecordsetClone
rst.FindFirst "[DESIGNATN] = " & str_DESIGNATN
Forms.Frm_Benchmarks.Bookmark = rst.Bookmark
DoCmd.Close
End Sub


Above I tried to create a recordset identical to the one in the main form, move to the correct record there, and then use the bookmark function to move to the same record on the screen form. I'm getting a "Data type mismatch in criteria expression" error. If I can move to the record on the form based on the variable str_DESIGNATN, I won't need the recordsetclone or bookmark functions.
I hope this makes it clearer.
Thanks,
HCEONETMAN

 

[PHV]

rst.FindFirst "[DESIGNATN] = '" & str_DESIGNATN & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Is there any value in str_DESIGNATN when you hit the command button?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[hceonetman]

Greg,
Based on debug.print statements placed in the code, the value in str_DESIGNATN matches the item I've chosen on the screen. The statement "rst.FindFirst "[DESIGNATN] = " & str_DESIGNATN" is the first one that fails to execute.

HCEONETMAN

 

[PHV]

hceonetman, have you read my previous post ?
[tt]rst.FindFirst "[DESIGNATN] = '" & str_DESIGNATN & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[hceonetman]

PHV,
I didn't see the earlier post, sorry. It worked like a charm. Syntax can be a wonderful thing when done right. (Unlike a 'sin tax'). A star to you, and thanks to you and Traingamer.

HCEONETMAN