Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Question Private Sub Form_Load() not running

    Hi

    I'm not a programmer in any way and I've created a database and want to be able to run a search be Operator and Job Number. However my code doesn't work and the Form_Load() isn't running. Below is the code I'm using. Please help!

    Option Compare Database
    Option Explicit
    Private Sub Form_Load()
    Me.Results.RowSource = ""
    End Sub

    Private Sub ShowMatches_Click()
    Me.Results.SetFocus
    Me.Results.Value = Null
    Me.Results.RowSource = _
    "SELECT Operator, Job Number FROM OperatorData " & _
    "WHERE Operator LIKE ""*" & DQ(Me.SearchByOperator.Value) & _
    "*"" AND JobNumber LIKE ""*" & DQ(Me.SearchByJobNumber.Value) & "*"""
    End Sub


    Private Function DQ(s As Variant) As String
    ' double-up double quotes for SQL
    DQ = Replace(Nz(s, ""), """", """""", 1, -1, vbBinaryCompare)
    End Function

    Thank you!

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    For a start I would not use Row.Source to search.
    I would leave the Row.Source as a standard query for the table Operator.
    I would use the Me.Flter and Me.FilterOn events.

    is the Operator alphanumeric?
    Is the JobNumber numberic? If so no need for quotes.

    Put this code behind a button:
    Me.Filter = "Operator = '" & SearchByOperator & "' AND Jobnumber = " & SearchByJobNumber
    Me.FilterOn = true


    Using ' obviates the need for ""

  3. The Following User Says Thank You to patt For This Useful Post:

    enginerd (2015-06-25)

  4. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Both the Operator and JobNumber are alphanumberics. I can now get the form to load but when I try searching an error pops up saying "Find" isnt available. I'm not sure what I'm doing incorrectly.

  5. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    All form_Load does is set the rowsource of what I assume is a listbox. I'm not even sure why that is necessary, just set the rowsource of the listbox in design mode.

    When do those other procedures get run?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    i removed the Me.Results.RowSource = "" and now it will run to an extent, but i type in the operator name for example and instead of searching my database for teh matching operator names, it says the "Find" option is unavailable (same window that appears in mocrosoft when you press Ctrl+F). So Its running but not executing what I want it to do. If that makes sense?

  7. #6
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    CODE.PNG

    That is what my code currently looks like. (Hopefully it attaches correctly)

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    Screen shots of code windows do not work very well. Try copying your code (CTRL+C) then pasting (CTRL+V) it into the post between [code] your code here [/code] tags. This will provide readable code which can be easily copied and manipulated by those trying to assist you. HTH
    Last edited by RetiredGeek; 2015-06-25 at 09:31.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    enginerd (2015-06-25)

  10. #8
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Option Compare Database
    Option Explicit
    Private Sub Form_Load()
    End Sub

    Private Sub ShowMatches_Click()
    Me.Results.SetFocus
    Me.Results.Value = Null
    Me.Filter = Operator = & SearchByOperator & _ OR JobNumber = & SearchByJobNumber
    Me.FilterOn = True
    End Sub

    Private Function DQ(s As Variant) As String
    ' double-up double quotes for SQL
    DQ = Replace(Nz(s, ""), """", """""", 1, -1, vbBinaryCompare)
    End Function

    This is what my code looks like now. the red line is where it's not working now.

  11. #9
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,723
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Code:
    Option Compare Database
    Option Explicit
    Private Sub Form_Load()
    End Sub
    
    Private Sub ShowMatches_Click()
    Me.Results.SetFocus
    Me.Results.Value = Null
    Me.Filter = Operator = & SearchByOperator & _ OR JobNumber = & SearchByJobNumber
    Me.FilterOn = True
    End Sub
    
    Private Function DQ(s As Variant) As String
    ' double-up double quotes for SQL
    DQ = Replace(Nz(s, ""), """", """""", 1, -1, vbBinaryCompare)
    End Function
    This is what you should have got when you did as RG suggested. If you click on 'go advanced' , there is a # button. Select the code and press this, then add any text outside of the code parameters.

  12. The Following User Says Thank You to access-mdb For This Useful Post:

    enginerd (2015-06-25)

  13. #10
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Code:
    Option Compare Database
    Option Explicit
    Private Sub Form_Load()
    End Sub
    
    Private Sub ShowMatches_Click()
    Me.Results.SetFocus
    Me.Results.Value = Null
    Me.Filter = Operator = & SearchByOperator & _ OR JobNumber = & SearchByJobNumber
    Me.FilterOn = True
    End Sub
    
    Private Function DQ(s As Variant) As String
    ' double-up double quotes for SQL
    DQ = Replace(Nz(s, ""), """", """""", 1, -1, vbBinaryCompare)

    This is what my code looks like. Still not working. You guys are great for helping.

  14. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Enginerd,

    I think your filter statement is at least part of the problem.

    Code:
    Me.Filter = "[Operator]=" & Chr(34) & SearchByOperator & Chr(34) & " OR [JobNumber]=" & Chr(34) & SearchByJobNumber & Chr(34)
    Hopefully I got the quotes ( Chr(34) ) in the right places. The problem here is keeping the quotes required for the VBA and the quotes required for the filter in the right places. There is more than one way to do this but my preferred method is to use the quote mark ( " ) for the VBA and the ANSII Character ( Chr(34) ) for the quotes needed by the Filter (this is also true when constructing SQL stmts).

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. The Following User Says Thank You to RetiredGeek For This Useful Post:

    enginerd (2015-06-25)

  16. #12
    New Lounger
    Join Date
    Jun 2015
    Posts
    22
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thank you everyone for your help! i got it to run

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •