Calling a Sub correctly?

[Guest]

Hello, I am attempting to use some code from an Access97-authored database.
The Sub simply makes a call to a Module to execute a Sub ('SetAutoValues').
'SetAutoValues' looks to set four (4) header fields in the next form in the
series w/ the same values present in the initial form of that series. Should
this set of code work, or does it need to be tweaked in an Access 2003
database?

Code:

Private Sub Form_Current()

Call SetAutoValues(Me)

End Sub

So far, I am not getting error messages, but the header fields are simply
not being populated.

Thanks,

 

[Andi Mayer]

Hello, I am attempting to use some code from an Access97-authored database.
The Sub simply makes a call to a Module to execute a Sub ('SetAutoValues').
'SetAutoValues' looks to set four (4) header fields in the next form in the
series w/ the same values present in the initial form of that series. Should
this set of code work, or does it need to be tweaked in an Access 2003
database?

Code:

Private Sub Form_Current()

Call SetAutoValues(Me)

End Sub

So far, I am not getting error messages, but the header fields are simply
not being populated.

you can skip the call

go to this line
press F9 (set breakpoint)
run the form and go step by step with F8 to see what the code is doing

 

[Guest]

Hi Andi,

THANK YOU for the debugging hint. I'm fairly proficient at Access, but not
so much at coding, and setting the breakpoints and then stepping thru was
really helpful. The code moves along fine w/ no errors in the sequence I
would expect my header fields on the 'next' form to be populated, but it
simply opens the form, but those header fields remain blank. If you don't
mind following along, here's the sequence:

1) 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

2) Calls 'OpenNextForm':

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

3) The above opens the next form in the seuence based on a lookup to a
table. That next form opens, and in the 'On Current:' adn 'SetAutoValues'
sub is called as follows:

Private Sub Form_Current()

Call SetAutoValues(Me)

End Sub

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
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

4) It steps thru all four fields, looking at those equivalent fields on the
'initial' form named in the code, then closes the active form and leaves the
'next' form in series open. Its working exactly the way I want it to, but
just not populating those four fields. I've tried everything, including
wiping out all test data except for one record, and still clicking on the
'Next' button opens the 'next' form, but will not auto-populate the four
header fields based on what is present on the first form of that series.
What could I be doing wrong? I don't want to gobble up too much time, but if
there's an obvious mistake, I would love to find it.

Thank you.

 

[Penguin]

I'm not sure but try this:

Call SetAutoValues(Me.Name)

Hi Andi,

THANK YOU for the debugging hint. I'm fairly proficient at Access, but not
so much at coding, and setting the breakpoints and then stepping thru was
really helpful. The code moves along fine w/ no errors in the sequence I
would expect my header fields on the 'next' form to be populated, but it
simply opens the form, but those header fields remain blank. If you don't
mind following along, here's the sequence:

1) 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

2) Calls 'OpenNextForm':

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

3) The above opens the next form in the seuence based on a lookup to a
table. That next form opens, and in the 'On Current:' adn 'SetAutoValues'
sub is called as follows:

Private Sub Form_Current()

Call SetAutoValues(Me)

End Sub

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
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

4) It steps thru all four fields, looking at those equivalent fields on the
'initial' form named in the code, then closes the active form and leaves the
'next' form in series open. Its working exactly the way I want it to, but
just not populating those four fields. I've tried everything, including
wiping out all test data except for one record, and still clicking on the
'Next' button opens the 'next' form, but will not auto-populate the four
header fields based on what is present on the first form of that series.
What could I be doing wrong? I don't want to gobble up too much time, but if
there's an obvious mistake, I would love to find it.

Thank you.

you can skip the call

go to this line
press F9 (set breakpoint)
run the form and go step by step with F8 to see what the code is doing

 

[Andi Mayer]

Private Sub Form_Current()

Call SetAutoValues(Me)

End Sub

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
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

is studyday a table-field or a control?

set a break point into one of this subs and you will see:

the onCurrent event will be fired a few time, at least after the
closing of the calling form and then you fill the fields with not
existing data from the calling form.

The On Error Goto without a messagebox is a real pain (I never use
it, while developing), you miss every error and believe all is running
fine

 

[Guest]

Hi Andi,

Again, thanks for the debugging tip. I put in a MsgBox for 'On Error GoTo
SetAutoValues_err' in the Sub 'SetAutoValues' and upon opening the form that
calls this Sub in the 'On Current' Event for the Current Form, I immediately
get the following error msg: 'On 'Micrisoft Office Access can't find the
form 'fScrEligCriteria' referred to in a macro or Visual Basic code'.

The curious thing is that this form does exist, and it is spelled correctly.
In fact the 'SetAutoValues' code executes perfectly from another form in the
database (i.e, filling in the four header fields in the form I am requesting
to open). The big difference is that this form is the 'initial' form in the
database, and does NOT call the Sub 'SetAutoValues' in the 'On Current' Event
of the form. There seems to be something in calling the 'SetAutoValues' from
the 'On Current' Event that prevents Access from finding the form-- is that a
possibility?

Thanks for the feedback!

 

[Andi Mayer]

Hi Andi,

Again, thanks for the debugging tip. I put in a MsgBox for 'On Error GoTo
SetAutoValues_err' in the Sub 'SetAutoValues' and upon opening the form that
calls this Sub in the 'On Current' Event for the Current Form, I immediately
get the following error msg: 'On 'Micrisoft Office Access can't find the
form 'fScrEligCriteria' referred to in a macro or Visual Basic code'.

yes it exists, but it isn't open therefore it's not in the
Forms.collection

use this instead:
assuming it's on the calling form

you open the new form and you set the values bevor you close the form
You don't need the formCurrent anymore on the called form
Btw: i doesn't check if the form is really open!!!!!

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
with forms(frmName)
!studyday = me!studyday
!patient = me!patient
!pat_init = me!pat_init
!site = me!site
End With
End If
DoCmd.Close acForm, strName

OpenNextForm_Err:
'MsgBox Err.Description
Resume Next

End Sub