Code to use a Command Button to update a field in a record.

[Imloyd76]

I have a form with a "Find" command button that pulls the information from a record in my database. One of those fields is a "Location" field. I have a command button on the form that I want to "Update" that "Location" field. So for example if I use my find button to pull a record and that items "Location" states that it's in row 45...I want to change the text in the box to whatever the new location is and hit the "Update Record" button and it only update that "Location" field in that record. Thanks in advance for any help.

[June7]

Where does the location value come from?

As long as that record has focus, maybe simple as: Me!Location = value

[Imloyd76]

Where does the location value come from?

As long as that record has focus, maybe simple as: Me!Location = value

I was hoping that would work, however, when I use that it clears out the value in that field completely rather than updating it with the new information.

[June7]

Show your code. How is the value derived?

[Imloyd76]

Show your code. How is the value derived?

Here's my code:

Option Compare Database

Private Sub cmdUpdateLocation_Click()
Me!Location = Value

End Sub
Private Sub cmdFindBox_Click()
Form.SetFocus
Form.Filter = "BoxID=" & "'" & Text25 & "'"
Form.FilterOn = True
If Location = "Shipped" Then
MsgBox ("This Item has already been Shipped")
Form.Filter = "BoxID='0'"
Form.FilterOn = True
Text25.SetFocus
Else: Location.SetFocus
End If

End Sub


Private Sub Form_Load()
Form.Filter = "BoxID='0'"
Form.FilterOn = True
End Sub

[June7]

Nothing in that code actually changes value of Location field.

Include code to modify Location field value. Step debug.

If BoxID is a number datatype field, don't use apostrophe delimiters in the Filter expression.

[Imloyd76]

Nothing in that code actually changes value of Location field.

Include code to modify Location field value. Step debug.

If BoxID is a number datatype field, don't use apostrophe delimiters in the Filter expression.

The Filter expression works fine as the BOXID's are Alpha Numeric. I'm only having problems with the Update of the Location field.

[June7]

There is no code in that procedure that modifies the Location field. Perhaps you need something like:

Else
Me.Location.SetFocus
Me.Location = "some value here"
End If

[Imloyd76]

There is no code in that procedure that modifies the Location field. Perhaps you need something like:

Else
Me.Location.SetFocus
Me.Location = "some value here"
End If

You've been very helpfull. I'm still running into problems though. I want the value of "Location" to change if I hit the cmdUpdate button. And more importantly I need it to update with whatever I type in that "Location" field on the form. So far all it's doing is removing the contents of that field instead of replacing the contents with the new data.

[R_Badger]

To clarify:

You have a form, which utilises your code to filter to the required record.

You then want to change that records location field, by inputting new data into it, and then hitting a command button.

I have a question; Can you currently type into the Location field?

It may be the property is set to locked, in which case, you don't require a command button necessarily, you just need to open the form in design view, right click the control, click properties, then click data, locked=no.

If it wont allow you to alter the data, there may be data validation applied to the field by the form control.

If neither of these help, it would be helpful if you cost attach a copy of your database. (Strip out any data you wouldn't write on the back of a postcard) and instead insert some dummy data so we have an idea of what we are looking at, as without looking at your form with this, we are a little blind.

If you can't post it, I have a couple of other thoughts

[Imloyd76]

To clarify:

You have a form, which utilises your code to filter to the required record.

You then want to change that records location field, by inputting new data into it, and then hitting a command button.

I have a question; Can you currently type into the Location field?

It may be the property is set to locked, in which case, you don't require a command button necessarily, you just need to open the form in design view, right click the control, click properties, then click data, locked=no.

If it wont allow you to alter the data, there may be data validation applied to the field by the form control.

If neither of these help, it would be helpful if you cost attach a copy of your database. (Strip out any data you wouldn't write on the back of a postcard) and instead insert some dummy data so we have an idea of what we are looking at, as without looking at your form with this, we are a little blind.

If you can't post it, I have a couple of other thoughts

You are exactly correct in your description of my needs. As far as the "Location" field goes that is not locked. Everything else that populates the filter is locked but as I am using this form to only change the location field it is not. I will try and attach a copy of my database. Thanks again for the help.

[R_Badger]

If the field isnt locked then it should be a simple case of changing the field, if that's not working you can try adding a commandbutton with the onclick event with a simple entry:

Private Sub Command0_Click()
RunCommand acCmdSaveRecord
End Sub

If I recall correctly then that should work

[Imloyd76]

That's exactly what I needed....Thank you so much.