Help needed designing Database

[Deus DNE]

Hi all,

I have 6 locations with unique alpha identifiers. Each location needs to
input jobs (31 fields of info) and obtain a unique UD for that job (Location
ID-Job Number-YY)

So first job entered is for location B this is given job ID 1 Unique ref
would then be B-0001-05

Second job is for location C this should be given job ID 1 (as it's the
first job for C) Unique ref would then be C-0001-05

Ad nauseum for all 6 locations.

an job entered has to have the job ID incremented by one based on the last
number given to that location.

All i can see is that autonumber is out, and some specific script written.

Currently this database has been put together with 6 identical tables named
as per the locations.

Serious amounts of help appreciated.

Thanks In Advance

Deus

 

[Tim Ferguson]

I have 6 locations with unique alpha identifiers. Each location needs
to input jobs (31 fields of info) and obtain a unique UD for that job
(Location ID-Job Number-YY)

Currently this database has been put together with 6 identical tables
named as per the locations.

Bad Idea -- you've not given any indication of any good reason why they
should be separate. Having one table will make much of your work later on
much easier.

All i can see is that autonumber is out, and some specific script
written.

It's not too hard, but the structure of Access means that you have to take
control of all record inserts. As long as the users don't have access to
table datasheets, querydefs, and so on then you can control it all with
forms.

I'll assume you have one table, with fields called LocationID, SerialNum,
YearNumber; and controls on the form called txtLocationID, txtSerialNum,
txtYearNumber bound to the appropriate fields.

You'll need a little bit of VBA that looks like

' this makes a string that looks like this:
' LocationID = "NYRK" AND YearNumber= 2005
' which is the same as the WHERE clause would be in a query
strWhere = "LocationID = """ & txtLocationID & """ AND " & _
"YearNumber = " & txtYearNumber


' varTemp should be dimmed as a Variant because it
' might get a Null returned
varTemp = DMax("SerialNumber", "Jobs", strWhere)

If IsNull(varTemp) Then
' if there are none already in the database, we have to
' start at one
txtSerialNumber = 1

Else
' okay, we need one more than the last biggest one
txtSerialNumber = varTemp + 1

End If


A couple of caveats: this is not safe in a multi-user setup, because a
second user might read the same DMax before the first one has written his
or her record back.

This code can in fact all be done in one line, but I broke it out so you
can see what is happening.

Hope that helps


Tim F