Repeating data in table that changes every "x" records

[BYarn]

We have a Access database. One of the columns stores a "Box #" for files that are stored in the box. The idea being that a files' location can be determined by the # of the box it is in.

Each box holds, say, 25 files each of which will obviously have the same 9 digit box # in the (yep) box # column. The box #'s may not be in sequence (ie, 123 and 124 may be followed by 132 and not 125)

The problem: when the first file is loaded into the DB with the new box #, can that # be repeated down the column until it's time for another box #. Bottom line-how to avoid typing the same box # over and over

 

[GingerR]

Are you typing directly into a table? A quick trick is to use keyboar ctrl+' (ctrl apostrophe).

Cooler solution #1: I suggest using Form/Subform setup.

How do you have your tables set up? If you only have everything in one table, i suggest doing this:

Have one table that is BOX# data: tblBoxes
Field: BoxNumber

Have a second table this Files + Boxes: tblBoxFiles (pretty much what you probably already have)

Field: BoxNumber
Field: FileNumber
Field: FileContents
etc

Then make a form with the recordsource of tblBoxes. Put the BoxNumber on it.
Make another form from tblBoxFiles.
Put the second form on to the first form, making it a subform. Make the child/master links be BoxNumber. That way anything you type into the subform will automatically have the same BoxNumber.


Cooler Solution #2: Another option is to just make one form with your current table as the recordsource. You could put in the form's OnCurrent event:

IF YOUR BoxNumber starts out as a 0 do this:

If Me.BoxNumber = 0 Then
     Me.BoxNumber.SetFocus
     SendKeys "^'" 'ctrl apostrophe copies previous rows data
    End If

IF your boxnumber starts out as null do this:

If isnull(Me.BoxNumber) Then
     Me.BoxNumber.SetFocus
     SendKeys "^'" 'ctrl apostrophe copies previous rows data
    End If

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244