Combo box not finding record... sometimes

[Manuel]

I have a combo box on a form which finds records that are stored in a table.
The look up value used by the combo box is a number stored as a DOUBLE in the
table. The number can be between 6 and 10 numeric characters in length. The
table contains about 75K records.

I’ve used the client\server model where each user has a front end copy of
the database on their C:\ drive which is linked to tables in a backend
database located on the company’s network.

The problem is sometimes users report that the record they type into the
combo box is not found. However, the record does exist in the table.

The ROW SOURCE for the combo box is as follows:

SELECT tbl_ForeData.LnNum FROM tbl_ForeData;

In the After Update event for the combo box control I have the following code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[LnNum] = " & Str(Nz(Me![cboLnNum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Other Code…

End sub

Thus far this issue has only been reported for numbers starting with 64
(e.g. 6412345), although I’m not sure why that would be the case.

Has anyone come across this particular issue; it’s a strange one. Your
assistance is greatly appreciated.

Thanks,
Manuel

 

[Dale Fye]

1. Double precision numbers don't always get represented the way you would
expect in the database, so evaluating them using the = comparison is
problematic. If these numbers are actually long integers, as in your
example, you should use that datatype.

2. Next, if the numbers are stored as DOUBLE in the table, why have you
formatted your FindFirst command to search for a string?

3. You should be using the rs.NoMatch property, not rs.eof

I think that should look like:

Set rs = me.recordsetclone
rs.findfirst "[LnNum] = " & NZ(Me.cboLnNum, 0)
if rs.NoMatch Then
msgbox "No matching value"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing

'Other code
End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Manuel]

I used the combo box wizard and that's the code that Access created. It's
always worked for me in the past, and in fact if I substitute your code, the
combo box just returns the first value in the table (like it's not even
attempting a search).

Also, when I change the data type from Double to Long Integer the database
drops the 10-digit numbers, e.g. 5551234567. I don't know why this is, but
it's been the case ever since I've worked with Access and 10 digit numbers.

Any thoughts?

Manuel

1. Double precision numbers don't always get represented the way you would
expect in the database, so evaluating them using the = comparison is
problematic. If these numbers are actually long integers, as in your
example, you should use that datatype.

2. Next, if the numbers are stored as DOUBLE in the table, why have you
formatted your FindFirst command to search for a string?

3. You should be using the rs.NoMatch property, not rs.eof

I think that should look like:

Set rs = me.recordsetclone
rs.findfirst "[LnNum] = " & NZ(Me.cboLnNum, 0)
if rs.NoMatch Then
msgbox "No matching value"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing

'Other code
End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a combo box on a form which finds records that are stored in a table.
The look up value used by the combo box is a number stored as a DOUBLE in the
table. The number can be between 6 and 10 numeric characters in length. The
table contains about 75K records.

I’ve used the client\server model where each user has a front end copy of
the database on their C:\ drive which is linked to tables in a backend
database located on the company’s network.

The problem is sometimes users report that the record they type into the
combo box is not found. However, the record does exist in the table.

The ROW SOURCE for the combo box is as follows:

SELECT tbl_ForeData.LnNum FROM tbl_ForeData;

In the After Update event for the combo box control I have the following code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[LnNum] = " & Str(Nz(Me![cboLnNum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Other Code…

End sub

Thus far this issue has only been reported for numbers starting with 64
(e.g. 6412345), although I’m not sure why that would be the case.

Has anyone come across this particular issue; it’s a strange one. Your
assistance is greatly appreciated.

Thanks,
Manuel

 

[Dale Fye]

Manuel,

Understand, and my mistake. the Long Integer datatype can hold values that
range from 0 to +/- 2,147,483,647. So if your numbers are larger than that,
you will have to use DOUBLE.

I would not define the recordset as an object. I would make sure you have a
reference to the Microsoft DAO 3.6 Object Library, and would then define it
as indicated below.

Then, try the following. I've actually included three options.
1. Try the INToption first. That should change drop the decimal portion of
each of the values.
2. Try FIX second, should basically do the same thing as INT
3. Try the Format option last. What this will do is format both the values
in the [LnNum] field and cboLnNum as strings.

I'm not sure what effect INT or FIX will have on an indexed field (assuming
LnNum is indexed), but the Format function will probably not take advantage
of the indexing of this field (if it is indexed), and could slow down the
search.

If one of these options does not work, you might want to try reposting your
original message with a subject that reads something like: "Repost: finding
a non-decimal double precision value using FindFirst". I still think the
problem relates to the decimal rounding

Private Sub cboLnNum_AfterUpdate()

Dim rs as DAO.Recordset

If Len(me.cboLnNum & "") = 0 then
'nothing selected, don't do anything
'assumes that you don't want to run the "Other code" mentioned below
'if no match is found
Goto FindRecordExit
Else
' Find the record that matches the control.
Set rs = Me.Recordset.Clone
rs.FindFirst "INT([LnNum]) = " & INT(me.cboLnNum)
'rs.FindFirst "FIX([LnNum]) = " & FIX(me.cboLnNum)
'rs.FindFirst "Format([LnNum], '0') = '" & Format(Me![cboLnNum],
"0")
If rs.NoMatch Then
msgbox "LnNum not found"
Goto FindRecordExit
Else
Me.Bookmark = rs.Bookmark
Endif
End if

' Other Code.

FindRecordExit:
If not rs is nothing then
rs.close
set rs = nothing
endif

End sub

HTH
Dale

I used the combo box wizard and that's the code that Access created. It's
always worked for me in the past, and in fact if I substitute your code,
the
combo box just returns the first value in the table (like it's not even
attempting a search).

Also, when I change the data type from Double to Long Integer the database
drops the 10-digit numbers, e.g. 5551234567. I don't know why this is,
but
it's been the case ever since I've worked with Access and 10 digit
numbers.

Any thoughts?

Manuel

1. Double precision numbers don't always get represented the way you
would
expect in the database, so evaluating them using the = comparison is
problematic. If these numbers are actually long integers, as in your
example, you should use that datatype.

2. Next, if the numbers are stored as DOUBLE in the table, why have you
formatted your FindFirst command to search for a string?

3. You should be using the rs.NoMatch property, not rs.eof

I think that should look like:

Set rs = me.recordsetclone
rs.findfirst "[LnNum] = " & NZ(Me.cboLnNum, 0)
if rs.NoMatch Then
msgbox "No matching value"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing

'Other code
End Sub

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a combo box on a form which finds records that are stored in a
table.
The look up value used by the combo box is a number stored as a DOUBLE
in the
table. The number can be between 6 and 10 numeric characters in
length. The
table contains about 75K records.

I've used the client\server model where each user has a front end copy
of
the database on their C:\ drive which is linked to tables in a backend
database located on the company's network.

The problem is sometimes users report that the record they type into
the
combo box is not found. However, the record does exist in the table.

The ROW SOURCE for the combo box is as follows:

SELECT tbl_ForeData.LnNum FROM tbl_ForeData;

In the After Update event for the combo box control I have the
following code:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[LnNum] = " & Str(Nz(Me![cboLnNum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Other Code.

End sub

Thus far this issue has only been reported for numbers starting with 64
(e.g. 6412345), although I'm not sure why that would be the case.

Has anyone come across this particular issue; it's a strange one. Your
assistance is greatly appreciated.

Thanks,
Manuel