Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171

    Trouble with syntax to change rowSource in listbox

    I am attempting to change the rowsource in a list box based on value in a text box on the same bound form.
    I have spent several hours working on the SELECT code and have failed.
    This is what my latest code looks like. Any thoughts?


    Private Sub Command17_Click()
    Dim strSource As String
    strSource = "SELECT [TblAllEvents].[Badge], [TblAllEvents].[EventNumber], [TblAllEvents].[FirstName], [TblAllEvents].[LastName], [TblAllEvents].[Attendees]" & "FROM [TblAllEvents]" & "WHERE TblAllEvents.EventNumber & Me.EventNumber"
    Me.LstVolunteers.RowSource = strSource
    Me.LstVolunteers.Requery



    End Sub

    Thanks

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    strSource = "SELECT [TblAllEvents].[Badge], [TblAllEvents].[EventNumber], [TblAllEvents].[FirstName], [TblAllEvents].[LastName], [TblAllEvents].[Attendees] FROM [TblAllEvents] WHERE TblAllEvents.EventNumber = " & Me.EventNumber

    Above assumes EventNumber is a number. If it's a string then
    strSource = "SELECT [TblAllEvents].[Badge], [TblAllEvents].[EventNumber], [TblAllEvents].[FirstName], [TblAllEvents].[LastName], [TblAllEvents].[Attendees] FROM [TblAllEvents] WHERE TblAllEvents.EventNumber = '" & Me.EventNumber & "'"


    Last edited by davegri; 01-21-2019 at 08:47 AM. Reason: string option

  3. #3
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    I have copied your code and I now get a compile error 'Argument not optional' and the Event number at the end is highlighted.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Do you have a function named EventNumber()?

    Access is assuming that you do. If the function exists, it requires an argument which is not present.
    If there is no such function, is there a field on the form named EventNumber?

    Other possibility, just change Me.EventNumber to [EventNumber]
    Last edited by davegri; 01-21-2019 at 12:59 PM. Reason: layout

  5. #5
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Thanks for speedy response
    No function named EventNumber. There is a field on the form with the name EventNumber.
    The change you suggested gives me the same result. Any other thoughts?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis follow instructions at bottom of my post.

    Why do you want to limit the list to badges already associated with the EventNumber?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    listbox-davegri-v01.zip
    This one works. Take a look to see what's different in yours.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It appears that the text box control name is the same as the bound field. Sometimes the Access gnomes get obstinate - you should not have the control name the same as the bound field. Try renaming the text box to "tbEventNumber" (tb = text box).

    This is how I might write the sub (if Event number is a number):
    Code:
    Private Sub Command17_Click()  '<<-- Command17 is a poor name.... just saying
        Dim strSource As String
        Dim intEventNum As Integer
    
         'get event number from form
        intEventNum = Val(Trim(Me.tbEventNumber & ""))   '<<-- Note text box name!
    
        strSource = "SELECT [TblAllEvents].[Badge], [TblAllEvents].[EventNumber], [TblAllEvents].[FirstName], [TblAllEvents].[LastName], [TblAllEvents].[Attendees]"
        strSource = strSource & " FROM [TblAllEvents]"
    
        If Nz(intEventNum, 0) > 0 Then
            strSource = strSource & " WHERE TblAllEvents.EventNumber = " & intEventNum
            Debug.Print strSource   '<< comment out when debugging is complete
    
            Me.LstVolunteers.RowSource = strSource
            Me.LstVolunteers.Requery
        Else
            MsgBox "Missing value or Error in control 'tbEventNumber'"
        End If
    
    End Sub
    To check the generated SQL, you can copy the SQL from the immediate window, create a new query, switch to SQL view, paste in the copied SQL and execute the query.

  9. #9
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    I finally got the attached code to work but when I attempt to add the order by to it it bombs out.
    Im not sure what the proper syntax is. Im trying to oreder by LastName.
    Should be easy right?

    Thanks

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Show what you tried.

    strSource = strSource & " WHERE EventNumber = " & intEventNum & " ORDER BY LastName"

    Since only one table is referenced, really don't need to prefix field references with table name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Using this ORDER By code my requery returns a blank listbox
    How can anything so straight forward be so difficult.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    " ORDER BY LastName"

    Did you include the space before ORDER?

  13. #13
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Sorry I did not include the actual code in my last post. It is as follows:

    WHERE TblAllEvents.EventNumber = " & IntEventNumber & "ORDER BY [LastName]"

    This seems to get the eventnumber ok since I can display it in a msgbox but it still returns a blank list box.

    Thanks by the way for the tip about not needing the table name when only one table is involved

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    WHERE TblAllEvents.EventNumber = " & IntEventNumber & "ORDER BY [LastName]"
    See post#12. You need that space!

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    As well as post 10. The space is there in the example code. Without it, the text will run together.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-01-2018, 07:31 AM
  2. Reversing rowsource order for a multicolumn listbox
    By wackywoo105 in forum Programming
    Replies: 6
    Last Post: 12-10-2014, 06:26 AM
  3. Change rowsource on cbo
    By Tvanduzee in forum Forms
    Replies: 1
    Last Post: 09-27-2012, 02:04 PM
  4. Updating listbox rowsource not working
    By j2curtis64 in forum Forms
    Replies: 13
    Last Post: 12-07-2011, 01:46 PM
  5. ListBox RowSource Update Not working in 2003
    By Access0307 in forum Programming
    Replies: 1
    Last Post: 05-05-2011, 07:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums