VBA SQL - append query

[dualvba]

Hi

I have a subform, within a form, I am using it to enter new items into a table (called items as it goes).

I have got an append query, using SQL, that is correctly adding the information entered into the subform, but in the main form, I have 2 combo boxes (pulling data via SQL query code), one to click the customer (from tblCustomers) and the other to select an auction date (from tblAuctionDates).

Can anyone help me with the VBA code please, to make sure these 2 combo boxes are added back to the items table as well, please?

I have a button (save) called cmdSave, and this is only saving the item details



In the subform, itemID is an auto number, Item description and price, all save back fine to the tblItems, but on the main form, they select the customer, and then the date, and I want it all to save back together, but the customer name and auction date are just blank.

Be so greatful, thanks so much
Clair

if this helps, here is my code for the cmdSave:

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

[Robeen]

Do you have Customer and Date fields on the sub form as well?
I'm guessing your code is only saving data to the Items table from the fields that are on the sub form.

If you want Customer and Date to be entered into the Items Table using your sub form - you should 'Add Existing Fields' to the Sub Form and make sure those fields are 'Bound' to the corresponding fields in your Items Table.
THEN . . . when your code runs - if you have the Customer and Date values in those two fields on the sub form - they will be saved to the Table.

It's been a while since I used Access 2003 - but I think there is an 'Add Existing Fields' button . . . ? Use that to add Customer & Date from the Items Table to your sub Form.
Then pull Customer and Date values from your Main Form to those fields on your sub form.

Let us know if you need more help.

[dualvba]

Hi Robeen

Thank you so much for coming back to me.

I have a table "items" and a form "items".

The table has
Auction Date
Customer ID
Item ID
Item Description
Reserve Price

On my Form,

I have Auction Date and Customer Name, both as combo boxes, using SQL and pulling inform back from The customers table and auction Date table.

In the subform, within the items form, is a nested table, where they can input all the items they would like to enter, their date entry has to be quite quick, so they can enter any number of rows here

On subform I have:

Item ID
Item Description
Reserve Price

And a big save button, but my code is only bringing back the 3 items from the subford, not the customer ID or Auction Date that are on the actual main form.

I hope that makes sense? I want Customer Name & Auction date to save back to the items table too, but am not sure how to scoop it all up together.

Thanks again
So much

[amrut]

Does One set of Customer ID and Auction date record has many corresponding records of Item ID,Item Description, Reserve Price ?

[Robeen]

Are you at liberty to post your DB [or an abridged version of it] here?
Typically with a Form -> Sub Form scenario, two things are set up in the background:
1. A Relationship between the Tables that are being used in the Main and Sub Forms.
2. The Sub Form is set up as a Child Form to the Main Form.

Also typically - there is one field in the Main Form's underlying Table that has a matching field in the Sub Form's underlying Table - and these two matching fields in the two Tables are used to Link the two Forms together so that when you enter data into the sub form - it automatically takes the matching value from the Main Form.

If you didn't set your Table Relationship and Forms Parent/Child properties up in this way, try reading up on this page [I found it very useful] to see what you can do about fixing your scenario:
http://office.microsoft.com/en-ca/ac...005187803.aspx

Let us know if you have any more questions!

All the best!!

[dualvba]

Oh brilliant, thank you Robeen.

Thanks so much for your help, I am going to look into this, and if I need any further help I'll be back.

Thanks again for your help everyone
:-)