Solved Create a Button to change a Field Value, Save the Record and Exit the Form?

[hbrehmer]

Am I asking a button command to do to much? I need the OnClick of a command button to change a bound field value (from 1 to 4), then save the form and exit the form. I have tried an embedded macro, events, etc. I just don't know enough about VBA coding to write the correct procedure. Can anyone help me? The form is based on a query. The field I'm updating is also hidden so the user doesn't see the value. Thank you!

 

[theDBguy]

Hi. What is the problem? Is the query updateable? Can you show us the code you're using? Are then any data validation code behind the form's BeforeUpdate event? Normally, if you just close the form, the data will be automatically saved to the table.

 

[hbrehmer]

Like I said, I think I'm complicating the event. I have a "Exit to Main Menu" button. When a user clicks this button, I need a field to be updated to a new value. I'm changing the status of a record, essentially. I don't want the user to update the field, I'd like it to happen behind the scenes. Yes, my query can be updated, but I have the form Recordset Type as Snapshot because I don't want the data on the main form to be editable, just the data in my subform. When the user completes the subform dataentry, they will click the close form button and the status of the main form query needs to update. Does that make sense?

 

[theDBguy]

Okay, if the data source (recordset) of the form is set to Snapshot, then the data can't be updated (I think). So, trying something like Me.ControlName=SomeValue might get an error. Is that what you were getting? If so, you may have to use an UPDATE query. For example:

CurrentDb.Execute "UPDATE TableName SET FieldName=SomeValue", dbFailOnError

Hope that helps...

 

[hbrehmer]

Doesn't the Update command change all fields' values? What code do I use to have it update only the record I'm working on?

 

[theDBguy]

Doesn't the Update command change all fields' values? What code do I use to have it update only the record I'm working on?

Oh, I'm sorry. Yes, you'll need a Where clause to pinpoint the current record. For example:

"UPDATE TableName SET FieldName=SomeValue WHERE ID=" & Me.ID

Hope that helps...

 

[hbrehmer]

It is still not working. I'm using your suggestion. Do I set the On Exit Event for the field I want to change? Or can I set it on the command button?

 

[theDBguy]

On the command button. You can also test it out in the Immediate Window, but use a literal value for the ID.

 

[Deleted member 147267]

I need the OnClick of a command button to change a bound field value

Closing a bound form saves the record, yes? So unless there's a problem with the form's recordsource not being updatable I must be missing something?

 

[Pat Hartman]

There is no need to run an update query.

Me.somefield = 1
DoCmd.RunCommand acCmdSaveRecord
Me.Close

 

[zeroaccess]

The complication is the use of Snapshot. It can be very useful, but in this case is conflicting with the way you want to use the form.

Maybe it's better to open as Dynaset and instead set the Allow Edits property (under Data) to No.

I do something similar, except I control for who's looking at the record and its status:

    Me.AllowEdits = (CurrentUserID = Me.txtUserID) And (Status = New)

So if you're not the one who created the record, and the status is no longer new, edits aren't allowed. This would need to be run On Load.

 

[hbrehmer]

Thank you! All of you! Yes, the Snapshot was blocking the save function. I have corrected the form to a Dynaset and now we are all good. I really need to take a class on writing code. It's been 25 years since I wrote my last program. As for blocking any edits, I just made all for the fields I didn't want a user to update as No Tab Stops and made the field blocks transparent, so the field is not highlighted on the screen. As for the allowing edits by currentuser id, I will have to program that at a later date. Still building this complicated ERP system. Again, thank you to all!

 

[zeroaccess]

Thank you! All of you! Yes, the Snapshot was blocking the save function. I have corrected the form to a Dynaset and now we are all good. I really need to take a class on writing code. It's been 25 years since I wrote my last program. As for blocking any edits, I just made all for the fields I didn't want a user to update as No Tab Stops and made the field blocks transparent, so the field is not highlighted on the screen. As for the allowing edits by currentuser id, I will have to program that at a later date. Still building this complicated ERP system. Again, thank you to all!

Allow Edits No is much easier, but if you want to do it per field, set the Enabled property to No and Locked Yes.

 

[isladogs]

Though it won't do any harm, if the Enabled property = No, there is no real need to set Locked =Yes as well

 

[zeroaccess]

Though it won't do any harm, if the Enabled property = No, there is no real need to set Locked =Yes as well

Locked Yes makes the field appear white as normal instead of gray. Depends what you're going for

 

[isladogs]

True but in my experience users often find it confusing when a textbox that looks enabled/editable is actually locked.
Better to have controls look disabled IMHO.

 

[Pat Hartman]

To prevent a user from updating a control, set its Locked property to Yes. Setting the background isn't going to stop them from updating the field if they click into it. TabStop just prevents tabbing into the field. It doesn't prevent clicking into it.

 

[isladogs]

Pat
I think you may have misread the last few posts which were discussing disabling controls so users cannot click in them.

 

[Pat Hartman]

You're correct. I thought hbrehmer said he was changing the background. However. I almost never set controls to enabled = No because I hate preventing the text from being scrapped so I am more likely to use background or color to indicate what is updateable. Nothing aggravates me more on a web page than being forced to scroll unnecessarily OR having the screen not scrapable.