Form Help

[mgstlucia]

Hi,
I have a main form (ORDER FORM) with a subform (PRODUCTS SUBFORM). The main part of the form has the name, address, etc. info and a unique id called CAPID. The main form is getting the member info from the Member query. The forms are linked by CAPID. The subform is getting the Products info (SKU#, Description, Price) from the Products query. I also have a CAPID field in the Product subform, so when I fill in the Member info at the top, the CAPID automically goes to the CAPID field in the subform.

The subform is used to order items. I enter the items and everything is great, but when I close the form, everything I have entered is added to the Products table. I don't want to add to the products table just use it as a source. I want it to add it to the Orders table. Then if I select the member that already has ordered items, those items show up in the subform when I would like it to be blank in order to submit a different order.

How do I get the forms to pull info (members and products) from one table(s) and update a different table?

Thanks

[PianoMan64]

Hi,
I have a main form (ORDER FORM) with a subform (PRODUCTS SUBFORM). The main part of the form has the name, address, etc. info and a unique id called CAPID. The main form is getting the member info from the Member query. The forms are linked by CAPID. The subform is getting the Products info (SKU#, Description, Price) from the Products query. I also have a CAPID field in the Product subform, so when I fill in the Member info at the top, the CAPID automically goes to the CAPID field in the subform.

The subform is used to order items. I enter the items and everything is great, but when I close the form, everything I have entered is added to the Products table. I don't want to add to the products table just use it as a source. I want it to add it to the Orders table. Then if I select the member that already has ordered items, those items show up in the subform when I would like it to be blank in order to submit a different order.

How do I get the forms to pull info (members and products) from one table(s) and update a different table?

Thanks

the reason is you've created the CAPID into the parts tables. That is where you're mistake is.

What you need to do is have a combo box that has the list from the parts table. Then when you find the part that you want, select it from the parts combo box and it will store the Part_ID number in the Order_Details table.

In your orders detail table you need to have a field for the CAPID, as well as each part_ID as well, as any special pricing that may be paying for that part.

This way you can create multiple orders for the same customer without having the orders from the past come up every time you get into that customer.

Hope that helps,

Joe P.

[mgstlucia]

Thank you for your reply. Since I have the form and subform linked by CAPID, I should choose another field to link? I may have my database structure and relationships wrong. After reading several articles on this subject, I did make some changes. Can you look at this and tell me if it looks correct?


Member Table is linked to the Order Details table by CAPID and has a one to many relationship.

I did have the Products and Order Details table linked by CAPID but since I deleted it from the Products table, I'm thinking I should linked it by SKU# in a one to many relationship.

OR Do I need to create a Autonumber field in both tables and link them that way?

Which field should I linked the main form and subform with?
Thanks

[PianoMan64]

Thank you for your reply. Since I have the form and subform linked by CAPID, I should choose another field to link? I may have my database structure and relationships wrong. After reading several articles on this subject, I did make some changes. Can you look at this and tell me if it looks correct?


Member Table is linked to the Order Details table by CAPID and has a one to many relationship.

I did have the Products and Order Details table linked by CAPID but since I deleted it from the Products table, I'm thinking I should linked it by SKU# in a one to many relationship.

OR Do I need to create a Autonumber field in both tables and link them that way?

Which field should I linked the main form and subform with?
Thanks

Ok, understand that concept for a moment.

Look at it from the Orders level first.

If you have an order, It has a ID (CAPID) from the customer so you can Identify the Customer, You have an ID to indentify the Order that the customer is placing with you. Then you have an ID that has what part number that customer is ordering from you.

So if you want to look up the customer, you can put in the customer ID and lookup the customer. This would then have all the orders the customer has ever place, and it would have the orderDetail of exactly which parts from that particular order that customer placed.

So you need to create a customer list, then an Order table, and a Order Detail table, and a Parts Table. You can also include, Employee table, Suppliers Table, and so on, depending how detailed you want to be with the information.

I hope that helps, in explaining how to create a Order Table setup.

If you need more details, let me know.

Can create example, I currently use MS Access 2003. If that is compatible with your setup let me know if you do need additional examples.

Joe P.

[mgstlucia]

I think I understand what you are saying and I've made some changes and it seems to be working the way I want. I still have some tweaking to do, but I would love to see your example. I am using Access 2003.

Thanks
Mary

[PianoMan64]

I think I understand what you are saying and I've made some changes and it seems to be working the way I want. I still have some tweaking to do, but I would love to see your example. I am using Access 2003.

Thanks
Mary

here is the example that I've quickly come up with.

Take a look at all the properties and settings and see if you see something interesting.

[mgstlucia]

Thank You very much.

So, I really don't need to include the address info for each customer on the form or the product info other than the product id and SKU#. I can pull all that info in when I run the report (or invoice) because of the relationships of the tables?

I noticed that you didn't do anything with the Products table. After populating that, would I still have to create a subform in the Orders form bound to OrderDetails?

I also noticed that when you hit enter in the last field, it automatically creates a new record. Mine doesn't do that because the last field is in the subform and it adds a new record in the subform. To add a new record, I have to go to the * at the bottom.

Thanks

[PianoMan64]

Thank You very much.

So, I really don't need to include the address info for each customer on the form or the product info other than the product id and SKU#. I can pull all that info in when I run the report (or invoice) because of the relationships of the tables?

I noticed that you didn't do anything with the Products table. After populating that, would I still have to create a subform in the Orders form bound to OrderDetails?

I also noticed that when you hit enter in the last field, it automatically creates a new record. Mine doesn't do that because the last field is in the subform and it adds a new record in the subform. To add a new record, I have to go to the * at the bottom.

Thanks

Oh, it's because If you go into the design of the form, that you'll notice I have the properties Cycle set to ALL RECORDS instead of Current Record or Current Page. This may be the reason that may be happening to you. By default it is set to ALL RECORDS.

Hope that helps.

Joe P.

[mgstlucia]

Mine is also set to All Records. The problem is that when I'm adding products to the subform, when I hit enter, it adds another record to the subform. When I'm done, how do I save the entire form and add a new record (order) without having to click on the * on the navigation bar.

I think I would like to have a command button that would say "Save Order" or "Enter New Order" that would save this record and go to the next record. I also need a way to open a blank form. When I open it now, it always shows the first record. I have searched again for answers to these, but couldn't find anything that works. I am getting frustrated with this whole thing. I don't know VB code, so I don't even know where to begin with that and the built in code builder just doesn't seem to give me the results I want.

Thanks

[mgstlucia]

OK, I was able to solve the go to new record problem. I created a command button "Submit Order" and it brings up a new record. Now I just need to have the form go to a new record when I open it.

Thanks

[mgstlucia]

OK, I figured out the problem with going to a new record when opening the form. I put this code in the Load Form event:

Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub

Thank you for all your help. Now, I am going on to tackle the reports. I guess walking away from this project for a few days, did the trick. LOL

Thanks