Opening report to specific record (1 Viewer)

RyLane · Feb 11, 2014

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 · Feb 11, 2014

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

RyLane · Feb 12, 2014

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

bob fitz · Feb 12, 2014

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

RyLane · Feb 12, 2014

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 · Feb 12, 2014

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

bob fitz · Feb 12, 2014

pr2-eugin said:
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 · Feb 12, 2014

bob fitz said:
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 · Feb 12, 2014

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 · Feb 12, 2014

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 · Feb 12, 2014

RyLane said:
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 · Feb 13, 2014

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 · Feb 13, 2014

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!

Opening report to specific record (1 Viewer)

RyLane

Registered User.

bob fitz

AWF VIP

RyLane

Registered User.

bob fitz

AWF VIP

RyLane

Registered User.

pr2-eugin

Super Moderator

bob fitz

AWF VIP

pr2-eugin

Super Moderator

RyLane

Registered User.

RyLane

Registered User.

bob fitz

AWF VIP

RyLane

Registered User.

RyLane

Registered User.

Similar threads

Users who are viewing this thread