My issue is with the sequence number fields. For certain load centers, there are sequence numbers such as
For this, if there is a 0 in front oft he number, this is what is taken as the maximum sequence number. If there is no zero (so it'd just be 999) then it uses the 1000 as the maximum number. The issue is that it never actually grabs the highest number in there... can anyone help me try to figure out why this is not working?? I would greatly appreciate it as I have no idea what the issue is... Thank you!!
It was set as text... but I realize that this is why it was not working.
The reason I had it as text is because there are programs stored in there from 10 years ago before this system was used that have letters in it... i.e. 1510999A or 1510999A01. Therefore, I had set the data type to text so that it would allow these values to be stored.
Is there a way to keep it as text and have it ignore these values when it chooses a sequence number?
Alternatively, I could create a new table just for those program numbers that have letters in it. However, when I do a search, would there be a way for Access to search both the table that has just numerical numbers and the table that has letters in it and display the results on the same page? All of the fields would be the exact same in both tables...
I would try to create a query based on the table "ProgramInfoTbl" with an ORDER BY clause on "SequenceNmbr", use it in the DMAX() expression (or use a SELECT TOP 1?) and process the result according to some rules that remain to be defined. It's hard to be precise without knowing the data set.
It is because these were created in 1999, where the process was not automated but manual. A smart numbering system was used where a sequence number would correspond to a specific part, and then the letters were used in case that multiple programs were needed to make the same part.
I think I am just going to put these letters in a different table. It might not be pretty, but it seems like the easiest thing seeing that my table now works fine with just the numbers.
I have another quick question... I have created a user form to allow for more new programs to be added to the table mentioned here. I also have a separate form to used to search through this table and bring up any programs. You can search by either the program number or various other fields. I noticed that when I add a program using the form, and then try to search for it using the search form, it does not return any results. However, when I open up the table it is there and has been saved. Do you know what the reason for this may be and if there's a way to fix this?