Related tables - passing information from one to another ?

[Robin Hall]

Is there a way of making the value of a field in one table dependent
upon the fields in another linked table?

I have (what started out as) a simple database to record returned
goods which has two tables. These have a one-to-many relationship;
one records the 'header' for each 'RMA' or set of goods (account no,
customer, location, date) and the other the 'lines' of the RMA.
They are linked through the autoassigned primary key of the 'header'
table, which is a four digit RMA number.
I have managed to set up the link with referential integrity so that
you cannot enter lines if the corresponding RMA does not exist.

What I am trying to do is have a field in the 'header' table which
counts how many lines in the 'lines' table match on the RMA number.

Is there any way to do this?

I have a suspicion now that I have considered the problem for a while
that I am taking the wrong approach (would I be better off not
including this information in the table but performing a 'count' of
matching lines in a form or report when I want to display the data -
otherwise am I trying to store the same information in two places,
which would be pointless ?) but I would be grateful to know whether it
can be done now that I have spent a while trying.

What I would really like to be able to do is write a form to enter RMA
lines which, when you enter the RMA number, will present you with the
next sequential line number for that RMA. I realise I have gone
sideways a little from that aim to the above question but I'm very
much a beginner and keep thinking of new questions

Thankyou for any advice,
Robin

 

[John Vinson]

I have a suspicion now that I have considered the problem for a while
that I am taking the wrong approach (would I be better off not
including this information in the table but performing a 'count' of
matching lines in a form or report when I want to display the data -
otherwise am I trying to store the same information in two places,
which would be pointless ?) but I would be grateful to know whether it
can be done now that I have spent a while trying.

Your suspicion is quite correct: you should NOT be storing this in
your header table. Instead use a Form (you shouldn't be looking at
table datasheets at all) with a Subform. The subform will show "record
n of m" on its navigation buttons automatically; or, you can put a
textbox txtCount on the subform footer with a Control Source of

=Count(*)

Then on the mainform you can put a second textbox with a control
source of

=subformname.Form!txtCount

where subformname is the Name property *of the Subform control* (not
necessarily the same as the name of the Form within that control).

What I would really like to be able to do is write a form to enter RMA
lines which, when you enter the RMA number, will present you with the
next sequential line number for that RMA. I realise I have gone
sideways a little from that aim to the above question but I'm very
much a beginner and keep thinking of new questions

This can be done with a single line of VBA code. In the Subform's
BeforeInsert event click the ... icon and invoke the Code Builder;
Access will give you the Sub and End Sub lines:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtLineno = NZ(DMax("[LineNo]", "[tablename]", _
"[RMA] = '" & [RMA] & "'")) + 1
End Sub

assuming that RMA is a Text field (leave off the ' and "'" if it's
Number).