Set Value in Continuous Form (1 Viewer)

[Gigitty]

Hi guys.

I have a yes/no (checkbox) I want to set to 'No' each time the form loads. I've tried {me.fieldname = No} in the OnLoad event. This only works for the first record on teh form. Anyone know how I can apply this to every record on the form? VBA beginner here..

thankyou in advance.
Gigitty

 

[June7]

Can execute an UPDATE action SQL on the entire table or if the form is filtered apply same filter criteria in the UPDATE.
In the form Open or Load event:

CurrentDb.Execute "UPDATE tablename SET fieldname = False"

 

[Pat Hartman]

In the load event, the only record you would reference is the first one. What is the purpose of updating the records when you view them?

 

[Gigitty]

In the load event, the only record you would reference is the first one. What is the purpose of updating the records when you view them?

It's being used as part of a Inventory and Ordering System and I want to reset this yes/no box on each record every time I open this [New Order] form so the user then clicks the yes/no box for each item they wish to order.

 

[theDBguy]

It's being used as part of a Inventory and Ordering System and I want to reset this yes/no box on each record every time I open this [New Order] form so the user then clicks the yes/no box for each item they wish to order.

Is the table for this form in the backend?

 

[Pat Hartman]

I don't understand why the y/n box is stored in the record. What if multiple people are creating an order at the same time, won't the details get combined? If you want an easy way to pick an item from a list and add it to an order, use the double click event of some field and append a row to the order details table. I don't use this method because I prefer to use bound forms and not run action queries to do thins I can do with a form.

I would have a main form for the order and a subform for the items being ordered. The Item row would have a combo that selects the item being ordered. The unit price should be one of the columns in the combo's RowSource. in the AfterUpdate event of the combo, copy the unit price from the combo to the item record. So all the user has to do is pick an item and enter a quantity. your code in the AfterUpdate event of the combo and Access do the rest of the work. This is a much simpler solution to implement.

 

[Gigitty]

Is the table for this form in the backend?

yes it is.

 

[theDBguy]

yes it is.

In that case, what would happen if another person is currently using that form when you open yours in your frontend? All their work will disappear, right?

 

[Gigitty]

In that case, what would happen if another person is currently using that form when you open yours in your frontend? All their work will disappear, right?

It's actually for an individual user (only 1 staff member), but I acknowledge your point. The idea is that as multiple items are ordered on each order, once the 'Add To Order' box is ticked and all items have been selected, the user then clicks the 'Save Order' button which does everything ni the background (see attached doc)

 

[Gigitty]

As an extra point of note, the end user wants to be able to see current 'Floor Stock', 'Base Stock' and 'Backorder' information (all of which is calculated in the background) for all stock items in the continuous form of which there are over 2500 which is why I've gone down this path. Couldn't think of an easier solution..

 

[Pat Hartman]

Couldn't think of an easier solution..

Then try what I suggested. It uses only a line or two of code and doesn't require running any action queries. The combo can show the calculated values. Or if you still prefer to pick from a form rather than a combo, use the double click method I suggested.

Maybe today you have only a single user but what if tomorrow you have two? Don't design a method that prevents you from being able to support multiple users at one time. Think of it as defensive programming.

 

[Gigitty]

Many thanks Pat.

 

[Pat Hartman]

You're welcome