Adding data to a form using append query

[SMi]

So I have a form in which it shows a person and how many samples they have. On the form I want to be able to add a product and how many samples the person will get, and add it to the list of samples they already have. I'm guessing I would have two subforms. One for what they already have, and one for what I am adding.

Currently I have 3 tables. One table is for the customer (name, ID, etc) Second table is the product table, in which I have listed a few families of products, and then more specific products in that family. A third table is my Allocation table, in which it gives the customer, the product family, the product name, and quantity. I then made a query based on the allocation table. I used this query to create a form. So my form shows the customer's ID and their last name, and in a subform shows the allocated products.



Now I'm trying to figure out how to be able to add products and quantities to what a customer already has. I am trying to use an append query for this and I'm not entirely sure what to do after I have created the query. I haven't set any criteria or anything because I'm also a little confused on that. Any help would be greatly appreciated!!

[kennejd]

So you want to keep track of the customer transactions? That seems like the best way to do what you want....then display the aggregate data in a separate subform...mayb just labels.

As far as running your append query, you could just use the button wizard to run a query on command. I'm not sure that's what you really want, though.

[SMi]

Well I created a new form in which there is a drop down for the samples and a text box for their quantities. I also used the button wizard so that it wuold run my append query. However, it doesn't do what I want it to do, so I think my append query is not really correct. When I run the query it just adds a line to my Allocation table but none of the info that I added in the form. I don't know how to fix my append query to make it do that.

[kennejd]

Can you post the SQL?

[SMi]

INSERT INTO Allocation ( CID, Family, Code, Quantity )
SELECT Customer.CID, Product.Family, Product.Code, Allocation.Quantity
FROM Product INNER JOIN (Customer INNER JOIN Allocation ON Customer.CID = Allocation.CID) ON (Product.ID = Allocation.Family) AND (Product.ID = Allocation.Code);

CID is for customer ID. I know this is not correct, as it doesn't even relate to the table. I also am not even entirely sure my relationships are correct. I'm very new to access! So I then tried this because someone suggested to use something like this but then it only added a line: (I only tried this out with a form called "Test")

INSERT INTO Allocation (CID, Family, Code, Quantity)
VALUES (forms!Test!cboCustomer, forms!Test!cboProduct, forms!Test!cboProduct, forms!Test!Quantitytextbox)

I'm pretty sure neither of them are right, but I'm not sure how to actually make the append query update to my Allocation table, it seems like a simple concept! In the end I want to know what the customer has (which I can already do) and be able to add to their samples (what I'm trying to do)

Thanks for your help and time!

[SMi]

Actually, I did some searching and found i could set the criteria on my key field to =Forms!MyForm!MyKeyField

So I changed my append query to this:

INSERT INTO Allocation ( CID, Family, Code, Quantity )
SELECT Customer.CID, Product.Family, Product.Code, Allocation.Quantity
FROM Product INNER JOIN (Customer INNER JOIN Allocation ON Customer.CID = Allocation.CID) ON (Product.ID = Allocation.Code) AND (Product.ID = Allocation.Family)
WHERE (((Customer.CID)=[Forms]![Test]![Combo35]) AND ((Product.Family)=[Forms]![Test]![Family]) AND ((Product.Code)=[Forms]![Test]![Code]) AND ((Allocation.Quantity)=[Forms]![Test]![Text7]));

However, when I go to run this, it says I'm about to append 0 rows. So I know this isn't correct either...