Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: Simple Cascading Combobox

    Hi,

    I have a simple two-box Cascading Combo on my Form (see attached database and/or coding below).

    It seems to work fine, so far, but needs some fine tuning.
    So what I am trying to do next, is to work out a simple solution for the following situations:-

    1. When TransactionType is Clicked, and the choice STAYS THE SAME (eg. TransactionType is “Electric”, then the user clicks the arrow on the combobox and selects the “Electric” choice again, then CompanyName should also stay the same.
    Note: This seems to work in the attached database at the moment…..

    2. When TransactionType is Clicked, and the choice is DIFFERENT TO WHAT WAS PREVIOUSLY THERE, then the CompanyName should go blank. Eg. I don’t want an “Electric” CompanyName if the TransactionType is “Maintenance”.

    Note: I have tried to insert
    Me.CompanyName = “”
    in the AfterUpdate event but that made my problem no.1 (above) not work properly because it also cleared the CompanyName even though the TransactionType remained the same.

    Code:
    Private Sub TransactionType_AfterUpdate()
    
            Me.CompanyName.Requery
            Me.CompanyName.SetFocus
          
    End Sub
    Attached Files Attached Files
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Nov 2009
    Posts
    223
    oops sorry, wrong revison of the DB attached.

    here is the latest one.
    Attached Files Attached Files
    What would you attempt to do if you knew you would not fail?

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There is an OldValue Property that you could check against the current Value, but the problem with using this is that if you go to a Record and find that

    Me.Category = "Electric"
    Me.CompanyName = "ElectricCo1"


    If you then change Category to "Phone," and select a "PhoneCo3," then change your mind and put the Category back to "Electric," "PhoneCo3" will still be accepted, because the OldValue of Category and the Current Value of Category is the same, 'Electric!'

    I think what you need to do is to check what Category a given CompanyName belongs to (using DLookup) and dump the current value if the TransactionType and Category do not match. I think something like this will do what you need:

    Code:
    Private Sub TransactionType_AfterUpdate()
     
    If DLookup("Category", "tblCompanyNames", "[CompanyName]='" & Me.CompanyName & "'") <> Me.TransactionType Then
     
     Me.CompanyName = Null
     Me.CompanyName.SetFocus
    
    End If
    
    End Sub


    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    Thanks for your advice, Linq, as always.

    I haven't used, or tried to understand DLookup yet, but luckily i managed to find another way to solve this problem that has been bugging me for weeks.

    Option Compare Database
    Option Explicit
    Dim RememberTransactionType As String

    Private Sub TransactionType_AfterUpdate()
    If RememberTransactionType <> Me.TransactionType Then
    Me.CompanyName = ""
    End If
    Me.CompanyName.Requery
    Me.CompanyName.SetFocus
    End Sub


    Private Sub TransactionType_Enter()
    RememberTransactionType = Me.TransactionType
    End Sub
    Cheers again.
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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