How do I move input data from one place in a form to a specified field in a table.

[rbowman40]

I am trying to design a form in which a category (field in a table) is selected from a combo box, then a value for that category is entered in a form field. I want that value to be placed in the field selected from the drop down list in the combo box. It would be like what happens when a bookkeeper selects an account field from a list and then enters a value for it. The value is then appended to the specified account field. I think this should be something easy, but I can't figure it out. I have made forms where an input field matches a table column, but I can't figure out how to specify the column (field) and then have the value go there. I would like a user to have just a couple of places to deal with in data entry instead of a massive form. I am pretty inexperienced with Access. Maybe I know only enough to be dangerous. :-/ Thanks.

I have Access 2003 running on XP Pro

[MMcCarthy]

I am trying to design a form in which a category (field in a table) is selected from a combo box, then a value for that category is entered in a form field. I want that value to be placed in the field selected from the drop down list in the combo box. It would be like what happens when a bookkeeper selects an account field from a list and then enters a value for it. The value is then appended to the specified account field. I think this should be something easy, but I can't figure it out. I have made forms where an input field matches a table column, but I can't figure out how to specify the column (field) and then have the value go there. I would like a user to have just a couple of places to deal with in data entry instead of a massive form. I am pretty inexperienced with Access. Maybe I know only enough to be dangerous. :-/ Thanks.

I have Access 2003 running on XP Pro

You will need to create an command button and in the On Click event put something like the following:

 
Private Sub commandBtnName_OnClick()
 
  DoCmd.RunSQL "UPDATE TableName SET " & Me.ComboboxName & _
      "=" & Me.textboxName & WHERE <you will have to identify the record here that you want to be updated> ;"
 
End Sub
 
 

[rbowman40]

Thank you. I'll try that, although I know next to nothing about VB.

[MMcCarthy]

Thank you. I'll try that, although I know next to nothing about VB.

Make an attempt and if it doesn't work post the code here and we will try to correct it for you.

[rbowman40]

OK . . . I created a command button on my form named Go. The idea is to select an acct from a combobox named Account Name and then enter an amount in a textbox named Amount which is to be transferred to an account field (matching Account Name) in a table named TransDistributi on. Here's the code I wrote following your example as best I could:

Private Sub Go_OnClick()
 
    DoCmd.RunSQL "UPDATE Transdistribution SET" & Me.Account_Name & "=" & Me.Amount & Me.TransDistribution
 
End Sub
 

I assume that in the OnClick property for Go, I am to choose [Event Procedure] or am I to name it? I tried "Private Sub Go_OnClick()" but Access thought I was trying to run a macro. Is there some way to do this with a macro?

[NeoPa]

The code looks mainly good.
However :
There should be a space after SET
There needs to be single-quotes around the new data
The name of the field (if there's any possibility of it containing spaces or clashing with reserved words) should have [] around it.
Try this :-

Private Sub Go_OnClick()
    DoCmd.RunSQL "UPDATE Transdistribution SET [" & Me.Account_Name & "]='" & Me.Amount & Me.TransDistribution & "'"
End Sub

[NeoPa]

Just a note.
I don't know what you were trying to do with the

"=" & Me.Amount & Me.TransDistribution

bit so I just copied it straight across.
In my version I added quotes as I saw you had the '&' bit in there for string concatenation.
If this was not intended and you wanted just the value of the amount, then lose the quotes AND the ' & Me.TransDistrib ution' bits.

[MMcCarthy]

OK . . . I created a command button on my form named Go. The idea is to select an acct from a combobox named Account Name and then enter an amount in a textbox named Amount which is to be transferred to an account field (matching Account Name) in a table named TransDistributi on. Here's the code I wrote following your example as best I could:

Private Sub Go_OnClick()
 
DoCmd.RunSQL "UPDATE Transdistribution SET Account=" &  Me.Amount & " WHERE Account_Name=" & Me.Account_Name ";"
 
End Sub
 

This code assumes that the table name is Transdistributi on and it contains two fields named Account which will be set with the Amount value and Account_Name which will equal the combo box value in the record being updated.

I assume that in the OnClick property for Go, I am to choose [Event Procedure]

yes

[rbowman40]

OK.... the code now looks like this:

Private Sub Go_OnClick()
    DoCmd.RunSQL "UPDATE Transdistribution SET [" & Me.Account_Name & "]= & Me.Amount"
End Sub

Did I do that right? Maybe I'm just way in over my head. I have some JPEG's of the table, form, and properties box for the go button. I will try to send them in a subsequent post, but have not quite figured out the "insert image" procedure the button above not withstanding. I wiped out this whole message once trying to insert the images, so I will send this separately. Thanks.

[rbowman40]

Trying to insert the images. Copy and Paste doesn't seem to work.