Last Value carried over to New Record

[Musa via AccessMonster.com]

I have two forms
Form1 Pk SurveyID (autonumber)
Seq1
Form2 SurveyID

Based on the SurveyID, I would like to take the last generated sequential
number (Form1) and place it in a newly created record in Form 2.


The data should look like this

Table1 SurveyID Seq1 Table 2 SurveyID Seq1 (on newrecord)
1 1 1 3
1 2 - -
1 3 - -
2 1 2 1
3 1 3 2
3 2 - -

 

[Klatuu]

Pass the new number to the form you are opening using the OpenArgs argument
of the OpenForm method.
Then in the Load event of the form being opened, populate the control it
goes in with the value of the OpenArgs property

If Not IsNull(Me.OpenArgs) Then
Me.txtTheNumberControl = CLng(Me.OpenArgs)
End IF

 

[Musa via AccessMonster.com]

Thanks.

I'm sorry I don't know what you mean when you say pass the new number to the
form you are opening..? Set as a default in the Openform method ?

Pass the new number to the form you are opening using the OpenArgs argument
of the OpenForm method.
Then in the Load event of the form being opened, populate the control it
goes in with the value of the OpenArgs property

If Not IsNull(Me.OpenArgs) Then
Me.txtTheNumberControl = CLng(Me.OpenArgs)
End IF

I have two forms
Form1 Pk SurveyID (autonumber)

[quoted text clipped - 13 lines]

3 1 3 2
3 2 - -

 

[Klatuu]

OpenArgs is the last (#7) argument of the OpenForm method.
So, for example let's say the control on the first form that has the number
you want to pass to the second form is txt.TheNumber, you would use that in
OpenArgs argument:

Docmd.OpenForm "The Other Form", , , , , , Me.txtTheNumber

The code I posted previously goes in the Load event of the "The Other Form"

It receives the value and populates a control with it.
--
Dave Hargis, Microsoft Access MVP

Thanks.

I'm sorry I don't know what you mean when you say pass the new number to the
form you are opening..? Set as a default in the Openform method ?

Pass the new number to the form you are opening using the OpenArgs argument
of the OpenForm method.
Then in the Load event of the form being opened, populate the control it
goes in with the value of the OpenArgs property

If Not IsNull(Me.OpenArgs) Then
Me.txtTheNumberControl = CLng(Me.OpenArgs)
End IF

I have two forms
Form1 Pk SurveyID (autonumber)

[quoted text clipped - 13 lines]

3 1 3 2
3 2 - -

 

[Musa via AccessMonster.com]

Sorry, I guess I'm not explaining my problem clearly.

I have a Client tbl/frm - Autonum Surveyid (PK)
Initial tbl/frm - Surveyid & Sequence number
Followup tbl/frm - Surveyid & Sequence number

The Initial tbl/frm and Followup tbl/frm are dependent on the Client tbl/frm.
When the User creates a Client record, they may complete more than one
Initial frm and followup frm for that Client. So, there will be 1 Client
record and multiple Initial and Followup records. The Initital frm is always
created before the Followup form.
Based on the Client record, I want to be able to automatically place the
LAST record Sequence number that was added to the Initial frm/tbl and place
it in a field (on a new record) of the Followup form.
So that, Client # 1 - may have 2 Initial records and the Followup form will
show that Client #1's LAST initial record for that particular Client ID (in
this case the Client has # 2 records).
Therefore, the data would like this :
SurveyID Seqnum
Client tbl 1
Initital tbl 1 1
1 2
Followup tbl 1 2
etc...

Thanks.

OpenArgs is the last (#7) argument of the OpenForm method.
So, for example let's say the control on the first form that has the number
you want to pass to the second form is txt.TheNumber, you would use that in
OpenArgs argument:

Docmd.OpenForm "The Other Form", , , , , , Me.txtTheNumber

The code I posted previously goes in the Load event of the "The Other Form"

It receives the value and populates a control with it.

Thanks.

[quoted text clipped - 14 lines]

 

[Klatuu]

Then you can read the value directly from the Client form when you create a
new record in the Initial or Followup forms. This would be in the form
Current event:

If Me.NewRecord Then
Me.txtSurveyID = Forms!frmClient!txtSurveyID
End If

--
Dave Hargis, Microsoft Access MVP

Sorry, I guess I'm not explaining my problem clearly.

I have a Client tbl/frm - Autonum Surveyid (PK)
Initial tbl/frm - Surveyid & Sequence number
Followup tbl/frm - Surveyid & Sequence number

The Initial tbl/frm and Followup tbl/frm are dependent on the Client tbl/frm.
When the User creates a Client record, they may complete more than one
Initial frm and followup frm for that Client. So, there will be 1 Client
record and multiple Initial and Followup records. The Initital frm is always
created before the Followup form.
Based on the Client record, I want to be able to automatically place the
LAST record Sequence number that was added to the Initial frm/tbl and place
it in a field (on a new record) of the Followup form.
So that, Client # 1 - may have 2 Initial records and the Followup form will
show that Client #1's LAST initial record for that particular Client ID (in
this case the Client has # 2 records).
Therefore, the data would like this :
SurveyID Seqnum
Client tbl 1
Initital tbl 1 1
1 2
Followup tbl 1 2
etc...

Thanks.

OpenArgs is the last (#7) argument of the OpenForm method.
So, for example let's say the control on the first form that has the number
you want to pass to the second form is txt.TheNumber, you would use that in
OpenArgs argument:

Docmd.OpenForm "The Other Form", , , , , , Me.txtTheNumber

The code I posted previously goes in the Load event of the "The Other Form"

It receives the value and populates a control with it.

Thanks.

[quoted text clipped - 14 lines]

3 1 3 2
3 2 - -

 

[Musa via AccessMonster.com]

I have the SurveyID read directly into the forms. I need to have the last
value for that Survey ID from the Initital form read into the Followup form.

Then you can read the value directly from the Client form when you create a
new record in the Initial or Followup forms. This would be in the form
Current event:

If Me.NewRecord Then
Me.txtSurveyID = Forms!frmClient!txtSurveyID
End If

Sorry, I guess I'm not explaining my problem clearly.

[quoted text clipped - 38 lines]

 

[Klatuu]

Sorry, but your question isn't making much sense

What does this mean?
I have the SurveyID read directly into the forms

It appears to contridict this:
I need to have the last value for that Survey ID from the Initital form read
into the Followup form

What do you mean by "last value"

--
Dave Hargis, Microsoft Access MVP

I have the SurveyID read directly into the forms. I need to have the last
value for that Survey ID from the Initital form read into the Followup form.

Then you can read the value directly from the Client form when you create a
new record in the Initial or Followup forms. This would be in the form
Current event:

If Me.NewRecord Then
Me.txtSurveyID = Forms!frmClient!txtSurveyID
End If

Sorry, I guess I'm not explaining my problem clearly.

[quoted text clipped - 38 lines]

3 1 3 2
3 2 - -

 

[Musa via AccessMonster.com]

The last SeqID value associated with the SurveyID

The SurveyID is carried over to all the forms from the Client tbl. I need the
last recorded (SEQID) from the Initial form carried over to the Followup frm.
Sorry that this so poorly explained.

Sorry, but your question isn't making much sense

What does this mean?
I have the SurveyID read directly into the forms

It appears to contridict this:
I need to have the last value for that Survey ID from the Initital form read
into the Followup form

What do you mean by "last value"

I have the SurveyID read directly into the forms. I need to have the last
value for that Survey ID from the Initital form read into the Followup form.

[quoted text clipped - 12 lines]

 

[Klatuu]

Sorry, I thought you said SurveyID.
Would the Initial form still be open with the last SeqID in a control? or
would the form be closed and you would have to get it from a table?
Is the Followup form opened from the Initial form?

Sorry to ask so many questions, but you set up is a bit unusual and I need
to know how it works before I can give a useful answer. Perhaps if you
describe the order of events of when each form is opened and from where and
when they are closed.
--
Dave Hargis, Microsoft Access MVP

The last SeqID value associated with the SurveyID

The SurveyID is carried over to all the forms from the Client tbl. I need the
last recorded (SEQID) from the Initial form carried over to the Followup frm.
Sorry that this so poorly explained.

Sorry, but your question isn't making much sense

What does this mean?
I have the SurveyID read directly into the forms

It appears to contridict this:
I need to have the last value for that Survey ID from the Initital form read
into the Followup form

What do you mean by "last value"

I have the SurveyID read directly into the forms. I need to have the last
value for that Survey ID from the Initital form read into the Followup form.

[quoted text clipped - 12 lines]

3 1 3 2
3 2 - -

 

[Musa via AccessMonster.com]

No, the Initial form would not be open and would have to be from the table.
The Initial and Followup form opens from the Client frm.

The User is able to search/add/delete a record from the Client form. The
Client form contains a Command button to open the Initial and Followup forms.
The User can add multiple records to the Initial form and Followup form but
cannot add a Followup before an Initial.

User opens Client form and adds record - Survey ID is automatically assigned -
Form remains Open

User needs to complete an Initial Survey - From the Client form User Clicks
on Command to Open
Initial Form -- Survey ID is carried over and a Sequence ID number is
generated.
User Closes form and returns to Client form.

Later in time, User needs to complete a Followup survey on this Client - User
opens Client form and searches for that Client - User then Opens the Followup
form -- SurveyID is carried over and (Last SequenceID form the Initial form
is carried over to a field in the Followup form. At this point, The Client
and Followup Form are Open but not the Initial Form.

It's the carrying over of the LAST Sequence ID from the Initial table to the
Followup form that I'm having trouble.
I couldn't think of a better way of doing this...


I looked at using MAX in an IF statement...

Sorry, I thought you said SurveyID.
Would the Initial form still be open with the last SeqID in a control? or
would the form be closed and you would have to get it from a table?
Is the Followup form opened from the Initial form?

Sorry to ask so many questions, but you set up is a bit unusual and I need
to know how it works before I can give a useful answer. Perhaps if you
describe the order of events of when each form is opened and from where and
when they are closed.

The last SeqID value associated with the SurveyID

[quoted text clipped - 18 lines]