How do I assign a batch number to rcds for a print report
in [Reports]
|
From: Dennis on 21 Feb 2010 16:38 Hi, I've have a few general questions on this subject. Now that I'm actuallly writing the code, I have a few detailed question. I have a working function to get me the next available batch number. The batch number, date, time, and report are stored in the tblBatchNo table. I have a query that is the record source for the the report. This query select all invoices with a print batch number. I know I need an update query to do the following, but I don't know how or where to start. I want to: 1. Post the batch number to all invoices that do not have a batch number. (This will enable me to only print the invoice once - unless the user reprints a batch.) 2. Write a new record to the tblCustActivity table that records the customer and batch number. This is an automatically keyed table. I'm using this table to provide a display to the user showing all of the documents (report name, date and time [from batch no]) that were printed for a given customer. Any help would be greatly appreciated. Dennis
From: Duane Hookom on 21 Feb 2010 16:47 This really isn't a reporting question. If you want to update records, you would create some type of form that would allow a user to select a subset of records. You would then run an update query (or other method) to update the records. Apparently you also want to run an append query (or other method). Once you have the records updated, then you open a report based on the same subset of records based on the updated field. -- Duane Hookom Microsoft Access MVP "Dennis" wrote: > Hi, > > I've have a few general questions on this subject. Now that I'm actuallly > writing the code, I have a few detailed question. > > I have a working function to get me the next available batch number. The > batch number, date, time, and report are stored in the tblBatchNo table. > > I have a query that is the record source for the the report. This query > select all invoices with a print batch number. > > I know I need an update query to do the following, but I don't know how or > where to start. I want to: > > 1. Post the batch number to all invoices that do not have a batch number. > (This will enable me to only print the invoice once - unless the user > reprints a batch.) > > 2. Write a new record to the tblCustActivity table that records the customer > and batch number. This is an automatically keyed table. I'm using this > table to provide a display to the user showing all of the documents (report > name, date and time [from batch no]) that were printed for a given customer. > > Any help would be greatly appreciated. > > > Dennis > > >
From: Dennis on 21 Feb 2010 17:02 Duane, "This really isn't a reporting question." I though about putting this in the Access Queries, but I started the general question in this category, so I kept the detailed question here. I placed it here because it was an SQL statement that had to do with a report. You are right, I could have placed it in the Access Queries, but I'm sure someone would have said it was a report issue. "if you want to update records, you would create some type of form that would allow a user to select a subset of records. " As stated: I have a query that is the record source for the the report. This query select all invoices with a print batch number. I could swith the query to a form, but this still does not tell me how to build the SQL statements that I need. "You would then run an update query (or other method) to update the records. Apparently you also want to run an append query (or other method). " Yes, this is exactly what I want to do. My questions was - Specifically how do I do this? A sample query would answer my question. "Once you have the records updated, then you open a report based on the same subset of records based on the updated field. " That is exactly what I will doing once I get the SQL statement. Obviously, I did not make my request very clear. Can any one provide a sample SQL statement to: 1. Post the batch number to all invoices that do not have a batch number in the tblInvoice table. 2. Write a new record to the tblCustActivity table that records the customer and batch number. Thank you for your assitance. Dennis
From: Duane Hookom on 21 Feb 2010 19:05 You specifications suggest updating every record in tblInvoice that doesn't have a batch number (regardless of customer). I don't recall you suggesting where the new batch number comes from. Assuming you have this in your form, you would have a button with code similar to: Dim strSQL as String strSQL = "UPDATE tblInvoice SET BatchNumber = '" & Me.txtBatchNumber & _ "' WHERE BatchNumber is Null" Currentdb.Execute strSQL, dbFailOnError The code to add the record to tblCustActivity would be similar to: Dim strSQL as String strSQL = "INSERT INTO tblCustActivity (CustNumber, BatchNumber) Values ('" & _ Me.txtCustNumber & "'. '" & Me.txtBatchNumber & "') " Currentdb.Execute strSQL, dbFailOnError -- Duane Hookom Microsoft Access MVP "Dennis" wrote: > Duane, > > > "This really isn't a reporting question." > I though about putting this in the Access Queries, but I started the general > question in this category, so I kept the detailed question here. I placed it > here because it was an SQL statement that had to do with a report. You are > right, I could have placed it in the Access Queries, but I'm sure someone > would have said it was a report issue. > > "if you want to update records, you would create some type of form that > would allow a user to select a subset of records. " > As stated: I have a query that is the record source for the the report. > This query > select all invoices with a print batch number. I could swith the query to a > form, but this still does not tell me how to build the SQL statements that I > need. > > "You would then run an update query (or other method) to update the > records. Apparently you also want to run an append query (or other method). " > Yes, this is exactly what I want to do. My questions was - Specifically how > do I do this? A sample query would answer my question. > > "Once you have the records updated, then you open a report based on the same > subset of records based on the updated field. " > That is exactly what I will doing once I get the SQL statement. > > Obviously, I did not make my request very clear. > > Can any one provide a sample SQL statement to: > > 1. Post the batch number to all invoices that do not have a batch number in > the tblInvoice table. > > 2. Write a new record to the tblCustActivity table that records the customer > and batch number. > > > Thank you for your assitance. > > > Dennis
From: Dennis on 21 Feb 2010 21:19 Duane, I have a function that get writes a batch number, report name, current date, current time, and user name to the tblBatchNo table. I get the batch number when I write the row to the table. The batch number is the automatically assigned number and the key to the tblBatchNo table. On the second SQL statement: The code to add the record to tblCustActivity would be similar to: Dim strSQL as String strSQL = "INSERT INTO tblCustActivity (CustNumber, BatchNumber) Values ('" & _ Me.txtCustNumber & "'. '" & Me.txtBatchNumber & "') " Currentdb.Execute strSQL, dbFailOnError How would the SQL Statement know which customers to update? Somehome I have to get the customer number from the updated invoices records (which have Cust No on them). Thanks. Dennis
|
Next
|
Last
Pages: 1 2 Prev: PDF/XPS export does not export bitmap contained in reports Next: Data split on pages |