Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have a form with a combo box called CurrentStatus loaded via SQL as follows:

    Column Widths = 0;1;0
    Bound Column = 1

    <pre>ID Description Code
    1 Active A
    2 Inactive I
    3 New N
    4 Renewal R
    </pre>


    Example, if the combo box is set to Active, in vba code how can I change it to Inactive without knowing the ID

    In code I want to lookup the combo box using the I code (which is consistent) and extract the ID 2 into variable lngID

    Then I want change the CurrentStatus combo as follows:

    Me.CurrentStatus = lngID

    Thanks, John

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

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    If you set the Row Source of the combo box to an SQL string, it is more work than if you set it to the name of a stored table or query. Is there a chance to do the latter?

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    I

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

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Although it would be possible to loop through the items of the combo box to locate the item with 'Inactive', it is much more efficient to use a DLookup on tblStatus. As single instruction will suffice, instead of writing a loop.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    My actual situation is a more complex than Dlookup can handle

    The following seems to work, do you see any problrms with this?

    Thanks, John


    <pre>Private Sub Change_Status_to_Inactive_Click()

    Dim i As Integer
    For i = 1 To Me.ID.ListCount
    If Me.ID.Column(2, i) = "I" Then
    Me.ID = Me.ID.Column(0, i)
    End If
    Next i

    End Sub
    </pre>


  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Why don't you use :
    Me.ID = 2
    as 2 is the ID for inactive.

    Second question :
    Can't you use A,I,N,R as ID for the records. This only if you don't are to far in the development of your application, as it would be necessary to change the type of the ID field to Text.
    Francois

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

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    This could still be handled by a DLookup. If your situation is more complex, while you don't tell us what that situation is, how could we be able to help you?

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Francois

    This is common lookup table for all clients using the program which contains values for many different simple combo boxes in the application.

    I for client A could be 2, for client B could be 3, etc

    The letter codes are unique and consistent

    This is an inherited db that I can

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi Hans

    This is common lookup table for all clients using the program which contains values for many different simple combo boxes in the application.

    I understand Dlookup to use only one criteria field

    <pre>SELECT tbl_OtherLookup_Clerk.lngID
    FROM tbl_OtherLookup_Clerk
    WHERE (((tbl_OtherLookup_Clerk.strCode)="I")
    AND ((tbl_OtherLookup_Clerk.strApplCode)="CLK")
    AND ((tbl_OtherLookup_Clerk.strTypeCode)="ST2"));
    </pre>


    Thanks, John

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

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Not sure what you mean, but the equivalent (more or less) of the SQL you posted would be
    <pre>DLookup("lngID", "tbl_OtherLookup_Clerk", "strCode='I' AND strApplCode='CLK' AND strTypeCode='ST2'")
    </pre>

    Note that I changed the quotes within the criteria part to single quotes, to avoid confusion with the double quotes enclosing the criteria as a whole.

  11. #11
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Lookup In Code? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I Like that!

    Thank you, John

Posting Permissions

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