Prev: PDF/XPS export does not export bitmap contained in reports
Next: Data split on pages
From: Duane Hookom on 21 Feb 2010 22:08
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.

--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

> 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
From: Dennis on 22 Feb 2010 00:36
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

From: Duane Hookom on 22 Feb 2010 15:06
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



--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

> 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
>
From: Dennis on 23 Feb 2010 01:00
Duane,

Thanks, I'll give it a try tomorrow.

Dennis
First  |  Prev  | 
Pages: 1 2
Prev: PDF/XPS export does not export bitmap contained in reports
Next: Data split on pages