Resettable counter

[Guest]

Hello,

I have 2 tables that are linked in a 1-to-many relationship. So for every
record in the parent table, there could be several records in the child table.
What I would like to do is enter one record for the child table on the
parent table's input form, and then let the user decide if he wants to add
any more records via a "make another entry" button.
On the main form, I would like to number such entries 1 -> n for every
record in the parent table

I guess I could just put a number of subforms on the main form and label
them 1 - 5 and make them visible if the button is pressed.
But I'm wondering if this can be done programmatically so that when I press
that button, a new subform will be inserted into the main form and numbered
(n+1).

This might be overly ambitious for me, but I'd like to get a feel for the
feasibility of this idea, and how much work it would require to implement it.

Thank you.

 

[Guest]

This is easily done in a single user environment, but less so in a multi-user
environment where steps need to be taken to prevent conflicts if two users
are adding rows simultaneously. I'll deal with the first scenario in some
detail below, but only give a brief outline of an approach to the second. We
can always come back to it if you want to give it a try.

In a single user environment you simply need to look up the last number in
the child table for the parent table's current record and add 1. I'd suggest
you use a tab control on the form, putting the control's bound to the parent
form's fields on the first page and the subform on the second. You then
don't have to worry about hiding/showing the subform as all the user has to
do is tab between the two pages of the tab control. You might want to put
some of the parent form's controls directly in its detail section above the
tab control so they'll be visible as identifiers when the user tabs to the
subform.

The only code you need is in the BeforeInsert event of the subform, so that
when a user starts to add a new child record it looks up the last number.
Let's assume the parent form is based on a Customers table and the subform on
Orders and they are linked by CustomerID. The field you want to update in
Orders with the incrementing number is called CustomerOrderNumber. So the
code would be:

Dim lngNewNumber as Long
Dim strCriteria As String

strCriteria = "CustomerID = " & Parent.CustomerID
lngNewnumber = Nz(DMax("CustomerOrderNumber", "Orders", strCriteria),0) + 1

Me.CustomerOrderNumber = lngNewNumber

When the user tabs to the subform it won't necessarily be at a new record of
course. You could force it to go to a new record automatically if you wished
with the following in the Change event procedure of the tab control:

If YourTabControl = 1 Then
Me.YourSubformControl.SetFocus
DoCmd.GoToRecord , Record:=acNewRec
End If

Note that a tab control's value is 1 when its on the second page, the Value
property being zero based. Or you could put a button on the main form to add
a new subform record with:

YourTabControl = 1
Me.YourSubformControl.SetFocus
DoCmd.GoToRecord , Record:=acNewRec

In a multi-user environment a common means of avoiding conflicts is to store
the last used numbers in an external database which is opened exclusively in
code to get and update the number. I make 10 attempts to open the external
database in a randomly timed loop. It extremely unlikely that none of these
will succeed, so the process is transparent to the user. The code to do this
is not trivial, but nor is it unduly complex. You can find a demo of mine at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


but it only deals with a single set of sequential numbers, not subsets as in
your case. It only requires minor amendments to do the latter, however.

Ken Sheridan
Stafford, England