Update Existing Record in One Table with Key from Another Table

[mkling]

I have an Event Table with an Index Key name 'Event ID' I have a sub key name 'Vendor ID' which I link to a Vendor table. In the Event form I have a combo box which displays a list of vendors from a prepopulated table and form named "Vendors". When I select a vendor, I want to update the Event table's 'Vendor ID' with the Index Key called [Vendor ID] from the Vendor table.



In the event form, I have a combo box named [ECompany Combo]. The rowsource is SELECT Vendor.[Company/Organization], Vendor.[Vendor ID] FROM Vendor;

This is the code I can't get to work:

Private Sub ECompany_Combo_AfterUpdate()
[event].[vendor id].value = [forms]![event].[ecompany combo].column(1)
End Sub

Thanks in advance

[ketbdnetbp]

Mkling - Assuming the Event table is the recordsource for the event form and [vendor ID] is a control on the Event Form, try

Me![vendor id] = [forms]![event]![ecompany combo].column(1)

Hope this helps,

Jim

[mkling]

Thanks Jim. In order to get it to "almost" work, I added the event.[vendor id] to the form and made it not visable. When I say it almost worked, what happened was it inserted a new record into the event table.

What I would like to do is update the record I am currently working on.

I created several queries to do this to other fields. One is Point of Contact Phone1 [POC Phone 1]. The SQL is:

UPDATE Event SET Event.[POC Phone 1] = [Forms]![Event].[QPOC Phone 1]
WHERE (((Event.[Event Name])=forms!Event![QEvent Name Combo]) And ((Event.[Event Date])=Forms!Event![QEvent Date Combo]));

Here the table is updated when I key in information. My challenge is updating the current record from another table. I use the event name and date of the event in my query to ensure i update the record I want.

Can I create a query which uses the [forms]![event].[ecompany combo].column(1) and the "WHERE" criteria?

[mkling]

I think I got it solved. Here is my code:

Private Sub ECompany_Combo_AfterUpdate()
Me.Vendor_ID = Me.ECompany_Combo.Column(1)
DoCmd.SetWarnings False
DoCmd.OpenQuery "Update Event with Vendor ID Query", acViewNormal
DoCmd.SetWarnings True

Here is my query:

UPDATE Event SET Event.[Vendor ID] = [Forms]![Event]![Vendor ID]
WHERE (((Event.[Event Name])=[forms]![Event]![QEvent Name Combo]) AND ((Event.[Event Date])=[Forms]![Event]![QEvent Date Combo]));

I had to have [event].[vendor id] on the form like you suggested. I hid it and it seems to be working.

Thank you