Update table using VBA

[Guest]

Hi,

I am trying to use VBA to update a field in a table - so far unsuccessfully.

My table has two fields, ContactName and ContactID.
My form only displays ContactName and this can be changed using a ComboBox
which is populated with a list of names (shown) and ID numbers (hidden).
Using the AfterUpdate event, I want to update the ContactID field for the
current record with the ID number for the Contact that was just selected.

So far I've got:
Private Sub cmbContactName_AfterUpdate()
Dim strSQL As String
strSQL = "UPDATE tblQuoteHeader " & _
"SET tblQuoteHeader.ContactID = '" & Me.cmbContactName.Column(1)
& "' " & _
"WHERE tblQuoteHeader.QuoteNumber = '" & Me.QuoteNumber & "';"
DoCmd.RunSQL (strSQL)
End Sub

Now this seems to work in itslef. However, it fails to update the record as
the form has Record Locking set to 'Edited Record'. Is there anyway round
this?

Regards
Michael

 

[Steve Schapel]

Michael,

I assume the Row Source of the combobox is based on another table that
contains the ContactID and ContactName?

Your desire to do this update operation would indicate a database design
flaw. It would generally be regarded as an invalid procedure, as it
flouts a basic database design principle. The tblQuoteHeader table
should not have the ContactName field in there in the first place. The
data entered via the combobox should be the ContactID. As such, the
Bound Column of the combobox should be the ContactID. If you need the
ContactName, then this should be obtained by a query which references
the main Contacts table (whatever it is called). Or if you need to
actually see both values on this form, there are a number of approaches
- this article may be of interest:
http://accesstips.datamanagementsolutions.biz/lookup.htm

By the way, your code would indicate that the ContactID field is a text
data type... is that right?

 

[J_Goddard via AccessMonster.com]

If your form is bound to the table tblQuoteHeader, and the record source of
the combo box is another table containing ContactName and ContactID, then all
you need to do is bind the combo box to the contactID field of tblQuoteHeader,
using the ContactID field of the combo box. You don't need to use the SQL.

HTH

John