Popup box asking for parameter when using sql query in access forms

Question

I have a form with 3 things for my users to fill in:

a textbox called ProjectName
two ActiveX control objects (Microsoft Time and Date picker Control 6.0) called ProjStartDate and ProjEndDate respectively

After my users have entered either of the above, they can click on a button that will call requery on a list box object in the form which will display the filtered results.

I have set the Row Source Type of the listbox to be "Table/Query" and its Row Source to be the below SQL query:

SELECT p.Title
FROM Project AS p
WHERE
  p.Title = [Forms]![Search by project]![ProjectName] 
  OR (p.StartDate <= [Forms]![Search by project]![ProjStartDate]
      AND p.EndDate >= [Forms]![Search by project]![ProjEndDate])
ORDER BY
  p.ProjectId DESC;

However, every time I enter the form, it will prompt me with a pop up box asking me for a parameter called Forms!Search by project!ProjectName , Forms!Search by project!ProjStartDate and Forms!Search by project!ProjEndDate . If I enter the parameter for, lets say project name correctly, the list box will display the correct filter results.

Furthermore, even if I enter the correct keyword in the ProjectName textbox and requery, the parameter pop up box will still occur. Do anyone knows how to solve this issue? Thanks a lot in advance!

Something is misnamed or misspelled. If you are using Access 2010, you can use intellisense in the query design window, if not, check the names on the form. In particular, check [Search by project], it seems to be the likely culprit. Presumably you have the form open when you run this? — Fionnuala
– Fionnuala, Commented Jan 4, 2013 at 9:56
It seems that if I test the query in form view,the query works without any parameter pop up box. However if I test it by running it when I start the form, it gives me the pop up box again. Might it have to do with me using a main page with tabs that includes the form with the problem? — Laughy
– Laughy, Commented Jan 5, 2013 at 14:22

HansUp · Accepted Answer · 2013-01-05 16:18:23Z

2

I first wrote this answer based on the assumption that all the controls were contained in a single form. However, if the list box is on a child form, the parent form's controls (such as [Forms]![Search by project]![ProjectName]) are not yet available when the child form first opens. If that is the case, you could leave the list box row source property empty, and wait until the parent form's Form Load event to assign your SELECT statement to the list box row source. Or leave the list box row source empty until the user clicks the command button.

The rest of this answer was about diagnosing a naming problem. Since you indicated in a comment that the query can work when run at certain times, I don't think naming is involved after all, but will leave it in case it may help someone else.

Open the Immediate window (Ctrl+g) and investigate why the db engine can't find those parameters.

With your "Search by project" form open in Form View, check its .Name property simply to verify Access sees the name you expect. If both you and Access agree on the form's name, you will get this ...

? Forms![Search by project].Name
Search by project

OTOH, if Access thinks the form has a different name, that attempt will trigger an error. If that happens, check the form name as Access sees it. This will give you the names of all open forms:

for each f in Forms : ? f.Name : next

Finally, examine the values of those form controls. Assuming "Search by project" is indeed the correct form name ...

? Forms![Search by project]!ProjectName
? Forms![Search by project]!ProjStartDate
? Forms![Search by project]!ProjEndDate

edited Jan 5, 2013 at 16:18

answered Jan 4, 2013 at 18:18

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laughy Over a year ago

Hi, where do i run these commands? I am sorry, I am new to access. Anyways I have found out that my form is indeed named [Search by project]...

HansUp Over a year ago

Sorry I was going in the wrong direction. See update. As a side point, I think you should learn to use the Immediate window because it's useful for trouble-shooting and it's where you view the output from Debug.Print statements. You can use Ctrl+g to open the Immediate window, then type ? Forms![Search by project]!ProjectName and press the Enter key.

Laughy Over a year ago

Ahh this helps immensely. Thanks!

Collectives™ on Stack Overflow

Popup box asking for parameter when using sql query in access forms

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related