Display one form record using query with contanenated prompt

[nastar1]

I'm trying to open a form to a specific record using an underlying query that prompts the user to input the record of choice. The prompt relies on a contanenated series of text fields. What I am running into is that the user is prompted first for what appears to be the first two concantenated fields followed by the actual parameter in the query.

The Click event on the switchboard form is:

DoCmd.OpenForm "frmWA", acNormal, "qrySearchWA", , acFormEdit, acWindowNormal

DoCmd.GoToRecord acDataForm, "frmWA", acFirst

The query "qrySearchWA" has a parameter:
WA#: [tblCLIN].[CLIN] & "-" & [tblCONTRACTS].[Name] & "-" & [FY] & "-" & Format([Seq],"000") & "-" & [Rev]

If I run the query by itself, I get the single prompt for the entire WA#.

If I run click the control on the switchboard form, I get first a prompt for "tblCLIN.Clin", followed by "tblCONTRACTS.Name", followed by the prompt for the WA# as seen in the query.

The qrySearchWA also has another concantenated field,
Contract#: [tblCONTRACTS].[Name] & " " & [tblCONTRACTS].[Number] & " " & [tblDO].[Number]

So I dont know if the Click event is bringing up the first parameter in each of the concantenated fields of the query or the first two parameters of the first concantenated field. At any rate, I'm hoping to get it so that only the prompt for the WA# is required by the user.

 

[lameid]

Try running the query that is the rowsource for form frmWA.

Otherwise please post the SQL for both queries.


My hunch is that you want a combobox that lists the concatenated possibilities and select from that. Then you can hide columns in that and use its afterupdate event to populate hidden textboxes. Finally modify the query to use the text boxes on the form as criteria on the individual fields instead of the textbox.