Update a table field programatically

[Ron]

I have 2 tables. Table1 contains Customer Information
Table2 contains Vendor Information. The fields in each
table are as follows..

Table1 Table2
Customer ID InstallCo
Name Contact Name
Zip Phone
Installer
Phone

What I need to do using a button on a form which displays
information from Table2, when the user clicks on a button
called assign it will prompt the user to enter a zipcode.
Once the user enters a zipcode the Installer field in
Table1 where all the records in table1 have zipcodes that
match what the user has entered, gets updated with
InstallCo information. I need help with this code. Thanks
in advance

 

[Dirk Goldgar]

I have 2 tables. Table1 contains Customer Information
Table2 contains Vendor Information. The fields in each
table are as follows..

Table1 Table2
Customer ID InstallCo
Name Contact Name
Zip Phone
Installer
Phone

What I need to do using a button on a form which displays
information from Table2, when the user clicks on a button
called assign it will prompt the user to enter a zipcode.
Once the user enters a zipcode the Installer field in
Table1 where all the records in table1 have zipcodes that
match what the user has entered, gets updated with
InstallCo information. I need help with this code. Thanks
in advance

If I understand you correctly, what you want to do is run an update
query on Table1 to set Installer = the current InstallCo in all records
for which Zip = the zip code entered by the user. The code for a button
named "cmdAssign" might look like this:

'---- start of example code -----
Private Sub cmdAssign_Click()

On Error GoTo Err_cmdAssign_Click

Dim db As DAO.Database
Dim strZip As String

If IsNull(Me.InstallCo) Then
MsgBox "No Installer!"
Exit Sub
End If

strZip = InputBox("Enter zip code:")
If Len(strZip) = 0 Then
MsgBox "No zip code entered."
Exit Sub
End If

Set db = CurrentDb

db.Execute _
"UPDATE Table1 SET Installer = " & _
Me.InstallCo & _
" WHERE Zip = '" & strZip & "'",
dbFailOnError

MsgBox InstallCo & " was assigned as the installer for " & _
db.RecordsAffected & " customers."

Exit_cmdAssign_Click:
Set db = Nothing
Exit Sub

Err_cmdAssign_Click:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_cmdAssign_Click

End Sub
'---- end of example code -----

Note: the above code assumes that InstallCo and Installer are both
numeric fields. If they are text, the line that builds the UPDATE
statement must be modified slightly, to enclose the value of InstallCo
in quotes.