Opening report to specific record

[RyLane]

Hi,

I can open a report right with a wherecondition that opens a report based on an agent name..

DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "'"

But it returns every record for that agent and I want to be able to specify the date that goes with the name.

For example Tom has a record for Feb 10, Jan 10, and Dec 13. I only want to see the record for Dec 13.

I am able to see this in my form by having a combo box for the agent and the date (the date box being based on the agent box). So now how can I add a condition to include the date combo box?

DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "' And [PS_dDate]='" & Me.Cbodt & "'"

Adding the condition gives me and type mismatch error, which I think may come down to the date combo box on the form having 3 columns (only 1 is visible). Any suggestions?

Thanks

 

[bob fitz]

Try:
DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "' And [PS_dDate]=#" & Me.Cbodt & "#"

 

[RyLane]

I get a syntax error when I run it with that line

 

[bob fitz]

Is the date shown in the combo box
EDIT
Is PS_dDate a Date type field in the table

 

[RyLane]

Yes, the date is shown and it is date type in the table. But still get
Syntax error in date query expression '([PS_Agent]='John Smith' And [PS_dDate]=#75#'

Does it have something to do with the date como box being a box that finds a record on my form based on the value selected? It also has 3 columns and the date is the only visible one.

 

[pr2-eugin]

Change the Bound Column to whichever column which has the date in it.

 

[bob fitz]

Change the Bound Column to whichever column which has the date in it.

Perhaps referencing the combo box column in the expression would work. Something like:
DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "' And [PS_dDate]=#" & Me.Cbodt.Column(?) & "#"
using a column number instead of the ?. Remember that column numbers start with zero.

 

[pr2-eugin]

Perhaps referencing the combo box column in the expression would work. Something like:
DoCmd.OpenReport "SelectPSReport", acViewPreview, , "[PS_Agent]='" & Me.CboAgt & "' And [PS_dDate]=#" & Me.Cbodt.Column(?) & "#"
using a column number instead of the ?. Remember that column numbers start with zero.

Yes that will work too.

For some reason I was thinking this was on a Query where you will not be able to reference the Column property of the ComboBox. In this case you can use the Column as Bob has suggested.

 

[RyLane]

That did the trick for the report but now the form itself does not populate. It stays blank after I make my selections in the combo boxes.

Now I do have have a SearchForRecord Macro in the date combo box which now might be off.

Object Type - (blank)
Object Name - (blank)
Record - First
Where Condition ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))

 

[RyLane]

Scratch my last post, I didn't see the last 2 posts.

It works perfectly now by referencing the column!

Many thanks to the both of you.

 

[bob fitz]

Scratch my last post, I didn't see the last 2 posts.

It works perfectly now by referencing the column!

Many thanks to the both of you.

Glad you've got it working. Good luck with your project

 

[RyLane]

Hello again, Quick follow-up for you,

How can I inlcude the ID in the openreport command?

Some dates are the same but the record is different so I I'd like the report to be populated by the ID as well. That way I can split up records with the same date and not have them appear on the same report.

Thanks

 

[RyLane]

Seemed to have go it to work with:

DoCmd.OpenReport "Copy of PSReport", acViewPreview, , "[PS_Agent]='" & Me.cboName & "' And [PS_dDate]=#" & Me.cboDate.Column(1) & "# And [ID]=" & Me.[ID].Value

So I'll go with that for now!