Colin,
Line numbers are not assigned until you 'export' the SETR to the Warantee folks. Once assigned, line numbers of SETRs are stored in tblSETRModelParts. They are not deleted when a SETR expires (but they do become 'available' again after the lag period). So, if you ever want to see line numbers of expired SETR's then just look them up from the table. QED.
Why, exactly, you'd want to do this escapes me since line numbers are reusable and have no meaning once the SETR has expired. But as long as I get that prius, what do I care?
for assigning vendor invoices
I never designed this db with vendor invoices in mind. This is something you've added on so it's hard for me to comment on. Also, I note there's a lot of new queries in the db that do not line up with the table names in this db and reference some fields that do not exist in this db (eg, qryActive, qryExpenses etc). Why are you adding all this stuff when none of it is necessary to achieve the goals you stated the db was to achieve? In any case, seems to me like you've imported queries from your old db into this one. You need to go through each of these and fix them so that they reference what they're intended to. Otherwise delete them as they only make it harder to figure out what is going on.
I also note that some of the queries I originally built now have missing references because you seem to have changed some table names etc. This will foul things up as those queries were all used at various points along the way. For example, the qryLineNumbers_AvailableNumbersNow has such an error. These
must be fixed for the code to work.
To get at the question of 'active' setrs you first need to define what you mean by 'active'.
Active could mean all SETRs that have not reached their expiration date.
Active could mean all SETRs that have not reached their expiration date AND have been provided to the warantee folks.
Active could mean all SETRs that have not reached their expiration date AND have been provided to the warantee folks AND have not had all their line numbers filled yet.
To get at the expiration date, make a query of tblSETR that shows SETRs where the expiration date field is greater that the current date.
To get at the question as to whether it has been given to the warantee folks, add another where clause to the query so that only SETRs with -1 in the tblSETR!SETRExported field are shown.
To get at the Line number issue make a query of tblSETRModelParts and limit the results to records where LineNumberExported = 0. Join this to your query that finds unexpired, exported SETRs etc such that only SETRs common to both are shown. That would be the list of active SETRs that would meet the third option.
Also, Colin, I note that you've placed the dangerous goods yes/no field in tblSETRModelParts instead of tblParts as I recommended. Just to be clear, this decision denormalizes your database and requires you to assign a dangerous goods value during data entry each time you add a part to a setr rather than just once when you add a new part to the db. Seems like a lot of make-work to me.
Before you do anything more please go through and fix/delete the queries that you have added, or fix the ones you have broken, or all the code that was written for you will not function.
And remember, the more you tinker with this db the further it moves away from something I understand and can provide advice on. If you insist of changing names of tables then you must make damned sure that any objects that reference those tables are updated to reflect those changes. That includes CODE and QUERIES. I do not feel inclined to debugging or fixing such problems as they are self-inflicted at this point.
).