How to code Append Query In VBA?

[CharlesWhiteman]

I have a Form & ComboBox. Based on the value selected in the Cb will denote which set of values from one table are appended to another table. in My case values are stored in TblItems and are appended to TblQuotedItems. On my FrmQuoteBuilder is a combo box which denotes which values are going to be appended but there is also a text box which contains the 'QuoteRef' and i also want to append this into the TblQuotedItems and i am thinking that if i do the append query in VB then there should be a way of including the 'txtQuoteref' in with the code whereas i am unable to using the standard query. How would i write this so as to work in Vb?

INSERT INTO TblQuoteItems
SELECT TblInvuS600.*
FROM TblInvuS600
WHERE ((([TblInvuS600].Users)=Forms!FrmQuoteBuilder!CbInvuUsers));

 

[GUIDO22]

I suggest you post a copy of the DB to the forum where we can try and see specifically see what you are trying to do...???

 

[Dennisk]

you can do it both ways.

In QBE you can reference a text box on a form in the name column as well as the criteria column (which is the most common).

for example

myNumber:Forms!Formname!ControlName

 

[CharlesWhiteman]

Here is a sample database

On openign the database goto opportunities and new quote. a user would fill out the fields and then goto quotebuilder to add items. in this case a number between 1-500 for the number of licences required. this appends from TblInvuS600 to TblQuotItems but i also need in TblQuoteItems the quoteID sas to be able to link them together.

 

[CharlesWhiteman]

I did as recommended and am almost there! On append, in my Db, there are two records which always append. i get an error now when running the query saying it cant append all the records. in the Tbl I can see one of the two records has been appended but not the other.

 

[Dennisk]

What is the message. The most common ones I have found when appending are :-

1) no data appended for Required fields
2) No data for text fields where the Allow Zero length propery set to no.
3) no Foreign key

 

[CharlesWhiteman]

The error message i get is Duplicate Output Destination 'QuoteID'

I implemented suggeston a couple above. in my case there are two records which match the append query criteria. It appends the first record but i get this message realting to the second?

 

[Dennisk]

QuoteID will be a unique column - check the design. If it set to the table's Primary key then it has to be unique, or it could be that the index is set to unique. You will have to decide whether this key is unique or not and whether to take appropriate action before the append query is run.

 

[CharlesWhiteman]

The TblQuoteItems already has a unique ItemID (PK) the QuoteID (FK) is not unique. I cant understand why the append won't append two records? I may have to go back to the drawing board. :-(

 

[Dennisk]

sorry I misread you post.
duplicate output destination I think refers to Column being referenced twice in your append query.

 

[CharlesWhiteman]

You were right and I failed to understand this properly. In my append query I had TblInvuS600 * and also QuoteID in the QBE window. Now I have restructured in QBE it works perfectly.