Create new record in diff table

[Allison]

Access 2003, Win XP

What code can I use to add a record to one table when using a form based off
of a different query/table?

Details: I want to use Allen Browne's "Modelling Human Relationships"
structure http://allenbrowne.com/AppHuman.html to set up some contact info.

The entry form is tied to a query based off of the tblClient table, and
filters for Corporate records only. I want to add a record to the tblGroup
table each time a NEW record is created from this entry form.

I want to take the bound text box "MainName" field from the entry form and
turn it into the "GroupName" field in the tblGroup table, plus add "Division"
to it. So, I'd like it to populate the equivalent of [MainName] & " " &
"Division" into the GroupName field on tblGroup. tblGroup has an autonumber
primary key that would need to trigger when this happens.

What code would I use to do this? And, would it go in the "After Update"
area of the form?

Thanks for any help you can give.

Allison

 

[Graham Mandeno]

Hi Allison

You could do this by execution a SQL "INSERT INTO" statement (same as an
append query). The code should go in the AfterInsert event of your form.
Something like this:

If Me!IsCorporate Then
strSQL = "Insert into tblGroup (GroupName) VALUES (""" _
& Me!MainName & " Division" & """);"
CurrentDb.Execute strSQL, dbFailOnError
End If

The autonumber primary key will look after itself.