Filling list box

[Guest]

I have a table with two fields: CourseNum (text) and time (text) in a table
called tblCourses. The course numbers are like this: 1001, 1002, 1003, 1101,
1102, 1103, 1201, 1202, 1203, etc.

The first three numbers denote the course and the last number the identifier
of that course. The first form I am using allows the user to select the
course number. After the user selects this I want the listbox (lstCourse) to
display only those courses from tblCourses that begin with that course number
and the time field for that record contains zero. . So if the user selected
100 from the first list box, the lstCourse listbox would display only 1001,
and 1003 if those were the only ones in the table with zeros in the second
field.

Using recordset, the following is what has been done. I error out on the
strFilter = "SELECT * FROM tblTimes WHERE CourseNum Like '" & strCriteria &
"'"
statement.

Private Sub Form_Load()
'load the list box with only the classes that have not already been
'modified with respect to there time field
Dim rst As ADODB.Recordset, strFilter As String, strCriteria As String
Dim rst2 As ADODB.Recordset
Set rst = New ADODB.Recordset
Set rst2 = New ADODB.Recordset
rst.Open "tblCourseCodes", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.MoveFirst 'no need to check eof of rst,its a static list
Do While Not rst.EOF
strCriteria = rst.Fields("CourseCodes").Value & "*"
strFilter = "SELECT * FROM tblTimes WHERE CourseNum Like '" &
strCriteria & "'"
rst2.Open strFilter, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
If Not rst2.EOF Then
rst2.MoveFirst
End If
Do While Not rst2.EOF
If rst2.Fields("Time").Value = 0 Then
rst.Fields("DisplayCourse").Value = True
Exit Do
End If
rst2.MoveNext
Loop
rst.MoveNext
Loop

End Sub

Thank you for your help.

 

[Jeff Boyce]

Billy

If you were building a query to find these, you'd use something like:

Like [First three digits] & *

as a selection criterion for the CourseNum field.

By the way, combining more than one fact in a single field (your CourseNum
holds Course and Identifier - two facts) is not good design. Instead, use
one field per fact.

If you want to see both combined, use a query and concatenate the fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[onedaywhen]

combining more than one fact in a single field
is not good design

Sure, Jeff, that's why you always store a date's year/month/day
elements in separate fields, always split a book's ISBN number into
region/publisher/title/check digit, etc <tongue firmly embedded in
cheek>.

Jamie.

--

 

[Jeff Boyce]

Actually, I don't. I use the date/time data-type to store a date, and,
since I don't care about the components that make up an ISBN, I just store
the entire "number" as text (<ignoring TIC>)<g>.

Regards

Jeff Boyce
Microsoft Office/Access MVP