Single record Append query

[JanetS]

I am building an entertainment db to manage my books, games, movies, music. I am creating a form to enter items. If I don't currently own the item I have a button which calls a macro to add the item to my wishlist. This is a single item append and I don't get the coding instruction in my book or what I've found on the web. I'm supposed to add values. I want to insert values from the current form. My code is:
INSERT INTO tbl_Wishlist (MediaID, UniqueID, etc)
VALUES (which changed to SELECT in SQLView) [frm_AddItem].[MediaID] AS Expr 1, [frm_AddItem].[UniqueID] AS Expr 2, etc.
When I trigger the macro I get a message box to enter the value of each field. I want this thing to automatically populate the fields in tbl_Wishlist from the values I enter into frm_AddItem for each item I want to add to my wishlist. Help!!!!

 

[PNGBill]

Exp 1 & Exp 2 indicate there may be an error in your SQL code.
This happens when the sql field name in your query is not the name in your table.

Are you able to attach a stripped down copy of your database that does not include any sensitive data ?

 

[JanetS]

Thank you for your response. I looked at my query statement and changed fieldname from Media to MediaID. Still getting parameter query response to enter data.
Attached is db, no sensitive data. frm_AddItem2, record 4 meets criteria (don't own item). Selecting AddToWishlist button executes qryA_Wishlist which acts on tbl_Wishlist.
Thanks

 

[PNGBill]

Some observations you may wish to consider.

Your Field Names use more then one word - good practice except for Title. Title is not on my list of Reserved Words but it could still be one.

Form Control Names should be different from the Control Source Name
eg frm_AddItem2 UniqueID should be (eg) txtUniqueID and of course the Control Source will remain as UniqueID.
This ensures no confusion regarding the Control and the Control Source.

 

[PNGBill]

See attached database.
I used a new qry qryA_Wishlist2 and included a DLookup to reference to txtUniqueID control on your Form.

Trust it makes sense

 

[JanetS]

Thank you. The code works but I'm not quite sure why. From what I see you added code to append UniqueID, how did the other fields get appended?

Janet

 

[PNGBill]

When you create an Action Query (Append, Delete, Update) it is best to first create a Select Query to ensure only the records you want are returned.

In this case you want a number of Fields to be returned - simple task for a Select Query except.. you only want records where the UniqueID matches (criteria)
The match is done by using DLookup in the Criteria of the query.
End result is all the fields are returned for the One Record that matches and these firleds are Appended.

The UniqueID is only used as the SQL/Query Criteria. The Append is done on all Fields selected in the query.
Does this make sense ?

NB DLookup treats Numeric Data different to Text Data. In case you replicate the code elsewhere and no record is selected.:banghead: