Form Control Number Issues

[donaldsj]

I’m attempting to build a database and I’m having a problem concerning control numbers. Our office tracks lost items from five different work sections and each section has it’s own set of control numbers, which are alphanumeric. Unfortunately we’re bound to using these style numbers. I initially started to build separate forms and tables for each section but then I thought it might be better to use one form and one table. By using one form I could great a drop down menu to select the work center and then theoretically automatically have the control number field filled in.

The control number consists of year (YY), work center ID, and sequence number (000), i.e. 0125FSLT001. I’d like the year to automatically sequence at the start of the fiscal year (1 Oct), and have the sequence number reset to 001 at the same time. Then throughout the year have the sequence number automatically sequence when new records are added. One section may loose more items than another, so each work centers IDs needs it’s own set of sequence numbers. That leads me to believe I need to at least build separate control number tables for each work center.

So the complicated questions are how do I get a drop down to automatically display the control numbers in a separate field? How do I get the year and sequence number to update and reset at the start of the New Year? And since the year, work section IDs, and sequence numbers might be separate field in “separate control number tables,” how do I merge them into one field in a form and store as one alphanumeric number in another table along with the rest of the data on the lost item? I probably need to consider the multi user issue too. I don’t see a situation where two people will be entering data into the form at the same time, but you never know.

In case you’re curious the items we’re tracking are lost tools, support equipment, and personal items. We also track if the items were found/recovered.

Any help or idea will are certainly appreciated. Thanks! Jeff

 

[jimbrooking]

Jeff,

Long question, lots of things to think about. Hope I can give you some ideas.

First, dealing with the Oct 1 Fiscal Year: You could adopt a convention that internal dates are offset by 90 days so it's easy to extract the fiscal year given an internal date. Calculate an internal date using, e.g., DateAdd( "m", 3, Now() ) You can "unconvert" using DateAdd( "m", -3, intDate ) in a query. The Fiscal Year of an "internal date" will be Format(intDate,"yy").

Next, thinking about the Control Number- Your losses table could have three fields: Year, WorkCtr, and SeqNo. Taken together these three fields would constitute the primary key for the table. For display purposes you could (in a query) define CtrlNo as

Format(Year,"00") & WorkCtr & Format(SeqNo,"000")

When a user signs on to your application you need to determine his/her WorkCtr somehow. If you use Access usernames/passwords you could look people up in a table containing possible userIDs (see UserID() function for Access 97 and earlier, or the UserName property in 2K or later -- I think -- don't use this much) and lookup the username in a table containing usernames and WorkCtrs. There may also be a way to assign users to groups that are named after work centers but I'm not smart on that form of security. Once you identify the WorkCtr you can save it in an invisible text box on a Main Menu (e.g., a switchboard form) so it can be referenced anywhere as Forms!frmMainMenu!tbxWorkCtr.

Now you can generate a query like

SELECT * From tblLosses WHERE WorkCtr = Forms!frmMainMenu!tbxWorkCtr

and base all the work center-specific forms and reports on this query.

To maintain a "next sequence" table, I'd define a table with a text field for the WorkCtr and a number field for the sequence number.

When you need a new sequence number you can read in as

intSeq = DLookup("NextSeq","tblSeqNos","WorkCtr = """ & Forms!frmMainMenu!tbxWorkCtr & """" )

Set next sequential number in this table using

strSQL = "UPDATE tblSeqNos SET NextSeq = " & intSeq + 1 & " WHERE WorkCtr = """ & Forms!frmMainMenu!tblWorkCtr & """;"
DoCmd.RunSQL strSQL

You may want to fool around with record locking but unless you have a slew of users I wouldn't bother; just don't dawdle when you get a number and save a new one. DO, however, set (Tools>Options Advanced tab) Record Locking to Edited Record in case you have more than one user fiddling with the same loss record at the same time.

Oh, and finally you need to reset "next sequence numbers" on Oct 1. Try something like

If DCount("SeqNo", "tblLosses", "Year = " & Format(DateAdd("m",3,Now()),"yy") ) = 0 then
...Reset SeqNo to 1 in tblSeqNos for all WorkCtrs
End If

in the database main menu's Open event. This resets all sequence nuumbers until some department logs a loss. The first loss will cause the DCount to return a non-zero so it will not update again until the FY changes next.

Hope some of these idea may be helpful.

Jim