writing query data back into the table?

[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

 

[John W. Vinson]

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

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?

 

[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

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?

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 W. Vinson]

John,

No forms are used.

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.

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?

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.

 

[Geoff Cox]

Ummm... any user interaction with data should use forms. I sure hope you do
have at least some forms in your database!!

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.

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.

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

 

[John W. Vinson]

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

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.

 

[big osborne]

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