Sequence Multiple Tables

[Musa via AccessMonster.com]

Hi,

I have a Client table and three additional tables Screen , Assess, and Follow-
up.

The Client table automatically assigns the Client_ID. This ID is carried
over to the other tables.
Now, a Client may have 1 or more Screenings, Assessments, and Follow-ups.
Using sequencing, how would I sequence the other three tables ?

For Example,
Client ID Screening Assessment Follow up
1 1 1 1
1 2 2
1 3
2 1 1 1
3 1 1 1
3 2 2
3 3 3 etc...

 

[Klatuu]

One way would be to use AutoNumber primary keys for the other tables, but
that would give you what you want.

I would suggest creating a Long Integer field for the other tables. Then
when you want to add a new record to one of the tables, find the highest
current value for the field and add 1 to it. I would use the Form Current
event:

If Me.NewRecord Then
Me.txtSeqNum = Nz(DLookup("[SeqNum]","Screen", "[ClientID] = " &
Me.Parent.txtClientID),0) + 1
End If

Note, Screen is not a good name in Access. It is a reserved word and can
cause problems.

 

[Musa via AccessMonster.com]

Thanks for your response.

The sequence number is starting on 2. Also, the Me.Parent was not recognized.


Private Sub Form_Current()
If Me.NewRecord Then
Me.SEQ_ID1 = Nz(DLookup("[SEQ_ID1]", "INITIAL_tbl", "[Survey_ID]= " & Me.
Parent.txtSurvey_ID), 0) + 1
End If
End Sub

One way would be to use AutoNumber primary keys for the other tables, but
that would give you what you want.

I would suggest creating a Long Integer field for the other tables. Then
when you want to add a new record to one of the tables, find the highest
current value for the field and add 1 to it. I would use the Form Current
event:

If Me.NewRecord Then
Me.txtSeqNum = Nz(DLookup("[SeqNum]","Screen", "[ClientID] = " &
Me.Parent.txtClientID),0) + 1
End If

Note, Screen is not a good name in Access. It is a reserved word and can
cause problems.

Hi,

[quoted text clipped - 15 lines]

3 2 2
3 3 3 etc...

 

[Klatuu]

I was assuming your client table was a main form and the other tables were in
subforms. Is that not correct?

Aslo, it was air code, so errors may be expected.
--
Dave Hargis, Microsoft Access MVP

Thanks for your response.

The sequence number is starting on 2. Also, the Me.Parent was not recognized.


Private Sub Form_Current()
If Me.NewRecord Then
Me.SEQ_ID1 = Nz(DLookup("[SEQ_ID1]", "INITIAL_tbl", "[Survey_ID]= " & Me.
Parent.txtSurvey_ID), 0) + 1
End If
End Sub

One way would be to use AutoNumber primary keys for the other tables, but
that would give you what you want.

I would suggest creating a Long Integer field for the other tables. Then
when you want to add a new record to one of the tables, find the highest
current value for the field and add 1 to it. I would use the Form Current
event:

If Me.NewRecord Then
Me.txtSeqNum = Nz(DLookup("[SeqNum]","Screen", "[ClientID] = " &
Me.Parent.txtClientID),0) + 1
End If

Note, Screen is not a good name in Access. It is a reserved word and can
cause problems.

Hi,

[quoted text clipped - 15 lines]

3 2 2
3 3 3 etc...

 

[Musa via AccessMonster.com]

No, the client table is separate from the other 3 tables.

The User enters information in the Client table and then Chooses (Initial ,
Assess, or Follow-up) to complete. There may be 1 client with more than one
Initial, Assess or Follow-up. I want to be able to tell the sequence of
records if a User places more than 1 record in any of the above tables.

All tables are in separate forms and are based on Client information being
present.

Thanks. (BTW, I took your suggestion and changed the name from SCREEN to
Initial)

I was assuming your client table was a main form and the other tables were in
subforms. Is that not correct?

Aslo, it was air code, so errors may be expected.

Thanks for your response.

[quoted text clipped - 27 lines]

 

[John W. Vinson]

I want to be able to tell the sequence of
records if a User places more than 1 record in any of the above tables.

One way to keep track of the sequence in which records are entered is to put a
Date/Time field in the table with a default value of Now(), to timestamp each
entry the instant it is created.

 

[Musa via AccessMonster.com]

I placed the following code in the Form's Before Update

If IsNull (Me! [ID]) Then
Me![SEQ_ID] = Nz (DMax ("[SEQ_ID]", "Initialtbl"),0)+1

The Results:

SurveyID (Autonum) SEQ_ID

30 1
31 2
30 3


I would like to get the following :
SurveyID (Autonum) SEQ_ID
30 1
31 1
30 2 etc...

 

[John W. Vinson]

If IsNull (Me! [ID]) Then
Me![SEQ_ID] = Nz (DMax ("[SEQ_ID]", "Initialtbl"),0)+1

The Results:

SurveyID (Autonum) SEQ_ID

30 1
31 2
30 3


I would like to get the following :
SurveyID (Autonum) SEQ_ID
30 1
31 1
30 2 etc...

Include the SurveyID as the optional criterion in your DMax:

Me![SEQ_ID] = Nz (DMax ("[SEQ_ID]", "Initialtbl", "[SurveyID] = " _
& Me!SurveyID),0)+1