help with update query please

[hullstorage]

i have a form that shows me a distinct value which is the invoice number for a given customer

as the same invoice number for that customer is in quite a few records
and the query only shows me 1 of the records how do i create an update query so that say on a form the invoice number is 6145 and the invoice status is "no" the update query changes all records with the invoice number 6145 invoice status to "yes"

hope this makes sense
this is how my form looks

customer invoice number Has been invoiced
========================================
SIMON 6145 NO
JOHN 6146 NO
LEE 6147 NO

Please bare in mind that the form only shows the invoice number of the first record and behind this invoice number there are many jobs

thanks

simon

 

[namliam]

If you have a field like this in multiple records that allways need to be the same... you have a faulty database design and you should go back to the drawing board!!

*sorry to be harsch*

A simple update query will do this for you... just create the update query in the designer and copy paste the SQL into code while replacing the Invoicenumber.

Currentdb.Execute "your update query where invoicenumber = " & Me.YourInvoiceNumber

Will do the trick for you.

Good Luck!

 

[MSAccessRookie]

i have a form that shows me a distinct value which is the invoice number for a given customer

as the same invoice number for that customer is in quite a few records
and the query only shows me 1 of the records how do i create an update query so that say on a form the invoice number is 6145 and the invoice status is "no" the update query changes all records with the invoice number 6145 invoice status to "yes"

hope this makes sense
this is how my form looks

customer invoice number Has been invoiced
========================================
SIMON 6145 NO
JOHN 6146 NO
LEE 6147 NO

Please bare in mind that the form only shows the invoice number of the first record and behind this invoice number there are many jobs

thanks

simon

Am I right in thinking that there is more than one entry in your invoice table with the same invoice number? That sounds more like an Invoice Details table to me. My Invoices come in multiple tables, the two most important of which are:

• tblInvoices - Contains a Primary key InvoiceNumber that is unique, and is used as a Foreign Key to all other Invoice related tables.
• tblInvoiceDetails - Contains a Primary Key InvoiceDetailID that is unique, and a Foreign Key InvoiceNumber referring to tblInvoices. Multiple Details can (and often do) refer to the same Invoice Number

As a good rule, Information that is frequently repeated in a table (either by content or by purpose), is often best removed from the table and replaced with a Foreign key to a record in a new table.

UPDATE: I see that the MailMan has beaten me to the post with similar advice. Good luck making it work