Please help! Relating form and 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 would like to do this using drop down boxes from a previous table. 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!!

If I can clear anything up let me know. I know it's a bit complicated!

[jzwp11]

If you are using forms bound to tables, you would not need an append query. Your main form would be bound to your customer table and then within that form, you would have a subform bound to your allocation table. Within that subform you would use a combo box tied to the product table to assign a new product to that customer. You would also be able to see all products previously assigned to that customer.

[SMi]

Well for the samples and quantities that are already associated with a customer I have a form set up. The thing is that for my allocation table, I used lookups to lookup the product names. I made a query based off of my allocation table and customer tables, and based my form off of my query. Because of this, my form had drop down menus for the product names, so I "locked" that field so other people won't be able to change the product name to a different one. So now I'm looking to make another subform with the fields NOT locked so that once I "submit" that, they will be added to the previous subform in which it gives samples the person already has. If that makes sense...

[jzwp11]

Another option is to use bound forms and make the subform as data entry only that way only new allocations can be added without even seeing the existing ones.

The other option is to use an unbound form with a combo box for the customer, a combo box for the products, a textbox for the quantity, and a command button. When the button is pressed, an append query is executed that takes the form data and appends it to the allocation table.

[SMi]

Hmm... I think the second one kind of sounds like what I want to try to do. The thing is that I want to be able to see what they already have (but not be able to change that) and then add more products/quantities to their name as I give them different samples in the future. Kind of like where I would put in the new samples they're getting, be able to press a button, and it will save into the form (or subform or whatever) of the samples they already have.

[jzwp11]

What you can do is to add a button to your current form that shows the items already allocated. That button will open the unbound form I suggested. After the append, you can refresh the original form and it will show the newly appended product.

[SMi]

That sounds perfect! Except I'm not exactly sure how to do the whole appending query part. I'm not entirely sure if I'm doing that correctly.

I created an append query. In it I have the Customer's ID from my Customer table, and the name of the product from my Products table. At first I included the "Quantity" section from my Allocation table but I don't think if this has to be included? So I got rid of that section.
Then I "appended to" my allocation table.

I also put a button on my form to run the append query, but it doesn't work. So I think I'm missing something - whether it's the criteria or something else. And if it is the criteria, I'm not even sure what the criteria is supposed to be. For example, I'm not looking to just include one product name, I want to include all that are in my products table.

[jzwp11]

In the unbound form you would have 2 combo boxes one for the customers and one for the products that would include all of your products. You would just select which one to add to the chosen customer's allocation. The append query would go something like this


INSERT INTO allocationtable (customerID, productID, quantity)
VALUES ( forms!formname!cboCustomer, forms!formname!cboproduct, forms!formname!quantitytextboxname)

[SMi]

Sorry, I'm completely new to Access. How would I create an unbound form? I created a blank form and created fields for Customer, Product code (which is a text - not number), product name, and quantity.

The SQL of my append query looks like this: (I used Test for the name of the form just for now.)
INSERT INTO Allocation ( ID, Code, Name, Quantity )
VALUES ( forms!Test!cboCustomer, FOrms!Test!cboProduct, forms!Test!cboProduct, forms!Test!quantitytextbox);

I don't think this is exactly right (the modifications that I made)

I also don't know how to get the drop down menus in my form to show the customer's ID and product names, etc. I've created them but it's pretty much blank...

[jzwp11]

I don't have Access 2010 here at work but I think it is basically the same as Access 2003. I think you just create a new blank form in design view. The form is just not linked to a table or query, so the controls you place on the form are not bound to an underlying table field. In Access 2010, sometimes if you have a query or table highlighted in the navigation pane and hit create form, Access automatically thinks that the new form is to be bound to the query/table highlighted in the navigation pane. So I would make sure that no tables or queries are highlighted and then create a blank form.

Before I can say whether the query is correct, I would need to see your table structure.


In a properly structured database, you would only save the key value of the product not both the key value and the product name in the allocation table. From your query, I assume that the primary key of the product table is the CODE field and the primary key of the customer table is the ID field.

For the combo boxes on your form, the bound field of each combo box should be the primary key field.


INSERT INTO Allocation (ID, Code, Quantity)
VALUES (forms!Test!cboCustomer, FOrms!Test!cboProduct, forms!Test!quantitytextbox);

I also don't know how to get the drop down menus in my form to show the customer's ID and product names,

You can use the combo box wizard to help you create the combo boxes. The combo boxes need to get their values from a table or query. The bound column of the combo box is generally hidden from the user, but you can always change that by adjusting the column widths property of the combo box after you have created it.