Auto increment and set range

  • Thread starter Thread starter Frank Knox via AccessMonster.com
  • Start date Start date
F

Frank Knox via AccessMonster.com

I would like to set up a simple sample tracking database. I have a form
with four fields.
RackId, RackNumber, Row and Colmun.
I have RackID set to the day of the week. I would like row to have the
range A-G, and Column to have the range 1-12. I would like the row number
to increment by 1 when the column number reaches 12 ie:
A11, A12, B1, B2 etc. When the G12 is reached I would like the rackNumber
to increment by 1. Is there a simple way to do all this? Any help would be
greatly appreciated.
please reply to fhknox(at)sbcglobal.net

Thank You in advance
 
First, how do you want to handle one week from the next? If last Tuesday you
got up to A11, do you want the first one this Tuesday to be A12 or start
from A1 again?
 
Racks will only be kept for 7 days. i will start a new rack then.
Thanks
Frank
 
First, I might set a Unique Index across all of the fields (include all the
fields in a single index) so that Access would prevent me from making a
mistake and duplicating an entry.

To get the incrementing you are wanting, you would need to call a routine in
the form's Current event that would make the calculations you are wanting.
You would check for the form being at a new record, and if so, call the
routine.

Example:
If Me.NewRecord Then
'call the routine
End If

From your example, the possible values are RackId (1-7), RackNumber (1-?),
Row (A-G), and Column (1-12). So, in the routine, you would need to find the
maximum of each lower rank where the higher rank = its maximum for the day
(1-7) in question. You would then add 1, check to see if that exceeds the
maximum, and if so, start over at 1 or A and add one to the next higher
column, again checking to see if that exceeds is maximum.

Opening a recordset sorted on the columns in the order listed, the last
record for the current day should be that maximum to that point for all of
the fields entered. (I changed Row and Column since these are reserved
words.) You can simply set the Default Value of the RackId textbox to
=Weekday(Date())

Dim rst As DAO.Recordset, db As DAO.Database, strSQL As String
Dim lngRackNumber As Long, strRackRow As String, intRackColumn As Integer
strSQL = "SELECT RackId, RackNumber, RackRow, RackColumn FROM tblMyTable
WHERE RackId = WeekDay(Date()) ORDER BY RackNumber, RackRow, RackColumn;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
'Check to see if there are any records
'if not, then this is the first record for the day
If rst.EOF And rst.BOF Then
Me.txtRackNumber = 1
Me.txtRackRow = "A"
Me.txtRackColumn = 1
Else
rst.MoveLast
If rst!RackColumn < 12 Then
intRackColumn = rst!RackColumn + 1
strRackRow = rst!RackRow
lngRackNumber = rst!RackNumber
Else
intRackColumn = 1
If strRackRow < "G" Then
strRackRow = Chr(Asc(strRackRow) + 1)
lngRackNumber = rst!RackNumber
Else
strRackRow = "A"
lngRackNumber = rst!RackNumber + 1
End If
End If
End If
Me.txtRackNumber = lngRackNumber
Me.txtRackRow = strRackRow
Me.txtRackColumn = intRackColumn
rst.Close
Set rst = Nothing
Set db = Nothing

This is untested, but should get you started. Watch for text wrapping due to
the news reader. The strSQL= line will definitely wrap, it should all be on
one line. This is also assuming that you have cleared the previous week's
records from the table for this weekday prior to running this.
 
Back
Top