After event macro to update another table using a drop down list field as criteria

[oldlearner]

Hi There. I Am new to access database design (teaching myself from a book) and am wondering if anyone can help please. I have a table with a dropdown list field. I want to update some fields (create a record) into another table, but only if they match the criteria for the item I have selected from that list. example: table1 has Id, FirstName, Surname and programme fields (the programme field is a drop down values list ie: programme1, programme2, programme3 etc.. Table2 has FirstName, Surname fields along with other fields. I want to drop FirstName and Surname from table 1 into table 2 using an after event macro but only the names from people who have been selected as programme2 from the drop down values list.



Any ideas or am I going about this the wrong way.

[June7]

Use VBA, not macro.

If Me.comboboxname.Column(x) = "programme2" Then
CurrentDb.Execute "INSERT INTO table2(FirstName, Surname) Values('" & Me.comboboxname.Column(x) & "', '" & Me.comboboxname.Column(x) & "')"
End If

The x is reference to combobox column index. Index begins with 0.

Why are you saving name parts instead of ID? Should not duplicate name into multiple tables. Names make poor unique identifiers.

[oldlearner]

Thanks for the info moderator. I am not familiar with VBA yet (haven't read that bit of the book yet). I seem to have managed to get a macro to work as I want using the IF Then Else and set field instructions incorporating the expression builder. As you rightly say I needed to and have used the ID field as a unique identifier.

Thanks again

[June7]

So this is resolved? Or you need more info about VBA?

Macro will not be able to run the dynamic INSERT sql action. Macro can open and run query objects but query cannot reference combobox column index. However, if you save the ID instead of name parts, and the value of the combobox is the ID field, then a query can reference the combobox and a macro could run the query.

[oldlearner]

Thanks June
Used a combo box as suggested and is acting as required now.