new table from same data as report

[Scott_Brasted via AccessMonster.com]

Greetings,

I hope this is the correct group, my question spans several topics.

I have created a db from the Access 2000 time and billing wizard. Mostly it
works ok. There are about a hundred peculiarities that I do not yet
understand. But for now, this is the thing I have decided to work on.

The db allows you to create an invoice from data that is displayed on a form
from a form and some criteria info you provide. You click a command button
and voila, a report with the same info. No real magic, but handy. What is
missing from the template db is the real magic, a way to keep track of the
invoices you produce. I can print invoices till the cows come home, but I
have no record of them.

What I am thinking about is creating a table that captures most of the same
data that the form sends to the report for each invoice. That's the simplest
expanation I can make of it. Before I take the time and bother all of you
with the details, etc, can this be done? Should it be done? If so what info
do you need to help me figure out how to do it?

Many thanks for taking the time to think about this.
Scott B

 

[Jeff Boyce]

Wouldn't that entail duplicating the data you already have?

If you are able to generate a report, the data has to be coming from
somewhere. No, not from the form, because the form is just a window on the
underlying data, which, in Access, is stored in tables.

If you take a look at the data source for the form, it will either be a
table or (preferably) a query.

Take a look at that query in design view. Can you use what you already have
to re-find the data that was used to generate your report(s)? It would sure
beat storing redundant data, and risking the data integrity...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Scott_Brasted via AccessMonster.com]

I agree. I have been thinking about this since I sent the posting. I need to
record the invoice, but I do not want duplicated data. Can I create a table
that has a unique InvoiceID for each invoice and pointers to the data shown
on the form and printed on the report?

The form that has the command button to print the invoice is fporm with a
subform. The form's control comes from a VB on_open event. The subform uses
an SQL statment. It is after the code. Here is the code (it's long):
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
If Not IsLoaded("Clients") Then
MsgBox "Open the Print Invoice form using the Preview Invoice button
on the Clients form."
Cancel = True
End If
End Sub
Private Sub Begin_Date_AfterUpdate()
Me![Print Invoice Time Subform].Requery
Me![Print Invoice Expenses Subform].Requery
End Sub
Private Sub End_Date_AfterUpdate()
Me![Print Invoice Time Subform].Requery
Me![Print Invoice Expenses Subform].Requery
End Sub
Private Sub Toggle_View_Click()
If Me![Toggle View].Caption = "&View Expenses" Then
Me![Print Invoice Time Subform].Visible = False
Me![Print Invoice Expenses Subform].Visible = True
Me![Toggle View].Caption = "&View Hours"
Else
Me![Print Invoice Expenses Subform].Visible = False
Me![Print Invoice Time Subform].Visible = True
Me![Toggle View].Caption = "&View Expenses"
End If
End Sub
Private Sub PreviewInvoice_Click()
On Error GoTo Err_PreviewInvoice_Click
DoCmd.OpenReport "Invoice", acPreview, , "[ProjectID]=" & [Project ID]
Me.Visible = False
Exit_PreviewInvoice_Click:
Exit Sub
Err_PreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_PreviewInvoice_Click
End Sub

Private Sub closePrintInvoi_Click()
On Error GoTo Err_closePrintInvoi_Click
DoCmd.Close
Exit_closePrintInvoi_Click:
Exit Sub
Err_closePrintInvoi_Click:
MsgBox Err.Description
Resume Exit_closePrintInvoi_Click
End Sub

Private Sub closePrintInvoiceForm_Click()
On Error GoTo Err_closePrintInvoiceForm_Click
DoCmd.Close
Exit_closePrintInvoiceForm_Click:
Exit Sub
Err_closePrintInvoiceForm_Click:
MsgBox Err.Description
Resume Exit_closePrintInvoiceForm_Click
End Sub

Subform SQL:
SELECT DISTINCTROW [Time Card Expenses].*, [LastName] & ", " & [FirstName] AS
[Employee Name], [Expense Codes].ExpenseCode
FROM Employees INNER JOIN ([Time Cards] INNER JOIN ([Time Card Expenses]
INNER JOIN [Expense Codes] ON [Time Card Expenses].ExpenseCodeID = [Expense
Codes].ExpenseCodeID) ON [Time Cards].TimeCardID = [Time Card Expenses].
TimeCardID) ON Employees.EmployeeID = [Time Cards].EmployeeID
WHERE ((([Time Card Expenses].ExpenseDate)>=[forms]![Print Invoice]![Begin
Date] And ([Time Card Expenses].ExpenseDate)<=[forms]![Print Invoice]![End
Date]) AND (([Time Card Expenses].ProjectID)=[forms]![Print Invoice]![Project
ID]));

Many thanks for even taking this on

Best,
Scott,

Wouldn't that entail duplicating the data you already have?

If you are able to generate a report, the data has to be coming from
somewhere. No, not from the form, because the form is just a window on the
underlying data, which, in Access, is stored in tables.

If you take a look at the data source for the form, it will either be a
table or (preferably) a query.

Take a look at that query in design view. Can you use what you already have
to re-find the data that was used to generate your report(s)? It would sure
beat storing redundant data, and risking the data integrity...

Regards

Jeff Boyce
Microsoft Access MVP

Greetings,

[quoted text clipped - 24 lines]

Many thanks for taking the time to think about this.
Scott B

 

[Jeff Boyce]

Not sure what you mean by "pointers to the data".

Would it be sufficient to capture the selection criteria used, thereby
making it possible to re-run the query and get the same data?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I agree. I have been thinking about this since I sent the posting. I need
to
record the invoice, but I do not want duplicated data. Can I create a
table
that has a unique InvoiceID for each invoice and pointers to the data
shown
on the form and printed on the report?

The form that has the command button to print the invoice is fporm with a
subform. The form's control comes from a VB on_open event. The subform
uses
an SQL statment. It is after the code. Here is the code (it's long):
Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
If Not IsLoaded("Clients") Then
MsgBox "Open the Print Invoice form using the Preview Invoice
button
on the Clients form."
Cancel = True
End If
End Sub
Private Sub Begin_Date_AfterUpdate()
Me![Print Invoice Time Subform].Requery
Me![Print Invoice Expenses Subform].Requery
End Sub
Private Sub End_Date_AfterUpdate()
Me![Print Invoice Time Subform].Requery
Me![Print Invoice Expenses Subform].Requery
End Sub
Private Sub Toggle_View_Click()
If Me![Toggle View].Caption = "&View Expenses" Then
Me![Print Invoice Time Subform].Visible = False
Me![Print Invoice Expenses Subform].Visible = True
Me![Toggle View].Caption = "&View Hours"
Else
Me![Print Invoice Expenses Subform].Visible = False
Me![Print Invoice Time Subform].Visible = True
Me![Toggle View].Caption = "&View Expenses"
End If
End Sub
Private Sub PreviewInvoice_Click()
On Error GoTo Err_PreviewInvoice_Click
DoCmd.OpenReport "Invoice", acPreview, , "[ProjectID]=" & [Project ID]
Me.Visible = False
Exit_PreviewInvoice_Click:
Exit Sub
Err_PreviewInvoice_Click:
MsgBox Err.Description
Resume Exit_PreviewInvoice_Click
End Sub

Private Sub closePrintInvoi_Click()
On Error GoTo Err_closePrintInvoi_Click
DoCmd.Close
Exit_closePrintInvoi_Click:
Exit Sub
Err_closePrintInvoi_Click:
MsgBox Err.Description
Resume Exit_closePrintInvoi_Click
End Sub

Private Sub closePrintInvoiceForm_Click()
On Error GoTo Err_closePrintInvoiceForm_Click
DoCmd.Close
Exit_closePrintInvoiceForm_Click:
Exit Sub
Err_closePrintInvoiceForm_Click:
MsgBox Err.Description
Resume Exit_closePrintInvoiceForm_Click
End Sub

Subform SQL:
SELECT DISTINCTROW [Time Card Expenses].*, [LastName] & ", " & [FirstName]
AS
[Employee Name], [Expense Codes].ExpenseCode
FROM Employees INNER JOIN ([Time Cards] INNER JOIN ([Time Card Expenses]
INNER JOIN [Expense Codes] ON [Time Card Expenses].ExpenseCodeID =
[Expense
Codes].ExpenseCodeID) ON [Time Cards].TimeCardID = [Time Card Expenses].
TimeCardID) ON Employees.EmployeeID = [Time Cards].EmployeeID
WHERE ((([Time Card Expenses].ExpenseDate)>=[forms]![Print Invoice]![Begin
Date] And ([Time Card Expenses].ExpenseDate)<=[forms]![Print Invoice]![End
Date]) AND (([Time Card Expenses].ProjectID)=[forms]![Print
Invoice]![Project
ID]));

Many thanks for even taking this on

Best,
Scott,

Wouldn't that entail duplicating the data you already have?

If you are able to generate a report, the data has to be coming from
somewhere. No, not from the form, because the form is just a window on
the
underlying data, which, in Access, is stored in tables.

If you take a look at the data source for the form, it will either be a
table or (preferably) a query.

Take a look at that query in design view. Can you use what you already
have
to re-find the data that was used to generate your report(s)? It would
sure
beat storing redundant data, and risking the data integrity...

Regards

Jeff Boyce
Microsoft Access MVP

Greetings,

[quoted text clipped - 24 lines]

Many thanks for taking the time to think about this.
Scott B

 

[Scott_Brasted via AccessMonster.com]

That would be a bonus. Maybe a record of the invoice number that is tied to
the client and project and date/time of invoice along with your suggestion?
What do you think?

Scott

Wha

Not sure what you mean by "pointers to the data".

Would it be sufficient to capture the selection criteria used, thereby
making it possible to re-run the query and get the same data?

Regards

Jeff Boyce
Microsoft Access MVP

I agree. I have been thinking about this since I sent the posting. I need
to

[quoted text clipped - 115 lines]

 

[Jeff Boyce]

So, you're saying you would NOT need to recreate the invoice, only to
remember that invoice # as "belonging to" a given client, project and
date/time? That sounds like a table!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

That would be a bonus. Maybe a record of the invoice number that is tied
to
the client and project and date/time of invoice along with your
suggestion?
What do you think?

Scott

Wha

Not sure what you mean by "pointers to the data".

Would it be sufficient to capture the selection criteria used, thereby
making it possible to re-run the query and get the same data?

Regards

Jeff Boyce
Microsoft Access MVP

I agree. I have been thinking about this since I sent the posting. I need
to

[quoted text clipped - 115 lines]

Many thanks for taking the time to think about this.
Scott B