Use a form to update record after user hits button

[AntMac]

This seems too easy but I'm not sure how to do it. So, I have a form designed to calculate values and add new records when the user clicks a button on the page. What I would like is to have a similar concept to review and update (if needed) a record. So, I have a form with all unbound text boxes or combo boxes. What I'm trying to do now is when you open the form, get prompted for 2 values which determine which record is the one in question. Filter to only that record. Then populate all the boxes with the appropriate data from the record. But I want this to be done in a way that the user can see the data and if they make a change to a field they have to hit a button in order to actually make the change. So, the change isn't done automatically when they leave the field. Switching the text boxes to bound seems to make the update automatic which is not what I want. The form does calculations so after seeing the calculations the user might not want to save the update.

 

[dmt32]

Hi,
Speaking personally, I would not link form controls to the worksheet.
Would be helpful to forum if you publish the code you have with your current form – then some here may be able offer suggestions to achieve what you want.

Dave.

 

[AntMac]

I built the original form as all text boxes and one combo box based on that idea. As far as creating the update one, so far all I have done is create a new query and told the form to use that query. The query prompts and filters the table to the one record in question. But I don't know how to match the fields in the table to the text boxes on the form.

 

[dmt32]

Hi,
You will have to forgive me – It's way past my bedtime & just realised that this is an Access question where I am very rusty so not really able to assist with your problem.
My sincere apologies to you but hopefully, someone will step in to assist you.

Dave

 

[Micron]

As with just about anything, there's more than one way. One would be to copy the record to a temp table which you also bind the form to. You perform the edits and do the calculations. If you don't like the outcome, either wipe the temp and start over or continue fiddling with record values. Once you're happy with the edits, either append (if it's a new record) or update the source table based on the temp values. How to do that can be derived from the next suggestion as I think it would apply to both. When done, wipe the temp. I presume the db is not shared, or if it is (in which case it should be split) the temp should be in the front end (FE). To have 2 or more current users in this process on one temp table means that when it's wiped, guess who also loses their data?

Another
If the form fields have to be unbound but the data is based on a source table, the process might be something like this:
Once the two selections are done, I presume in your case they will click a button. The button click event would
- validate that the two inputs are there
- if not, message the user and terminate
- if OK, the code continues and you need one of the following
* an update (we are updating?) query that references each of the form fields. In query design, you'd see Forms!frmYourFormName.YourControlName for each query field and those values would be appended (or updated as the query case may be) to the source table.
OR
*you construct an append or update sql statement in code and execute it. Should you take this route, you're basically doing the same thing but without a stored query. You would also want to prevent the warning messages about adding/altering data. I'd recommend the .Execute method of the database object. This you cannot employ if you use a query; you'd have to cycle warnings on/off, which could leave them off for the entire session if code fails in a particular fashion.
Maybe that's enough for you to deal with for now...

 

[Micron]

This you cannot employ if you use a query;

I had a bit of brain flatulence there. That statement is inncorrect.

 

[AntMac]

From your choices I think I like the temp table one as it is the most straight forward for me. I'll give it a shot. Thank You