Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Record Selection using strWhere (A2K)

    Access Folks!

    Nice to see you all back.

    Scenario:

    Three different people are inputting items from three separate terminals with all of the data going into one table.

    As records are created, an autonumber (CN) is being created as well as each person's Id being embedded in the record. It would look like this:

    Person 1: Id 39
    Creates: CN4264, CN4265, CN4268, CN4270

    Person 2: Id 78
    Creates: CN4266, CN4267, CN4271, CN4273

    Person 3: Id 85
    Creates: CN4262, CN4263, CN4272, CN4274

    Now, each of these individuals have to print out Labels for only the items they entered based on CN / Id

    The following code enables any of them to select a range of numbers from an input form with To and From fields:

    Private Sub txtRangeEnd_01_Exit(Cancel As Integer)
    Dim strWhere As String ' Do Not Change These, ever!
    Dim lngLen As Long ' Do Not Change These, ever!
    Const conJetDate = "#mm/dd/yy#" ' Do Not Change These, ever!
    '
    ' This reads the CN's entered in the From and To boxes
    '
    If Not IsNull(Me.txtRangeStart_01) Then
    strWhere = strWhere & "([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "
    Me.Recalc
    End If
    '
    'This finds the requested range of CN's
    '
    lngLen = Len(strWhere) - 5

    If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True
    End If

    Me.Refresh
    DoCmd.OpenReport "rpt 01 LogIn - Label", acViewPreview, , strWhere
    DoCmd.RunCommand acCmdZoom100


    Dim ctl As Control
    For Each ctl In Me.Section(acDetail).Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    ctl.Value = Null
    Case acCheckBox
    ctl.Value = False
    End Select
    Next
    Me.FilterOn = False
    Me.Recalc

    End Sub

    The above code works perfectly with respect to selecting CN’s between any two numbers.

    Where I'm having a problem is having not only the To and From numbers selected, but also their specific Id's.

    I tried a number of statements and the closest one that comes anywhere near working is the following:

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "

    I get a Compile Error: Expected: ) and the Between is highlighted.

    So, if this worked, Person 2 would retrieve and print CN4266, CN4267, CN4271, CN4273

    Thanks in advance for any help on this,

    Andy McCraw
    Cheers,
    Andy

  2. #2
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Check your syntax. You have two left hand brackets(at the beginning and before[SRT_CN]) and only one closing bracket at the end. Didn't have time to parse carefully but that's why it is cacking out on you. I'll leave it to smarter people to tell you where you need to put the second one.

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Hi Peter,

    Thanks for the quick reply.

    Yes there exists a case of the disappearing bracket.

    So I changed:

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "

    to

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & ") And " & Me.txtRangeEnd_01 & ") AND

    Where I added the 2nd right bracket after Me.txtRangeStart_01 & which seemed logical.

    Except to Access.

    I again got a Compile Error: Expected: ) and the Between is highlighted
    Cheers,
    Andy

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Record Selection using strWhere (A2K)

    I think it is helpful to bracket each separate condition, but you don't need a bracket between the two dates in the Between clause.


    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " ) And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND
    Regards
    John



  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Good Morning John,

    Tried this:

    My original version:

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & ") And " & Me.txtRangeEnd_01 & ") AND "

    and your suggested version:

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & " ) And " & ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND

    Both gave me the "Compile Error: Expected : ) " and the Between is highlighted in both cases.

    I also tried both versions of the last AND; with and without the closing "
    I also tried both versions Without the last AND.
    Guess who's grasping at straws!

    I'm obviously missing something simple.

    Thanks for your suggestion,

    Cheers,
    Cheers,
    Andy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record Selection using strWhere (A2K)

    If SRT_CN is a number field, try

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & ") And ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "

    If SRT_CN is a text field, it should be

    strWhere = strWhere & "([F01_UserId] = " & Me.txtUserId & ") And ([SRT_CN] Between " & Chr(34) & Me.txtRangeStart_01 & Chr(34) & " And " & Chr(34) & Me.txtRangeEnd_01 & Chr(34) & ") AND "

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Hans,

    Really, really, really glad to see you back. Hope your time off wasn't too stressful considering the problems you folks were having.

    Anyway,

    As Usual!

    Dead Perfect!

    Cheers,
    Cheers,
    Andy

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Hans,

    You know me (unfortunately for you) that nothing is so good that it can't be enhanced.

    Same situation with respect to the label printing, which, as I mentioned, worked perfectly using your code, which is executed on:

    Private Sub txtRangeEnd_01_Exit(Cancel As Integer)
    .
    .
    strWhere = strWhere & "([F01_UserId] = " & Me.txt_UserId & ") And ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "
    .
    .
    End Sub

    Within the same table/query I have a Yes/No field called: [F01_LabelPrinted] which I want to "Turn On" to indicate that the labels selected above have been printed.

    I inserted:

    Me.[F01_LabelPrinted] = True

    within the txtRangeEnd_01 routine above, but it got lost in the translation and did not turn on the indicator.

    Ideally ( as always ) this should happen "automatically" so as not to necessitate any further action on the user's part.

    Would appreciate any suggestions.

    Cheers,

    ps. additonal thought: if and when the indicator is turned on, I need to make sure that it does not get accidentally printed again if it were selected in the above code for printing. Sorry about the afterthought
    Cheers,
    Andy

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record Selection using strWhere (A2K)

    1) If you don't want to print records for which F01_LabelPrinted has already been set to True, you could add the following to strWhere:

    strWhere = strWhere & "[F01_LabelPrinted] = False AND "

    This will impose the additional condition that F01_LabelPrinted is False.

    2) To set F01_LabelPrinted to True in multiple records, you need to execute an update query or equivalent SQL statement. Something like this:

    Dim strSQL As String
    strSQL = "UPDATE [NameOfTheTable] SET [F01_LabelPrinted] = True WHERE " & strWhere
    CurrentDb.Execute strSQL, dbFailOnError

    Replace NameOfTheTable with the actual name of the table that contains the F01_LabelPrinted field. The above code should be executed *after* printing the labels, otherwise the "printed" flag will already have been set to True before printing...

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Hans,

    Awesome! This will be worked on first thing tomorrow morning, and hopefully I won't have to badger you further on this particular subject.

    Cheers,
    Cheers,
    Andy

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Hans,

    OK, another step forward has been achieved.

    Now, before the final question…. See end of this diatribe …

    For those who may have been following this thread and also to verify that all is in place before the question, here is the complete working code todate:

    '===================================
    Private Sub txtRangeEnd_01_Exit(Cancel As Integer)
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "#mm/dd/yy#"
    '===================================
    If Not IsNull(Me.txtRangeStart_01) Then
    strWhere = strWhere & "[F01_LabelPrinted] = False AND "
    strWhere = strWhere & "([F01_UserId] = " & Me.txt_UserId & ") And ([SRT_CN] Between " & Me.txtRangeStart_01 & " And " & Me.txtRangeEnd_01 & ") AND "
    Me.Recalc
    End If
    '===================================
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    '===================================
    Me.Refresh
    Me.Visible = False
    DoCmd.OpenReport "rpt 01 LogIn - Label", acViewPreview, , strWhere
    DoCmd.RunCommand acCmdZoom100
    '===================================
    Dim ctl As Control
    For Each ctl In Me.Section(acDetail).Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    ctl.Value = Null
    Case acCheckBox
    ctl.Value = False
    End Select
    Next
    '===================================
    Me.FilterOn = False
    Me.Recalc
    Me.Visible = False

    Dim strSQL As String
    strSQL = "UPDATE [qry 01 CN] SET [F01_LabelPrinted] = True WHERE " & strWhere
    CurrentDb.Execute strSQL, dbFailOnError
    End Sub
    '===================================
    '===================================

    And now for the description of the current scenario and the question:

    When a range of SRT_CN’s is selected and they have already been printed, it pulls up a blank label indicating that there was no data to print. Which is ugly, but correct.

    So, having trolled through the Forum, I inserted this into the “rpt 01 LogIn – Label”:

    Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data to display"
    Cancel = True
    End Sub

    Which of course works very nicely, except of course, you get the old runtime error “2501”.

    So, back to trolling because this is a common problem.
    Good news is that I found a multitude of examples on how to get past this, except

    They were all based on the scenario that the error handling routines were within an On Click situation.

    Unfortunately, in my case, my whole show is based on On Exit situation and does not seem to lend itself to the suggested error handling routines.

    Is there a way around this or should I just bite the bullet and accept a blank label preview?

    Thanks in advance for your patience with my verbosity ( aka running off at the mouth/fingers )

    Cheers,
    Cheers,
    Andy

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record Selection using strWhere (A2K)

    You can add an error handling section to the txtRangeEnd_01_Exit procedure, just like to any other procedure. But it is important to know what exactly you want to do when the report is canceled due to lack of data. Do you still want to clear all controls, and do you want to turn off the filter?

  13. #13
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Good Morning Hans,

    In a perfect world, the following would happen:

    If there were labels to print:
    It would go to preview mode in preparation for printing
    It would clear all controls
    It would turn off the filter
    It would close the form

    If there were NO labels to print:
    It would display the message
    It would clear all controls
    It would turn off the filter
    It would close the form

    Pretty short answer for me,

    Cheers,
    Cheers,
    Andy

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Record Selection using strWhere (A2K)

    You could structure your code like this:

    Private Sub txtRangeEnd_01_Exit(Cancel As Integer)
    On Error GoTo ErrHandler
    ' Existing code goes here
    ...
    ...
    DoCmd.OpenReport "rpt 01 LogIn - Label", acViewPreview, , strWhere
    DoCmd.RunCommand acCmdZoom100

    ContinueHere:
    ' Existing code goes here
    ...
    ...
    ' We're done - get out
    Exit Sub

    ErrHandler:
    If Err = 2501 Then
    ' Ignore error and continue
    Resume ContinueHere
    Else
    ' Something really went wrong - show message (and get out)
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

  15. #15
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Record Selection using strWhere (A2K)

    Hans,

    Correct me if I’m wrong, but the routine starts with:

    Private Sub txtRangeEnd_01_Exit(Cancel As Integer)

    But you put ErrHandler: after my End Sub and then had an End Sub after ErrHandler:

    I got a label not defined error. My assumption was that you need a Sub for every End Sub.

    I hope I'm not missing something obvious as usual,

    Cheers,
    Cheers,
    Andy

Page 1 of 2 12 LastLast

Posting Permissions

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