writing query data back into the table?

  • Thread starter Thread starter Geoff Cox
  • Start date Start date
G

Geoff Cox

Hello,

I wrote this code some while ago and cannot see how to correct it!

At the moment when the query is run the invoice number is created but
it is not written into the table from which the data is taken.

How do I do this?

invoice_number: "rbest_" & Format(Date(),"mm-yy") & "_" &
DCount("*","[query-renewals]","[ID] <=" & [ID])

Cheers

Geoff
 
Hello,

I wrote this code some while ago and cannot see how to correct it!

At the moment when the query is run the invoice number is created but
it is not written into the table from which the data is taken.

How do I do this?

invoice_number: "rbest_" & Format(Date(),"mm-yy") & "_" &
DCount("*","[query-renewals]","[ID] <=" & [ID])

Cheers

Geoff
Click to expand...

It wouldn't be; you're just dynamically calculating a field here for display
purposes. As written the invoice number will change every month and every time
you add a new record.

What's the context? Are you just opening a query datasheet? Do you have a Form
to enter new invoices?
 
Hello,

I wrote this code some while ago and cannot see how to correct it!

At the moment when the query is run the invoice number is created but
it is not written into the table from which the data is taken.

How do I do this?

invoice_number: "rbest_" & Format(Date(),"mm-yy") & "_" &
DCount("*","[query-renewals]","[ID] <=" & [ID])

Cheers

Geoff
Click to expand...

It wouldn't be; you're just dynamically calculating a field here for display
purposes. As written the invoice number will change every month and every time
you add a new record.

What's the context? Are you just opening a query datasheet? Do you have a Form
to enter new invoices?
Click to expand...

John,

No forms are used.

The idea is to run the query im the middle of each month to give
subscribers to the web site a reminder that their subscription must be
renewed by the end of the current month.

I use mailshot software to email these subscribers and the email
contains an invoice using the invoice number created by the query.

I can of course simply export the query and have a record of the
invoice numbers for each month's set of users but it would be nice to
automatically add the invoice numbers to the table from which the data
is taken.

Can this be done?

Cheers

Geoff
 
John,

No forms are used.
Click to expand...

Ummm... any user interaction with data should use forms. I sure hope you do
have at least some forms in your database!!
The idea is to run the query im the middle of each month to give
subscribers to the web site a reminder that their subscription must be
renewed by the end of the current month.

I use mailshot software to email these subscribers and the email
contains an invoice using the invoice number created by the query.
Click to expand...

The invoice number shown is dynamically calculated, based on today's date and
the count of records in the table. As such it will ipso facto be different,
certainly month to month, and probably run to run if new records are added.
I can of course simply export the query and have a record of the
invoice numbers for each month's set of users but it would be nice to
automatically add the invoice numbers to the table from which the data
is taken.

Can this be done?
Click to expand...

If (and it's probably a Bad Idea) you want to store only the latest invoiceID
in the table, create an Update query and update the InvoiceID field to this
expression. If (perhaps a bit better idea) you want to maintain a record of
all invoices sent, not just the last one, create a new Invoices table, and use
this expression in an Append query to add records to it.
 
Ummm... any user interaction with data should use forms. I sure hope you do
have at least some forms in your database!!
Click to expand...

John,

I do use a form to add new records - I meant that forms are not used
in the invoice calculation query process.
The invoice number shown is dynamically calculated, based on today's date and
the count of records in the table. As such it will ipso facto be different,
certainly month to month, and probably run to run if new records are added.
Click to expand...

the number is the number associated with each of the records where
renewal should happen in the current month ..
If (and it's probably a Bad Idea) you want to store only the latest invoiceID
in the table, create an Update query and update the InvoiceID field to this
expression. If (perhaps a bit better idea) you want to maintain a record of
all invoices sent, not just the last one, create a new Invoices table, and use
this expression in an Append query to add records to it.
Click to expand...

yes, I would like to have a record of all invoice numbers used so a
new table is the way to go ...

Thanks

Cheers

Geoff
 
yes, I would like to have a record of all invoice numbers used so a
new table is the way to go ...
Click to expand...

Cool. You should be able to modify your existing query into an Append query to
do this very easily; if you have trouble post back.
 
Geoff Cox said:
Hello,

I wrote this code some while ago and cannot see how to correct it!

At the moment when the query is run the invoice number is created but
it is not written into the table from which the data is taken.

How do I do this?

invoice_number: "rbest_" & Format(Date(),"mm-yy") & "_" &
DCount("*","[query-renewals]","[ID] <=" & [ID])

Cheers

Geoff
Click to expand...
 
Back
Top