How do I assign a batch number to rcds for a print report

[Dennis]

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

 

[Duane Hookom]

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.

 

[Dennis]

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

 

[Duane Hookom]

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

 

[Dennis]

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

 

[Duane Hookom]

You just aren't providing enough information. I expect you might be able to
insert a record by selecting from tblInvoice which should have all the values
you need.

 

[Dennis]

Duane,

I'm sorry that I did not provide enough information. When I'm knee deep in
something, I forget that people can not read my mind. .

Ok, let's try this.

I have my invoice table

tblInvoice - 1 record per invoice. Could have as few a one to print (if
they need it immediately) or a couple of hundred rows.
Key : InvNo - autoassigned number
Flds: CustNo
BatchNo - If nothing in this field, it has not been printed.
InvoiceDate
Due Date


tblInvoiceDet - This tables has all of the line items for the invoice, but
is not part of this issue, but included for completeness.


tblBatchNo
key: BatchNo – automatically assigned number
Flds ReportCode – This field contains the name of the report. Once this
is working for invoices, I will use it for other one time reports.
RunDate – Date the report was run.
RunTime – Time the report was run.
UserName – Name of the person who ran the report.


tblCustomer-Batch
key: TrackingNo – system assigned automatic number.
Flds BatchNo – the batch number assigned to the batch.
CustNo – This is the customer number from the invoice file.


I have a function that opens the tblBatchNo table, updates the fields with
current date, time, user names, and writes the record to tblBatchNo. The
function returns the batch no that was automatically assigned from the row
update.


Here is the pseudo code for what I want to do.


BatchNo = GetNextBatchNo(“Invoicesâ€)
Select all rows from the tblInvoice with no BatchNo.
Write the BatchNo to the BatchNo field on the selected row in the tblnvoice
table.
Using the customer numbers from the selected rows in the tblInvoice table,
write a row to the tblCustomer-Batch table for every customer that was in the
tblInvoice table.

I will then select the rows in the tblInvoice table with the current BatchNo
and print the actual invoices. When the program is done printed, it will ask
the user if the invoices printed ok. If they did not, it will allows the
user to print the current BatchNo.


I think this is all the information I have. If there is something missing,
please point it out so I can provide it.

Thank you for your assistance. It is GREATLY appreciated.


Dennis

 

[Duane Hookom]

I would use your function to get the batch number and then update the records
in tblInvoice.

Dim lngBatchNum as Long
Dim strSQL as String
lngBatchNum = YourFunctionHere()
strSQL = "UPDATE tblInvoice SET BatchNo = " & lngBatchNum & _
" WHERE BatchNo is Null"
Currentdb.Execute strSQL, dbFailOnError

Then append records to tblCustomer-Batch.

strSQL = "INSERT INTO [tblCustomer-Batch] (BatchNo, CustNo) " & _
"SELECT DISTINCT BatchNo, CustNo FROM tblInvoice " & _
" WHERE BatchNo = " & lngBatchNum
Currentdb.Execute strSQL, dbFailOnError

 

[Dennis]

Duane,

Thanks, I'll give it a try tomorrow.

Dennis