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

[ahuffman24]

Hi,

I am using Access 2016. I am creating a program that tracks orders in a company. Each time an order is placed, an order number is assigned to the order, and depending on what the customer wants, item numbers are assigned to each type of part that they are ordering. There could be multiple items on each order, and there could be multiple parts per item made; for example, if a customer orders 4 pipes to be made, those pipes are assigned 1 item number, and each pipe is then assigned a serial number under that item. So, the order in total would be something like this: Order # 5, item 1, serial numbers D-1, D-2, D-3, and D-4.

Sometimes certain instances require an item number or specific serial number of an item to be moved to a different order. For instance, if we consider the previous example of the 4 pipes, pipe D-4 may need to be moved to Order #6 for some reason, but none of the other pipes. In other instances, an item number may need to be changed but remain on the same order number. For instance, if we consider the previous example, item 1 (containing pipes D-1 through D-4) may need to be changed to item 2 but still remain on Order #5. And finally, some cases may require both the order number and item number to change, so for instance, Order #5 may change to Order #6 and item 1 may change to item 2.

I need to create a form that will allow the user to do this process with ease. The issue I have, however, is that I have two tables linking this data: a Customer Info Table and an Inspection Table. The Customer Info Table holds the Order Number and Item Number, and the Inspection table holds the serial numbers for each part. The two tables are linked via the ID of the Customer Order Info Table and a Customer_ID field created in the Inspection table. This is what the relationships look like and how the Order/Item numbers in the Customer Order Info table are linked to the Inspection Table:







Since the ID numbers are linked, if an order number or item number is to be changed and moved to a different order, the ID number would need to change. The user will not have any direct access to the ID number because, for ease of use, I only want them to have to deal with the Order Numbers and Item Numbers. So my idea is to allow the user to choose which order they want to modify and type in the new Order Number and/or Item Number that they want to move the part to. This would mean that in the background, the database would actually be modifying the ID number. Is there a way that I can code this with VBA or a macros to get the ID number to change based on the information the user enters?

[rpeare]

can you post a sample database with some junk data in it. I'm reasonably certain you can do this without a whole ton of coding. The key element, though, is if you want your items re-ordered if you remove something from the queue

For instance if you have item 1, 2, 3, 4 in your original order and you move item 3 to a different order number, are you going to maintain the serial numbers the way they originally appeared or are they going to be modified

For instance in your current Customer Order ID 2. If you moved item S-400 to a different order would you need to change the serial number on item S-401 to S-400 and item S-402 to S-401?, would the item you moved to a different order then get a new serial number or would it retain it's serial number? You have to have rules around these items or it's going to get messy quickly.

[ahuffman24]

hi rpeare, thank you for the reply. The serial numbers would not change if they are moved to a different order/given a new item number because the serial numbers are specific to the part. Here is a zipped folder of my database with sample data:

Sample Database.zipSample Database.zipSample Database.zip

[rpeare]

Sample Database.zip

Some notes:

your table structure is bad.

You should have something like

tblCustomer
C_ID  C_Name ---> customer specific fields
1     Customer A
2     Customer B
3     Customer C

tblOrder
O_ID  C_ID  O_Date --->  other order specific fields
1     3     1/1/2019
2     1     1/5/2019

tblItem
I_ID  I_Number  I_Name   ---> other item specific fields (like tolerances for items etc)
1     12010-A  Elbows  
2     12000-A  Reducing Elbow

tblOrderItem
OI_ID O_ID  I_ID  OI_Qty  -----> other order item specific fields
1     1     2     5
2     1     1     20

The whole point of a relational database is your tables hold the minimum data necessary to allow you to do what's necessary and to leverage information without having to do repeated data entry/copying of data.

I am enclosing an updated version of your database showing you how to move one record from one order to another using your current scheme.

If you want to be able to order your items within an order in a reliable way you would have to insert something like a sequence number into your table and build a routine to modify those items within your 'queue' up or down as you require.

I would really strongly suggest you look at articles on data normalization (p.s. though your naming structure is fairly sound you do have spaces in object names, in particular CUSTOMER_ORDER_ ID on your INSPECTION_TABLE has a space before ID, you shouldn't be using spaces in object names and I suspect this is just a typo but I would suggest fixing it) and rework your data structure, it would allow you to use the form/subform scheme which is way, way, way less clunky than what you're currently doing.

[ahuffman24]

@rpeare, the zipped file I sent you did not have a "modify order" form. I created one and have included a screenshot of it. My thought was that the user can just highlight and erase the current order number or item number next to whichever serial number they need to move and type in the new order number and item number for the serial number. And everything will update automatically. I do not know if this is the best approach, please give me feedback!

[rpeare]

I do not know what you're asking in this post. I specifically mentioned if you wanted to modify the serial numbers and you said no. So how are you determining the re-numbering, are you saying to redefine the serial #'s or no?

[ahuffman24]

I do not want to modify the serial numbers, I only want to modify the item numbers or order numbers. For example, if I need to move serial number S-374 to a new order, I would erase the 58012 order number it currently has and type in the order number that I want to move it to.

[rpeare]

didn't the sample I sent back do that?

[ahuffman24]

Sorry, i missed the attachment. This is the form I'm seeing, but I'm not sure what to enter and where to enter it:

[rpeare]

you access it from your DETAILS form don't open it on it's own

[ahuffman24]

I see now. It is working, thank you so much!

[ahuffman24]

@rpeare, just wondering where the insp_ID is stored and why you needed to add that? I just want to understand how it works in case I ever have any issues with it

[ahuffman24]

@rpeare, thank you again for all of your help. Your code worked on my database, but it is not exactly what I was looking to do. I was probably unclear in my initial explanation, and I apologize. What I would like to do is to allow the user to move a serial number to an already existing order but create a NEW item number for that serial number. Your code moves the serial number to an already-existing item number. I have attached a copy of my database. I did modify my tables slightly, feel free to view the relationships between them and if you have any questions please let me know.

Sample Database1.zip

[rpeare]

Insp_ID is just an unbound text box on the screen confirming the move. I populate that with a lookup when the form opens just to make it easy to use queries etc by directly referencing a control.

Also, I asked you in post #2 on this thread if you wanted to renumber and you said no. If that's changed all you'd have to do is run a similar update query on the record after you've copied it to the new order. I did not look into how you are generating your serial numbers to know how you did it.

If you want me to look at this I want a specific example of a piece of data as it is now, and how you'd want it to look after the copy.

[ahuffman24]

Your question to me earlier was not about re-numbering the item number, it was about re-numbering the serial number. As I said before, I do NOT want to re-number the serial number. I want to be able to MOVE the serial number to an already existing order and create NEW item number for it. The item number and the serial number are not the same thing, but they are linked via a relationship. The breakdown is like this: A company places an order, which is assigned an order number. They specifiy which items they want under that order and how many of those items that they want made. The serial numbers correspond with the quantity of each item. So, if a company places order 55555 and wants 5 of item 1, then the serial numbers for item 1 of order 55555 could look someting like T-1, T-2, T-3, T-4, and T-5. The company may also want item 2 to be made under the same order 55555, and there would be serial numbers corresponding to however many parts of item 2 they want.

Sometimes, some of these serial numbers need to be taken off of their current order and moved to a different order and given a NEW item number under that order. For example, if serial number T-1 on order 55555 of item 1 needs to be moved to order 33333, I want the user to be able to select serial number T-1, specify in some way that they want to move it to order 33333, and allow the user to create a new item number for T-1 (maybe they need to create item 4 for that serial number, for example). The main point is that the order they're moving the serial number to already exists within the system, but the item number does not already exist on the order they're moving it to; they will have to create a new item number when they move the serial number to a different order.

You asked for a specific example. Do you want a specific example in my database, or is the example that I gave you above enough?