Replacing Critereia with code (1 Viewer)

[xveganx007]

i have to create a form that you can choose an organization from a combo box and then type in a new name that you would like to call the organization and also change all of the organizations with that name in the table. how would i go about doing this?
thanks in advance,
matt stewart

 

[Hoa Le]

First, create a form named fUPDATE with a combo box whose lookup value is the subject table's company name; name this combo box txtCompanyName. Next, on the same form create a textbox named txtNewCompanyName and a button named cmdUpdate. Behind the OnClick event of this button, attach:

'--------------
Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

Dim stDocName As String

stDocName = "qUpdate"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdUpdate_Click:
Exit Sub

Err_cmdUpdate_Click:
MsgBox Err.Description
Resume Exit_cmdUpdate_Click

End Sub
'--------------
Finally, create an update query named qUpdate with the subject table. Bring the field [CompanyName] to the design grid, then type in row Update to: Forms!fUPDATE!txtNewCompanyName
then type in row Criteria:
Forms!fUPDATE!txtCompanyName

To test this form, pick a company name from the combo box, then type in a new name in txtNewCompanyName and click button. Voila`, all are updated accordingly!

HTH
Hoa Le

 

[xveganx007]

Thanks Hoa Le,
it worked great, you're a lifesaver.
matt stewart