Combo box reading from query

[Guest]

I have created a query combining a couple of fields
(Name: FirstName&" "&LastName) from a table to display in a combo box on a form. The combo box will not allow me to select anything from the combo box. I have gone through all the properties for the combo box and compared it to another combo box that works as this one should and I can't find the problem. I'm becoming frustrated. I have even tried recreating the form and the query and still have no luck. Please help. Thanks!

--Catherine

 

[Dirk Goldgar]

I have created a query combining a couple of fields
(Name: FirstName&" "&LastName) from a table to display in a combo box
on a form. The combo box will not allow me to select anything from
the combo box. I have gone through all the properties for the combo
box and compared it to another combo box that works as this one
should and I can't find the problem. I'm becoming frustrated. I have
even tried recreating the form and the query and still have no luck.
Please help. Thanks!

--Catherine

Is the combo box on a form that has AllowEdits set to No?
Unfortunately, even unbound controls become unupdatable when the form
they are on is made read-only.

 

[Guest]

Allow Edits is actually set to Yes. I'm open to other suggestions. THANKS!

 

[Guest]

Actually, the Allow Edit property is set to Yes. I'm open to other suggestions. THANKS!

 

[Dirk Goldgar]

Allow Edits is actually set to Yes. I'm open to other suggestions.
THANKS!

And you're not opening the form in read-only mode? Can you edit other
controls on the form?

When you say "The combo box will not allow me to select anything", what
exactly do you mean? What behavior and/or error message do you get? Is
the combo box bound to a field, or unbound?

It may help for you to post the SQL of the combo box's RowSource, as
well as its ControlSource, BoundColumn, and ColumnCount properties.

 

[Guest]

I'm using the form as a search form. The only other control is a command button. I'm not getting any errors, although initially I got an error that stated the control could not be edited because it was bound to an autonumber field. That error no longer appears in the status bar and no error box appears.

Query in SQL: SELECT [FirstName] & " " & [LastName] AS Name, tblTherapistContactInfo.TherapistID
FROM tblTherapistContactInfo
ORDER BY tblTherapistContactInfo.LastName, tblTherapistContactInfo.FirstName;

Control Source = Name
Row Source Type = Table/Query
Row Source = qryFindAPACTherapist
Bound Column = 2
Column Count = 2
Column Width = 2";0"

I'm still stumped. I have experienced corruption in some of my databases on forms and I needed to start them over. I've tried starting this form again, even in a different database, and I'm still struggling. I'm a little baffled. I appreciate your quick responses, Dick, and hope you can find the problem I'm having.

 

[Dirk Goldgar]

I'm using the form as a search form. The only other control is a
command button. I'm not getting any errors, although initially I got
an error that stated the control could not be edited because it was
bound to an autonumber field. That error no longer appears in the
status bar and no error box appears.

Query in SQL: SELECT [FirstName] & " " & [LastName] AS Name,
tblTherapistContactInfo.TherapistID
FROM tblTherapistContactInfo
ORDER BY tblTherapistContactInfo.LastName,
tblTherapistContactInfo.FirstName;

Control Source = Name
Row Source Type = Table/Query
Row Source = qryFindAPACTherapist
Bound Column = 2
Column Count = 2
Column Width = 2";0"

I'm still stumped. I have experienced corruption in some of my
databases on forms and I needed to start them over. I've tried
starting this form again, even in a different database, and I'm still
struggling. I'm a little baffled. I appreciate your quick responses,
Dick, and hope you can find the problem I'm having.

You say the form is being used as a search form. Shouldn't it be
unbound, then? Yet your combo box has a ControlSource. That doesn't
make sense if this is a search control. Try clearing the combo box's
ControlSource property.

I'm afraid my "quick responses" have to end now, as I have to go offline
for the evening. I'll check back later to see if this has helped at
all. Good luck.

 

[Guest]

Dirk,
Thank you for your help. Now that I have made the control unbound, the combo box will allow me to change the value in it. (I don't understand why another form will work with the control bound and this one will not, but making it unbound fixed the problem.)

Now, I'm still having some trouble with my actual search feature (sigh). Maybe you can find what's not working in the code below to explain why the record I find is not the record displayed, but instead the first record is displayed.

Private Sub cmdSearch_Click()
'This seeks a specific APAC Therapist on the form and takes the user to the correct record.
Dim rstTherapist As DAO.Recordset
Dim strTherapistID As String
Dim intTherapistID As Integer
Dim varBookmark As Variant

Set rstTherapist = Me.RecordsetClone
intTherapistID = Me.txtName.Column(1)
rstTherapist.FindFirst ("TherapistID=" & intTherapistID)
If Not rstTherapist.nomatch Then
Me.Bookmark = rstTherapist.Bookmark
End If

Me.txtName = Null

DoCmd.OpenForm "frmTherapistInformation", datamode:=acFormEdit
varBookmark = rstTherapist.Bookmark
Me.Bookmark = varBookmark

End Sub

I will look for responses in the next couple of days. THANKS!

--Catherine

I'm using the form as a search form. The only other control is a
command button. I'm not getting any errors, although initially I got
an error that stated the control could not be edited because it was
bound to an autonumber field. That error no longer appears in the
status bar and no error box appears.

Query in SQL: SELECT [FirstName] & " " & [LastName] AS Name,
tblTherapistContactInfo.TherapistID
FROM tblTherapistContactInfo
ORDER BY tblTherapistContactInfo.LastName,
tblTherapistContactInfo.FirstName;

Control Source = Name
Row Source Type = Table/Query
Row Source = qryFindAPACTherapist
Bound Column = 2
Column Count = 2
Column Width = 2";0"

I'm still stumped. I have experienced corruption in some of my
databases on forms and I needed to start them over. I've tried
starting this form again, even in a different database, and I'm still
struggling. I'm a little baffled. I appreciate your quick responses,
Dick, and hope you can find the problem I'm having.

You say the form is being used as a search form. Shouldn't it be
unbound, then? Yet your combo box has a ControlSource. That doesn't
make sense if this is a search control. Try clearing the combo box's
ControlSource property.

I'm afraid my "quick responses" have to end now, as I have to go offline
for the evening. I'll check back later to see if this has helped at
all. Good luck.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk,
Thank you for your help. Now that I have made the control unbound,
the combo box will allow me to change the value in it. (I don't
understand why another form will work with the control bound and this
one will not, but making it unbound fixed the problem.)

Now, I'm still having some trouble with my actual search feature
(sigh). Maybe you can find what's not working in the code below to
explain why the record I find is not the record displayed, but
instead the first record is displayed.

Private Sub cmdSearch_Click()
'This seeks a specific APAC Therapist on the form and takes the user
to the correct record.
Dim rstTherapist As DAO.Recordset
Dim strTherapistID As String
Dim intTherapistID As Integer
Dim varBookmark As Variant

Set rstTherapist = Me.RecordsetClone
intTherapistID = Me.txtName.Column(1)
rstTherapist.FindFirst ("TherapistID=" & intTherapistID)
If Not rstTherapist.nomatch Then
Me.Bookmark = rstTherapist.Bookmark
End If

Me.txtName = Null

DoCmd.OpenForm "frmTherapistInformation", datamode:=acFormEdit
varBookmark = rstTherapist.Bookmark
Me.Bookmark = varBookmark

End Sub

I will look for responses in the next couple of days. THANKS!

I'm a little confused about the nature of your search form. I see that
you are opening a secondary form, "frmTherapistInformation", presumably
intending to show details for the therapist that was selected. But
you're also using the search form's RecordsetClone and Bookmark
properties to position the search form itself to the record for that
therapist. I was assuming that the search form was unbound, especially
since you said earlier that "the only other control is a command
button."

Tell me, does your search form display any records, or is it just for
choosing a therapist and opening "frmTherapistInformation" to display
that thereapist? If the latter is the case, then you have a lot of
unnecessary and probably erroneous code in there, and all you need is:

'----- start of revised code -----
Private Sub cmdSearch_Click()
' Open the Therapist Information form to show the specific
' APAC Therapist selected by the user.

If IsNull(Me.txtName) Then
MsgBox "Please choose a therapist first."
Exit Sub
End If

DoCmd.OpenForm "frmTherapistInformation", _
WhereCondition:="TherapistID=" & Me.txtName.Column(1), _
DataMode:=acFormEdit

Me.txtName = Null

End Sub
'----- end of revised code -----

If, on the other hand, there's more to this search form than I'm aware
of, then the above code may not be what you want. You'll have to tell
me more about both forms, the search form *and* frmTherapistInformation,
and what you want to have happen when you click the button.

 

[Van T. Dinh]

Your code does not have any action to change the CurrentRecord of the
newly-opened Form (frmTherapistInformation) and therefore the Form
displaysthe "first" Record.

My guess is that you got confused with the word "Me" which refers to the
Object whose context the code is executed in, in the case the frmSearch and
NOT the frmTherapistInformation. Thus the last 2 statements:

varBookmark = rstTherapist.Bookmark
Me.Bookmark = varBookmark

do nothing regarding the current Record of the Form frmTherapistInformation.

Another thing you may not be aware of is that 2 different Recordsets, even
if they refer to the same set of Records, will have different BookMarks so
had the "Me" above referred to frmTherapistInformation, you still most
likely won't get to the right Record (you may even get an error).

Try:

****Untested code****
Private Sub cmdSearch_Click()
' This seeks a specific APAC Therapist on the form and takes the user to
the correct record.
Dim rstTherapist As DAO.Recordset
Dim frmTI As Access.Form

DoCmd.OpenForm "frmTherapistInformation", datamode:=acFormEdit
DoEvents
Set frmTI = Forms("frmTherapistInformation")
Set rstTherapist = frmTI.RecordsetClone
rstTherapist.FindFirst "[TherapistID] = " & Me.cboName
' (by convention, "cbo" for ComboBox)
If (rstTherapist.NoMatch = False) Then
frmTI.Bookmark = rstTherapist.Bookmark
End If

' Cleaning up
DoCmd.Close acForm, Me.Name, acSaveNo
rstTherapist.Close
Set rstTherapist = Nothing
Set frmTI = Nothing
End Sub
********

HTH
Van T. Dinh
MVP (Access)

Dirk,
Thank you for your help. Now that I have made the control unbound, the

combo box will allow me to change the value in it. (I don't understand why
another form will work with the control bound and this one will not, but
making it unbound fixed the problem.)

Now, I'm still having some trouble with my actual search feature (sigh).

Maybe you can find what's not working in the code below to explain why the
record I find is not the record displayed, but instead the first record is
displayed.

Private Sub cmdSearch_Click()
'This seeks a specific APAC Therapist on the form and takes the user to the correct record.
Dim rstTherapist As DAO.Recordset
Dim strTherapistID As String
Dim intTherapistID As Integer
Dim varBookmark As Variant

Set rstTherapist = Me.RecordsetClone
intTherapistID = Me.txtName.Column(1)
rstTherapist.FindFirst ("TherapistID=" & intTherapistID)
If Not rstTherapist.nomatch Then
Me.Bookmark = rstTherapist.Bookmark
End If

Me.txtName = Null

DoCmd.OpenForm "frmTherapistInformation", datamode:=acFormEdit
varBookmark = rstTherapist.Bookmark
Me.Bookmark = varBookmark

End Sub

I will look for responses in the next couple of days. THANKS!

--Catherine

I'm using the form as a search form. The only other control is a
command button. I'm not getting any errors, although initially I got
an error that stated the control could not be edited because it was
bound to an autonumber field. That error no longer appears in the
status bar and no error box appears.

Query in SQL: SELECT [FirstName] & " " & [LastName] AS Name,
tblTherapistContactInfo.TherapistID
FROM tblTherapistContactInfo
ORDER BY tblTherapistContactInfo.LastName,
tblTherapistContactInfo.FirstName;

Control Source = Name
Row Source Type = Table/Query
Row Source = qryFindAPACTherapist
Bound Column = 2
Column Count = 2
Column Width = 2";0"

I'm still stumped. I have experienced corruption in some of my
databases on forms and I needed to start them over. I've tried
starting this form again, even in a different database, and I'm still
struggling. I'm a little baffled. I appreciate your quick responses,
Dick, and hope you can find the problem I'm having.

You say the form is being used as a search form. Shouldn't it be
unbound, then? Yet your combo box has a ControlSource. That doesn't
make sense if this is a search control. Try clearing the combo box's
ControlSource property.

I'm afraid my "quick responses" have to end now, as I have to go offline
for the evening. I'll check back later to see if this has helped at
all. Good luck.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

I tried this and it works GREAT. THANK YOU.

Your code does not have any action to change the CurrentRecord of the
newly-opened Form (frmTherapistInformation) and therefore the Form
displaysthe "first" Record.

My guess is that you got confused with the word "Me" which refers to the
Object whose context the code is executed in, in the case the frmSearch and
NOT the frmTherapistInformation. Thus the last 2 statements:

varBookmark = rstTherapist.Bookmark
Me.Bookmark = varBookmark

do nothing regarding the current Record of the Form frmTherapistInformation.

Another thing you may not be aware of is that 2 different Recordsets, even
if they refer to the same set of Records, will have different BookMarks so
had the "Me" above referred to frmTherapistInformation, you still most
likely won't get to the right Record (you may even get an error).

Try:

****Untested code****
Private Sub cmdSearch_Click()
' This seeks a specific APAC Therapist on the form and takes the user to
the correct record.
Dim rstTherapist As DAO.Recordset
Dim frmTI As Access.Form

DoCmd.OpenForm "frmTherapistInformation", datamode:=acFormEdit
DoEvents
Set frmTI = Forms("frmTherapistInformation")
Set rstTherapist = frmTI.RecordsetClone
rstTherapist.FindFirst "[TherapistID] = " & Me.cboName
' (by convention, "cbo" for ComboBox)
If (rstTherapist.NoMatch = False) Then
frmTI.Bookmark = rstTherapist.Bookmark
End If

' Cleaning up
DoCmd.Close acForm, Me.Name, acSaveNo
rstTherapist.Close
Set rstTherapist = Nothing
Set frmTI = Nothing
End Sub
********

HTH
Van T. Dinh
MVP (Access)

Dirk,
Thank you for your help. Now that I have made the control unbound, the

combo box will allow me to change the value in it. (I don't understand why
another form will work with the control bound and this one will not, but
making it unbound fixed the problem.)

Now, I'm still having some trouble with my actual search feature (sigh).

Maybe you can find what's not working in the code below to explain why the
record I find is not the record displayed, but instead the first record is
displayed.

Private Sub cmdSearch_Click()
'This seeks a specific APAC Therapist on the form and takes the user to the correct record.
Dim rstTherapist As DAO.Recordset
Dim strTherapistID As String
Dim intTherapistID As Integer
Dim varBookmark As Variant

Set rstTherapist = Me.RecordsetClone
intTherapistID = Me.txtName.Column(1)
rstTherapist.FindFirst ("TherapistID=" & intTherapistID)
If Not rstTherapist.nomatch Then
Me.Bookmark = rstTherapist.Bookmark
End If

Me.txtName = Null

DoCmd.OpenForm "frmTherapistInformation", datamode:=acFormEdit
varBookmark = rstTherapist.Bookmark
Me.Bookmark = varBookmark

End Sub

I will look for responses in the next couple of days. THANKS!

--Catherine

I'm using the form as a search form. The only other control is a
command button. I'm not getting any errors, although initially I got
an error that stated the control could not be edited because it was
bound to an autonumber field. That error no longer appears in the
status bar and no error box appears.

Query in SQL: SELECT [FirstName] & " " & [LastName] AS Name,
tblTherapistContactInfo.TherapistID
FROM tblTherapistContactInfo
ORDER BY tblTherapistContactInfo.LastName,
tblTherapistContactInfo.FirstName;

Control Source = Name
Row Source Type = Table/Query
Row Source = qryFindAPACTherapist
Bound Column = 2
Column Count = 2
Column Width = 2";0"

I'm still stumped. I have experienced corruption in some of my
databases on forms and I needed to start them over. I've tried
starting this form again, even in a different database, and I'm still
struggling. I'm a little baffled. I appreciate your quick responses,
Dick, and hope you can find the problem I'm having.

You say the form is being used as a search form. Shouldn't it be
unbound, then? Yet your combo box has a ControlSource. That doesn't
make sense if this is a search control. Try clearing the combo box's
ControlSource property.

I'm afraid my "quick responses" have to end now, as I have to go offline
for the evening. I'll check back later to see if this has helped at
all. Good luck.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

I tried this and it worked GREAT. THANK YOU.

I'm a little confused about the nature of your search form. I see that
you are opening a secondary form, "frmTherapistInformation", presumably
intending to show details for the therapist that was selected. But
you're also using the search form's RecordsetClone and Bookmark
properties to position the search form itself to the record for that
therapist. I was assuming that the search form was unbound, especially
since you said earlier that "the only other control is a command
button."

Tell me, does your search form display any records, or is it just for
choosing a therapist and opening "frmTherapistInformation" to display
that thereapist? If the latter is the case, then you have a lot of
unnecessary and probably erroneous code in there, and all you need is:

'----- start of revised code -----
Private Sub cmdSearch_Click()
' Open the Therapist Information form to show the specific
' APAC Therapist selected by the user.

If IsNull(Me.txtName) Then
MsgBox "Please choose a therapist first."
Exit Sub
End If

DoCmd.OpenForm "frmTherapistInformation", _
WhereCondition:="TherapistID=" & Me.txtName.Column(1), _
DataMode:=acFormEdit

Me.txtName = Null

End Sub
'----- end of revised code -----

If, on the other hand, there's more to this search form than I'm aware
of, then the above code may not be what you want. You'll have to tell
me more about both forms, the search form *and* frmTherapistInformation,
and what you want to have happen when you click the button.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)