Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Suggestion for Best Approach (2002)

    Hi,

    I'm looking for suggestions on the best way to approach this. I have frmStoreDemographicLookup which contains lstStoreName. The lst contains a variety of data, last column being [Active Status] which is set to yes/no. The user needs to the option to refresh the list based on whether the store is active (yes) inactive (no) or to see all stores both active and inactive.

    I've placed a frame [optActiveStatus] with three choices [active], [inactive], [allstores] if it. There is a button [cmdRefreshList] which is meant to requery the list based on the option chosen. I placed the following code on the click even

    'Enters status choice

    Select Case Me.OptActiveStatus

    Case 1
    Me.lstStoreName.Column(4) = True
    Case 2
    Me.lstStoreName.Column(4) = False
    Case 3
    IsNull Me.lstStoreName.Column(4)
    End Select

    'Requeries list after a status is chosen
    Me.lstStoreName.Requery

    When I run the code I get an error stating an object is required. I'm not sure what to do or even if this is the best approach.

    Thanks,
    Leesha

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

    Re: Suggestion for Best Approach (2002)

    You have to change the Row Source property of the list box. What is the current Row Source of the list box?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion for Best Approach (2002)

    Hi Hans!

    I would never have thought to change the row source, nor what to put there! Here is what I have presently:

    SELECT tblStoreDemographics.ID, tblStoreDemographics.[WALMART NO] AS [WALMART ID], tblStoreDemographics.[STORE NAME], tblStoreDemographics.STORE_ID AS [STORE ID], tblStoreDemographics.[ACTIVE STATUS]
    FROM tblStoreDemographics
    ORDER BY tblStoreDemographics.[WALMART NO];

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

    Re: Suggestion for Best Approach (2002)

    Try this:

    Dim strSQL As String

    ' First part of SQL
    strSQL = "SELECT ID, [WALMART NO] AS [WALMART ID], [STORE NAME], " & _
    "STORE_ID AS [STORE ID], [ACTIVE STATUS] FROM tblStoreDemographics "

    ' Add criteria
    Select Case Me.OptActiveStatus
    Case 1
    strSQL = strSQL & "WHERE [ACTIVE STATUS] = True "
    Case 2
    strSQL = strSQL & "WHERE [ACTIVE STATUS] = False "
    Case 3
    ' No criteria needed
    End Select

    ' Add sort order
    strSQL = strSQL & "ORDER BY [WALMART NO]"

    ' Set row source
    lstStoreName.RowSource = strSQL

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion for Best Approach (2002)

    OMG!! That is perfect!!!!

    Thank you so much!!

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion for Best Approach (2002)

    OK, so now I'm trying to get the form to automatically load to case 1 or to active so the user doesn't have to set it each time the form opens. If I use the code

    me.optAction.setfocus
    me.lstStoreName.requery

    I get an error that says I can't set the focus to the optSetFocus. What am I missing?

    Thanks,
    Leesha

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

    Re: Suggestion for Best Approach (2002)

    Instead of

    me.optAction.setfocus

    use

    Me.optAction = 1

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion for Best Approach (2002)

    Hi Hans,

    I tried your suggestion and now I get an error that states I can't assign a value to this object.

    Leesha

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion for Best Approach (2002)

    Hi Hans,

    I tried using the same code to option box optActionStatus vs the option button itself and this time it opened with the correct button active. I must've misunderstood what you wanted me to do.

    Thanks,
    Leesha

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

    Re: Suggestion for Best Approach (2002)

    No, I was wrong - I didn't look back at the code higher up in the thread, so I didn't notice that you were referring to an option button instead of the group box. Glad you found the correct solution yourself.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Suggestion for Best Approach (2002)

    The only reason I tried it was I was trying to think like you! God I'd kill for your brain. I find these days that before I click the button to post the question, I stop and think "what would Hans or Francoise tell me to do" and then I go back and try it again. 9 times out of 10 I don't get it but every once in awhile I get lucky!

    Thanks,
    Leesha

Posting Permissions

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