Append Query to Append records from multiple tables to 1 table

[sud2017]

Thought to post a copy of my db if you'd kindly prefer to have a look, in case I am missing something important in explanation. 'Select Opp' Combobox in QuoteEntryForm is where I am trying to run the Append Query from. Thanks and your time and help is very much appreciated.



These are the values where I have some items details stored:
Combobox 'Select Job'- 'HouseBrandZ', 'Select Opp' - 'DoorBrandX'

[John_G]

Thanks for posting the database - with it, I found the answer.

For some strange reason, Query1 was not recognizing the form references as numeric data. I fixed that by forcing the expressions in the query to numeric with the Val() function:

QID: Val([Forms]![QuoteEntryForm]![QuoteID_PK]) and Val([Forms]![QuoteEntryForm]![CmbOppList])

Then, I made two other small changes. First, I took the after update event off [CmbOppList]. It made no sense to run the query there - what happens of you select the wrong one and have to change it? You would end up with records in the ItemsT table that shouldn't be there. Instead I put the call to Query1 in the Save Record procedure, which I changed to an Event Procedure (I hate those Embedded Macros) which looks like this:

Private Sub Command30_Click()
    On Error GoTo Command30_Err
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenQuery "Query1", acViewNormal, acEdit
    '
    ' Requery the subform
    '
    Me!ItemsTsubform.Form.Requery
    Exit Sub
Command30_Err:
    MsgBox "Error when saving:" & vbCrLf & Err.Description
End Sub

I gave a quick test and it looks like it's working. The real problem was those numerical form field references.

Let us know how it goes!

[sud2017]

Thank you very much for analysing this for me John_G. Your time and help is very much appreciated. Thanks very much to June7 as well for correcting my query design.

I sensed last night that I shouldn't be using Query1 in Select Opp combobox because it appended the records when I re-selected combobox value after saving form records.

I will be testing with your suggested changes tonight and will post the result. I am glad I decided to seek help in this forum, otherwise I was getting really demotivated with further progress of my project, after getting this far. It's my first database so every step brings new learning. Thank you again.

[John_G]

Glad we can help.

every step brings new learning.

Absolutely. And the best way to learn Access is to use it. One fun way is to set up a "sandbox" database where you can experiment and try new things without trashing your active data.

[sud2017]

I tested the db with your suggested changes and it's working smoothly. Thanks very much indeed for your help and thanks to the forum as well

I am sure I will be back with more question with a different thread. Thank you again