Form to enter data across multiple linked tables.

[ED007]

I have three tables.

<LOT>
LOT – PRIMARY FIELD
GRADE – TEXT
ETC.

<DRUMS>
DRUM COUNT – AUTONUMBER
LOT – LINKED TO LOT FIELD FROM LOT TABLE
DRUM ID – INTEGER (RANGES FROM 1-25)

<ASH>
ASH DATA COUNT – AUTONUMER
DRUM COUNT– LINKED TO DRUM COUNT IN DRUMS TABLE
SAMPLE – INTERGER FROM 1-5
ASH LEVEL – DOUBLE

I have set up a form that will let me enter all the required data for the
<lot> table and other tables that are directly linked to it. However, I am
having some trouble figuring out how to set up the form to let me enter the
ash level data.

Basically what I want is a table that contains three columns.

DRUM ID SAMPLE ASH DATA

The user would just fill up the table for the lot in question.

Any ideas?

 

[ndalton]

Maybe a couple of subforms?

Your main form would have only info on LOTs; embedded in the LOT form would
be a subform for info only on DRUMs; embedded in THAT subform would be a
THIRD subform with all the information about ASH.

Subforms can be a very flexible way to do stuff like this. That's how I
would do it.

Hope that helps. Good luck.

-ndalton

 

[ED007]

I had thought about that and I can do it that way, BUT it is a lot of mouse
movement for the user. Enter the drum number, click on the sub form with the
mouse, enter the sample number and result, click back on the other sub form,
enter the second drum, click on the other subform, etc.

Is their another option, or do I not have the subforms set up correctly for
easy data entry?

Ed

 

[ndalton]

I see. Yeah, I don't think there's any way to get into a subform without
clicking there, but then again I'm rather new to this.

Hmmm...what are the relationships between LOT and DRUM, and between DRUM and
ASH. I'm guessing they're 1:M and 1:M, respectively. Is this correct?

 

[ED007]

Yes both relationships are one to many.

I am stuck on this.

 

[ndalton]

I'm thinking it's impossible without subforms, my reasoning being this: each
form (whether subform or regular) can have only one Record Source, and if you
want to be able to edit records, the record source has to be a table, so
within a SINGLE form, you can only edit data from ONE table.

There MAY be some way to make it happen programatically with DAO or ADO, but
I don't even want to start thinking about how to do that.

This is a toughie; sorry I couldn't help you out. Let me know if you find a
solution. Good luck.

-ndalton

 

[ED007]

OK I thought I almost have it.

I wrote a querry that pulled

LOT - DRUM - SAMPLE - ASH VALUE

from the three related tables.

I then wrote a subform that displayed
DRUM - SAMPLE - ASH VALUE

And placed it on the main form linking the lot fromt eh main form to the lot
(not shown) on the sub form.

This works reasonably well, EXCEPT it will not let me add any new info. I
can change some of the data that it present, differnt ash value, change a
sample #, BUT not change the drum #. If I change the drum number it changes
all the drums of that #. For example if I three sets of data that are for
drum 1 and I realize that the las one is really for drum 6 when I change the
1 to a 6 all the drum 1s are changed to drum 6.

 

[ndalton]

My skull hurts thinking about it now...it's getting really hard for me to
understand what's going on. Maybe you could email me your database; I would
have time to look at it sometime tomorrow. If you want to, email your .mdb
file to: (e-mail address removed)

-ndalton

 

[David M C]

I've had this same problem. The best way I could think of was using subforms.
The main form should be for your lots. Then have two subforms, one for drums,
and one for ash (don't have ash as a subform of drums). The record selected
in the main form will determine the drums displayed. The drum selected will
determine the ash records displayed.

Each subform should be based on a query. The Primary key field should have
the criteria [Forms]![Formname]![PrimaryKeyControlName]. In the On Current
event of each subform, you should requery the subform affected by the change
in selection (so the drums subform should requery the ash subform).

Whilst you could achieve the same affect with multiple levels of nested
subforms, this way is much easier to reference when using code.

Dave