I have noticed some posts very similar to my question, apologies if I am duplicating but I couldn't see quite the same issue I am having.
I have a subform in continuous forms view with a list of runners each with an autonumber PK RunnerRef
The text box control (txtRunner) with this pk as the control source has a double click event that opens a pop up form enabling the user to amend details for this particular runner. The query uses the parameter forms!frmRunnersAndClubs!txtRunnerRef as the criteria for the pop up form which displays the one runner selected to amend their details. Some simple vba code is used to put the form into data entry = false so records can be edited rather than only new records added. I use the same form and a button to add new runners but the vba is used to define the form as data entry = true to only add new records.
This has always worked fine for me in the past and it works fine in this application with the same set up for venues and events.
When I run the query and remove the criteria it displays all records as I would expect but as soon as I use the forms!frmRunnersAndClubs!txtRunner criteria it produces one new record not the record I have selected. I have checked the name of the forms and the text box over and over again but no matter what I do it won’t use the txtRunner as the criteria. If I run enter the runner ref parameter manually it works.
Thank you
SELECT tblRunners.RunnerRef, tblClubs.ClubName, tblRunners.ClubRef, tblRunners.Name1, tblRunners.Name2, tblRunners.Sex, tblRunners.AgeCat FROM tblClubs INNER JOIN tblRunners ON tblClubs.ClubRef=tblRunners.ClubRefWHERE(((tblRunners.RunnerRef)=[forms]![frmRunnersAndClubs]![txtRunner]));
Private Sub txtRunner_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmRunners"
Forms!frmRunners.DataEntry = False
Forms!frmRunners.txtBanner.SetFocus
Forms!frmRunners.txtBanner.Text = "Amend Runner Details"
End Sub
I have a subform in continuous forms view with a list of runners each with an autonumber PK RunnerRef
The text box control (txtRunner) with this pk as the control source has a double click event that opens a pop up form enabling the user to amend details for this particular runner. The query uses the parameter forms!frmRunnersAndClubs!txtRunnerRef as the criteria for the pop up form which displays the one runner selected to amend their details. Some simple vba code is used to put the form into data entry = false so records can be edited rather than only new records added. I use the same form and a button to add new runners but the vba is used to define the form as data entry = true to only add new records.
This has always worked fine for me in the past and it works fine in this application with the same set up for venues and events.
When I run the query and remove the criteria it displays all records as I would expect but as soon as I use the forms!frmRunnersAndClubs!txtRunner criteria it produces one new record not the record I have selected. I have checked the name of the forms and the text box over and over again but no matter what I do it won’t use the txtRunner as the criteria. If I run enter the runner ref parameter manually it works.
Thank you
SELECT tblRunners.RunnerRef, tblClubs.ClubName, tblRunners.ClubRef, tblRunners.Name1, tblRunners.Name2, tblRunners.Sex, tblRunners.AgeCat FROM tblClubs INNER JOIN tblRunners ON tblClubs.ClubRef=tblRunners.ClubRefWHERE(((tblRunners.RunnerRef)=[forms]![frmRunnersAndClubs]![txtRunner]));
Private Sub txtRunner_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmRunners"
Forms!frmRunners.DataEntry = False
Forms!frmRunners.txtBanner.SetFocus
Forms!frmRunners.txtBanner.Text = "Amend Runner Details"
End Sub