Solved Help on Select code on combobox

[lookforsmt]

HI All

I have 4 combo box as shown in image
Org :
BankId :
CBKACName :
CBKACName_cbk :

Based on 1st and 2nd combo box inputs the 3rd and 4th combo box gets updated.
i have put the below Afterupdate code

Private Sub BankId_AfterUpdate()
    Me!BankName = Me.BankId.Column(2)
    
    If Me.BankId = "001" Then
       Me.CBKACName = "ABC_cbAC"
       Me.CBKAC = "1234567890"
       Me.CBKACName_cbk = "PQR_cbAC"
       Me.CBKAC_cbk = "3712345678"
    Else
    End If
    
    If Me.BankId = "002" Then
       Me.CBKACName = "PQR_cbAC"
       Me.CBKAC = "3712345678"
       Me.CBKACName_cbk = "ABC_cbAC"
       Me.CBKAC_cbk = "1234567890"
    Else
    End If
End Sub

How can i create the same logic using the Select code, since there are multiple combination scenarios
Attached image shows few example of combination

When user selects from combo box ORG and combox BankId update combo box CBKACName & CBKACName_cbk as mentioned below
ABC and 001 then update ABC_cbAC and PQR_cbAC

I am trying to switch to Select code from below code, any suggestion pls

Thanks

 

[Uncle Gizmo]

combo box ORG and combox BankId

Does ORG have several bank accounts (BankID)?

 

[Minty]

The syntax is quite simple

Select Case BankID

    Case "001"
       Me.CBKACName = "ABC_cbAC"
       Me.CBKAC = "1234567890"
       Me.CBKACName_cbk = "PQR_cbAC"
       Me.CBKAC_cbk = "3712345678"
    Case "002"
       Me.CBKACName = "PQR_cbAC"
       Me.CBKAC = "3712345678"
       Me.CBKACName_cbk = "ABC_cbAC"
       Me.CBKAC_cbk = "1234567890"
               
    Case "003"
        etc etc...
    
    Case Else 
   ' -- handy to catch a duff entry
End SELECT

 

[lookforsmt]

thanks Uncle Gizmo for the response. ORG has just two Bank at the moment, but may increase in future

Thanks Minty for your response too, i will try the select code example and update you

thanks once again

 

[Uncle Gizmo]

ORG has just two Bank at the moment, but may increase in future

As I suspected.

If you increase the amount of information then you're going to have to rewrite the case statement every time. A better option would be to put the data in a table.

I think you have a possibility of changing your structure into something which is more manageable and easier to update.

If you were to provide an explanation of the business process , then I'm sure a better solution will become apparent.

 

[lookforsmt]

Hi! Minty, i have applied the same code.
it works fine, however, i want to add one more criteria based on the ORG. So if the ORG is PQR, the account nos should be different.

Private Sub BankId_AfterUpdate()
    Me!BankName = Me.BankId.Column(2)
'----------------------------------------------
'    If Me.BankId = "001" Then
'       Me.CBKACName = "ABC_cbAC"
'       Me.CBKAC = "1234567890"
'       Me.CBKACName_cbk = "PQR_cbAC"
'       Me.CBKAC_cbk = "3712345678"
'    Else
'    End If
'
'    If Me.BankId = "002" Then
'       Me.CBKACName = "PQR_cbAC"
'       Me.CBKAC = "3712345678"
'       Me.CBKACName_cbk = "ABC_cbAC"
'       Me.CBKAC_cbk = "1234567890"
'    Else
'    End If
'----------------------------------------------
Select Case BankId

    Case "001"
       Me.CBKACName = "ABC_cbAC"
       Me.CBKAC = "1234567890"
       Me.CBKACName_cbk = "PQR_cbAC"
       Me.CBKAC_cbk = "3712345678"
    
    Case "002"
       Me.CBKACName = "PQR_cbAC"
       Me.CBKAC = "3712345678"
       Me.CBKACName_cbk = "ABC_cbAC"
       Me.CBKAC_cbk = "1234567890"
              
    Case "003"
       Me.CBKACName = "ABC_cbAC"
       Me.CBKAC = "1234567890"
       Me.CBKACName_cbk = "NA"
       Me.CBKAC_cbk = "NA"
    
    Case Else
       Me.CBKACName = "NA"
       Me.CBKAC = "NA"
       Me.CBKACName_cbk = "NA"
       Me.CBKAC_cbk = "NA"
End Select
End Sub

 

[Minty]

I'm not sure I understand the issue, you are setting the ORG Name in the case statement, so why not set the relevant account number at the same time?

Or as @Uncle Gizmo suggested simply put all the setting into a table and do a DLookUp() based on the BankID in the criteria.

 

[lookforsmt]

Thanks Uncle Gizmo, i feel too this will be more convenient. But i am not sure how do i put i create this table
To brief on the process.
User will 1st select the ORG, it may be ABC or PQR, then he selects the required BankId, if the ORG and BankId is same (ABC) then case "001" applies and if it is same as (PQR) then the case "001" is in inter changed between CBKACName & CBKACName_cb

There are multiple combinations,
Hope i have able to clarify.

 

[Minty]

Create a table with all the options in it.
It is not a normalised structure, it is purely for providing a lookup.

BankID, ORG, ACName, AcNum, etc etc

Populate it then in your dlookup use the BankID and Org as the criteria.

 

[lookforsmt]

Thanks Minty, i will give it a try, but my actual BankId has around 100+ bank names. If i have to replicate with two ORG, it will be exactly double the rows and in future there may be additional ORG in future, then i need to replicate another 100+ records.

Is there a different way or can there be two criteria applied in select code if that is doable.

 

[Minty]

If you have over a hundred codes then a table is the only way to go.
It's far easier to duplicate and extend table contents than hundreds of lines of code.
You can easily create the rows in excel and simply paste them into your table. One off operation, rinse and repeat if another ORG appears.

OR

Have a second table with the ORG and accounts and do a cartesian join in a query to give you the results and do the lookup on that.

 

[lookforsmt]

Thanks Minty, let me try creating the table
Meanwhile, how would the Dlookup code look like,

 

[Minty]

Something like

Me.CBKACName = Dlookup("ACName","YourLookupTable","[BankID] = '" & Me.BankID & "' AND OPR = '" & Me.YourOPRCombo & "'")   
Me.CBKAC = Dlookup("ACNumber","YourLookupTable","[BankID] = '" & Me.BankID & "' AND OPR = '" & Me.YourOPRCombo & "'")
Me.CBKACName_cbk = Dlookup("ACName_CBK","YourLookupTable","[BankID] = '" & Me.BankID & "' AND OPR = '" & Me.YourOPRCombo & "'")
Me.CBKAC_cbk = Dlookup("CBKAC_cbk ","YourLookupTable","[BankID] = '" & Me.BankID & "' AND OPR = '" & Me.YourOPRCombo & "'")

Substitute the first part of the dlookup for your lookup table field containing the value you need.

 

[lookforsmt]

Thank you Minty, will try this code

 

[lookforsmt]

HI i am getting below error after i have substituted the details. Pls help what i am doing wrong.

Run-time error 3075; Syntax error (missing operator) in query expression "ABC_cbAC"

 

[lookforsmt]

HI, i am now getting new error 2471, any suggestions
i am attaching my db, if i have put the code in the proper place
thanks

 

[lookforsmt]

HI, i am just trying to identify why the error code 2471 is appearing.
I dont know whether i am updated the code correctly or the table itself is wrong.

 

[MajP]

No such field named OPR

 

[lookforsmt]

HI! MajP, how arey you Sir, hope you are doing well.
I tried to fill in the code provided by Minty in post #13, did not ask him the meaning of OPR and tired to replace with other controls from the table

 

[Minty]

Sorry I meant to use ORG. I'll look further when not sitting on the couch!