Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: If statement help needed

    I have two listboxes on an Access 2003 form, and need to write an IF statement in a command button based on the value of a column in each of the listboxes -

    If listbox1 (column2) numerical value does not match listbox2 (column3) numerical value, then produce a message, else run the code..

    What should be the correct syntax for this if statement?

    Thankyou.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Listboxes have a Column(index) property. The Column index starts counting with Zero ( 0 ) not 1. ). So 0 = Column 1

    So try something like:

    If Me.Listbox1.Column(1) <> Me.ListBox2.Column(2) Then
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Hello, thankyou for replying, I appreciate it.

    Your suggestion does work, but only if I select a row from both listboxes.

    I do have to select a row in the first list box, not in the second list box as there may be lots of rows in column2.

    How can I make the code work without having to select a row in the second listbox? The code is in an on-click event in a command button.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by moss2076 View Post
    Hello, thankyou for replying, I appreciate it.

    Your suggestion does work, but only if I select a row from both listboxes.

    I do have to select a row in the first list box, not in the second list box as there may be lots of rows in column2.

    How can I make the code work without having to select a row in the second listbox? The code is in an on-click event in a command button.
    I am getting a little confused here. You origianlly said this:

    Quote Originally Posted by moss2076 View Post
    I have two listboxes on an Access 2003 form, and need to write an IF statement in a command button based on the value of a column in each of the listboxes
    Based on the above original post, this means that the user would have to select something in both list boxes. Based on this, the code I gave you was designed to handle this.

    Then you said:
    Quote Originally Posted by moss2076 View Post
    Your suggestion does work, but only if I select a row from both listboxes.
    Is that not what you originally ask for?


    Then you ask:
    Quote Originally Posted by moss2076 View Post
    How can I make the code work without having to select a row in the second listbox?

    <confused>
    How do you compare something to nothing? That is invalid Logic in Access VBA.

    What you as asking is how to write VBA code so Access compares a value in the first listbox to nothing from the second list box. Is that correct?

    </confused>


    I will take a SWAG (Scientific Wild A** Guess) at this.

    Try:
    Code:
    If Nz(Me.Listbox1.Column(1),"") <> Nz(Me.ListBox2.Column(2),"No Selection") Then
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Ok Im sorry if I caused any confusion, I will try again

    2 listboxes, one command button.

    I select a row from first listbox, the value of the field in column2 (for example the value of 1) may match the value(s) which are of the field in column3 of the second listbox (for example the value of 1).

    There may be 1, 10, 1000 etc rows in the second listbox so I dont need to select anything in the second listbox. I just need a message box to appear if the values do match in both listboxes.

    Does that make more sense?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to paraphrase
    you want to select a value in a listbox
    based on whether that value exists in another list box you want to take some action (or not)

    ...are you matching against the selected value of the second list box?
    OR any value in other rows/elements of the list box

    so what is the source of the data in the second listbox?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    ...are you matching against the selected value of the second list box?
    OR any value in other rows/elements of the list box
    Im not selecting (clicking on) any rows in the second listbox. The value of the thrid column of the second listbox will be the same if there is 1, 10 or 100 records in the second listbox.
    ...so what is the source of the data in the second listbox?
    It is a query consisting of fields from 3 tables. One of the fields is the field in the second listbox.

  8. #8
    Join Date
    May 2010
    Posts
    601
    I am not sure it this is what healdem was thinking, but it will be a lot easier to write a little VBA code to filter the same record source as the second lix bos looking to see it there are any matches to the selection in the first list box.

    It would probably help if you would explain what you are doing. Maybe then what you are attempting will make more sense.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the easiest and quickest way of doing your match will be to use something like a dlookup, assuming your second list box is populated by a query from a table. based on that dlookup you can decide what you wantr to do as your if statement.

    however that means your second listbox must be read only (ie you cannot add any new rows.

    the dlookup goes in the first listbox's on clcik event
    specify your criteria as required. effectively a dlookup is a rewritten standard SQL select statement, but designed for non developers. the criteria equates to the "where" clause
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Aug 2004
    Posts
    364
    What is happening in the two listboxes is this..

    In the first listbox I select a user who is a member of a group, so the listbox displays userID, userName, GroupID, GroupName.

    When the command button is clicked the userID and GroupID is added to the second listbox to create a visit (with its own visitID, date, time etc).

    This is working fine.

    I should not be able to add a userID and GroupID if users are members of a different groupID which is displayed in the second listbox. This is where I need the message to appear. I have added a screen grap of the two listboxes with the first one selected where the GroupID of the second listbox is different to the first listbox.
    Attached Thumbnails Attached Thumbnails Capture.JPG  

  11. #11
    Join Date
    May 2010
    Posts
    601
    Thanks. That helps a lot. Things are starting to make sense.

    What if the second list box is empty. How will you know which group?

    Whhat determine which group is displayed in the second list box?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  12. #12
    Join Date
    Aug 2004
    Posts
    364
    The second list box has a bound field (visitID) which is linked to the formand the visitID textbox field(which is an autonumber). If the second listbox is empty, it is because a group hasnt been added to the form fields via another seperate listbox. When a group has been added, the members of that group are shown in the original first listbox.

    Complicated I know but it does work. Ive added a larger screen shot.
    Attached Thumbnails Attached Thumbnails Capture2.JPG  

Posting Permissions

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