auto field fill in in form

[canroc]

(I'm a Newbie here):

I have a table of household items with fields including "container" (i.e. boxes etc.) and "room". I have a table of "container-rooms" in which each container is associated with a room it is in.

On my form I enter the container in which the item is in, if it is in a container, and I enter the room. But, the container already is associated with a room, so if a container is entered then I'd like the room field to be automatically populated with the room associated with that container.

Any ideas on how to do this?

BTW, I think it is time for me to buy a good book on Access.

 

[Darrylles]

Hi,

The way to do this, you should have 3 tables:
[tt]
tblRoom tblContainer tblItem
pk <-| pk <-| pk
RoomNo | BoxNo | ItemNo
desc | desc | desc
|->> room_fk |->> container_fk
[/tt]
pk = primary key, _fk = foreign key.
All pk's and xxxx_fk's are long integer, pk's are autonumber.

Create 3 forms: frmRoom, frmContainer & frmItem.

Add the frmItem form to the frmContainer form as a subform, add this frmContainer form to the frmRoom form as a subform.
Complete the master/child properties using the pk and _fk's.

When you add a new container in a room, then the tblRoom pk value is copied to the new tblContainer record's room_fk field.
When an item is added to a container, the tblContainer!pk value is copied to the new tblItem record's container_fk field.

Regards,

Darrylle




Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com

 

[canroc]

Thanks for the reply. I think my first append was not explained well.

I do have a table similar to the reply, however I have an additional field in tblItem, let's call room_fk

tblRoom tblContainer tblItem
pk <-| pk <-| pk
RoomNo | BoxNo | ItemNo
desc | desc | desc
|->> room_fk |->> container_fk
|-------------------->> room_fk

Not every item is necessarily in a container but I want to know what room it is in. Room_fk needs to be entered in manually if the item is not in a container, or filled in automatically based on what is in container_fk.

How to do this automatic entry is what I am trying to figure out.

Thanks.

 

[medic133]

Not sure if this will work, but what about applying a filter? Create a query (query1) to include botht the room and the container fields. In the criteria for the room enter [forms]![formname]![roomfieldname]. Then in the LostFocus event of the room field on the form do the following:

dim varx as integer
varx=Nz(DCount("roomfieldname","queryname"),0)
If varx <> 0 then
Docmd.ApplyFilter "queryname"
end if

Somewhere in code when you go to a new record, you may need to set the filter to nothing (me.filter="") to keep the filter from being applied all the time. Hope this at least gives you an idea! Good luck!!

 

[Darrylles]

Hi again (better late than never),

Be assured that my girlfriend and I have fought over this (if it works, a star is expected - lol).

New table design ('box' instead of 'container' to save space):
[tt]
tblRoom tblBox tblBoxItem
pk <---| pk <-----| pk
desc | desc |-->> box_fk
|-->> room_fk item_fk <---|
| |
| |
| tblRoomItem tblItem <---|
| pk |---> pk
|-->> room_fk | desc
item_fk <-----|
[/tt]

As an aside - terminology:

'1 to many' means:
0 or 1 or many record(s) exist and EACH may be related to 0, 1 OR many records from another table.

'1 to 1' means:
0 or 1 or many record(s) exist and EACH may be related to 0 OR 1 record from another table.

tblItem is basically a lookup table for BOTH tblBoxItem and tblRoomItem to ensure no duplicated records, but the relationship is a real 1 to 1 nevertheless.

tblRoomItem and tblBoxItem are simply 'link' tables containing no 'real' data; they POINT to real data in tblItem.

If you look at the design as if in the real world, the design fits: a room has boxes which contain items. A room also contains it's own items.

If an item with the same name exists in the real world, then it must have a distinguishing feature. This can be stored in the tblItem table with it's distinguishing feature in field Desc.

I hoped you had at least some understanding of relational databases, your response says that you have.

The above is my only solution, and it seems to be logical. IF someone comes up with a better one - I'd be very interested.

Regards,

Darrylle





Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com

 

[Darrylles]

Hi again,

Sorry, tblBoxItem!item_fk should point to tblItem!pk - NOT the tablename.

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com

 

[canroc]

Hi,

Thanks for the append.

Sorry for the late response, but I have not been able to look at this until now.

Your solution makes perfect sense for this problem.

I now need to figure out how to set up my forms for data entry so that all tables get updated properly. This might get a little tricky.

 

[Darrylles]

Hi can,

Graphically, this may make more sense - no logical changes though..

[tt]
tblRoom tblBox tblBoxItem
pk <---| pk <-----| pk
desc | desc |-->> box_fk
|-->> room_fk item_fk <-----|
| |
| |
| tblRoomItem | tblItem
| pk |---> pk
|---------------------->> room_fk | desc
item_fk <-----|

[/tt]

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com

 

[canroc]

Actually, I think I can go back to the original simple design. In the item table, I'll just leave the room_fk as NULL when the box_fk is populated (I'm doing this now in my form).

So, when there is a box, the room_fk is NULL, when there is no box, the box_fk is NULL and room_fk has a value.

When generating a query or report, I'll look to see if box_fk is not NULL, and when so, I'd get the associated room from the box_fk and fill in the value in the report.

This makes it simple. Of course if NULL values are allowed in tables that is, and it seems that they are.