Update Query

Clive Moss

Anyone able to help?

I want to update a specified number of records that match a criteria. If I
use an update query it alters all the records that match - I need to alter
only a variable number. Is there a way to do this?

Specifically:

I have a list of available serial numbers that will satisfy an order. Some
of those will have been previously allocated to another order, those that
have not, have 0 in a field called Allocate_To the ones that have been
allocated have an order ID in that field.
I have then a new order to allocate for a qty of (say) 10 and I therefore
need to insert the relevant order ID into 10 records.
An Update query allocates the order ID to all records where Allocate_To = 0.
How do I restrict this action to the specified number?

Hope this makes sense

TIA

Clive Moss

Nov 12 '05 #1

PC Datasheet

Clive,

In your Update query set the Allocate_to field criteria to 0. Click anywhere in
the window that displays the objects your query is based on. A popup menu will
appear, go to the Top property and enter 10. When you run the query, the first
10 records where Allocate_To is 0 will be updated.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Clive Moss" <cl***@REMOVEinstant-image.co.uk> wrote in message
news:%O*********************@news-text.cableinet.net...

Anyone able to help?

I want to update a specified number of records that match a criteria. If I
use an update query it alters all the records that match - I need to alter
only a variable number. Is there a way to do this?

Specifically:

I have a list of available serial numbers that will satisfy an order. Some
of those will have been previously allocated to another order, those that
have not, have 0 in a field called Allocate_To the ones that have been
allocated have an order ID in that field.
I have then a new order to allocate for a qty of (say) 10 and I therefore
need to insert the relevant order ID into 10 records.
An Update query allocates the order ID to all records where Allocate_To = 0.
How do I restrict this action to the specified number?

Hope this makes sense

TIA

Clive Moss

Nov 12 '05 #2

Clive Moss

Thanks for prompt reply - I should have said that I am trying to do this
from a procedure fired by an OnClick action

Clive

"PC Datasheet" <sp**@nospam.com> wrote in message
news:%b******************@newsread1.news.atl.earth link.net...

Clive,

In your Update query set the Allocate_to field criteria to 0. Click anywhere in the window that displays the objects your query is based on. A popup menu will appear, go to the Top property and enter 10. When you run the query, the first 10 records where Allocate_To is 0 will be updated.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Clive Moss" <cl***@REMOVEinstant-image.co.uk> wrote in message
news:%O*********************@news-text.cableinet.net...

Anyone able to help?

I want to update a specified number of records that match a criteria. If I use an update query it alters all the records that match - I need to alter only a variable number. Is there a way to do this?

Specifically:

I have a list of available serial numbers that will satisfy an order. Some of those will have been previously allocated to another order, those that have not, have 0 in a field called Allocate_To the ones that have been
allocated have an order ID in that field.
I have then a new order to allocate for a qty of (say) 10 and I therefore need to insert the relevant order ID into 10 records.
An Update query allocates the order ID to all records where Allocate_To = 0. How do I restrict this action to the specified number?

Hope this makes sense

TIA

Clive Moss

Nov 12 '05 #3

PC Datasheet

I'm Not sure what you are meaning by your response but you can run the Update
query in the OnClick event with the code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "NameOfYourUpdateQuery"
DoCmd.SetWarnings True

Steve
PC Datasheet
"Clive Moss" <cl***@REMOVEinstant-image.co.uk> wrote in message
news:pJ*********************@news-text.cableinet.net...

Thanks for prompt reply - I should have said that I am trying to do this
from a procedure fired by an OnClick action

Clive

"PC Datasheet" <sp**@nospam.com> wrote in message
news:%b******************@newsread1.news.atl.earth link.net...

Clive,

In your Update query set the Allocate_to field criteria to 0. Click

anywhere in

the window that displays the objects your query is based on. A popup menu

will

appear, go to the Top property and enter 10. When you run the query, the

first

10 records where Allocate_To is 0 will be updated.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
www.pcdatasheet.com
"Clive Moss" <cl***@REMOVEinstant-image.co.uk> wrote in message
news:%O*********************@news-text.cableinet.net...

Anyone able to help?

I want to update a specified number of records that match a criteria. If I use an update query it alters all the records that match - I need to alter only a variable number. Is there a way to do this?

Specifically:

I have a list of available serial numbers that will satisfy an order. Some of those will have been previously allocated to another order, those that have not, have 0 in a field called Allocate_To the ones that have been
allocated have an order ID in that field.
I have then a new order to allocate for a qty of (say) 10 and I therefore need to insert the relevant order ID into 10 records.
An Update query allocates the order ID to all records where Allocate_To = 0. How do I restrict this action to the specified number?

Hope this makes sense

TIA

Clive Moss

Nov 12 '05 #4