Passing Form Values to Report

[Johnny Drama]

Hello all,

I've got an interesting situation that I can't figure out for the life of me. I have a form where users can select a control number from a combo box. They then click a command button which passes the control number they selected (and the associated control data) to a form. It was working fine until I added a criteria for the CategoryID in the query the form pulls data from. Now when I click the command button it pops up a box asking for the CategoryID value and then a second box asking for the Control Number value.

Thoughts? Command button code below:

Private Sub btn_viewleadsheet_Click()
On Error GoTo Err_btn_viewleadsheet_Click

DoCmd.RunCommand acCmdSaveRecord

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptPhaseOneLeadSheet"
stLinkCriteria = "[Category] & "'" & [Control Number] = " & " '" & Me![Test Control] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_btn_viewleadsheet_Click:
Exit Sub

Err_btn_viewleadsheet_Click:
MsgBox Err.Description
Resume Exit_btn_viewleadsheet_Click

End Sub


Thanks in advance

 

[boblarson]

Bad syntax. You are missing an AND.

stLinkCriteria = "[CategoryID]='" & Me![Category] & "' AND [Control Number] ='" & Me![Test Control] & "'"

 

[boblarson]

and part that should have been inside the double quotes was not.

 

[Johnny Drama]

Thanks, I'll give it a shot.

 

[Johnny Drama]

No dice, still get a request for a parameter for Category

 

[boblarson]

Well, it would be because of this:

Me![Category]

are you sure CATEGORY is a field in the form's record source?

If you are trying for a CONTROL on the form named category then you would really want the dot:

Me.Category

 

[Johnny Drama]

Here's the REALLY odd thing now...yes, I was trying for a control on the form named CATEGORY, but before I got your last message I removed the CATEGORY control from the form, the query, and the reference in the command button event, and it still asks me for a parameter for the Category!?!?!?!? Bizzaro.

 

[boblarson]

Not bizzare - something else you have is referring to it. The trick is finding it.

 

[Johnny Drama]

Nope, nothing. Searched through all the code for the entire form, searched all of the controls on the form, all the fields on the form, and so on and so on. Guess I'm going to have to rethink this and start from scratch.

 

[boblarson]

Try importing everything into a brand new database shell. Sometimes that will clear those phantom things up. If that doesn't work perhaps we can take a look at the database to see what we can find.

 

[Johnny Drama]

Good suggestion. I'll give it a shot after I let the smoke in my head clear out a bit...