Prev: Junction Tables
Next: Auto populate field in table
From: dutchgenius on 27 Apr 2010 12:35
I am using a simple form for the user to enter data into the main table. one
of the fields available is "Box Number" and each of these boxes holds 16
items. i use a separate field "slot number" to designate where each item is
stored in the box. is there a way to store the data when entering it into
the form, so that each slot number can only be used on time for each box
number?

i want to create some sort of data checker or error message that will tell
the user "this slot number in box X is already used".

any ideas how i can set this up? cheers
From: BruceM via AccessMonster.com on 27 Apr 2010 13:34
If each record is for information about a Box, you may have something like
this for tables:

tblBox
BoxID (primary key, or PK)
BoxNumber
Description
etc.

tblSlots
Slot ID (PK)
BoxID (link to tblBox)

Have a main form based on tblBox, with a subform based on tblSlot. If the
slot numbers are 1-16, you could have a text box (txtSlot) bound to SlotID.
In the form's Current event:

Me.txtSlot.DefaultValue = _
Nz(DMax("[SlotID]","[tblSlots]","[BoxID] = " & Me.Parent.BoxID),0) + 1

The underscore is a line continuation character for VBA. It is not needed,
but I used it here in case of word wrapping in the newsreader making the code
hard to read.

The above assumes BoxID is a number field. I don't know if BoxID should be
the same as BoxNumber or not. It depends in part on whether there could be
more than one record in tblBox for a given box number.


dutchgenius wrote:
>I am using a simple form for the user to enter data into the main table. one
>of the fields available is "Box Number" and each of these boxes holds 16
>items. i use a separate field "slot number" to designate where each item is
>stored in the box. is there a way to store the data when entering it into
>the form, so that each slot number can only be used on time for each box
>number?
>
>i want to create some sort of data checker or error message that will tell
>the user "this slot number in box X is already used".
>
>any ideas how i can set this up? cheers

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201004/1

From: KARL DEWEY on 27 Apr 2010 18:53
If I understand you, I think you need a compound index set to No Duplicates.

--
Build a little, test a little.


"dutchgenius" wrote:

> I am using a simple form for the user to enter data into the main table. one
> of the fields available is "Box Number" and each of these boxes holds 16
> items. i use a separate field "slot number" to designate where each item is
> stored in the box. is there a way to store the data when entering it into
> the form, so that each slot number can only be used on time for each box
> number?
>
> i want to create some sort of data checker or error message that will tell
> the user "this slot number in box X is already used".
>
> any ideas how i can set this up? cheers
 | 
Pages: 1
Prev: Junction Tables
Next: Auto populate field in table