Use form to update multiple records based on dropdown

[gillyr7]

I am new to Access, so I apologise if this is very easy. I have a table (shown below) where one field has duplicate values (male and female). I would like my form to select male or female in the drop down, and then enter in the amount in the text box. After that, I click the button and every value for my drop down selection is updated with the amount I typed in. Is this possible?



Original Table:


Form:


End Result:

[ranman256]

when user picks the gender, filter the list:

cboGender_afterupdate()
  If IsNull(cboGender) Then
  Me.FilterOn = False
Else
  Me.Filter = "[gender]='" & cboGender & "'"
  Me.FilterOn = True
End If
end sub

then clicking the button will update those record using a query

sub btnUpd_click()
    docmd.openquery "quUpd1GenderAmts"
end sub

the query quUpd1GenderAmts, would look like
update table set [amount] = forms!myForm!txtAmt where [gender] = forms!myForm!cboGender

[isladogs]

Hi & welcome to the forum

Yes it is easy - you need to create an update query (or the SQL equivalent) that runs either from a button click or the after update event of the Gender or Amount controls
Suggest you use a button click

In the query add the fields Gender & Amount.
In the update to row for Amount field, enter the control name Forms!MyFormName.txtAmount
For the Gender field, enter Forms!MyFormName.cboGender in the criteria row

SQL equivalent is (replace with your button name, table name, form & control names)

Private Sub Command1_Click()    
    CurrentDb.Execute "UPDATE MyTableName SET MyTableName.Amount = " & [Forms]![MyFormName].[txtAmount] & "" & _
        " WHERE (((MyTableName.Gender)='" & [Forms]![MyFormName].[cboGender] & "'));"

End Sub

EDIT - I typed this before seeing ranman's answer. Choose whichever you prefer

[gillyr7]

I tried to run this, but I keep getting the error: Microsoft Access can't find the field '|1' referred to in your expression. Do you know where that might stem from? Thanks.

Update Query:



Button Error:

[isladogs]

I had intended you would adapt my code to you own table, from and control names rather than repeat my own.
The query as written should work from the button click on the form or as suggested use the SQL version I posted before.

If you are still stuck, I can post a working example using that code.

[gillyr7]

I got it to work. Thanks for your help.