Solved

Fill List Box from Combo Box selection

Posted on 2004-08-31
13
278 Views
Last Modified: 2008-02-01
I have a form where once the user selects an option from a combo box, two list boxes should fill based on queries.  My VBA code is below.  I'm currently having two problems with my form.  First of all, I get an error number 424 (object required) in my form_activate sub on the line indicated in the below code.  Secondly, the textbox that is suppose to be grabbing the value of the first column has a value of "#Name?" instead.  I know this is an error of some kind, but I'm not sure where to fix it.

Option Compare Database

'*********************************************************
'
'Form Name: frmAssign_Course
'
'************************************************************

Dim blnSelectRecord As Boolean  'Record is selected
Private Function LockContactInfo(IsLocked As Boolean)
On Error GoTo errLock
'on false, disable list boxes and buttons
    Me!lstNotInCourse.Enabled = Not IsLocked
    Me!lstInCourse.Enabled = Not IsLocked
    Me!cmdAddUsers.Enabled = Not IsLocked
    Me!cmdRemoveUsers.Enabled = Not IsLocked
    Me!cmdSubmit.Enabled = Not IsLocked

errLock:
    MsgBox (Err.Description & "Lock")
   
End Function

Public Sub Refresh_InCourseData()
On Error GoTo errRefreshIn
    'declare variables needed to set up connection and update lstInCourse
    Dim connRefresh As ADODB.Connection
    Dim rsRefresh As ADODB.Recordset
    Dim strStatement As String
    Dim strRowList As String
   
    'set up connection
    Set connRefresh = CurrentProject.Connection
   
    'SQL statement to fill lstInCourse
    strStatement = "SELECT USER.USERNAME, USER.CUSTNMBR " & _
                    "FROM USERS INNER JOIN (COURSES INNER JOIN tblLINK " & _
                    "ON COURSES.COURSEID = tblLINK.COURSEID) " & _
                    "ON USERS.CUSTNMBR = tblLINK.CUSTNMBR " & _
                    "WHERE ((tblLINK.COURSEID)= & [Forms]![frmAssign_Course]![txtCourseID])" & _
                    "ORDER BY USER.USERNAME"
   
    'set recordset
    Set rsRefresh = New ADODB.Recordset
    rsRefresh.Open strStatement, connRefresh
   
    'clear lstInCourse
    strRowList = ""
    lstInCourse.RowSource = strRowList
   
    'set up column formatting
    Me!lstInCourse.ColumnCount = 2
   
    'set header
    strRowList = "User Name, Customer Num;"
    Me!lstInCourse.RowSource = strRowList
   
    'populate lstInCourse
    If (Not rsRefresh.BOF) Then 'if not before the first record
        While (Not rsRefresh.EOF) 'while not the end of recordset
            strRowList = strRowList & rsRefresh.Fields("USERNAME").Value & " -- " & rsRefresh.Fields("CUSTNMBR").Value
            rsRefresh.MoveNext
        Wend
    End If
   
    'assign string to list box
    Me!lstInCourse.RowSource = strRowList
   
    'close recordset and connection
    rsRefresh.Close
    Set connRefresh = Nothing

errRefreshIn:
    MsgBox (Err.Description & "Refresh In")
End Sub
Public Sub Refresh_NotInCourseData()
On Error GoTo errRefreshNot
    'declare variables needed to set up connection and update lstInCourse
    Dim connRefresh As ADODB.Connection
    Dim rsRefresh As ADODB.Recordset
    Dim strStatement As String
    Dim strRowList As String
   
    'set up connection
    Set connRefresh = CurrentProject.Connection
   
    'SQL statement to fill lstInCourse
    strStatement = "SELECT USER.USERNAME, USER.CUSTNMBR " & _
                    "FROM USERS " & _
                    "WHERE (((USERS.CUSTNMBR) NOT IN (" & _
                    "SELECT USERS.CUSTNMBER FROM USER " & _
                    "INNER JOIN (COURSES INNER JOIN tblLINK " & _
                    "ON COURSES.COURSEID = tblLINK.COURSEID) " & _
                    "ON USERS.CUSTNMBR = tblLINK.CUSTNMBR " & _
                    "WHERE ((tblLINK.COURSEID)= & [Forms]![frmAssign_Course]![cmboCourse.Column(0).value])" & _
                    "ORDER BY USER.USERNAME"
   
    'set recordset
    Set rsRefresh = New ADODB.Recordset
    rsRefresh.Open strStatement, connRefresh
   
    'clear lstInCourse
    strRowList = ""
    lstNotInCourse.RowSource = strRowList
   
    'set up column formatting
    Me!lstNotInCourse.ColumnCount = 2
   
    'set header
    strRowList = "User Name, Customer Num;"
    Me!lstNotInCourse.RowSource = strRowList
   
    'populate lstInCourse
    If (Not rsRefresh.BOF) Then 'if not before the first record
        While (Not rsRefresh.EOF) 'while not the end of recordset
            strRowList = strRowList & rsRefresh.Fields("USERNAME").Value & " -- " & rsRefresh.Fields("CUSTNMBR").Value
            rsRefresh.MoveNext
        Wend
    End If
   
    'assign string to list box
    Me!lstNotInCourse.RowSource = strRowList
   
    'close recordset and connection
    rsRefresh.Close
    Set connRefresh = Nothing

errRefreshNot:
    MsgBox (Err.Description & "Refresh Not")
End Sub
Private Sub Form_Activate()
'when form is activated, check for correct data to display
On Error GoTo errActivate
    'If a Course is selected in combo box
    If Not IsNull(Me![cmboCourse]) Or Me![cmboCourse] = "" Then
        'make sure appropriate records are displayed
        blnSelectRecord = True
       '<<<<<<<<<<ERROR IS ON NEXT LINE>>>>>>>>>>>
        Me.Recordset.FindFirst "[COURSEID]=" & Me![cmboCourse].Column(0).value
        'refresh list boxes
        Refresh_InCourseData
        Refresh_NotInCourseData
    End If
   
errActivate:
    MsgBox (Err.Description & Err.Number & " - Activate")
End Sub

Private Sub cmboCourse_AfterUpdate()
'After selecting an item in the combo box
On Error GoTo errCUpdate
    Dim rs As Object
    Dim strCouseID As String

Me!txtCourseID.Requery

    If Not (IsNull(Me![cmboCourse]) Or Me![cmboCourse] = "") Then
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CourseID] = " & Str(Me![cmboCourse])
        'set recordset to selected item in combo box
        Me.Bookmark = rs.Bookmark
    End If
    Me.Refresh
   
'Show error description on error
errCUpdate:
    MsgBox (Err.Description & " Update")
End Sub

Private Sub cmboCourse_Change()
'On changing the combo box
On Error GoTo errCChange
    strCourseID = "SELECT COURSEID FROM COURSE " & _
                    "WHERE COURSENAME = [Forms]![frmAssign_Course]![cmboCourse.column(1).value]"
    txtCourseID.Value = strCourseID
    'If a record is selected, lock the list boxes
    blnSelectRecord = True
   
    'Get recordset
    Dim rs As Object
    Dim blnMatch As Boolean
    'set recordset
    Set rs = Me.Recordset
   
    blnMatch = False    'haven't found the record
    rs.MoveFirst        'move to first record
    While Not blnMatch
        'Browse through the recordset until correct record is found
        If rs!COURSEID = Val(Me![txtCourseID]) Then
            blnMatch = True
        Else
            rs.MoveNext
        End If
    Wend
   
    'Once match is found, refresh list boxes
    Refresh_InCourseData
    Refresh_NotInCourseData
   
    'enable buttons and boxes
    Me!cmdAddUsers.Enabled = True
    Me!cmdRemoveUsers.Enabled = True
    Me!lstNotInCourse.Enabled = True
    Me!lstInCourse.Enabled = True
   
'On error, show error description
errCChange:
    MsgBox (Err.Description & " Change")
End Sub

Private Sub cmdSubmit_Click()
'Submits changes to database and locks fields
On Error GoTo errSubmit

    If Not blnSelectRecord Then
    'if a record is not selected ask to select record
        MsgBox ("Please Select a Course")
    Else
    'if a record is selected, lock the fields
    '(users are saved to course before submit)
        LockContactInfo True
    End If
   
    'save results
    DoCmd.Save
           
'on error, show error description and exit sub
errSubmit:
    MsgBox (Err.Description & "Submit")
    Exit Sub
End Sub
Private Sub cmdClose_Click()

On Error GoTo errClose


    DoCmd.Close

errClose:
    MsgBox (Err.Description & "Close")
End Sub
0
Comment
Question by:21erinswift
13 Comments
 
LVL 12

Expert Comment

by:fulscher
ID: 11944057
First of all, please set OPTION EXPLICIT at the top your code and re-compile:

Option Compare Database
Option Explicit

'*********************************************************
0
 

Author Comment

by:21erinswift
ID: 11944121
Okay, I did and I still get the same error
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11944145
Remove the .Value property, i.e.:
Me.Recordset.FindFirst "[COURSEID]=" & Me![cmboCourse].Column(0)

Since you are explicitly referring to a combo box's column, the Value property does not apply to a Column.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11944158
The same applies to the textbox that is trying to retrieve the value.

If you have:
=Form!cmboCourse.Column(1).Value

use:
=Form!cmboCourse.Column(1)

Note that you cannot use Me! in a control source. If you have it, replace it with Form!
0
 

Author Comment

by:21erinswift
ID: 11944166
Now I get error 91 - object variable or with block variable not set
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11944367
Try changing the line to read:
Me.RecordsetClone.FindFirst "[COURSEID]=" & Me![cmboCourse].Column(0)
Me.Bookmark=Me.RecordsetClone.Bookmark

Does that work?
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:21erinswift
ID: 11944661
No, now I get an error that there is an invalid reference to RecordsetClone property.  Me.RecordsetClone.FindFirst is not valid
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11944739
Hmm, is your form bound to an ADO recordset?
0
 
LVL 4

Expert Comment

by:leslynf
ID: 11944755
Check placement of quotes in your sql statements.
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 250 total points
ID: 11944763
BTW - your sub's error handling code is missing a line to skip it if there is no error:
Private Sub Form_Activate()
'when form is activated, check for correct data to display
On Error GoTo errActivate
    'If a Course is selected in combo box
    If Not IsNull(Me![cmboCourse]) Or Me![cmboCourse] = "" Then
        'make sure appropriate records are displayed
        blnSelectRecord = True
       '<<<<<<<<<<ERROR IS ON NEXT LINE>>>>>>>>>>>
        Me.Recordset.FindFirst "[COURSEID]=" & Me![cmboCourse].Column(0).value
        'refresh list boxes
        Refresh_InCourseData
        Refresh_NotInCourseData
    End If
    Exit Sub         <------------------------------------------- This line added

errActivate:
    MsgBox (Err.Description & Err.Number & " - Activate")
End Sub
0
 

Author Comment

by:21erinswift
ID: 11944814
shanesuebsahakarn - Adding that line got rid of my errors, but my text box still isn't displaying the correct answer.  I'm going to close this question and open a new one for that.  Thank you for your help.

Erin
0
 

Author Comment

by:21erinswift
ID: 11944910
Sorry, my fault, this didn't actually fix the problem.  I just didn't re activate the form.  
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11944977
What's the recordset of your form? Is it bound to an SQL Server table, or an Access table?
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now