Changing the ID number to modify an object's link to primary key

[rpeare]

I'm not following here and your data structure is a hindrance I gave you a utility to move a record to another order that should still apply, but yes, I need a specific example in the database you loaded of what your 'before' and 'after' should look like.

[ahuffman24]

Okay. Since we last communicated, I actually modified my tables to better fit the data structure. I have included a current copy of my database in this post. You can look at my new table relationships and see that I have 2 one-to-many relationships set up; one is for one order number to many item numbers, and the other is for one item number to many serial numbers. This is because a customer can place 1 order (order number) with multiple items (item number) and order as many of each item that they want (serial numbers).

Sample Database1 (3).zip

I've been thinking that what you coded in my previous database will work fine. But since I changed the table relationships, the code you sent me is not compatible with my current database, and every time I click the "modify" button in my Modify_Details_Form, I get an error. Would you be able to take a look at it please and let me know what I should change ein your code to make this work? Thank you so much for all of your help, and let me know if I can clarify anything else for you.

[rpeare]

This is still not a properly normalized table structure. Look at post #4 of this thread.

You're making this way harder on yourself. For instance in your customer_order_Info_table you are are allowing your users to free-form enter a customer name. What you really should have is a CUSTOMER table. The customer table would *only* have information specific to the customer, like the customer name, maybe a tax ID, things like that, possibly a CUSTOMER CONTACTS table so you could list relevant people to the customer.

Your ORDERS table should be store the CUSTOMER table ID primary key and any order specific fields for instance, order date, ship date, discounts applied, notes, etc. By allowing your users to and type customer names you're begging for data problems. I.e. Walmart, wallmart, walmar may all be the same customer but because of the slight difference in spelling you won't be able to group them. This is going to make it especially difficult on you to choose which order number to apply any changes to if you are going to look up possible targets by the customer name. *Any* place you allow your users to type in a response/field you are going to have to give up any hope of doing something useful (this is my personal bias).

The utility I gave you to copy between orders will need to be modified every time you modify your data structure so that's why it doesn't work right now, you've altered the structure to the point the code or query involved no longer recognize a field name.

Finally, how are you determining the order of items under each customer order, is it the Item number field?

What happens if you insert an item with an item # of 4.1 into an order already having an item 4.1? how is the item # being generated? is it just being typed in by the user? Why not auto generate a sequence number for every new item you add to an order then manipulate that? Why are you using decimal numbers in a text field for your item numbers if you do that 10.1 will appear before 2.01 which is not what you want I don't think.

I am enclosing a database with a structure that is probably closer to what you really want. Theoretically, if you are inspecting every element of every order you could dump all your inspection fields into tblOrderProduct in my example. If you aren't a separate table is the way to go.

If you use this structure and you continue to use bound forms you can set up a form/subform arrangement rather than doing it the clunky way you've currently got things set up. Also enclosed is an example of a form/subform arrangement with a properly normalized structure.

Duraloy.zip

This is a simple example only and there are a number of things you will have to do to protect your data like making sure your required elements are present before adding or updating a record (BEFORE INSERT and BEFORE UPDATE events respectively) There are also things you can do to make this easier like give your users a search function that does not rely on the ctrl-f keystrokes

[ahuffman24]

@rpeare thanks for the reply, see the next post for my response

[ahuffman24]

I will look into your database. A couple things though. I am working under the direction of my boss, so some of the things you are suggesting are out of my control, such as how the items are numbered. I am not the person who creates the item numbers, order numbers, or serial numbers. Someone else in my company does all of that, so I am just doing what I am told. I do not know why they use decimal numbers for their item numbers, but they do. So, the order in which the items are stored does not matter; all that matters is that each item number is linked to the appropriate order number and that each serial number is linked to the appropriate item number. Does that answer your question about how I am ordering the item numbers under each customer order?

The way that I have my tables set up links the data in the way that they are currently keeping track of it. I do see your point on making a separate table for the customer info and a separate table for the order info, but in regards to specific customer info, I am creating this database for a section of my company that only cares about the customer name. They do not care about anything else regarding who the customer is. That is why I have the customer name in the same table as the order number, because the customer name and order number do go hand-in-hand.

Finally, I am fully aware that every time I modify my data structure, I have to modify the code. I have a strong feeling that something in the VBA code of the frmreassign that you created needs to be modified because of the way I now have my tables set up, however I have no experience in VBA programming whatsoever, so I can't begin to think of how to modify the code because I don't know any of the VBA syntax. I see in your code that you are telling the Inspection Table to update its ID number based on the Customer Info Table ID number, but I now have an extra table called Item_Number_Table that links the Customer Order Info table to the Inspection Table, so I need to somehow tell the program to account for this. If there's any way you could guide me on that, it would be greatly appreciated.

[ahuffman24]

@rpeare, I checked out your database. Your database is nice, but for my company and the people that will be using it, my database is more fitting for their needs because I will have multiple people looking at and altering the forms. I am still wondering if you could help me out with changing your original code so that I can use it for my current database.