Create new Record in secondary table from main form

[Milothicus]

My main form is used to create new records in the 'input' table. there's an autonumber primary key, and then there's an enquiry_number field (syntax: E67xx). A secondary table tracks contact with clients, and the record to edit in the contact log is selected from a combo box.

what i'd like is to have a new record created with the enquiry_number field in the contact log filled in with each new enquiry number from the input table.

So, in the beforeinsert event in the input form (bound to the input table), i have the code that creates the new enquiry number. can i open a new record in the contact log table and insert the enquiry number into that new record in the same event? what's the code to switch tables, insert a new record, copy a value into the new record and switch back to the form?

any help is appreciated

 

[WayneRyan]

Milo,

The following will insert a record into SomeTable, the fields
are string, numeric and date respectively.

The command does not return anything! If the insert fails due to
a Null value in a field (or duplicate key), you won't "know" it.

DoCmd.RunSQL "Insert Into SomeTable (FieldA, FieldB, FieldC) " & _
             "Values ('" & Me.FieldA & "', " & Me.FieldB & ", #" & Me.FIeldC & "#);"

Wayne

 

[Milothicus]

Thanks a lot. The code on its own produced a key violation. i'll try to explain why for future readers.

in my main form, in the before_insert event, i put a code to create a new enquiry number (E7603, E7617, etc) by adding one to the max. I placed the code given to me by Wayne right after that. because the destination of the copying was a combo box referencing my main table, the main table needed to be saved to apply a new primary key value to the new enquiry number. therefore, i placed the following code between my new code generation and the copying code from wayne:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Once again, thanks for the help.

 

[Milothicus]

That's much nicer. I changed it and it works..... the other code was the best i could find. i think that's even what the help file recommended using......