Append data from subform

[cdg]

Hi
I have a question regarding a subform, when opening the main form (frmBookAppointment) with a customer record on it which is based on an appointment query, the subform (tblVehiclesSubform) data is opened with that particular customers vehicles displayed. this form is linked to the main form using a CustomerID field, the subform is in a datasheet view so that if the customer has multiple vehicles they are easily visable in rows.Also hidden in the suform is a VehicleID which is unique to every vehicle, here's my problem, what i want to happen is that you can double click on the row with the vehicle details on it that you want to make an appointment for and that vehicles VehicleID will appent to the appointments table (tblAppointments) in field (ApptVehicleID)for that particular appointment.
Ive been playing around with this for weeks and cant seem to acheive what i want to happen, still learning access by trial and error.

Any suggestions or any ideas to acheive this more simply would be greatly appreciated.

Thanks Chris

 

[pbaldy]

You could create a saved append query that got its values from the form and/or subform as required, then run it from the double click event of that control (DoCmd.OpenQuery). You can right click and select Build to help you build the correct syntax for referring to the form.

 

[cdg]

Thanks for your suggestion.

I have tried an append query but cant seem to get it to work , I have changed the subform from datasheet to continuous forms and added a command button at the edge of the form, but not sure how to code it to work. Or would you suggest leaving it in datasheet form?

Thanks again Chris

 

[pbaldy]

Personally I don't like datasheet view, so I'd stick with continuous view. I feel like I have more control that way.

"cant seem to get it to work" doesn't give me much to go on. What is the SQL of the query? How/where are you executing it? What error do you get? Can you post the db with the failing effort so we can fix it?

 

[cdg]

Cheers for the help

Will post the SQL and all the info tomorrow if thats ok as its on my laptop and i dont have it to hand at the moment but appreciate your help and look forward to your input.

Thanks in advance

Chris

 

[cdg]

Hi

I think ive sort of nailed it, I created a query with the following SQL

INSERT INTO tblAppointments ( ApptVehicleID )
SELECT tblVehicles.VehicleID
FROM tblVehicles
WHERE (((tblVehicles.VehicleID)=[Forms]![frmBookAppointment]![tblVehicles subform].[Form]![VehicleID]));

Now with the BookAppointments Form open i have the subform in continuous forms and the query runs from the command button. when pressed it says it will append 1 records but when i press it again it cannot append due to the following error "Appts set the field to null due to a type conversion failure and it didn't add 0 records due to key violations, 0 records due to lock violations,and (1) records due to validation rule violations.

Ive checked the data types in both tables and they both seem the same, is this what the error message is saying or is it something else.

Thanks Chris

 

[pbaldy]

My guess is that there is at least one field in the destination table that doesn't allow Nulls, and you're not populating it.

 

[cdg]

Hi

Many thanks for all your help, I eventually sorted it. the problem mwas with my Vehicle table structure, I re created it and altered my other tables and it all works fine.

Thanks again for all your input.

Regards Chris