Goto last record in a form

[Guest]

I have a form, frmLevel3, that is a subform of frmLevel2, that is a subform
of frmLevel1. When the user enters data into frmLevel3 and accepts the
current record, I run code to calculate and set the Default value of a field
on frmLevel3 for the next record. Setting the Default value requires I use
the requery command which then puts the record selector at the first field of
the first record. I would like the selector to move to a new record. I've
tried using docmd.movetorecord, but I can't get the syntax right.

Any suggestions would be greatly appreciated.

 

[Ken Snell [MVP]]

Post the code that you're currently using... I'm not understanding why a
requery is needed in order to set the default value of a control?

 

[Guest]

The default value is calculated as:

nz(DMax("[Organism_ID]","[TRAWLS_LV3]","[Sample_ID]=" & [Forms]![TRAWLS_LV1
Form]![SampleID] & " AND [Species_Code]=" & [Forms]![TRAWLS_LV1
Form]![TRAWLS_LV2 subform].[Form]![SpeciesCode]),0)+1

Primary key for LV1 table is SampleID
Primary key for LV2 is SampleID + Species_Code
Primary key for LV3 is SampleID + Species_Code + Organism_ID

So as they're entering data into the form corresponding to LV3, I need the
Organism_ID to autoincrement, starting at 1, for each change in Sample_ID or
Species_Code, so as to maintain referential integrity.

 

[Ken Snell [MVP]]

Where does the requery action fit in with this?

Instead of setting a default value, why not get this value using an event
that occurs when a new record is being started -- for example, you could run
this code in the form's Current event procedure and wrap it with an If ..
Then .. End If section and test for Me.NewRecord = True for example.


--

Ken Snell
<MS ACCESS MVP>

The default value is calculated as:

nz(DMax("[Organism_ID]","[TRAWLS_LV3]","[Sample_ID]=" &
[Forms]![TRAWLS_LV1
Form]![SampleID] & " AND [Species_Code]=" & [Forms]![TRAWLS_LV1
Form]![TRAWLS_LV2 subform].[Form]![SpeciesCode]),0)+1

Primary key for LV1 table is SampleID
Primary key for LV2 is SampleID + Species_Code
Primary key for LV3 is SampleID + Species_Code + Organism_ID

So as they're entering data into the form corresponding to LV3, I need the
Organism_ID to autoincrement, starting at 1, for each change in Sample_ID
or
Species_Code, so as to maintain referential integrity.

Post the code that you're currently using... I'm not understanding why a
requery is needed in order to set the default value of a control?

 

[Guest]

Thanks Ken. Good approach. I had to handle the case when a species was
specified in LV2 but no corresponding records are to be entered in LV3.
After adding this test, its working great. Thanks.

Where does the requery action fit in with this?

Instead of setting a default value, why not get this value using an event
that occurs when a new record is being started -- for example, you could run
this code in the form's Current event procedure and wrap it with an If ..
Then .. End If section and test for Me.NewRecord = True for example.


--

Ken Snell
<MS ACCESS MVP>

The default value is calculated as:

nz(DMax("[Organism_ID]","[TRAWLS_LV3]","[Sample_ID]=" &
[Forms]![TRAWLS_LV1
Form]![SampleID] & " AND [Species_Code]=" & [Forms]![TRAWLS_LV1
Form]![TRAWLS_LV2 subform].[Form]![SpeciesCode]),0)+1

Primary key for LV1 table is SampleID
Primary key for LV2 is SampleID + Species_Code
Primary key for LV3 is SampleID + Species_Code + Organism_ID

So as they're entering data into the form corresponding to LV3, I need the
Organism_ID to autoincrement, starting at 1, for each change in Sample_ID
or
Species_Code, so as to maintain referential integrity.

Post the code that you're currently using... I'm not understanding why a
requery is needed in order to set the default value of a control?


--

Ken Snell
<MS ACCESS MVP>

I have a form, frmLevel3, that is a subform of frmLevel2, that is a
subform
of frmLevel1. When the user enters data into frmLevel3 and accepts the
current record, I run code to calculate and set the Default value of a
field
on frmLevel3 for the next record. Setting the Default value requires I
use
the requery command which then puts the record selector at the first
field
of
the first record. I would like the selector to move to a new record.
I've
tried using docmd.movetorecord, but I can't get the syntax right.

Any suggestions would be greatly appreciated.

 

[Ken Snell [MVP]]

Glad to be of assistance. Good luck.

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. Good approach. I had to handle the case when a species was
specified in LV2 but no corresponding records are to be entered in LV3.
After adding this test, its working great. Thanks.

Where does the requery action fit in with this?

Instead of setting a default value, why not get this value using an event
that occurs when a new record is being started -- for example, you could
run
this code in the form's Current event procedure and wrap it with an If ..
Then .. End If section and test for Me.NewRecord = True for example.


--

Ken Snell
<MS ACCESS MVP>

The default value is calculated as:

nz(DMax("[Organism_ID]","[TRAWLS_LV3]","[Sample_ID]=" &
[Forms]![TRAWLS_LV1
Form]![SampleID] & " AND [Species_Code]=" & [Forms]![TRAWLS_LV1
Form]![TRAWLS_LV2 subform].[Form]![SpeciesCode]),0)+1

Primary key for LV1 table is SampleID
Primary key for LV2 is SampleID + Species_Code
Primary key for LV3 is SampleID + Species_Code + Organism_ID

So as they're entering data into the form corresponding to LV3, I need
the
Organism_ID to autoincrement, starting at 1, for each change in
Sample_ID
or
Species_Code, so as to maintain referential integrity.






:

Post the code that you're currently using... I'm not understanding why
a
requery is needed in order to set the default value of a control?


--

Ken Snell
<MS ACCESS MVP>

I have a form, frmLevel3, that is a subform of frmLevel2, that is a
subform
of frmLevel1. When the user enters data into frmLevel3 and accepts
the
current record, I run code to calculate and set the Default value of
a
field
on frmLevel3 for the next record. Setting the Default value
requires I
use
the requery command which then puts the record selector at the first
field
of
the first record. I would like the selector to move to a new
record.
I've
tried using docmd.movetorecord, but I can't get the syntax right.

Any suggestions would be greatly appreciated.