good combo box code causes error when installed as part of subform

[slowuptake]

I have a subform with two combo boxes, in which the value in 2nd combo
(cboTask) is dependent upon value in the 1st (cboProject). The code to
perform this is working perfectly when the subform is run independently.

However, when I pull the subform into the main form, the code is unable to
find the values for cboProject ... placing an "enter parameter value" box
onto the screen and asking for Forms!frmTimeInputSub!cboProject

If I enter a valid parameter fro cbo Project, then the remainder of the code
works correctly, meaning cboTask returns the correctly sorted list.

Below is all the code from the subform. I assumed if I replaced
Me.cboProject.ItemDate(0) with Forms!frmTimeInputSub!cboProject...
in Private Sub Form_Load() with that this would solve the problem.
But it doesn't.

Is it a syntax problem, or is there something that needs be public, or am I
as usual even further off track?

regards,
slowuptake

Code =====>

Option Compare Database

Private Sub chrProject_AfterUpdate()

End Sub

Private Sub cboProject_AfterUpdate()

cboTask = Null
cboTask.Requery
cboTask = Me.cboTask.ItemData(0)

End Sub

Private Sub Form_Current()

cboTask.Requery

End Sub

Private Sub Form_Load()

If IsNull(cboProject) Then
cboProject = Me.cboProject.ItemData(0)
Call cboProject_AfterUpdate
End If

End Sub

 

[Arvin Meyer [MVP]]

Me refers to the current form or report. It can only be used from within
that form or report. A subform is a control, but when it has focus, it is a
form. You can refer to it from outside by referring to its form property:

Me!NameOfSubformControl.Form.FormPropertyOrControl

From within the subform, you need to refer to the Parent, so if cboProject
is in the main form, you'd use:

Me.Parent.cboProject

If you need to find out where you are when the code is running, set a
breakpoint in your code and step through it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[slowuptake]

Arvin,

I think I didn't explain well in original question.

The combo box (cboProject) that isn't functioning is in the subform. All
the combo does is read a list of projects from a table. As far as I can
tell, the error occurs when it tries to execute the onLoad event code
attached to the subform (or perhaps it might be in cboProject_AfterUpdate due
to the embedded call).

The reason it has onLoad event code is that a second combo (cboTask), also
inside the subform, sorts the table it is linked to based upon the value in
cboProject. I took the code from Access downloads (one combo dependent upon
value in another). Code works perfectly if I open subform and run it alone,
but fails when subform is embedded in main form.

I tried to replace Me.cboProject with Me!frmTimeInputSub.cboProject
but the result is same, an input parameter value box asking for
Me!frmTimeInputSub!cboProject
and again if I run subform by itself everything works.

What I noticed this time is that when first entering the subform, the
subform shows a value in cboProject (from existing records), and when I click
the combo box the dropdown box with list of all projects appears, however
when I attempt to select I get the "input parameter value" box. If I tab
through to a new (empty) record, I get the "input parameter value"
immediately upon entering new record.

I'm totally confused now.

regards,
Slowuptake

 

[Arvin Meyer [MVP]]

You need to step through the code and tell us exactly where it fails, and
what the error message is, so we can reproduce the error.

Open the code window of the form.

Set a breakpoint by clicking in the left margin at the earliest entry point.

Open the form so that the code fires.

Press F8 and step through the code one line at a time until it errors.

Tell us what happens.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer [MVP]]

One other thing, it may be as simple as using:

cboProject_AfterUpdate

or:

Call cboProject_AfterUpdate()

but not:

Call cboProject_AfterUpdate
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[slowuptake]

Arvin,

Ok I tried my best, here are results in detail.
At bottom this response is full print of code.

1) I tried the two syntax suggestions you made. Both seemed to produce same
error. Remainder of testing done using cboProject_AfterUpdate (without the
"Call")

2) set breakpoints at the start of each of the 4 subroutines in the subform
code and tried as much as possible to single step (F8) through code with
results as below

3) on click of the "frmTimeInput" icon in Access database box debugger
immediately evaluates sub Form_Load() as = "FLF" (value from first record in
table underlaying sub form, which is correct linkage for record 1 in the
table underlaying the main form) then ends the sub

4) evaluates sub Form_Current() next. Evaluates cboTask.Requery, however a
watch on cboTask indicates the value always remains <out of context>

5) frmTimeInput opens

6) main form requires choosing values from 3 combo boxes and then pressing a
button to retrieve a unique record from table underlaying main form. Chose
to select the 4th record, then tabbed into subform

7) debugger evaluates sub Form_Current(), and again watch shows
cboTask.Requery remains <out of context>

8) debugger opens main form code and stops at the "End If" statement in sub
cmdFindTimeSheet_Click. Debugger then stops upon evaluating "Exit" and won't
go further. I tried return to the form, but there was no cursor visible and
I couldn't follow further.

9) I reset debugger and try opening form again. Steps 3, 4, 5 above same.

10) I use main form combo boxes to select first record in underlaying main
form table. This time (and unlike in 7 above) debugger doesn't open, I tab
into subform until reaching combo cboProject. The correct dropdown list in
cboProject is visible.

11) Whether choose same or different value (as was initially in first
record) for cboProject the result is

12) debugger evaluates sub cboProject_AfterUpdate() as follows:

13) cboTask watch evaluates a correct value (i.e. "1.0 tender phase")

14) cboTask=Null evaluates to "Null"

15) cboTask.Requery evaluates to "Null"

16) cboTask = Me.cboTask.ItemData(0) evaluates and causes "enter parameter
value" box to appear with error message "Forms!frmTimeInputSub!cboProject"

17) I enter a value for cboProject into parameter query box

18) debugger ends sub cboProject_AfterUpdate() and stops

19) I return to form, combo cboProject is active with just entered value
highlighted

20) tab to combo cboTask, dropdown list appears correct and values can be
chosen

21) tab to next box in form (cboTask is last in normal order for a subform
record)

22) debugger opens as sub Form_Current()

23) cboTask.Requery evaluates and responds with "enter parameter value" box

regards,
Slowuptake

SUBFORM CODE (in full) ==>

Option Compare Database

Private Sub chrProject_AfterUpdate()

End Sub

Private Sub cboProject_AfterUpdate()

cboTask = Null
cboTask.Requery
cboTask = Me.cboTask.ItemData(0)

End Sub

Private Sub Form_Current()

cboTask.Requery

End Sub

Private Sub Form_Load()

If IsNull(cboProject) Then
'cboProjet = Me.cboProject.ItemData(0)
cboProject = Me!frmTimeInput.cboProject.ItemData(0)
'Call cboProject_AfterUpdate
cboProject_AfterUpdate
End If

End Sub


MAINFORM CODE (in full) ==>


Option Compare Database



Private Sub cmdFindTimeSheet_Click()
On Error GoTo Err_cmdFindTimeSheet_Click

Dim IngMyVar As Long

'Collect employee, month and year of timecard desired.
'Return the primary key number (idnTimeCardID) in variable IngMyVar.

IngMyVar = Nz(DLookup("idnTimeCardID", "tblTimeCardList", _
"chrEmployee=" & Chr$(34) & cboEmployee & Chr$(34) & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth & Chr$(34) & _
" AND sngYear=" & cboYear), 0)


'Set current record in tblTimeCardList equal to IngMyVar

With Me.RecordsetClone
.FindFirst "[idnTimeCardID] = " & IngMyVar
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_cmdFindTimeSheet_Click:
Exit Sub

Err_cmdFindTimeSheet_Click:
MsgBox Err.Description
Resume Exit_cmdFindTimeSheet_Click

End Sub

 

[Arvin Meyer [MVP]]

Send the form/subform (with enough sample data to test) in a compacted,
zipped file to the email address at the datastrat.com website in my sig.
Tonight, I'll have a look and see what I can do to fix it for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Arvin,

Ok I tried my best, here are results in detail.
At bottom this response is full print of code.

1) I tried the two syntax suggestions you made. Both seemed to produce same
error. Remainder of testing done using cboProject_AfterUpdate (without the
"Call")

2) set breakpoints at the start of each of the 4 subroutines in the subform
code and tried as much as possible to single step (F8) through code with
results as below

3) on click of the "frmTimeInput" icon in Access database box debugger
immediately evaluates sub Form_Load() as = "FLF" (value from first record in
table underlaying sub form, which is correct linkage for record 1 in the
table underlaying the main form) then ends the sub

4) evaluates sub Form_Current() next. Evaluates cboTask.Requery, however a
watch on cboTask indicates the value always remains <out of context>

5) frmTimeInput opens

6) main form requires choosing values from 3 combo boxes and then pressing a
button to retrieve a unique record from table underlaying main form. Chose
to select the 4th record, then tabbed into subform

7) debugger evaluates sub Form_Current(), and again watch shows
cboTask.Requery remains <out of context>

8) debugger opens main form code and stops at the "End If" statement in sub
cmdFindTimeSheet_Click. Debugger then stops upon evaluating "Exit" and won't
go further. I tried return to the form, but there was no cursor visible and
I couldn't follow further.

9) I reset debugger and try opening form again. Steps 3, 4, 5 above same.

10) I use main form combo boxes to select first record in underlaying main
form table. This time (and unlike in 7 above) debugger doesn't open, I tab
into subform until reaching combo cboProject. The correct dropdown list in
cboProject is visible.

11) Whether choose same or different value (as was initially in first
record) for cboProject the result is

12) debugger evaluates sub cboProject_AfterUpdate() as follows:

13) cboTask watch evaluates a correct value (i.e. "1.0 tender phase")

14) cboTask=Null evaluates to "Null"

15) cboTask.Requery evaluates to "Null"

16) cboTask = Me.cboTask.ItemData(0) evaluates and causes "enter parameter
value" box to appear with error message "Forms!frmTimeInputSub!cboProject"

17) I enter a value for cboProject into parameter query box

18) debugger ends sub cboProject_AfterUpdate() and stops

19) I return to form, combo cboProject is active with just entered value
highlighted

20) tab to combo cboTask, dropdown list appears correct and values can be
chosen

21) tab to next box in form (cboTask is last in normal order for a subform
record)

22) debugger opens as sub Form_Current()

23) cboTask.Requery evaluates and responds with "enter parameter value" box

regards,
Slowuptake

SUBFORM CODE (in full) ==>

Option Compare Database

Private Sub chrProject_AfterUpdate()

End Sub

Private Sub cboProject_AfterUpdate()

cboTask = Null
cboTask.Requery
cboTask = Me.cboTask.ItemData(0)

End Sub

Private Sub Form_Current()

cboTask.Requery

End Sub

Private Sub Form_Load()

If IsNull(cboProject) Then
'cboProjet = Me.cboProject.ItemData(0)
cboProject = Me!frmTimeInput.cboProject.ItemData(0)
'Call cboProject_AfterUpdate
cboProject_AfterUpdate
End If

End Sub


MAINFORM CODE (in full) ==>


Option Compare Database



Private Sub cmdFindTimeSheet_Click()
On Error GoTo Err_cmdFindTimeSheet_Click

Dim IngMyVar As Long

'Collect employee, month and year of timecard desired.
'Return the primary key number (idnTimeCardID) in variable IngMyVar.

IngMyVar = Nz(DLookup("idnTimeCardID", "tblTimeCardList", _
"chrEmployee=" & Chr$(34) & cboEmployee & Chr$(34) & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth & Chr$(34) & _
" AND sngYear=" & cboYear), 0)


'Set current record in tblTimeCardList equal to IngMyVar

With Me.RecordsetClone
.FindFirst "[idnTimeCardID] = " & IngMyVar
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_cmdFindTimeSheet_Click:
Exit Sub

Err_cmdFindTimeSheet_Click:
MsgBox Err.Description
Resume Exit_cmdFindTimeSheet_Click

End Sub

One other thing, it may be as simple as using:

cboProject_AfterUpdate

or:

Call cboProject_AfterUpdate()

but not:

Call cboProject_AfterUpdate
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

(cboTask),
also I
tab public,
or

 

[slowuptake]

Thanks for that, will do.
Subject line will be slowuptake.
Sender address liam@****.ie

regards,
slowuptake

Send the form/subform (with enough sample data to test) in a compacted,
zipped file to the email address at the datastrat.com website in my sig.
Tonight, I'll have a look and see what I can do to fix it for you.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Arvin,

Ok I tried my best, here are results in detail.
At bottom this response is full print of code.

1) I tried the two syntax suggestions you made. Both seemed to produce same
error. Remainder of testing done using cboProject_AfterUpdate (without the
"Call")

2) set breakpoints at the start of each of the 4 subroutines in the subform
code and tried as much as possible to single step (F8) through code with
results as below

3) on click of the "frmTimeInput" icon in Access database box debugger
immediately evaluates sub Form_Load() as = "FLF" (value from first record in
table underlaying sub form, which is correct linkage for record 1 in the
table underlaying the main form) then ends the sub

4) evaluates sub Form_Current() next. Evaluates cboTask.Requery, however a
watch on cboTask indicates the value always remains <out of context>

5) frmTimeInput opens

6) main form requires choosing values from 3 combo boxes and then pressing a
button to retrieve a unique record from table underlaying main form. Chose
to select the 4th record, then tabbed into subform

7) debugger evaluates sub Form_Current(), and again watch shows
cboTask.Requery remains <out of context>

8) debugger opens main form code and stops at the "End If" statement in sub
cmdFindTimeSheet_Click. Debugger then stops upon evaluating "Exit" and won't
go further. I tried return to the form, but there was no cursor visible and
I couldn't follow further.

9) I reset debugger and try opening form again. Steps 3, 4, 5 above same.

10) I use main form combo boxes to select first record in underlaying main
form table. This time (and unlike in 7 above) debugger doesn't open, I tab
into subform until reaching combo cboProject. The correct dropdown list in
cboProject is visible.

11) Whether choose same or different value (as was initially in first
record) for cboProject the result is

12) debugger evaluates sub cboProject_AfterUpdate() as follows:

13) cboTask watch evaluates a correct value (i.e. "1.0 tender phase")

14) cboTask=Null evaluates to "Null"

15) cboTask.Requery evaluates to "Null"

16) cboTask = Me.cboTask.ItemData(0) evaluates and causes "enter parameter
value" box to appear with error message "Forms!frmTimeInputSub!cboProject"

17) I enter a value for cboProject into parameter query box

18) debugger ends sub cboProject_AfterUpdate() and stops

19) I return to form, combo cboProject is active with just entered value
highlighted

20) tab to combo cboTask, dropdown list appears correct and values can be
chosen

21) tab to next box in form (cboTask is last in normal order for a subform
record)

22) debugger opens as sub Form_Current()

23) cboTask.Requery evaluates and responds with "enter parameter value" box

regards,
Slowuptake

SUBFORM CODE (in full) ==>

Option Compare Database

Private Sub chrProject_AfterUpdate()

End Sub

Private Sub cboProject_AfterUpdate()

cboTask = Null
cboTask.Requery
cboTask = Me.cboTask.ItemData(0)

End Sub

Private Sub Form_Current()

cboTask.Requery

End Sub

Private Sub Form_Load()

If IsNull(cboProject) Then
'cboProjet = Me.cboProject.ItemData(0)
cboProject = Me!frmTimeInput.cboProject.ItemData(0)
'Call cboProject_AfterUpdate
cboProject_AfterUpdate
End If

End Sub


MAINFORM CODE (in full) ==>


Option Compare Database



Private Sub cmdFindTimeSheet_Click()
On Error GoTo Err_cmdFindTimeSheet_Click

Dim IngMyVar As Long

'Collect employee, month and year of timecard desired.
'Return the primary key number (idnTimeCardID) in variable IngMyVar.

IngMyVar = Nz(DLookup("idnTimeCardID", "tblTimeCardList", _
"chrEmployee=" & Chr$(34) & cboEmployee & Chr$(34) & " AND " & _
"chrMonth=" & Chr$(34) & cboMonth & Chr$(34) & _
" AND sngYear=" & cboYear), 0)


'Set current record in tblTimeCardList equal to IngMyVar

With Me.RecordsetClone
.FindFirst "[idnTimeCardID] = " & IngMyVar
If .NoMatch Then
MsgBox "Timeheet not found!", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With

Exit_cmdFindTimeSheet_Click:
Exit Sub

Err_cmdFindTimeSheet_Click:
MsgBox Err.Description
Resume Exit_cmdFindTimeSheet_Click

End Sub

One other thing, it may be as simple as using:

cboProject_AfterUpdate

or:

Call cboProject_AfterUpdate()

but not:

Call cboProject_AfterUpdate
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

You need to step through the code and tell us exactly where it fails, and
what the error message is, so we can reproduce the error.

Open the code window of the form.

Set a breakpoint by clicking in the left margin at the earliest entry
point.

Open the form so that the code fires.

Press F8 and step through the code one line at a time until it errors.

Tell us what happens.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

Arvin,

I think I didn't explain well in original question.

The combo box (cboProject) that isn't functioning is in the subform.
All
the combo does is read a list of projects from a table. As far as I can
tell, the error occurs when it tries to execute the onLoad event code
attached to the subform (or perhaps it might be in
cboProject_AfterUpdate
due
to the embedded call).

The reason it has onLoad event code is that a second combo (cboTask),
also
inside the subform, sorts the table it is linked to based upon the value
in
cboProject. I took the code from Access downloads (one combo dependent
upon
value in another). Code works perfectly if I open subform and run it
alone,
but fails when subform is embedded in main form.

I tried to replace Me.cboProject with
Me!frmTimeInputSub.cboProject
but the result is same, an input parameter value box asking for
Me!frmTimeInputSub!cboProject
and again if I run subform by itself everything works.

What I noticed this time is that when first entering the subform, the
subform shows a value in cboProject (from existing records), and when I
click
the combo box the dropdown box with list of all projects appears,
however
when I attempt to select I get the "input parameter value" box. If I
tab
through to a new (empty) record, I get the "input parameter value"
immediately upon entering new record.

I'm totally confused now.

regards,
Slowuptake

:

Me refers to the current form or report. It can only be used from
within
that form or report. A subform is a control, but when it has focus, it
is a
form. You can refer to it from outside by referring to its form
property:

Me!NameOfSubformControl.Form.FormPropertyOrControl

From within the subform, you need to refer to the Parent, so if
cboProject
is in the main form, you'd use:

Me.Parent.cboProject

If you need to find out where you are when the code is running, set a
breakpoint in your code and step through it.