I am trying to create a search results form that has text boxes that are populated by data from multiple queries. I do not want to use subforms, so form/control binding is (I believe) an impossibility. Instead, I'm trying to directly access the relevant query row field and increment the row field using
DoCmd.GoToRecord acDataQuery, "My query name", acNext, 1
whenever a button is pressed. However, this method seems to only let me access the first record in the query, as setting the text box value to
"My Query name".Fields("Desired Field")
only gives me the first record's result. My next step was trying to use Recordset navigation using this code
Private Sub Command2_Click()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Base_FH_Qry")
'DoCmd.GoToRecord acDataQuery, "Base_FH_Qry", acNext, 1
[Forms]![Search_Results_Flat_Code_Frm]![CBT_FH].Value = rs.Fields("comm_amt_ati")
rs.MoveNext
End Sub
This causes me to re-open the Recordset every time the button is clicked, so it doesn't help me either.
My specific question is, which, if any, of these methods (Multiple bound queries to form, directly access query fields, or Recordset navigation) are viable and what do I need to change in my approach to get them to work? Thank you for your time.
