Add records to two related tables

[kysterathome]

Hi guys,

Probably a simple thing, but my lid'l brain is lost:

Setup:
Two tables:
Table A with many names that feed (with dropdown) from Table B where the names are the primary key (I have of course related the two tables).

Problem:
In a form I want the user to use a dropdown when entering names. The dropdown feeds from table B (since each name would only occur once). However, the user can also add a NEW name. I would like this new name to be added once in Table A together with all the other items he/she fills out, but also in Table B, so that the new name is present in the dropdown for the next record that is entered.

I am grateful for any indication you can give me here. My boss would love me if I get this thing up and running ...

Chris

 

[toddtharp]

set the combo's limit to list to false.

On the after update event, check to see if the combo box has a value, if not not, insert the new value into the table using docmd.runsql()

Sub cmbOx After_Update

if isnull(me.cmbOx) then
   'Docmd.setwarnings false
   Docmd.RunSQL( "INSERT INTO tblLookup (Field1) SELECT """ & me.cmbOx.column(1) & """ as Field1;")
   'docmd.setwarnings true
   
   me.cmbOx.requery
end if

end sub

Replace tblLookup with your lookup table name and Field1 with the field name in that table getting the string. If you have autonumber, you need not include that in the INSERT statement, Access will increase it for you, if you have other fields in the lookup table, they will be null after this update. If you want to include them in the INSERT, add them with preceding commas to where Field1 is.

Remove the comment marks in the setwarnings true and false parts once you have it working.

Best of luck.

 

[kysterathome]

Thanks Toddtharp,

I am a complete beginner with codes, and... I can't get it to work. I do indeed have autonumbers, which may be the reason I can't get it to work - not sure what part you want me to remove. Here is the code:

Private Sub Currency_AfterUpdate()


If IsNull(Me.Currency) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO Currencies (Currency) SELECT """ & Me.Currency.Column(1) & """ as Currency;")
DoCmd.SetWarnings True

Me.Currency.Requery
End If

End Sub

What you say?

Chris