Would like parameter query dialog box to be combo!

[Nvijayk]

Hi
I have a report based on a query in which one of the fields has a criteria ( selecting a name among 9 names) for a group by.
I do the [which name] now on the criteria box in the query and it works. I do get the pop up when I ask for the report.The problem is the user has to get the name exactly.
I would like this pop up to be a Combo box so the user can select. I tried the unbound form but can't figure out how to do this for a combo box.Can anyone please help. I need rather detailed instructions given my grasp limitations.

Many thanks

nvk

 

[JimHorton]

nvk,
No quick & dirty way, but a slow & dirty way is to generate the sql in a module, using a variable for the parm, and put up a form in Dialog mode (with a combobox of course) to get the variable's value.
--Jim

 

[Nvijayk]

Hi Jim
I generally understand the process suggested but I am not competent to execute it. Could you please help with the actual dtails of what I should do?

Sorry for the ignorance manifested.

Regards

Vijay

 

[JimHorton]

Ok, lets say you're calling this query from a form, say, a button click.

In this form (we'll call it form1) you have a public variable (dimm'd just below the Opiton statement in the Form's Module (if you have the form module open to work in the button click event, just select "General" in the upper left dropdown of the form's module window):

Option Explicit
Public m_strName as string 'this variable is accessible to other forms & modules

Now you also have form2, with a combobox. It needs nothing else but the combo box with you're list of whatever the parameter choices will be. It helps to design the form with no navigation buttons, no close box, you can make it look identical to that little parameter box that comes up for querys.

In form2, in the After_Update of the combobox, have the code:
forms!form1.m_strName = me!combobox1 'assign to form1's public variable
docmd.close 'close form to and resume with form1

Then in the button click of the first form:

private sub somebutton_click()
dim strSq as string
m_strName = "" 'init the var first--since it's public and you don't know where it's been or who's written to it, but this is something you'll need to keep track of

'Now call the Form2, ie the 'parameter box'
docmd.openform "form2",,,,,acDialog 'dialog cause code to stop dead here until form2 closes

'Now, we aren't at this line of code unitl above form closes, so check if value was entered
If m_strName <> &quot;&quot; then
'prepare the sql and then run it
strSq = &quot;Select * from SomeTable where someName = &quot;&quot;&quot; & m_strName & &quot;&quot;&quot;&quot;
docmd.runsql strSQ
else
msgbox &quot;No paramter entered&quot;
end if

end sub

That's just the basics, you can obviously get more elaborate with this method. I hope that helps,
--Jim

 

[Nvijayk]

Hi Jim
Many thanks. I am trying it and hopefully it should be okay. I'll let you know how I get on in the near future.

regards

Vijay

 

[Nvijayk]

Hi Jim

Have tried but am running into a compile error message when I click on the button in Form1.
This is the code I have in the code for the on click for the button in form1.

Option Compare Database

Option Explicit
Public m_strName As String

Private Sub Command0_Click()
Dim strSq As String
m_strName = &quot;&quot;
DoCmd.OpenForm &quot;form2&quot;, , , , acDialog
If m_strName <> &quot;&quot; Then
strSq = &quot;SELECT qryX.TNo, qryX.Date, qryX.X, qryX.NetX, qryX.M&quot;
FROM qryX
WHERE ((qryX.X) = &quot;&quot;&quot; & m_strName & &quot;&quot;&quot;&quot&quot;
ORDER BY qryX.Date;
DoCmd.RunSQL strSq
Else
MsgBox &quot;No selection made&quot;
End If

End Sub

Could you please see where is the problem. The message says 'vaiable not defined' and highlights the 'qryX' in the FROM part of the SQL statement.

Thanks

Vijay

 

[JimHorton]

vijay,
You need to put the entire sql on one line (or use the line continuation &quot;_&quot. Tek-tips may have formatted my post with a break in the &quot;strSq =&quot; Line, so I'll format it here with the continuation that should work:

strsq = &quot;SELECT qryX.TNo, qryX.Date, &quot; & _
&quot; qryX.X, qryX.NetX, qryX.M &quot; & _
&quot; FROM qryX&quot; & _
&quot; WHERE ((qryX.X) = &quot;&quot;&quot; & m_strName & &quot;&quot;&quot &quot; & _
&quot; ORDER BY qryX.Date;&quot;
DoCmd.RunSQL strsq

Note that when you concatentate sql strings or use a line continuation, it's a good habit to always precede the next line with a space, since it may be hard to when the previous line ends at a character.
--Jim

--Jim

 

[jsolutions]

Not to steer you in a totally different direction, but there is a simpler way to manage this. You said your report is based on a parameter query with the criteria of [Which Name]. All you need to do is create a form. I will call it frmNames. On the form create a combo box containing the names that you want to choose from. I will call the combo box cmbChoice. Create a command button to open the report (the command button wizard will do nicely here.)

Now all you need to do is in the query that the report is based on, change the criteria from [Enter Name] to forms!frmNames!cmbChoice. Click the command button that you created. This will open the report which in turn opens the query which in turn looks for the data in the combo box.

Just a note - the notation forms!frmNames!cmbChoice tells Access that you are reading data from a field called cmbChoice. that is on an object called frmNames contained in the collection of forms. Don't really need to understand that to make it work, but it is nice to know.

Second Note - the solution above assumes that there is data in the combo box. If you do not have a default value for the combo box, you will need to put in some error trapping to catch the null value - otherwise your report will run with no name being selected.

Let me know if you have any problems. Hope this helps.

 

[JimHorton]

js,
Your solution is the standard way of doing it. What I was giving was a way of replicating the behaviour he'd asked for, which can be used, say if the report is open on it's own from the database window., which would otherwise pop up the parameter box:
[forms!frmNames!cmbChoice]
with no combobox.

The code above that was in form1 would be in the report's Open event, and the resulting sql would be assinged to the report.recordsource, with the obvious changes in public variable references. It can be made more universal by passing the report object name in the OpenArgs in form2, which can then determine which object called form2, to determine which variable to assign the output of the combobox. Alternatively, a global can be used, but I try to avoid that.

As I had said, you can get very elaborate with it all f you want...
--Jim

 

[jsolutions]

jim- no worries - was just trying to throw out an alternate solution. I started from the point that you had mentioned in your post of having a form open and clicking a button to run the report/query. Wasn't really thinking about running it from the database window - I usually steer clear of having users do anything from there.

You are 100% correct, you can get as elaborate as you want in Access.

--Jay

 

[Nvijayk]

Hi Jim & Jay
I am obviously dealing with two geniuses.
Unfortunately I am having difficulty in both the suggestions. I did warn you I was a bit thick.

Jim's solution
It works to the point of letting me select in the combo box and if no selection is made the message box works okay. But if I made a selection &quot;Purchases&quot; then I get a Run-time error '3075'
&quot;Syntax error (missing operator) in query expression
'qryX.MFROMqryXWHERE((qryX.X)= &quot;Purchases&quot'
This happens whatever selection I make and obviously the error appears with that selection substituted.

Jay's solution
It works to the point of letting me select in the combo box but the report does not display any data except the field headings.No error messages appear.The same happens if I do not make selection.

Please advise.I can't thank you both enough.

Regards

Vijay

 

[JimHorton]

vijay,
You have no space between &quot;qryx.m&quot; and &quot;From&quot;. That's why its important to use leading spaces when combining sql strings--you never have to scroll to the right to see if you left a trailing space in the previous line. I make it a rule to start the next line of sql with a space when I'm writing it in code like that.
--Jim

 

[Nvijayk]

Hi Jim
While it appears like there is no space between them in the error message, in the actual code there was space. This is exactly how the code is in the application.
Private Sub Command0_Click()
Dim strSq As String
m_strName = &quot;&quot;
DoCmd.OpenForm &quot;form2&quot;, , , , , acDialog
If m_strName <> &quot;&quot; Then
strSq = &quot;SELECT qryX.TNo, qryX.Date, qryX.X, qryX.NetX, qryX.M&quot; & _
&quot;FROM qryX&quot; & _
&quot;WHERE ((qryX.X) = &quot;&quot;&quot; & m_strName & &quot;&quot;&quot&quot; & _
&quot;ORDER BY qryX.Date;&quot;
DoCmd.RunSQL strSq
Else
MsgBox &quot;No selection made&quot;
End If
End Sub

I feel horrible to keep coming back but having reached thus far, I want to crack it.

regards

Vijay

 

[JimHorton]

vijay,
Look at the end of the first line of the SELECT statement--
qryX.m&quot; & _
There's no space after the qryX.M, then there's the line continuation, (which is not a space), then it continues:
&quot;From...&quot;
with no leading space in the &quot;From..&quot; string. I think if you put a space before the F in from, before the W in WHERE, and before the O in Order by, you should be ok (assuming the rest of the query's fieldnames, etc are valid)
--Jim

 

[Nvijayk]

Hi Jim
I did what you advised and that has sorted the error. A new one has popped out which says
Run-time error 2342
A RunSQL action requires an argument consisting of an SQL Statement.
What's this one Jim?

In the meanwhile, there is good news in that Jay's solution has been sorted and is working. I had made a silly 'case' mistake.I would still like to get this going and I have already learnt a lot from your solution.

Msg for Jay
Many thanks and delighted with your advise.

Regards

Vijay

 

[jsolutions]

Vijay - glad it worked out for you. Sorry to be so late jumping back in this thread today, but had a nightmare of a report to build.

Let me know if you run into any more snags. I have marked this thread for email notification, so I will see it if you add to the thread.

Jay

 

[JimHorton]

Vijay,
What you need to do there is put a breakpoint right on the &quot;Docmd.Runsql&quot; line. Just select that line and hit F9, this will highlight it it red. Then run everything, and when the code stops on that line, press Control-G, which brings up the debug window. In the bottom half of the window, type in:
?strSq
...then hit Enter. The sql should display. If nothing displays, then, assuming the above code hasn't changed (except for putting leading spaces in) I think it's time for an excorcist. The way that code is--with the spaces added--should result in a valid string that should display in the debug window.

So if it displays, then what you can do is copy the line and open a new query in design view, don't add any tables, and in the View menu, choose SQL. Paste the code line from the debug window into the blank area (It might already say 'Select', but paste over that), and then try to go back to 'normal design view'. At this point you'll either get a syntax error for the query or it will display normally, where you could then run it and see what results.
--Jim