Combo Box Debug error

[Linda RQ]

Hi Everyone,

I'll explain the best I can but I know there is more to this problem than I
have figured out so I may need some leading questions to help me explain.

Really new and clueless to VB. I have a lookup combo box on a form. The
user can look for active or inactive patients depending on what form they
are on and if the patient is in the list, they can select that name and the
form will go to that patient's record. Before this line

rs.FindFirst "[PtID] = " & Me.Combo78
was this
rs.FindFirst "[PtLName] = " & Me.Combo78

The problem with the first one was duplicate names. If I have 2 Smith's,
and select the 1st or 2nd one, the record would always go only to the first
Smith. As time goes by, this gets worse because now I have 20 Smith's. I
thought I figured it out and changed my combo box to find PtID instead of
Last Name. Now it will go to which ever Smith I select but if the user
types in Zeek to look for Zeek I get an error to debug this rs. line. I
think it's because the bound column is set to 4 (which is PtID) and my first
column is last name which is what the combo box reveals. I looked in the
data tab of the properties for my combo box and tried to change "Limit to
List" to no but it wouldn't let me because the first visible column, which
is determined by the ColumnWidths property isn't equal to the bound column.
Adjust the ColumnWidths property first, then set limit to list property.
Here is my ColumnWidths property....1";1";1";0" Column 1 is LName, Column 2
is FName, Column 3 is Room number and Column 4 is PtID.

Here is the current code which is opened up to debug if the user leaves the
combo box blank after searching.

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtID] = " & Me.Combo78
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Hopefully someone can decipher my code <g> and help.

Thanks,
Linda

 

[Allen Browne]

Linda, take at look at:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

It explains how to set up the combo correctly, how to save the current
record before moving to another one, and how to check whether the desired
record was actually found.

 

[Marshall Barton]

I'll explain the best I can but I know there is more to this problem than I
have figured out so I may need some leading questions to help me explain.

Really new and clueless to VB. I have a lookup combo box on a form. The
user can look for active or inactive patients depending on what form they
are on and if the patient is in the list, they can select that name and the
form will go to that patient's record. Before this line

rs.FindFirst "[PtID] = " & Me.Combo78
was this
rs.FindFirst "[PtLName] = " & Me.Combo78

The problem with the first one was duplicate names. If I have 2 Smith's,
and select the 1st or 2nd one, the record would always go only to the first
Smith. As time goes by, this gets worse because now I have 20 Smith's. I
thought I figured it out and changed my combo box to find PtID instead of
Last Name. Now it will go to which ever Smith I select but if the user
types in Zeek to look for Zeek I get an error to debug this rs. line. I
think it's because the bound column is set to 4 (which is PtID) and my first
column is last name which is what the combo box reveals. I looked in the
data tab of the properties for my combo box and tried to change "Limit to
List" to no but it wouldn't let me because the first visible column, which
is determined by the ColumnWidths property isn't equal to the bound column.
Adjust the ColumnWidths property first, then set limit to list property.
Here is my ColumnWidths property....1";1";1";0" Column 1 is LName, Column 2
is FName, Column 3 is Room number and Column 4 is PtID.

Here is the current code which is opened up to debug if the user leaves the
combo box blank after searching.

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtID] = " & Me.Combo78
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

With linit to list set to No, checking for EOF is the wrong
way to guard against the id not found. You should also use
Dim rs As DAO.Recordset and RecordsetClone.

Here's my idea of how to do that and deal with nothing
selected in the combo box:

Private Sub Combo78_AfterUpdate()
If IsNull(Me.Combo78) Then
Beep
Else
With Me.RecordsetClone
.FindFirst "[PtID] = " & Me.Combo78
If Not .NoMatchThen Me.Bookmark = .Bookmark
End With
End If
End Sub

I can not understand why you would want to set LimitToList
to No. Do you have patient records for patients that are
not in the patient's table.

The BoundColumn does not need to be displayed for any of
this to work so maybe I don't understand what you are doing
or maybe you have gone astray.

 

[Linda RQ]

I'll explain the best I can but I know there is more to this problem than
I
have figured out so I may need some leading questions to help me explain.

Really new and clueless to VB. I have a lookup combo box on a form. The
user can look for active or inactive patients depending on what form they
are on and if the patient is in the list, they can select that name and
the
form will go to that patient's record. Before this line

rs.FindFirst "[PtID] = " & Me.Combo78
was this
rs.FindFirst "[PtLName] = " & Me.Combo78

The problem with the first one was duplicate names. If I have 2 Smith's,
and select the 1st or 2nd one, the record would always go only to the
first
Smith. As time goes by, this gets worse because now I have 20 Smith's. I
thought I figured it out and changed my combo box to find PtID instead of
Last Name. Now it will go to which ever Smith I select but if the user
types in Zeek to look for Zeek I get an error to debug this rs. line. I
think it's because the bound column is set to 4 (which is PtID) and my
first
column is last name which is what the combo box reveals. I looked in the
data tab of the properties for my combo box and tried to change "Limit to
List" to no but it wouldn't let me because the first visible column, which
is determined by the ColumnWidths property isn't equal to the bound
column.
Adjust the ColumnWidths property first, then set limit to list property.
Here is my ColumnWidths property....1";1";1";0" Column 1 is LName, Column
2
is FName, Column 3 is Room number and Column 4 is PtID.

Here is the current code which is opened up to debug if the user leaves
the
combo box blank after searching.

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtID] = " & Me.Combo78
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

With linit to list set to No, checking for EOF is the wrong
way to guard against the id not found. You should also use
Dim rs As DAO.Recordset and RecordsetClone.

Here's my idea of how to do that and deal with nothing
selected in the combo box:

Private Sub Combo78_AfterUpdate()
If IsNull(Me.Combo78) Then
Beep
Else
With Me.RecordsetClone
.FindFirst "[PtID] = " & Me.Combo78
If Not .NoMatchThen Me.Bookmark = .Bookmark
End With
End If
End Sub

I can not understand why you would want to set LimitToList
to No. Do you have patient records for patients that are
not in the patient's table.

The BoundColumn does not need to be displayed for any of
this to work so maybe I don't understand what you are doing
or maybe you have gone astray.

I thought since it was set to limit to list, that's why they were getting an
error when they typed in a name that wasn't on the list so I tried setting
it to No but obviously that's not the problem or what I should do <g>. Here
is the process.....The first thing they do when they get a new order is to
check to see if the patient is already on therapy. They click in the combo
box and type the patient's name or the first few letters, if the name isn't
there, then they add a new record.

The problem is, when the user types the wrong name in there to search for
the patient, if they don't select a name from the list and click the "Add
New Patient" button an error to stop the VB error box pops up to stop the
code or debug this rs.FindFirst "[PtID] = " & Me.Combo78. They then
have to cancel out of that, select any name and then add the new record.

Linda

 

[Marshall Barton]

I'll explain the best I can but I know there is more to this problem than
I
have figured out so I may need some leading questions to help me explain.

Really new and clueless to VB. I have a lookup combo box on a form. The
user can look for active or inactive patients depending on what form they
are on and if the patient is in the list, they can select that name and
the
form will go to that patient's record. Before this line

rs.FindFirst "[PtID] = " & Me.Combo78
was this
rs.FindFirst "[PtLName] = " & Me.Combo78

The problem with the first one was duplicate names. If I have 2 Smith's,
and select the 1st or 2nd one, the record would always go only to the
first
Smith. As time goes by, this gets worse because now I have 20 Smith's. I
thought I figured it out and changed my combo box to find PtID instead of
Last Name. Now it will go to which ever Smith I select but if the user
types in Zeek to look for Zeek I get an error to debug this rs. line. I
think it's because the bound column is set to 4 (which is PtID) and my
first
column is last name which is what the combo box reveals. I looked in the
data tab of the properties for my combo box and tried to change "Limit to
List" to no but it wouldn't let me because the first visible column, which
is determined by the ColumnWidths property isn't equal to the bound
column.
Adjust the ColumnWidths property first, then set limit to list property.
Here is my ColumnWidths property....1";1";1";0" Column 1 is LName, Column
2
is FName, Column 3 is Room number and Column 4 is PtID.

Here is the current code which is opened up to debug if the user leaves
the
combo box blank after searching.

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtID] = " & Me.Combo78
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

With linit to list set to No, checking for EOF is the wrong
way to guard against the id not found. You should also use
Dim rs As DAO.Recordset and RecordsetClone.

Here's my idea of how to do that and deal with nothing
selected in the combo box:

Private Sub Combo78_AfterUpdate()
If IsNull(Me.Combo78) Then
Beep
Else
With Me.RecordsetClone
.FindFirst "[PtID] = " & Me.Combo78
If Not .NoMatchThen Me.Bookmark = .Bookmark
End With
End If
End Sub

I can not understand why you would want to set LimitToList
to No. Do you have patient records for patients that are
not in the patient's table.

The BoundColumn does not need to be displayed for any of
this to work so maybe I don't understand what you are doing
or maybe you have gone astray.

I thought since it was set to limit to list, that's why they were getting an
error when they typed in a name that wasn't on the list so I tried setting
it to No but obviously that's not the problem or what I should do <g>. Here
is the process.....The first thing they do when they get a new order is to
check to see if the patient is already on therapy. They click in the combo
box and type the patient's name or the first few letters, if the name isn't
there, then they add a new record.

The problem is, when the user types the wrong name in there to search for
the patient, if they don't select a name from the list and click the "Add
New Patient" button an error to stop the VB error box pops up to stop the
code or debug this rs.FindFirst "[PtID] = " & Me.Combo78. They then
have to cancel out of that, select any name and then add the new record.

Ahh, so you do want to use the combo to check if patients
are in the list or not. If they are, find their record and
if not, add them. In this case you should use the NotInList
event to make that distinction. Here's a vague outline of
the event procedure:

Sub Combo78_NotInList(. . .
If MsgBox("Do you want to add this name?")=vbYes Then
'NewData has the name that was entered in the
'combo box. Save it in a variable so it can be
'stuffed into the new record.
strNewName = NewData
' create and save the new record stuff here instead
'of using a button.
. . .
Response = acDataErrAdded
Else
Response = acDataErrContinue
Combo78.Undo
End If
End Sub

You already have the find record code in the AfterUpdate
event.

Be sure to read Allen's page and VBA Help on NotInList
event.