Avatar of asaidi
asaidi asked on

scroll down list in query in access

Hi
please find attached txt file that i wrote as query in access 2003,and in this query i declared parameters to let the user choose..my question can i use a list for names that the user choose from my table of names instead of writing the names
sometimes he cannot spell well the name and the query returns zero..
query.txt
SQL

Avatar of undefined
Last Comment
asaidi

8/22/2022 - Mon
koutny

I am afraid that is not possible. You would have to create a form and place a list box/combo box on the form and then use this list box/combo box in the query.

so basically you would display the form to the user first, with a button which would say something like 'Show records'.  The user would select the name (and possibly enter the dates as well in the text boxes you would provide), then click on the button and your code behind the button would open the query.
ASKER
asaidi

i did this but i click on the button the same things happend that the query ask me to put the two dates..that i have already put in the form..
secondly in my form i inserted a list that displays the name but in the properties i can see list05 in name how i can take a variable name to let my query search with the name
ASKER CERTIFIED SOLUTION
koutny

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
asaidi

Hi koutny
i did this and thanks but still the query asks me for inputing dates and i have already done them in my form
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
koutny

hi,

have you got it working now? I suggest you double check that the names of the form and the textboxes on the form are the same as used in the query.
ASKER
asaidi

yes i did that and it is working ..my problem now i cannot link between form then query then report..
normally when i want to print a report with name and dates i must run the form then the data result from the query must be saved then i have my report..
it is in the easy way: when i click on print the report on the menu i must have the form then when i click on the button i have my preview result in the report
koutny

Two possible ways

1. Base your report on the query (which references the controls on the form).
You open the the form first, not the report. When you click on a button on the form the code behind that button opens the report (which runs the query automatically):

private sub cmdShow_click()
    On Error Resume Next
    Me.Visible = False
    DoCmd.OpenReport "report_name", acViewPreview
end sub

You could add the following code in the on close event of the report to close the form with the parameters (if you didn't want it to retain the values when printing the report second time):

Private Sub Report_Close()
    On Error Resume Next
    DoCmd.Close acForm, "form_name", acSaveNo
End Sub

2. Second possiblity would be to open the report first. The report would be based on the query without any parameters.
In the on open event handler for the report you would display your form, in dialogue mode. Once the form would be 'closed' (me.visible = false) by clicking a button on the form you would set a filter for the report:

Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String
    On Error Resume Next
    DoCmd.OpenForm "form_name", , , , , acDialog
   strFilter = ""
    strFilter = "cpd.date Is Not Null And cpd.date Between #" & Format(Forms![frmSearchParams]![BeginningDate], "Long date") & "# And #" & Format(Forms![frmSearchParams]![EndingDate], "Long date") & "# And names.name = '" & Forms![frmSearchParams]![list05] & "'"
    DoCmd.Close acForm, "form_name", acSaveNo
   
    Me.Filter = strFilter
    Me.FilterOn = True
End Sub


The code behind the button in the form would then be just

private sub cmdShow_click()
    Me.Visible = False
end sub
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
asaidi

Thank you very much for your help