Parameter Query in a form

[rhett7660]

Hi all..

For some reason I cannot get this to work and it is driving me crazy. Here is what I have. A combo box on a form (frmTest) that is drawing from a table, tblInvestigator. This part works.

I then have a query that has the following line of code in it to draw from the form.

[Forms]![frmtest]![cboInv]

The query works fine when I run the query a pop up box asks me to enter something into the box and the parameter is queing me from [Forms]![frmtest]![cboInv]. When I type in a name I get the desired results. So I know this part is working.

When I try to run a test using the query and the form it doesn't work. I use the drop down list cboVieInv to select a person. Then I go back to the query and run it.. I don't get any results.


I have attached a sample of what I am talking about.

Thanks
R~

 

[boblarson]

You have the parameter set on InvLName, but your combo box is not based on a query (which it should be instead of the table) and your first column (the InvestigatorID) is the bound column and NOT InvLName. Either change the column order using a query, or set the bound column to 2.

 

[rhett7660]

Bob...

But if I base the cbo off the query won't that give me say 4 versions of test, 4 versions of testing etc, instead of just one of each?

If I base the cbo off a table that is just used as a look up I will only get the investigators displayed once.

I have changed the bound to 2 and used the following code in the command button cmd (cmdViewInv)

Private Sub cmdViewInv_Click()
On Error GoTo Err_cmdViewInv_Click

Dim stDocName As String

stDocName = "rptCurrentInv"
DoCmd.OpenReport "rptCurrentInv", acPreview, , "[InvLName]=" & Me.cboInvLName

Exit_cmdViewInv_Click:
Me.cboInvLName = Null
Exit Sub

Err_cmdViewInv_Click:
'MsgBox Err.Description
Resume Exit_cmdViewInv_Click
End Sub

I have used this before and it has worked fine, but for some reason I cannot get this to work with this database. Just using the cbo to do a parameter query that generates the report.

R~

 

[boblarson]

Actually, all you have to do is to use the Grouping within the query if you want just a single instance of any particular name. Actually, using the table as the rowsource could give you an instance for everything in there so you would end up with multiples that way, if you don't use a query to narrow things down.

 

[rhett7660]

Bob...

Hmm.. That is interesting re: having the look up table. I didn't think I could get more then one instance if it isn't in the table.

Now how can I get this to work, what am I doing wrong? I have used this exact same setup (changing the names of course) in three other databases but for some reason it is not working in this one?

R~

 

[boblarson]

Can you explain further what your desired final output is supposed to be. I'm not sure I understand exactly what you want.

 

[rhett7660]

Sure can...

Here is what I what like to do, and I have done it in the past, but....

Have a drop down list of the investigators. So the end user can pick which investigator they want to pick. Once the have selected the investigator they hit the command button to open a report that shows the current list of applicants that are assisgned to that investigator. I have the query setup, with a the code to look at the form cbobox. When I run the query by itself I get the prompt, enter in a name and it shows me what I want to see.

When I run the rpt I am prompted and I put in the name of the investigator and it shows me what I want to see.

When I am on the print form, I select a name from the drop down list and then hit the command button nothing happens. What I would like to see happen is the report is generated.

R~

 

[boblarson]

Check out the form and combo box. When you select an investigator the report opens to their info.

 

[rhett7660]

Good lord.. that was quick and yes that is it!

What was I doing wrong? I looked at the code and it looks pretty much the same as what I had with the expection of the report info. I just had it based on a the query as a whole..

If I want it to have a command button can I just copy over your code to the command button or is there some other stuff I need to put in?

R~

 

[boblarson]

I modified the query to pull by investigator ID (criteria being the combo box)and then saved the query. I then created the report based on the query and then when the report is opened it will be based off of the query and the criteria in the combo box.

yes, you should be able to move the code from the combo's after update event to a command button.

 

[rhett7660]

Bob..

Thank you very much and yup the code worked fine and man is it fast too. The running of the query etc..

Thanks again
R~

 

[rhett7660]

Bob..

I looked at your code and applied it to do the following:

SELECT tblInvestigator.InvestigatorID, [InvLName] & ", " & [InvFName] AS InvestigatorName FROM tblInvestigator;

to:

SELECT tblApplicant.FOSLName, [FOSLName]AS FOSName FROM tblApplicant

But, instead of just giving me what is in the list, it is giving me every name and space even if there are say two "test1" it is looking like this:

test1
test1

test2




test3

Etc..

Is there a way to only show me say something like this:

test1
test2
test3

I am using the same query you designed I just copied and renamed it. Here is the query squl:

SELECT tblApplicant.ApplicantID, tblApplicant.InvestigatorID AS tblApplicant_InvestigatorID, tblApplicant.AppLName, tblApplicant.AppFName, tblApplicant.AppMInitial, tblApplicant.SocNumber, tblApplicant.Status, tblApplicant.Active, tblApplicant.ReasonForInactive, tblApplicant.EndResult, tblApplicant.EndResultMemo, tblApplicant.FOSLName, tblInvestigator.InvestigatorID AS tblInvestigator_InvestigatorID, tblInvestigator.InvLName, tblInvestigator.InvFName, tblInvestigator.EmpNumber, tblInvestigator.Rank, [AppLName] & ", " & [AppFName] & " " & [AppMInitial] AS AppName, [InvLName] & ", " & [InvFName] AS InvName
FROM tblInvestigator INNER JOIN tblApplicant ON tblInvestigator.InvestigatorID = tblApplicant.InvestigatorID
WHERE (((tblApplicant.Active)=Yes) AND ((tblApplicant.FOSLName)=[Forms]![frmReports]![cbofos]));

Thanks
R~

 

[boblarson]

Use GroupBy if you are typing in the query manually. If using the QBE, you can select the Totals button to get the GroupBy.

 

[boblarson]

Oh, and the reason I put in the LastName, FirstName is because, What IF you have two people with the same last name? You will still get both last names showing up, because their ID will be different, but you won't be able to tell who is who.

 

[rhett7660]

Bob..

That is what I figured re:names.. I am trying the other now.

Thanks again
R~

 

[rhett7660]

Bob..

Did the group by but it still showed the spaces and listed the names multipal times. (I made sure to use unique names for testing purposes).

R~

 

[boblarson]

To get one last name use:
SELECT tblInvestigator.InvestigatorID, tblInvestigator.InvLName
FROM tblInvestigator
GROUP BY tblInvestigator.InvestigatorID, tblInvestigator.InvLName;

 

[rhett7660]

Bob..

I was so close.. thank you again!

R~

 

[rhett7660]

Hi again..

Last question here is the code I am using:

SELECT tblApplicant.FOSLName, [FOSLName] & " - " & [AppLNAme] AS FOSLName FROM tblApplicant GROUP BY tblApplicant.FOSLName;

I wanted to combine to fields from within the same table, same as the above code but it won't let me. What am I doing wrong? I keep getting errors.

R~

 

[boblarson]

Group by is done first, before the Alias is assigned, so you need to use the actual field name in the group by. That's also probably why my earlier code didn't work

You can nest a query to get a group by for the alias, though. I haven't done actual nested SQL but if you create a base query with the combined field you have and then do another query that is based on that query and then grouped by your alias it will work for you.