Change field in table when button on form clicked

[mousemat]

I have a problem with changing / updating a record in the table from un an unbound form.

Heres what I have.

A form whereby invoice details are entered, upon clicking the command button, the data is sent to the report and a new record in the INVOICES table is created. This works fine.

What I need to do now is to CHANGE the CUSTOMERTYPE field in the CUSTOMERS table from PROSPECT to CUSTOMER. The default for the field is set to PROSPECT when entering new records.

I am updating the Invoice table with the following code:

Public Sub AddInvoice()
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tbl_Invoices", dbOpenDynaset)

With rst
.AddNew
rst!CustomerID = CustomerID
rst!Date = Date
rst!Description = Description
rst!Amount = Amount
rst!InvoiceNumber = InvoiceNo
rst!CompanyName = CompanyName
.Update
End With

End Sub

How can I update the CUSTOMERTYPE field of the CUSTOMERS Table with the new data?

 

[bradcccs]

Mousemat,

Have you considered using an update query.

You would be able to use the current CustomerID as the qualifying paramater and update the CustomerTable.

Just a thought.

Brad.

 

[theprez]

bradcccs is correct. You can create an update query that updates type to Customer where customerid = customer name or, since you are familiar with recordsets,

Set rst = CurrentDb.OpenRecordset("customers", dbOpenDynaset)

With rst
.findfirst("customerid " = customerid)
if not .nomatch
.edit
rst!customertype = "Customer"
.update
else
msgbox "no customer"
end if
End With

Check the syntax on the findfirst

 

[mousemat]

Hi Bradccs & Theprez

Thanks for your responses.

I tried the update query first but couldn't get the syntax correct so i went along the recordset route.

Here is the code I used for the FindFirst:

.FindFirst "CustomerID= " & CustomerID & ""

All is working well thanks.