Solved Specific Record from Continuous Forms To Pop up Single Form To Amend That Specific Record (1 Viewer)

[Gimlett]

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

 

[jdraw]

Is there a space before where in your code?

 

[June7]

Use arguments of OpenForm method to open form filtered to a specific record and allow Edit mode.

DoCmd.OpenForm "frmRunners", , , "RunnerRef=" & Me.txtRunner, acFormEdit

Is txtBanner a textbox bound to a field?

 

[Gimlett]

Sorry jdraw yes, I hurridely pasted and copied, from the access sql view

 

[Gimlett]

Thanks June7, let me try that and txtBanner isn't bound I just use it empty as a title depending on amend or add records

 

[June7]

I prefer to have a form control its own setup. So can even pass the banner info with OpenForm using OpenArgs argument.

DoCmd.OpenForm "frmRunners", , , "RunnerRef=" & Me.txtRunner, acFormEdit, , "Amend Runner Details"

Expression in txtBanner ControlSource: =Nz([OpenArgs], "Add New Runner")

 

[Gimlett]

Thank you so much June7, you've fixed it. I'd only referenced the main form and forgotten to reference the subform

It was forms!frmRunnersAndClubs!txtRunner
It should be forms!frmRunnersAndClubs!frmRunnersAndClubsSubform!txtRunner

All works fine now and thanks for the heads up on the better way of doing it, I will experiment with that.

An example of simple muppetry on my part I'm afraid

 

[June7]

Even with your original code, don't set Focus on txtBanner, use Value property instead of Text. This control should be Locked Yes and TabStop No. Or use a label control and set its Caption. If you use a textbox then make use of textbox capabilities as shown above.

I don't use dynamic parameterized queries. Prefer VBA to construct filter criteria.