SubForm ComboBox

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

I have a form that loads a sub forms based on cbo. On one of my sub forms,
I have another unbound cbo that shows family members, based on the ID passed
from a strSQL query. The cbo on sub form is used as a record selector.
This is controled in the cbo Change event.

Everything works fine, the correct names show up when the sub form is
loaded; the sub form goes to the correct record when name is selected in
cbo, with one exception.

When the sub form is first loaded, clicking on the selected name in the cbo
does not move subform to selected record. The name in cbo has to be
selected again, then sub form moves to selected record.

What is the problem?

This is the code for loading sub form in the cbo change event on parent form
'...........................
Me.subClient_Information.SourceObject =
"fsubClient_Family_member"
Me.subClient_Information.Visible = True
Me.subClient_Information.Enabled = True
Me.subClient_Information.Locked = False
Set frm = Me!subClient_Information.Form
strSQL = "SELECT DISTINCTROW [FN] & ' ' & [LN], FMID "
strSQL = strSQL & "FROM tblFamily_Member "
strSQL = strSQL & "WHERE CFID = '" & strCFID & "' "
strSQL = strSQL & "GROUP BY [FN] & ' ' & [LN], FMID;"
frm.cboFamily.RowSource = strSQL
frm.txtFamily = intFMCount
frm.cboFamily.SetFocus
frm.cboFamily.Dropdown

This is code for sub form cbo change event
'************************************
Private Sub cboFamily_Change()
On Error GoTo ErrorHandler


strSearch = "[FMID] =" & Format(Me.cboFamily.Column(1))
Debug.Print strSearch

Me.Requery
'Find matching record
Set rst1 = Me.RecordsetClone
rst1.FindFirst strSearch
If rst1.NoMatch Then
Me.Detail.Visible = False
Else
Me.Bookmark = rst1.Bookmark
End If
rst1.Close
Me.Detail.Visible = True


ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Err.Clear
Resume ErrorHandlerExit
End Sub

Please Help

-Bryan
 
Move your "OnChange" code to the "AfterUpdate" event of the combo box.
What's likely happening is that your code is running before the combo box's
value has been updated; thus, the first time you make a selection, there is
no value there to be found.
 
Thank you Ken, that was the problem. It is working correctly now.

-Bryan


Ken Snell said:
Move your "OnChange" code to the "AfterUpdate" event of the combo box.
What's likely happening is that your code is running before the combo box's
value has been updated; thus, the first time you make a selection, there is
no value there to be found.

--
Ken Snell
<MS ACCESS MVP>

Bryan Hughes said:
Hello,

I have a form that loads a sub forms based on cbo. On one of my sub forms,
I have another unbound cbo that shows family members, based on the ID passed
from a strSQL query. The cbo on sub form is used as a record selector.
This is controled in the cbo Change event.

Everything works fine, the correct names show up when the sub form is
loaded; the sub form goes to the correct record when name is selected in
cbo, with one exception.

When the sub form is first loaded, clicking on the selected name in the cbo
does not move subform to selected record. The name in cbo has to be
selected again, then sub form moves to selected record.

What is the problem?

This is the code for loading sub form in the cbo change event on parent form
'...........................
Me.subClient_Information.SourceObject =
"fsubClient_Family_member"
Me.subClient_Information.Visible = True
Me.subClient_Information.Enabled = True
Me.subClient_Information.Locked = False
Set frm = Me!subClient_Information.Form
strSQL = "SELECT DISTINCTROW [FN] & ' ' & [LN], FMID "
strSQL = strSQL & "FROM tblFamily_Member "
strSQL = strSQL & "WHERE CFID = '" & strCFID & "' "
strSQL = strSQL & "GROUP BY [FN] & ' ' & [LN], FMID;"
frm.cboFamily.RowSource = strSQL
frm.txtFamily = intFMCount
frm.cboFamily.SetFocus
frm.cboFamily.Dropdown

This is code for sub form cbo change event
'************************************
Private Sub cboFamily_Change()
On Error GoTo ErrorHandler


strSearch = "[FMID] =" & Format(Me.cboFamily.Column(1))
Debug.Print strSearch

Me.Requery
'Find matching record
Set rst1 = Me.RecordsetClone
rst1.FindFirst strSearch
If rst1.NoMatch Then
Me.Detail.Visible = False
Else
Me.Bookmark = rst1.Bookmark
End If
rst1.Close
Me.Detail.Visible = True


ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Err.Clear
Resume ErrorHandlerExit
End Sub

Please Help

-Bryan
Click to expand...
Click to expand...
 
Glad to hear it.

--
Ken Snell
<MS ACCESS MVP>

Bryan Hughes said:
Thank you Ken, that was the problem. It is working correctly now.

-Bryan


Ken Snell said:
Move your "OnChange" code to the "AfterUpdate" event of the combo box.
What's likely happening is that your code is running before the combo box's
value has been updated; thus, the first time you make a selection, there is
no value there to be found.

--
Ken Snell
<MS ACCESS MVP>

Bryan Hughes said:
Hello,

I have a form that loads a sub forms based on cbo. On one of my sub forms,
I have another unbound cbo that shows family members, based on the ID passed
from a strSQL query. The cbo on sub form is used as a record selector.
This is controled in the cbo Change event.

Everything works fine, the correct names show up when the sub form is
loaded; the sub form goes to the correct record when name is selected in
cbo, with one exception.

When the sub form is first loaded, clicking on the selected name in
Click to expand...
the
cbo
does not move subform to selected record. The name in cbo has to be
selected again, then sub form moves to selected record.

What is the problem?

This is the code for loading sub form in the cbo change event on
Click to expand...
parent
form
'...........................
Me.subClient_Information.SourceObject =
"fsubClient_Family_member"
Me.subClient_Information.Visible = True
Me.subClient_Information.Enabled = True
Me.subClient_Information.Locked = False
Set frm = Me!subClient_Information.Form
strSQL = "SELECT DISTINCTROW [FN] & ' ' & [LN], FMID "
strSQL = strSQL & "FROM tblFamily_Member "
strSQL = strSQL & "WHERE CFID = '" & strCFID & "' "
strSQL = strSQL & "GROUP BY [FN] & ' ' & [LN], FMID;"
frm.cboFamily.RowSource = strSQL
frm.txtFamily = intFMCount
frm.cboFamily.SetFocus
frm.cboFamily.Dropdown

This is code for sub form cbo change event
'************************************
Private Sub cboFamily_Change()
On Error GoTo ErrorHandler


strSearch = "[FMID] =" & Format(Me.cboFamily.Column(1))
Debug.Print strSearch

Me.Requery
'Find matching record
Set rst1 = Me.RecordsetClone
rst1.FindFirst strSearch
If rst1.NoMatch Then
Me.Detail.Visible = False
Else
Me.Bookmark = rst1.Bookmark
End If
rst1.Close
Me.Detail.Visible = True


ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Err.Clear
Resume ErrorHandlerExit
End Sub

Please Help

-Bryan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top