Access form field override

[GreyFog]

I have created a form with fields for part number and quantity that populate table "B". I want the field for quantity to display the current quantity listed for that part number from table "A". When the user opens the form, enters the part number then tabs to the quantity field they need to be able to accept the "default quantity" from table "A" or override it with a new quantity that will populate table "B". HELP!

 

[Corticus]

Hi Greyfog,

I would use a query for this. Create a new query, and drag over table A and include the fields of interest. You will need a new field for 'optional'.

I think you would want something like

Table A
partNo/qty/optional(T/F)

You won't actually be writing over your standard quantity, Access doesn't really work like that. You have to store both numbers (standard/optional). The field 'useoption' will determine which quantity to go with.

Your query should display all records from TableA (you must narrow this down using criteria).

If you base a form of this query, and have the criteria for this query look to a text box on the form, you can enter a part number (you'll need to add a requery action to the textbox on_change event to keep the form fresh) and have a listbox, or datasheet display the part number's qunatity. I think you will need a button that says something like 'add optional qty', and when clicked, creates a new record, checks optional, and sets the part number to the number in the text box. Now you have a blank field for quantity, and when filled, you will have a new record with quantity, optional = true, and the part number, which I think will cover your bases.

HTH,