By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Update Query

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.