Copy form field to another table

[Dega]

I have a simple tblCustomer form with firstname, lastname, address, etc.



And a subform called tblBookings.

The tblCustomer "lastname" field needs to be copied over to tblGroup which has a primary key and a groupname field.

I would rather it be copied over than have the user enter it in a text box which could result in a typo and make the form less confusing for double entries. (The lastname is used as an identifier for persons travelling in a group)

The idea would then query the tblGroup with a combo box type field and choose the desired groupname for entry in the tblBookings.

I think I can do this on the frmCustomers: QueryBuilder properties but not sure how to do it.

Thanks

[TheShabz]

off the top of my head...

UPDATE tblGroup
SET lastname = Forms!myForm!myControl
WHERE primarykey = (SELECT primaryKey FROM tblCustomer WHERE lastname = Forms!myForm!myControl)

i THINK this will work.

[Dega]

UPDATE tblGroups
SET lastname = Forms!frmCustomers!myControl
WHERE GroupID = (SELECT GroupID FROM tblCustomers WHERE lastname = Forms!frmCustomers!myControl)

Thanks ...

What does the myControl (action button?) refer to and will this query run when the form is submitted or should I use a "button" control to update it.

[TheShabz]

the myControl would be most likely a combobox containing customer names. theres threads in here about creating a realtime customer search txtbox/combobox team which i think will help you a lot here. You would then have a button that would run that query.

One concern I do have is that there's a good chance you will come up with multiple customers with the same last name. I'm not sure how it's unique like you said it is. But if you have a way to do it, go for it.

Also, this is assuming there is already an entry for this customer in your group table. It is merely updating the lastname and not changing any other information. Are you looking to create a new entry in your group table?

[Dega]

Yes, it would be a new entry in the tblGroup.

As far as duplicate entries the lastname may have to have the firstname inital appended to it because no duplicates are allowed in the table. This is not going to be a big problem as it doesn't happen often on these tours.

My idea was fill out the tblCustomers with personal info then submit it. This is where I need the "lastname" copied over to the tblGroups.

THEN on the tblBookings subform which I have done some work on get the rest of the details that are needed. A query on the tblBookings as you suggested would retrieve the "lastname" and use it as a groupname. (combo box).

So this would be a new entry in tblGroup (GroupID, GroupName).

Thanks for your input.

[TheShabz]

replace the query with:
INSERT INTO tblGroup (lastname)
VALUES (Forms!frmCustomers!myControl)

This will create a new record in tblGroup and insert whatever is in the control into the lastname field. if you want more fields, just add more field names in the INSERT INTO and put more values into the VALUES, in the proper order.

[Dega]

Thank you for your help.