Appending data from a report to a history table 1

[JBuckley2000]

Hello,

I am having a problem with saving records in a production history table. As of now, I have created a database which uses a report (PrintTickets) to print out certain production tickets once a product is made. I am wanting to track the tickets that are printed out each day, but I am unsure as how to save the entered ticket information. The report runs off a query which asks the operator to enter in the order number and part number, then the rest of the information is printed on the ticket by looking at the part number table such as stack quantity and description of the product.

I am wondering how do I append the information used on the report into a new production history table. If I need to provide more information, please reply.
Thanks,

JSF

Jason Facey
jfacey@lithonia.com

 

[KyleS]

Hi Jason,

I would make an "Append Query" (look it up in help if you're not familiar - it's pretty straight forward) that takes the info from the instance of the report and "appends" it to your "historical" table. Use Expressions similar to this in the criteria of your query to get the proper information:

Field is "NAME"
in the "Criteria" line of the query builder, you should have this: Reports![YourReportName]![YourFieldName]

Now, go to the properties section in the report's design view, select the "Events" tab and go to "On Close". Select "[Event Proceedure]" from the drop-down list and then press the "..." button just to the right of the field. That will bring up the VBA module assigned to that report.

Paste this in there:
(Remember to leave out the underlined parts, as Access will pre-populate them)

Private Sub Report_Open()
On Error GoTo Error_Report_Open

DoCmd.SetWarnings False

DoCmd.OpenQuery "[YourNewAppendQueryName]"

DoCmd.SetWarnings True

Exit_Report_Open:
Exit Sub

Error_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Close
End Sub

This will tell Access to run the append query when you hit the close button and put the data where it belongs. (You could also put this on "On Close", it's a matter of preference.

(If my instructions are lacking, please let me know and I will clarify)

Kyle

 

[JBuckley2000]

Hey Kyle,

I think I understand where you are coming from, but I am having problems writing the append query.

I set it up so that when I run the Ticket Query which the report uses, the information will be appended to the history table. My problem is, for some reason, when I run the append, I should only have about 2 tickets that will be created and appended, but instead, it appends about 36 records - I have no idea why. Am I right to use the query that the report runs off of and the history table in the Append Query?

Thanks for trying to help me! JSF

Jason Facey
jfacey@lithonia.com

 

[JBuckley2000]

Ok, ok, disregard the last post. I was putting the history table into the append query, when all I really needed to include was the Ticket Query that report runs off of. Now I am going to try and finish the rest of it...sorry about me being a moron, Kyle. JSF

Jason Facey
jfacey@lithonia.com

 

[JBuckley2000]

Hey again Kyle,

I put the code in just as you have it written, but this error message comes up along with the expression you gave me to fix:

Compile Error:
Label Not Defined

The part of the module that is highlighted to fix is:

Resume Exit_Report_Close

Is this because the report runs off of a query that it cannot close properly? I checked to see if it is because I run the report off a switchboard that doesn't show the results on the screen, but just prints the results right away to a printer - this isn't the problem though. Even when I go directly to the report to show the results on screen, I get the same error.

Thanks for listening to my yammering. JSF

Jason Facey
jfacey@lithonia.com

 

[Craig0201]

Why a history table? Keep the same table and add a column for archieved or live.

Craig

 

[JBuckley2000]

A history table because I want to be able to pull the part numbers from the part number table then save whatever ticket information in a production history table. Sometimes the user will have to add new parts to the part table and it wouldn't make sense to also have the production data included since it will show what is entered that day together, not differentiating between production and just regulat part additions. JSF

Jason Facey
jfacey@lithonia.com

 

[KyleS]

Hi there,
The line of code isn't working because I'M the moron here. It should read: Resume Exit_Report_Open

Sorry about that...


Kyle

 

[JBuckley2000]

SOrry to write again Kyle, but some problems still came up for me - this is what is in the module:

Option Compare Database
Option Explicit

___________________________________________________________


Private Sub Report_Close()

End Sub
___________________________________________________________

Private Sub Report_Open()
On Error GoTo Error_Report_Open

DoCmd.SetWarnings False
DoCmd.OpenQuery "[Append Tickets to Production Record]"
DoCmd.SetWarnings True

Exit_Report_Open:
Exit Sub

Error_Report_Open:
MsgBox Err.Description
Resume Exit_Report_Open
End Sub

I have been busy the past day and I was hoping you could still tell me why I am getting an error message - "The experession On Close you entered as the event property settings produced the following error: Event procedure declaration does not match description of event having the same name."

Thanks for looking at this. JSF

Jason Facey
jfacey@lithonia.com

 

[KyleS]

Jason,
Go ahead and delete this part of your module:



Private Sub Report_Close()

End Sub
___________________________________________________________

And go to the properties section for the report and make sure there is nothing in the "On Close" event for the report.

Since there's no code there you can just get rid of it, and that's where the error is coming from so that should fix things...

Happy to help, if you have any more questions, just ask!

Kyle