SubForm ComboBox

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
 
K

Ken Snell

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.
 
B

Bryan Hughes

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...
 
K

Ken Snell

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...
 

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

Problem with Combobox and linked tables 1
RecordsetClone On Subform 3
RecordSource Qry for Subform 3
Add To Combo blues 1
Paired Multi-Select Listboxes Question BY Helen Feddema 4

Top