Launching Report from Form Only Shows Report for First Record

[mjones]

Hi All,

Me again. I know I'm a pain in the neck, but this is a big problem so
hope to bug you all again.

I've made a wonderful invoice - works great with lots of wonderful
features ..... except

The Preview button from the form, only works for the first record.
When I change the form to the next record, I get #ERROR or blank
fields.

The code for the button is:

Private Sub PreviewInvoice_Click()
Call CommandSave_Click
DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub

Private Sub CommandSave_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub

The tInv table has proper InvNo (invoice numbers) in the record's
fields.

The report rInv record source is -

SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec,
tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode, tClient.*,
tInv.PaymentMethod, tInv.ClassDates, tInv.Terms, tInv.DueDate,
tInventoryTransactions.ProductCode, tProduct.ProductDescription,
tProduct.UnitPrice, tInventoryTransactions.UnitsSold, tInv.InvDtlNo,
tInv.ReceiptNo, tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]) AND ((tInv.ClientID)=[tClient].
[ID]));

You'd be right by saying, "She doesn't know what she's doing so she's
throwing everything in just in case."

The form fInv record source is SELECT tInv.* FROM tInv;

Any hints where to look would be, as always, greatly appreciated.

Michele

 

[mjones]

Hi All,

Me again.  I know I'm a pain in the neck, but this is a big problem so
hope to bug you all again.

I've made a wonderful invoice - works great with lots of wonderful
features ..... except

The Preview button from the form, only works for the first record.
When I change the form to the next record, I get #ERROR or blank
fields.

The code for the button is:

Private Sub PreviewInvoice_Click()
    Call CommandSave_Click
    DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub

Private Sub CommandSave_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub

The tInv table has proper InvNo (invoice numbers) in the record's
fields.

The report rInv record source is -

SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec,
tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode, tClient.*,
tInv.PaymentMethod, tInv.ClassDates, tInv.Terms, tInv.DueDate,
tInventoryTransactions.ProductCode, tProduct.ProductDescription,
tProduct.UnitPrice, tInventoryTransactions.UnitsSold, tInv.InvDtlNo,
tInv.ReceiptNo, tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]) AND ((tInv.ClientID)=[tClient]..
[ID]));

You'd be right by saying, "She doesn't know what she's doing so she's
throwing everything in just in case."

The form fInv record source is SELECT tInv.* FROM tInv;

Any hints where to look would be, as always, greatly appreciated.

Michele

I figured out what causes the problem, but don't know how to fix it.
Just thought to let you know so not to waste your time.

If I add a student and classcode, it works ... in other words, the
invoice only works if I'm invoicing a class, not for anything else.

I try to figure out a simple way to explain this part and come back
with a later post.

Thanks for listening.

 

[mjones]

Hi All,

Me again.  I know I'm a pain in the neck, but this is a big problem so
hope to bug you all again.

I've made a wonderful invoice - works great with lots of wonderful
features ..... except

The Preview button from the form, only works for the first record.
When I change the form to the next record, I get #ERROR or blank
fields.

The code for the button is:

Private Sub PreviewInvoice_Click()
    Call CommandSave_Click
    DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub

Private Sub CommandSave_Click()
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub

The tInv table has proper InvNo (invoice numbers) in the record's
fields.

The report rInv record source is -

SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec,
tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode, tClient.*,
tInv.PaymentMethod, tInv.ClassDates, tInv.Terms, tInv.DueDate,
tInventoryTransactions.ProductCode, tProduct.ProductDescription,
tProduct.UnitPrice, tInventoryTransactions.UnitsSold, tInv.InvDtlNo,
tInv.ReceiptNo, tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]) AND ((tInv.ClientID)=[tClient].
[ID]));

You'd be right by saying, "She doesn't know what she's doing so she's
throwing everything in just in case."

The form fInv record source is SELECT tInv.* FROM tInv;

Any hints where to look would be, as always, greatly appreciated.

Michele

I figured out what causes the problem, but don't know how to fix it.
Just thought to let you know so not to waste your time.

If I add a student and classcode, it works ... in other words, the
invoice only works if I'm invoicing a class, not for anything else.

I try to figure out a simple way to explain this part and come back
with a later post.

Thanks for listening.

Here's the summary.

A tClient table has name, address parts, phone, etc. The autonumber
ID key identifies a unique client.

About eight forms/reports have full or partial client information,
e.g. receipt with full name, address, etc.

Forms select clients from dropdowns with record source:
SELECT tClient.ID, tClient.LName & ", " & tClient.FNameF AS Expr1 FROM
tClient ORDER BY tClient.LName & ", " & tClient.FNameF;

Reports display information in objects like this: =[FNameF] & " " &
[LName] – like Jack Black for first and last name.

This new invoice is unique because more than one client is needed.
Specifically, an invoice needs a company client (tInv.PayerID) for
bill to and sometimes needs the first & last name of a student client
(tInv.ClientID).

tInv table has fields for both PayerID and ClientID. These fields
could be the same or different for an invoice record/

To summarize, rInv report, doesn’t work unless all three (PayerID,
ClientID, and ClassCode) are selected from dropdowns. Since, ClientID
and ClassCode must be blank for some invoices, this causes the
problem.

My first battle is determining how to make the report object with
=[FNameF] & " " & [LName] know which tClient.ID to use - the one
relating to tInv.PayerID or the one relating to tInv.ClientID.

In other words, the report object should be the tClient.FNameF with
the tClient.ID matching the tInv.PayerID for one report object. The
other matches tInv.ClientID. It’s probably a query, but I’m not
knowledgeable enough to do this. Can a kind someone out there please
help?

 

[Rob Parker]

Hi again Michele,

I've not gone through this in detail, but I suspect that your problem arises
because of the INNER JOINs in the query which is the record source for rInv.
An Inner Join requires matching records in both tables - it will not return
any records where a field in one of the tables is null. Try changing the
joins to outer joins (either LEFT JOIN or RIGHT JOIN - depends on how you
set them up which one you get). Do this in the query builder by
right-clicking on the line joining the fields, then select Join Properties;
in the dialog box, click the appropriate one of the "Include ALL records
from ... and only those ..."

HTH,

Rob

Hi All,

Me again. I know I'm a pain in the neck, but this is a big problem so
hope to bug you all again.

I've made a wonderful invoice - works great with lots of wonderful
features ..... except

The Preview button from the form, only works for the first record.
When I change the form to the next record, I get #ERROR or blank
fields.

The code for the button is:

Private Sub PreviewInvoice_Click()
Call CommandSave_Click
DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub

Private Sub CommandSave_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub

The tInv table has proper InvNo (invoice numbers) in the record's
fields.

The report rInv record source is -

SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec,
tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode, tClient.*,
tInv.PaymentMethod, tInv.ClassDates, tInv.Terms, tInv.DueDate,
tInventoryTransactions.ProductCode, tProduct.ProductDescription,
tProduct.UnitPrice, tInventoryTransactions.UnitsSold, tInv.InvDtlNo,
tInv.ReceiptNo, tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]) AND ((tInv.ClientID)=[tClient].
[ID]));

You'd be right by saying, "She doesn't know what she's doing so she's
throwing everything in just in case."

The form fInv record source is SELECT tInv.* FROM tInv;

Any hints where to look would be, as always, greatly appreciated.

Michele

I figured out what causes the problem, but don't know how to fix it.
Just thought to let you know so not to waste your time.

If I add a student and classcode, it works ... in other words, the
invoice only works if I'm invoicing a class, not for anything else.

I try to figure out a simple way to explain this part and come back
with a later post.

Thanks for listening.

Here's the summary.

A tClient table has name, address parts, phone, etc. The autonumber
ID key identifies a unique client.

About eight forms/reports have full or partial client information,
e.g. receipt with full name, address, etc.

Forms select clients from dropdowns with record source:
SELECT tClient.ID, tClient.LName & ", " & tClient.FNameF AS Expr1 FROM
tClient ORDER BY tClient.LName & ", " & tClient.FNameF;

Reports display information in objects like this: =[FNameF] & " " &
[LName] – like Jack Black for first and last name.

This new invoice is unique because more than one client is needed.
Specifically, an invoice needs a company client (tInv.PayerID) for
bill to and sometimes needs the first & last name of a student client
(tInv.ClientID).

tInv table has fields for both PayerID and ClientID. These fields
could be the same or different for an invoice record/

To summarize, rInv report, doesn’t work unless all three (PayerID,
ClientID, and ClassCode) are selected from dropdowns. Since, ClientID
and ClassCode must be blank for some invoices, this causes the
problem.

My first battle is determining how to make the report object with
=[FNameF] & " " & [LName] know which tClient.ID to use - the one
relating to tInv.PayerID or the one relating to tInv.ClientID.

In other words, the report object should be the tClient.FNameF with
the tClient.ID matching the tInv.PayerID for one report object. The
other matches tInv.ClientID. It’s probably a query, but I’m not
knowledgeable enough to do this. Can a kind someone out there please
help?

 

[mjones]

Hi again Michele,

I've not gone through this in detail, but I suspect that your problem arises
because of the INNER JOINs in the query which is the record source for rInv.
An Inner Join requires matching records in both tables - it will not return
any records where a field in one of the tables is null.  Try changing the
joins to outer joins (either LEFT JOIN or RIGHT JOIN - depends on how you
set them up which one you get).  Do this in the query builder by
right-clicking on the line joining the fields, then select Join Properties;
in the dialog box, click the appropriate one of the "Include ALL records
from ... and only those ..."

HTH,

Rob

Hi All,
Me again. I know I'm a pain in the neck, but this is a big problem so
hope to bug you all again.
I've made a wonderful invoice - works great with lots of wonderful
features ..... except
The Preview button from the form, only works for the first record.
When I change the form to the next record, I get #ERROR or blank
fields.
The code for the button is:
Private Sub PreviewInvoice_Click()
Call CommandSave_Click
DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub
Private Sub CommandSave_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub
The tInv table has proper InvNo (invoice numbers) in the record's
fields.
The report rInv record source is -
SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec,
tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode, tClient.*,
tInv.PaymentMethod, tInv.ClassDates, tInv.Terms, tInv.DueDate,
tInventoryTransactions.ProductCode, tProduct.ProductDescription,
tProduct.UnitPrice, tInventoryTransactions.UnitsSold, tInv.InvDtlNo,
tInv.ReceiptNo, tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]) AND ((tInv.ClientID)=[tClient].
[ID]));
You'd be right by saying, "She doesn't know what she's doing so she's
throwing everything in just in case."
The form fInv record source is SELECT tInv.* FROM tInv;
Any hints where to look would be, as always, greatly appreciated.
Michele

I figured out what causes the problem, but don't know how to fix it.
Just thought to let you know so not to waste your time.

If I add a student and classcode, it works ... in other words, the
invoice only works if I'm invoicing a class, not for anything else.

I try to figure out a simple way to explain this part and come back
with a later post.

Thanks for listening.

Here's the summary.

A tClient table has name, address parts, phone, etc.  The autonumber
ID key identifies a unique client.

About eight forms/reports have full or partial client information,
e.g. receipt with full name, address, etc.

Forms select clients from dropdowns with record source:
SELECT tClient.ID, tClient.LName & ", " & tClient.FNameF AS Expr1 FROM
tClient ORDER BY tClient.LName & ", " & tClient.FNameF;

Reports display information in objects like this: =[FNameF] & " " &
[LName] – like Jack Black for first and last name.

This new invoice is unique because more than one client is needed.
Specifically, an invoice needs a company client (tInv.PayerID) for
bill to and sometimes needs the first & last name of a student client
(tInv.ClientID).

tInv table has fields for both PayerID and ClientID.  These fields
could be the same or different for an invoice record/

To summarize, rInv report, doesn’t work unless all three (PayerID,
ClientID, and ClassCode) are selected from dropdowns.  Since, ClientID
and ClassCode must be blank for some invoices, this causes the
problem.

My first battle is determining how to make the report object with
=[FNameF] & " " & [LName] know which tClient.ID to use - the one
relating to tInv.PayerID or the one relating to tInv.ClientID.

In other words, the report object should be the tClient.FNameF with
the tClient.ID matching the tInv.PayerID for one report object.  The
other matches tInv.ClientID.  It’s probably a query, but I’m not
knowledgeable enough to do this.  Can a kind someone out there please
help?

Thanks Rob. I also suspect it's something to do with this query --
especially since I don't fully understand it. Right and Left Joins -
what? It's a hint and I appreciate it. I'll go read up on those and
queries in general.

I foolishly tried randomly changing all joins to left joins and got an
error - getting desperate and starting to try anything. I didn't know
about clicking on the line. One must get their thinking cap on when
considering the 3 options; especially with multiple joins.

I did discover that I must have both PayerID (payer) and ClientID
(student) the same or the invoice gets all errors and can't even get
the invoice number. Another hint that sounds like this query again.

Thanks and have a great night,

Michele

 

[mjones]

Hi again Michele,

I've not gone through this in detail, but I suspect that your problem arises
because of the INNER JOINs in the query which is the record source for rInv.
An Inner Join requires matching records in both tables - it will not return
any records where a field in one of the tables is null.  Try changingthe
joins to outer joins (either LEFT JOIN or RIGHT JOIN - depends on how you
set them up which one you get).  Do this in the query builder by
right-clicking on the line joining the fields, then select Join Properties;
in the dialog box, click the appropriate one of the "Include ALL records
from ... and only those ..."


"mjones" <[email protected]> wrote in message

Hi All,
Me again. I know I'm a pain in the neck, but this is a big problem so
hope to bug you all again.
I've made a wonderful invoice - works great with lots of wonderful
features ..... except
The Preview button from the form, only works for the first record.
When I change the form to the next record, I get #ERROR or blank
fields.
The code for the button is:
Private Sub PreviewInvoice_Click()
Call CommandSave_Click
DoCmd.OpenReport "rInv", acViewPreview, , "InvNo=" & Me!InvNo
End Sub
Private Sub CommandSave_Click()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
End Sub
The tInv table has proper InvNo (invoice numbers) in the record's
fields.
The report rInv record source is -
SELECT tInv.PayerID, tClient.ID, tInv.ClientID, tInv.AmountRec,
tInv.InvNo, tInv.InvDate, tInv.InvNote, tInv.ClassCode, tClient.*,
tInv.PaymentMethod, tInv.ClassDates, tInv.Terms, tInv.DueDate,
tInventoryTransactions.ProductCode, tProduct.ProductDescription,
tProduct.UnitPrice, tInventoryTransactions.UnitsSold, tInv.InvDtlNo,
tInv.ReceiptNo, tInv.PrevDepMethod, tInv.PrevDepAmount,
tInventoryTransactions.InvoiceID, tInv.Payment
FROM (tInventoryTransactions INNER JOIN ((tClient INNER JOIN tInv ON
tClient.ID = tInv.PayerID) INNER JOIN tClass ON tInv.ClassCode =
tClass.ClassCode) ON tInventoryTransactions.InvoiceID = tInv.InvNo)
INNER JOIN tProduct ON tInventoryTransactions.ProductCode =
tProduct.ProductCode
WHERE (((tInv.PayerID)=[tClient].[ID]) AND ((tInv.ClientID)=[tClient].
[ID]));
You'd be right by saying, "She doesn't know what she's doing so she's
throwing everything in just in case."
The form fInv record source is SELECT tInv.* FROM tInv;
Any hints where to look would be, as always, greatly appreciated.
Michele
I figured out what causes the problem, but don't know how to fix it.
Just thought to let you know so not to waste your time.
If I add a student and classcode, it works ... in other words, the
invoice only works if I'm invoicing a class, not for anything else.
I try to figure out a simple way to explain this part and come back
with a later post.
Thanks for listening.

Here's the summary.

A tClient table has name, address parts, phone, etc.  The autonumber
ID key identifies a unique client.

About eight forms/reports have full or partial client information,
e.g. receipt with full name, address, etc.

Forms select clients from dropdowns with record source:
SELECT tClient.ID, tClient.LName & ", " & tClient.FNameF AS Expr1 FROM
tClient ORDER BY tClient.LName & ", " & tClient.FNameF;

Reports display information in objects like this: =[FNameF] & " " &
[LName] – like Jack Black for first and last name.

This new invoice is unique because more than one client is needed.
Specifically, an invoice needs a company client (tInv.PayerID) for
bill to and sometimes needs the first & last name of a student client
(tInv.ClientID).

tInv table has fields for both PayerID and ClientID.  These fields
could be the same or different for an invoice record/

To summarize, rInv report, doesn’t work unless all three (PayerID,
ClientID, and ClassCode) are selected from dropdowns.  Since, ClientID
and ClassCode must be blank for some invoices, this causes the
problem.

My first battle is determining how to make the report object with
=[FNameF] & " " & [LName] know which tClient.ID to use - the one
relating to tInv.PayerID or the one relating to tInv.ClientID.

In other words, the report object should be the tClient.FNameF with
the tClient.ID matching the tInv.PayerID for one report object.  The
other matches tInv.ClientID.  It’s probably a query, but I’m not
knowledgeable enough to do this.  Can a kind someone out there please
help?

Thanks Rob.  I also suspect it's something to do with this query --
especially since I don't fully understand it.  Right and Left Joins -
what?  It's a hint and I appreciate it.  I'll go read up on those and
queries in general.

I foolishly tried randomly changing all joins to left joins and got an
error - getting desperate and starting to try anything.  I didn't know
about clicking on the line.  One must get their thinking cap on when
considering the 3 options; especially with multiple joins.

I did discover that I must have both PayerID (payer) and ClientID
(student) the same or the invoice gets all errors and can't even get
the invoice number.  Another hint that sounds like this query again.

Thanks and have a great night,

Michele

Hi All,

Looking at this issue further, I believe resolving it is above my
head.

I would like to pay someone for two or three hours of their time to
connect with me online and help me resolve it. If you are interested,
please email me privately with your rate. So you know I'm
trustworthy, my LinkedIn profile is http://www.linkedin.com/in/michelejonespmp.

Right now, receipts and invoices don't work if a class is being paid
for by a student's company (10% off all invoices) - only if the
student pays themselves. Also, an invoice only works when a class is
one of the items being invoiced (10% don't work). Obviously, these
things need to be fixed. Until fixed, for these situations, we'll
continue using our old Excel system.

Thanks again,

Michele