Passing Form changes to a report

[Stowers]

Hi Guys
Can someone help me do the following . I have a form (sorry I would have posted a image but apparently I need 10 posts) but the jist of it is

JobDate .... CompanyInvoiced PaidOut PaidOutDate

10/10/2011 ABC ................... £200.00
11/10/2011 XYZ ................... £200.00
12/10/2011 GHF ................... £200.00


This is created via the following query

SELECT tblOrderDetails.OrderDetailsID, tblDrivers.DriverID, tblDrivers.DriverName, tblDrivers.VATABLE, tblOrderDetails.OrderDate, tblPaymentsOut.VATRate, tblPaymentsOut.PaidOut, tblPaymentsOut.PAIDOUTDATE, tblClients.[Invoice To Co], tblClients.ClientCo
FROM tblDrivers INNER JOIN (tblClients INNER JOIN (tblOrderDetails INNER JOIN tblPaymentsOut ON tblOrderDetails.OrderDetailsID = tblPaymentsOut.OrderDetailsID) ON tblClients.ClientsID = tblOrderDetails.InvTag) ON tblDrivers.DriverID = tblPaymentsOut.DriverID
WHERE (((tblDrivers.DriverName)="ian shaw") AND ((tblPaymentsOut.PAIDOUTDATE) Is Null))
ORDER BY tblOrderDetails.OrderDate DESC;

What I want to be able to do is enter a date in the PaidOutDate (not always all of the records) and then produce a report on only those records (displayed on the form) that have the PaidOutDate populated.

I can easily produce a report if I just put IS NOT NULL in the reports query PAIDOUTDATE but that will show all of the records that have been paid previously as well.

I have been told I can pass a string thru to the report based on the form ???? but no matter how much I read the docmd openreport documentation nothing makes sense.

Help would be much appreciated. I have attached a test DB just in case.

The form is called FDRIVERPAID and the report RDRIVERPAID

Ian

 

[vbaInet]

So the criteria will be:

[PaidOutDate] <= SomeDate

Here's how you pass it to OpenReport:

http://baldyweb.com/wherecondition.htm

Substitute OpenForm for OpenReport. They are equivalent commands.

 

[Stowers]

Hi
Have tried what you suggested and have got the following
run time error 3075
syntax error in date in query expression '(paidoutdate = ##'

Am I still missing something, you have the following line in your reply

[PaidOutDate] <= SomeDateIs this meant to go anywhere.

 

[vbaInet]

Let me see the code that you tried.

 

[Stowers]

HI VBAINET

DoCmd.OpenReport "RDRIVERPAID", acViewPreview, , "PAIDOUTDATE = #" & Me.PAIDOUTDATE & "#"

 

[vbaInet]

It would appear that Me.PaidOutDate is empty or invalid at the time the code was run.

 

[Stowers]

Hi VBAINET

Its definately got a date there

Have finally (hopefully) managed to upload DB

I have mangaed to do the form pass to form (see my DB) but even that was wrong, to the extent that it only passes the record that the cursor is in

 

[vbaInet]

I don't need to see your db

Have you verified that it is returning a date?

For example, you can use an Msgbox to verify this in the form:

Msgbox Nz(Me.PaidOutDate, "No Date")

 

[Stowers]

hI vbainet

Have added the message box and the following is happening

If I add a date to the record and move cursor to another record (without date) it displays that syntax error

If I leave the cursor in the record with the date it displays the msg box with the date I entered and produces the report. GREAT . IT WORKS .

I will just have to remember to leave the cursor in a record with the date present . Unless you have a work around on that as well ????

Much appreciated

Ian

 

[vbaInet]

When you edit a record, it needs to be commited or saved to the database before trying to query based on that field.

You do this by running one of:

If Me.Dirty Then Me.Dirty = False

Or this:

DoCmd.RunCommand acCmdSaveRecord

... before you run the OpenReport command.

Where is the button placed? In the section where your records are or in the Header section?

 

[Stowers]

Hi VBAINET

In the Header but your code wont help if I leave the cursor in a blank reord or WILL IT

I will try the docmd

ian

 

[vbaInet]

They are equivalent. That was why I said one or the other.

From a design point of view, it makes sense to place your buttons in the Detail section of your form. That way your users are sure exactly what record they would like displayed.

 

[Stowers]

Hi VBAINET

I can appreciate the design aspects but going back to my original post

JobDate .... CompanyInvoiced PaidOut PaidOutDate

10/10/2011 ABC ................... £200.00 11/11/2011
11/10/2011 XYZ ................... £200.00 11/11/2011
12/10/2011 GHF ................... £200.00

If I enter the date in the 1st and 2nd records and then have the cursor in the 3rd record I will get the syntax error . If the cursor is in the 1st or 2nd record it will display both the 1st and 2nd records on the report (which is what I want)
I was just wondering in my last post , that if the cursor is in the 3rd record is there a workaround ?? ie that it produces the report on the 1st and 2nd records

Much appreciated for your help

Ian

 

[vbaInet]

It's a simple matter of validating the date field:

If IsDate(Me.DateField) Then
    DoCmd.OpenReport ... with criteria
Else
    DoCmd.OpenReport ... no criteria
End If

 

[Stowers]

Thanks VBAINET

Will play around later this afternoon , have got to do a bit of work now

Thanks again

Ian