Need Help with this Insert query

[Azhar.iq]

DoCmd.RunSQL (" Insert into TbBookingCustomerDependent (bookingid, customerid) values ( " & test & " , select ID from TblCustomers where HeadID = " & Me.txtCustomerID.Value & " ")

I have two tables TblCustomers & TbBookingCustomerDepended (weird name huh). TblCustomer holds all my customer details. Whenever a customer makes a booking (Through booking form) and that booking relates to multiple customers, i want to insert data about those customer into TbBookingCustomerDependent.

E.g.

Customer books 5 seats on a flight, now the detail of the customer booking the seats would go into tblbooking, and the remaining 4 customers would be related to this booking in TbBookingCustomerDependent - which contains only 2 fields bookingID and customerID.

In the above query test variable contains the bookingID.

When i run the above code in vba on buttonclick, i get the following error

Run-time error 3075

syntax error in query expression 'select ID from tblcustomers where headid = 1'

 

[Isskint]

hi Azhar.iq

SQL strings need to end with a semicolon ;

I assume HeadID is a number? needs to be in ' ' otherwise.

Although it may not affect anything, i find it helpful to always enclose field names in [] brackets and CAPITALISE keywords like VALUES.

 

[CJ_London]

SQL strings need to end with a semicolon ;

Sorry Isskint - not true, but it is good practice

your subquery needs to be in brackets and you are missing the end bracket for values 'set'

DoCmd.RunSQL ("Insert into TbBookingCustomerDependent (bookingid, customerid) values (" & test & ", (select ID from TblCustomers where HeadID = " & Me.txtCustomerID & "));")

 

[Azhar.iq]

hi Azhar.iq

SQL strings need to end with a semicolon ;

I assume HeadID is a number? needs to be in ' ' otherwise.

Although it may not affect anything, i find it helpful to always enclose field names in [] brackets and CAPITALISE keywords like VALUES.

Yes HeadId is a number.

Sorry Isskint - not true, but it is good practice

your subquery needs to be in brackets and you are missing the end bracket for values 'set'

DoCmd.RunSQL ("Insert into TbBookingCustomerDependent (bookingid, customerid) values (" & test & ", (select ID from TblCustomers where HeadID = " & Me.txtCustomerID & "));")

Ok i tried your way and now it gives me the following error

'Runtime error 3067

Query input must contain at least one table or query

 

[CJ_London]

Ah, as a value in a query it must only return one value - as written, you may know there is only one value, but Access doesn't

modify to

DoCmd.RunSQL ("Insert into TbBookingCustomerDependent (bookingid, customerid) values (" & test & ", (select First(ID) from TblCustomers where HeadID = " & Me.txtCustomerID & "));")

Also, make sure that txtCustomerID is not null - the subquery will fail otherwise. I'm also assuming that all values are numeric.

I can't remember off the top of my head, but it may be that insert queries do not like subqueries so an alternative to try is

DoCmd.RunSQL ("Insert into TbBookingCustomerDependent (bookingid, customerid) values (" & test & ", " & Dlookup("ID","TblCustomers","HeadID = " & Me.txtCustomerID) & ");")

 

[Azhar.iq]

Ah, as a value in a query it must only return one value - as written, you may know there is only one value, but Access doesn't

modify to

DoCmd.RunSQL ("Insert into TbBookingCustomerDependent (bookingid, customerid) values (" & test & ", (select First(ID) from TblCustomers where HeadID = " & Me.txtCustomerID & "));")

Also, make sure that txtCustomerID is not null - the subquery will fail otherwise. I'm also assuming that all values are numeric.

I can't remember off the top of my head, but it may be that insert queries do not like subqueries so an alternative to try is

DoCmd.RunSQL ("Insert into TbBookingCustomerDependent (bookingid, customerid) values (" & test & ", " & Dlookup("ID","TblCustomers","HeadID = " & Me.txtCustomerID) & ");")

I guess that is the problem, there are multiple values or records i want this insert query to insert and there are multiple records. How do i do that??

 

[Azhar.iq]

Got it

It should have only select, not values keyword

DoCmd.RunSQL ("Insert into TblBookingCustomerDependent (bookingid, customerid) select " & test & ", Tblcustomers.ID from TblCustomers where HeadID = " & Me.txtCustomerID.Value & ";")

This query will insert multiple records in TblBookingCustomerDependent, depending on how many records have HeadID = somevalue