If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
new table from same data as report
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
#2
|
|||
|
|||
new table from same data as report
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" u56211@uwe wrote in message news:9f5c975eb9638@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200911/1 |
#3
|
|||
|
|||
new table from same data as report
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, Jeff Boyce wrote: 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 -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
new table from same data as report
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. "Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message news:9f68760a630e6@uwe... 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, Jeff Boyce wrote: 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 -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
new table from same data as report
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 Jeff Boyce wrote: 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 -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
new table from same data as report
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. "Scott_Brasted via AccessMonster.com" u56211@uwe wrote in message news:9f6a78f2a156e@uwe... 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 Jeff Boyce wrote: 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 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|