Simple Cascading Combo problems

[reddevil1]

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.

Private Sub TransactionType_AfterUpdate()
        Me.CompanyName.Requery
        Me.CompanyName.SetFocus
      
End Sub

 

[reddevil1]

oops sorry, wrong revison of the DB attached.

here is the latest one.

 

[JHB]

Try adding this code:

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

 

[reddevil1]

THAT'S IT, THAT'S IT, THAT'S IT...........

After weeks of searching for a reasonably simple solution to the problem, JHB has found it.

Thanks a million, pal, for submitting the suggestion.

I am a fairly new VBA beginner, and think i know how the code works (by remembering the TransactionType in the Enter event every time it is used. Then only applying the rest of the code if the 'remembered' data is not the same as the actual data in the TransactionType combo?

If I have understood that correctly, then yipppppeeeeee.

Cheers again