Auto-populating fields on next form in series?

G

Guest

Hello,

I have some code (see following) that will lookup to a table with Form name
and a number assigned to that form to open the 'next' form in a series via a
Command Button:

Private Sub Form_Current()
Call SetAutoValues(Me)
End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click
Call OpenNextForm(Me.Name)
Exit_Next_Click:
Exit Sub
Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click
End Sub


Code for the Subs insside a Module that called by the above Subs:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

ALL forms in the series have the same 4 fields in the Header section of the
form. The code opens the next Form in the series beautifully, but I would
like it to auto-populate the values of those 4 header fields to the values
set in the equivalent header fields in the first form of the series. I have
the following code 'SetAutoValues' (below), but no matter how I call it, I
either get an error, or
the 4 header fields do not get auto-populated w/ no error message received
(where 'fScrEligCriteria' is the initial form and 'patient' is the unique
number assigned to the patient):

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
With frm
!studyday = Forms!fScrEligCriteria!studyday
!patient = Forms!fScrEligCriteria!patient
!pat_init = Forms!fScrEligCriteria!pat_init
!site = Forms!fScrEligCriteria!site
End With
SetAutoValues_err:
Resume Next
End Sub

Any ideas on how to BOTH navigate to next form in series and autopopulate
the 4 header fields by clicking on the Command Button would be greatly
appreciated.
 
N

Nikos Yannacopoulos

Pat,

I suspect this has to do with the fact that the line of code:

DoCmd.Close acForm, strName

in Sub OpenNextForm closes the previous form as soon as the next one is
opened, so when your code in Sub SetAutoValues looks for form
fScrEligCriteria, that one is already closed (assuming the hardcoded
form name is correct, and the control names are the same on all forms!).

Since the four controls' data is to be carried along unchanged
throughout the four forms, I would do what you are after from within Sub
OpenNextForm, by reading the controls' values from the previous form
just before closing it (as opposed to try to read from the first one in
the series). To achieve this, I would modify the code as follows (watch
out for wrapping):

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" &
strName & "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " &
intOrder + 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If

'new section
Me.studyday = Forms(strName).studyday
Me.patient = Forms(strName).patient
Me.pat_init = Forms(strName).pat_init
Me.site = Forms(strName).site
'end of new section

DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

HTH,
Nikos

Pat said:
Hello,

I have some code (see following) that will lookup to a table with Form name
and a number assigned to that form to open the 'next' form in a series via a
Command Button:

Private Sub Form_Current()
Call SetAutoValues(Me)
End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click
Call OpenNextForm(Me.Name)
Exit_Next_Click:
Exit Sub
Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click
End Sub


Code for the Subs insside a Module that called by the above Subs:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

ALL forms in the series have the same 4 fields in the Header section of the
form. The code opens the next Form in the series beautifully, but I would
like it to auto-populate the values of those 4 header fields to the values
set in the equivalent header fields in the first form of the series. I have
the following code 'SetAutoValues' (below), but no matter how I call it, I
either get an error, or
the 4 header fields do not get auto-populated w/ no error message received
(where 'fScrEligCriteria' is the initial form and 'patient' is the unique
number assigned to the patient):

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
With frm
!studyday = Forms!fScrEligCriteria!studyday
!patient = Forms!fScrEligCriteria!patient
!pat_init = Forms!fScrEligCriteria!pat_init
!site = Forms!fScrEligCriteria!site
End With
SetAutoValues_err:
Resume Next
End Sub

Any ideas on how to BOTH navigate to next form in series and autopopulate
the 4 header fields by clicking on the Command Button would be greatly
appreciated.
Click to expand...
 
G

Guest

Hi Nikos (and all),

Inserting the code as noted below, I keep getting the error message,
'Invalid use of Me keyword'. Doesn't 'Me' just mean the active form?

Thanks, Patrick

Nikos Yannacopoulos said:
Pat,

I suspect this has to do with the fact that the line of code:

DoCmd.Close acForm, strName

in Sub OpenNextForm closes the previous form as soon as the next one is
opened, so when your code in Sub SetAutoValues looks for form
fScrEligCriteria, that one is already closed (assuming the hardcoded
form name is correct, and the control names are the same on all forms!).

Since the four controls' data is to be carried along unchanged
throughout the four forms, I would do what you are after from within Sub
OpenNextForm, by reading the controls' values from the previous form
just before closing it (as opposed to try to read from the first one in
the series). To achieve this, I would modify the code as follows (watch
out for wrapping):

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" &
strName & "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " &
intOrder + 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If

'new section
Me.studyday = Forms(strName).studyday
Me.patient = Forms(strName).patient
Me.pat_init = Forms(strName).pat_init
Me.site = Forms(strName).site
'end of new section

DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

HTH,
Nikos

Pat said:
Hello,

I have some code (see following) that will lookup to a table with Form name
and a number assigned to that form to open the 'next' form in a series via a
Command Button:

Private Sub Form_Current()
Call SetAutoValues(Me)
End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click
Call OpenNextForm(Me.Name)
Exit_Next_Click:
Exit Sub
Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click
End Sub


Code for the Subs insside a Module that called by the above Subs:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

ALL forms in the series have the same 4 fields in the Header section of the
form. The code opens the next Form in the series beautifully, but I would
like it to auto-populate the values of those 4 header fields to the values
set in the equivalent header fields in the first form of the series. I have
the following code 'SetAutoValues' (below), but no matter how I call it, I
either get an error, or
the 4 header fields do not get auto-populated w/ no error message received
(where 'fScrEligCriteria' is the initial form and 'patient' is the unique
number assigned to the patient):

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
With frm
!studyday = Forms!fScrEligCriteria!studyday
!patient = Forms!fScrEligCriteria!patient
!pat_init = Forms!fScrEligCriteria!pat_init
!site = Forms!fScrEligCriteria!site
End With
SetAutoValues_err:
Resume Next
End Sub

Any ideas on how to BOTH navigate to next form in series and autopopulate
the 4 header fields by clicking on the Command Button would be greatly
appreciated.
Click to expand...
Click to expand...
 
G

Guest

Hi Nikos,

I found that by having the 'initial' form open (which it does on database
startup) that the code will execute properly. Thanks for your help on this
one-- you helped me think out where my bugs were and how to address them.

Thanks again,
Patrick

Nikos Yannacopoulos said:
Pat,

I suspect this has to do with the fact that the line of code:

DoCmd.Close acForm, strName

in Sub OpenNextForm closes the previous form as soon as the next one is
opened, so when your code in Sub SetAutoValues looks for form
fScrEligCriteria, that one is already closed (assuming the hardcoded
form name is correct, and the control names are the same on all forms!).

Since the four controls' data is to be carried along unchanged
throughout the four forms, I would do what you are after from within Sub
OpenNextForm, by reading the controls' values from the previous form
just before closing it (as opposed to try to read from the first one in
the series). To achieve this, I would modify the code as follows (watch
out for wrapping):

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" &
strName & "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " &
intOrder + 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If

'new section
Me.studyday = Forms(strName).studyday
Me.patient = Forms(strName).patient
Me.pat_init = Forms(strName).pat_init
Me.site = Forms(strName).site
'end of new section

DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

HTH,
Nikos

Pat said:
Hello,

I have some code (see following) that will lookup to a table with Form name
and a number assigned to that form to open the 'next' form in a series via a
Command Button:

Private Sub Form_Current()
Call SetAutoValues(Me)
End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click
Call OpenNextForm(Me.Name)
Exit_Next_Click:
Exit Sub
Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click
End Sub


Code for the Subs insside a Module that called by the above Subs:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

ALL forms in the series have the same 4 fields in the Header section of the
form. The code opens the next Form in the series beautifully, but I would
like it to auto-populate the values of those 4 header fields to the values
set in the equivalent header fields in the first form of the series. I have
the following code 'SetAutoValues' (below), but no matter how I call it, I
either get an error, or
the 4 header fields do not get auto-populated w/ no error message received
(where 'fScrEligCriteria' is the initial form and 'patient' is the unique
number assigned to the patient):

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
With frm
!studyday = Forms!fScrEligCriteria!studyday
!patient = Forms!fScrEligCriteria!patient
!pat_init = Forms!fScrEligCriteria!pat_init
!site = Forms!fScrEligCriteria!site
End With
SetAutoValues_err:
Resume Next
End Sub

Any ideas on how to BOTH navigate to next form in series and autopopulate
the 4 header fields by clicking on the Command Button would be greatly
appreciated.
Click to expand...
Click to expand...
 
N

Nikos Yannacopoulos

Patrick,

Glad you cracked it. The reason why you got 'Invalid use of Me keyword'
is the sub OpenNextForm sitting in a general module rather than the
form's own module. This was the natural thing to do on your part, the
sub intended to work with several forms, and the mistake was all mine
for overseeing this obvious fact. In order to work, the guilty code
section should be changed to:

'new section
With Forms(frmName)
.studyday = Forms(strName).studyday
.patient = Forms(strName).patient
.pat_init = Forms(strName).pat_init
.site = Forms(strName).site
End With
'end of new section

Regards,
Nikos

Pat said:
Hi Nikos (and all),

Inserting the code as noted below, I keep getting the error message,
'Invalid use of Me keyword'. Doesn't 'Me' just mean the active form?

Thanks, Patrick

:

Pat,

I suspect this has to do with the fact that the line of code:

DoCmd.Close acForm, strName

in Sub OpenNextForm closes the previous form as soon as the next one is
opened, so when your code in Sub SetAutoValues looks for form
fScrEligCriteria, that one is already closed (assuming the hardcoded
form name is correct, and the control names are the same on all forms!).

Since the four controls' data is to be carried along unchanged
throughout the four forms, I would do what you are after from within Sub
OpenNextForm, by reading the controls' values from the previous form
just before closing it (as opposed to try to read from the first one in
the series). To achieve this, I would modify the code as follows (watch
out for wrapping):

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" &
strName & "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " &
intOrder + 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If

'new section
Me.studyday = Forms(strName).studyday
Me.patient = Forms(strName).patient
Me.pat_init = Forms(strName).pat_init
Me.site = Forms(strName).site
'end of new section

DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

HTH,
Nikos

Pat said:
Hello,

I have some code (see following) that will lookup to a table with Form name
and a number assigned to that form to open the 'next' form in a series via a
Command Button:

Private Sub Form_Current()
Call SetAutoValues(Me)
End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click
Call OpenNextForm(Me.Name)
Exit_Next_Click:
Exit Sub
Err_Next_Click:
DoCmd.Close acForm, Me.Name
Resume Exit_Next_Click
End Sub


Code for the Subs insside a Module that called by the above Subs:

Sub OpenNextForm(strName As String)
Dim intOrder As Integer, frmName As String
On Error GoTo OpenNextForm_Err
intOrder = DLookup("[FormOrder]", "LU_Forms", "[FormName]= '" & strName
& "'")
frmName = DLookup("[FormName]", "LU_Forms", "[FormOrder]= " & intOrder
+ 1)
If Not IsNull(frmName) Then
DoCmd.OpenForm frmName, , , , acAdd
End If
DoCmd.Close acForm, strName
OpenNextForm_Err:
'MsgBox Err.Description
Resume Next
End Sub

ALL forms in the series have the same 4 fields in the Header section of the
form. The code opens the next Form in the series beautifully, but I would
like it to auto-populate the values of those 4 header fields to the values
set in the equivalent header fields in the first form of the series. I have
the following code 'SetAutoValues' (below), but no matter how I call it, I
either get an error, or
the 4 header fields do not get auto-populated w/ no error message received
(where 'fScrEligCriteria' is the initial form and 'patient' is the unique
number assigned to the patient):

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err
' Set Automatic Values in each form in series
With frm
!studyday = Forms!fScrEligCriteria!studyday
!patient = Forms!fScrEligCriteria!patient
!pat_init = Forms!fScrEligCriteria!pat_init
!site = Forms!fScrEligCriteria!site
End With
SetAutoValues_err:
Resume Next
End Sub

Any ideas on how to BOTH navigate to next form in series and autopopulate
the 4 header fields by clicking on the Command Button would be greatly
appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

'Locking' records entered via a form 1
No error message when required field left blank? 2
Locking records 3
User not alerted of duplicate 'key' records? 7
How to check in advance to see if a record already exists? 3
Lock records after data entry 1
Form Navigation 5
Choosing when to save a record? 3

Top