March 22, 2018 at 5:46 pm
Hello,
I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
I have 3 fields in the shipping form I want to use because they use to create the number
Field 1- FldCompanyID (ie Autobots= 13)
Field 2- FldContactID (ie Optimus Prime= 1)
Field 3- FldYearIssued (ie 2018)
I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
1312018
In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
1312018001
So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
=[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
Then via a command button (Cmd05) run a macro that:
Executes a query to append table (TblOGML)
I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
Any help would be great and I thank you all for taking the time to read this.
James
March 22, 2018 at 6:22 pm
Primary keys are for relating to other records in other tables. Creating an "intelligent" primary key is going to be a pain to manage. Why can you not use a regular IDENTITY column, and then have several required fields in the table? (You could have a group of fields that together must be unique, but they're not necessarily the primary key.)
March 22, 2018 at 10:00 pm
pietlinden - Thursday, March 22, 2018 6:22 PMPrimary keys are for relating to other records in other tables. Creating an "intelligent" primary key is going to be a pain to manage. Why can you not use a regular IDENTITY column, and then have several required fields in the table? (You could have a group of fields that together must be unique, but they're not necessarily the primary key.)
I didn't mention but I receive all of my containers back due to the reoccurring shipments with the same type of items. I use the primary keys assigned to the aforementioned setup I'm currently trying to make to control the supply chain from shipping through return. It also helps me to see how long I have boxes in suspense of being returned. I'm trying to do it to where I don't have to sift through the container ID's to get the last one used by a specific customer. This will save around 3 minutes per transaction and an overall of 2hrs per day that I can be spending not doing paperwork.
Let me ask you this, if I did have a field: FldContainerID how would I auto-populate that field based on the FldContactID Input?
March 23, 2018 at 12:03 am
Let me back up a step. if you want to identify a Shipment etc, you can use an AutoNumber primary key. Sure, they're meaningless, but you can have foreign keys pointing to that Shipment's owner etc.
drybone - Thursday, March 22, 2018 5:46 PMHello,
I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
I have 3 fields in the shipping form I want to use because they use to create the number
Field 1- FldCompanyID (ie Autobots= 13)
Field 2- FldContactID (ie Optimus Prime= 1)
Field 3- FldYearIssued (ie 2018)
I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
1312018
In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
1312018001
So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
=[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
Then via a command button (Cmd05) run a macro that:
Executes a query to append table (TblOGML)
I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
Any help would be great and I thank you all for taking the time to read this.James
James, you can do the "serial number" part with DMAX().+ 1. There are tons of examples on http://www.utteraccess.com . There's also an example on http://www.mvps.org/access (moved somewhere)... but if you look it up (if that part still works)... there's an example there. I just think that stuff gets messy fast. What you're asking for is like an "intelligent" key. They're a PITA to maintain.
March 30, 2018 at 1:39 pm
pietlinden - Friday, March 23, 2018 12:03 AMLet me back up a step. if you want to identify a Shipment etc, you can use an AutoNumber primary key. Sure, they're meaningless, but you can have foreign keys pointing to that Shipment's owner etc.drybone - Thursday, March 22, 2018 5:46 PMHello,
I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
I have 3 fields in the shipping form I want to use because they use to create the number
Field 1- FldCompanyID (ie Autobots= 13)
Field 2- FldContactID (ie Optimus Prime= 1)
Field 3- FldYearIssued (ie 2018)
I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
1312018
In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
1312018001
So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
=[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
Then via a command button (Cmd05) run a macro that:
Executes a query to append table (TblOGML)
I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
Any help would be great and I thank you all for taking the time to read this.James
James, you can do the "serial number" part with DMAX().+ 1. There are tons of examples on http://www.utteraccess.com . There's also an example on http://www.mvps.org/access (moved somewhere)... but if you look it up (if that part still works)... there's an example there. I just think that stuff gets messy fast. What you're asking for is like an "intelligent" key. They're a PITA to maintain.
James,
After dealing with Access for more than 20 years, it is MUCH, MUCH easier to have foreign key relationships to tables that hold the details of the Company and Contract. Creating your own composite to become an "intelligent key" is far more trouble than it could ever be worth. Having those foreign key relationships in place makes everything else a piece of cake.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 31, 2018 at 8:27 am
sgmunson - Friday, March 30, 2018 1:39 PMpietlinden - Friday, March 23, 2018 12:03 AMLet me back up a step. if you want to identify a Shipment etc, you can use an AutoNumber primary key. Sure, they're meaningless, but you can have foreign keys pointing to that Shipment's owner etc.drybone - Thursday, March 22, 2018 5:46 PMHello,
I'm currently working on a project to track containers that I ship out. My problem is that I have different people I ship to on a regular basis and I want a shipping container number to automatically generate based on the person it is going to. What I want to do is laid out below:
I have 3 fields in the shipping form I want to use because they use to create the number
Field 1- FldCompanyID (ie Autobots= 13)
Field 2- FldContactID (ie Optimus Prime= 1)
Field 3- FldYearIssued (ie 2018)
I want to be able to input all that info onto the form and then combine it in Field 4 which will be the Primary key for the table (TblOGML) to which the form is bound to. The output will look like this:
1312018
In addition I want it to automatically add a serial number to the end of that string to identify the particular package that was sent out based on field 2 info. Which would make it look like:
1312018001
So I tried to do this by using all the bound info in fields 1-3 on the form and placing it into an unbound field 4.
=[FldCompanyID] & "" & [FldContactID] & "" & [FldYearIssued]
Then via a command button (Cmd05) run a macro that:
Executes a query to append table (TblOGML)
I just don't see how I can add a serial number to the end of the string automatically based on the information in Field 2.
Any help would be great and I thank you all for taking the time to read this.James
James, you can do the "serial number" part with DMAX().+ 1. There are tons of examples on http://www.utteraccess.com . There's also an example on http://www.mvps.org/access (moved somewhere)... but if you look it up (if that part still works)... there's an example there. I just think that stuff gets messy fast. What you're asking for is like an "intelligent" key. They're a PITA to maintain.
James,
After dealing with Access for more than 20 years, it is MUCH, MUCH easier to have foreign key relationships to tables that hold the details of the Company and Contract. Creating your own composite to become an "intelligent key" is far more trouble than it could ever be worth. Having those foreign key relationships in place makes everything else a piece of cake.
+1000 to that!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply