Combo box with leading 9

[Julie]

I have an unbound combo box that populates correctly but I have another field
[ID_No] that is populated after the combo selection but it does not work for
any values beginning with 9. The field [ID_No] is a text field.

Private Sub cboIDNo_Click()
SQLtxt = "select * from tblIDlookup Where ID_No= '" & Left$(cboIDNo.Text, 4)
& "'"
Set rsTo = CurrentDb.OpenRecordset(SQLtxt, dbOpenSnapshot)
If rsTo.RecordCount <> 0 Then
rsTo.MoveFirst
[FundNo] = rsTo![ID_No]
[Fund] = rsTo![ID Name]
rsTo.Close
End If

End Sub

Thank you!

 

[Dirk Goldgar]

I have an unbound combo box that populates correctly but I have another
field
[ID_No] that is populated after the combo selection but it does not work
for
any values beginning with 9. The field [ID_No] is a text field.

Private Sub cboIDNo_Click()
SQLtxt = "select * from tblIDlookup Where ID_No= '" & Left$(cboIDNo.Text,
4)
& "'"
Set rsTo = CurrentDb.OpenRecordset(SQLtxt, dbOpenSnapshot)
If rsTo.RecordCount <> 0 Then
rsTo.MoveFirst
[FundNo] = rsTo![ID_No]
[Fund] = rsTo![ID Name]
rsTo.Close
End If

End Sub

There's nothing special about a leading 9, as that code goes, so it's likely
to have to do with some other attribute of the ID_No field or the way the
combo box is filled. Here are a couple of observations:

1. Your code assumes that ID_No is a text field, not numeric. Is that
correct?

2. Your SQL statement truncates the value from cboIDno to the leftmost 4
characters. If you're trying to look up an ID_No with 5 digits, you won't
find it. I can only assume there's a reason for doing this truncation, but
I don't know what it is.

To debug this, you might set a breakpoint on the " Set rsTo =
CurrentDb.OpenRecordset" line, and then make various choices in the combo
box, including one that begins with 9. Each time, when the code stops at
the breakpoint, display the value of SQLtxt, and you'll see what you're
really trying to query.

I can also offer a couple of secondary observations, which probably don't
have anything to do with the problem at hand:

3. You're using the Text property of the combo box. It would be more
natural, in Access VBA, to refer to the Value property, either explicitly:

SQLtxt = "select * from tblIDlookup Where ID_No= '" & _
Left$(cboIDNo.Value, 4) & "'"

or implicitly:

SQLtxt = "select * from tblIDlookup Where ID_No= '" & _
Left$(cboIDNo, 4) & "'"

4. This statement:

rsTo.MoveFirst

serves no purpose and should be deleted. If there are any matching records,
the recordset will always be positioned at the first one when the recordset
is opened.

5. This statement:

rsTo.Close

should be moved to after the End If statement. You want to close the
recordset when you're done with it, regardless of whether it was empty.

 

[Klatuu]

In VBA, the Text property only has a value while the control has the focus.
That is why the Value property would be better if you really need to use a
property in this instance at all.

I have an unbound combo box that populates correctly but I have another
field
[ID_No] that is populated after the combo selection but it does not work
for
any values beginning with 9. The field [ID_No] is a text field.

Private Sub cboIDNo_Click()
SQLtxt = "select * from tblIDlookup Where ID_No= '" & Left$(cboIDNo.Text,
4)
& "'"
Set rsTo = CurrentDb.OpenRecordset(SQLtxt, dbOpenSnapshot)
If rsTo.RecordCount <> 0 Then
rsTo.MoveFirst
[FundNo] = rsTo![ID_No]
[Fund] = rsTo![ID Name]
rsTo.Close
End If

End Sub

There's nothing special about a leading 9, as that code goes, so it's
likely to have to do with some other attribute of the ID_No field or the
way the combo box is filled. Here are a couple of observations:

1. Your code assumes that ID_No is a text field, not numeric. Is that
correct?

2. Your SQL statement truncates the value from cboIDno to the leftmost 4
characters. If you're trying to look up an ID_No with 5 digits, you
won't find it. I can only assume there's a reason for doing this
truncation, but I don't know what it is.

To debug this, you might set a breakpoint on the " Set rsTo =
CurrentDb.OpenRecordset" line, and then make various choices in the combo
box, including one that begins with 9. Each time, when the code stops at
the breakpoint, display the value of SQLtxt, and you'll see what you're
really trying to query.

I can also offer a couple of secondary observations, which probably don't
have anything to do with the problem at hand:

3. You're using the Text property of the combo box. It would be more
natural, in Access VBA, to refer to the Value property, either explicitly:

SQLtxt = "select * from tblIDlookup Where ID_No= '" & _
Left$(cboIDNo.Value, 4) & "'"

or implicitly:

SQLtxt = "select * from tblIDlookup Where ID_No= '" & _
Left$(cboIDNo, 4) & "'"

4. This statement:

rsTo.MoveFirst

serves no purpose and should be deleted. If there are any matching
records, the recordset will always be positioned at the first one when the
recordset is opened.

5. This statement:

rsTo.Close

should be moved to after the End If statement. You want to close the
recordset when you're done with it, regardless of whether it was empty.

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

(please reply to the newsgroup)

 

[Julie]

Thank you Dirk! I also appreciate the added tips that you included.

I have an unbound combo box that populates correctly but I have another
field
[ID_No] that is populated after the combo selection but it does not work
for
any values beginning with 9. The field [ID_No] is a text field.

Private Sub cboIDNo_Click()
SQLtxt = "select * from tblIDlookup Where ID_No= '" & Left$(cboIDNo.Text,
4)
& "'"
Set rsTo = CurrentDb.OpenRecordset(SQLtxt, dbOpenSnapshot)
If rsTo.RecordCount <> 0 Then
rsTo.MoveFirst
[FundNo] = rsTo![ID_No]
[Fund] = rsTo![ID Name]
rsTo.Close
End If

End Sub

There's nothing special about a leading 9, as that code goes, so it's likely
to have to do with some other attribute of the ID_No field or the way the
combo box is filled. Here are a couple of observations:

1. Your code assumes that ID_No is a text field, not numeric. Is that
correct?

2. Your SQL statement truncates the value from cboIDno to the leftmost 4
characters. If you're trying to look up an ID_No with 5 digits, you won't
find it. I can only assume there's a reason for doing this truncation, but
I don't know what it is.

To debug this, you might set a breakpoint on the " Set rsTo =
CurrentDb.OpenRecordset" line, and then make various choices in the combo
box, including one that begins with 9. Each time, when the code stops at
the breakpoint, display the value of SQLtxt, and you'll see what you're
really trying to query.

I can also offer a couple of secondary observations, which probably don't
have anything to do with the problem at hand:

3. You're using the Text property of the combo box. It would be more
natural, in Access VBA, to refer to the Value property, either explicitly:

SQLtxt = "select * from tblIDlookup Where ID_No= '" & _
Left$(cboIDNo.Value, 4) & "'"

or implicitly:

SQLtxt = "select * from tblIDlookup Where ID_No= '" & _
Left$(cboIDNo, 4) & "'"

4. This statement:

rsTo.MoveFirst

serves no purpose and should be deleted. If there are any matching records,
the recordset will always be positioned at the first one when the recordset
is opened.

5. This statement:

rsTo.Close

should be moved to after the End If statement. You want to close the
recordset when you're done with it, regardless of whether it was empty.

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

(please reply to the newsgroup)