Solved Help on Select code on combobox (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:54
Joined
May 21, 2018
Messages
8,463
Sorry I did not read the enitre thread to see what it was supposed to do. I just started looking for that field. However what you wrote is not the same as what Minty posted even if you change to ORG. Look at what he wrote for the ORG = ... He references the ORG combobox and you wrote bankID
 

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
Thank Minty & MajP for the clarification, I replaced the word to ORG and the ORG combobox,
i put below sample of what i have done the change. so far, but i am still getting the same error.


Code:
Private Sub BankId_AfterUpdate()
    Me!BankName = Me.BankId.Column(1)

Select Case BankId
     Case "001"
  Me.BankId = "001"
     Me.CBKACName = DLookup("ABC_cbAC", "tbl_ORG", "[BankID] = '" & Me.BankId & "' AND ORG = '" & Me.ORG & "'")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:54
Joined
May 21, 2018
Messages
8,463
Looking at your data
tbl_Org tbl_Org

ORGBankIdBankNameCBKACNameCBKACCBKACName_cbkCBKAC_cbk
ABC001ABC BankABC_cbAC1234567890PQR_cbAC3712345678
ABC002PQR BankABC_cbAC1234567890NANA
ABC003XYZ BankABC_cbAC1234567890NANA
ABC004NANANANANA
PQR001ABC BankPQR_cbAC3712345678NANA
PQR002PQR BankPQR_cbAC3712345678ABC_cbAC1234567890
PQR003XYZ BankPQR_cbAC3712345678NANA
PQR004NANANANANA
I see Org and BankID as unique. However this could just be an example and not representative. If it is unique then this whole thing can be simplified.
If I pick ABC I can pick 001-003, and all the other fields are defined by that combination. If that rule is true then you simply display the other fieds

I do not know if that is true and could be just this example.
Could you have multiple records of ABC 001 with different bank names, CBKACName, etc.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:54
Joined
May 21, 2018
Messages
8,463
If the above is correct, you can basically get rid of all of the code and get rid of most of the fields in the incident log. You only store ORG and bank ID in the incident log.
However, I would have to imagine in the real world all banks have a unique ID. At least here in the US they do. So you do not need Org stored either. Do you not have unique bank IDs.
 

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
Thanks MajP, i am trying to understand your logic and yes the above you analyzed is correct.
I am just trying to clarify or better understanding, there won't be any futher additions. Hope this is clear

Code:
2 ORG:       1) ABC       2) PQR (This can increase in future)
4 BankId:    a) 001       b) 002      c) 003   d) 004
2 CBKACName: i) ABC_cbAC ii) PQR_cbAC
 

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
HI@ MajP, There are close to 99 banks so far, the logic is same for 97 banks which is ABC 003 or PQR 003,
For the remaining 2 banks: ABC 001-002 and PQR 001-002 as mentioned in row 1&2 and 5&6
ABC 004 & PQR 004 is not applicable so mentioned NA as there is no bank involved.

Hope if i am able to clarify this.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Sep 12, 2006
Messages
15,614
It's possibly a bit late now, but I would have the bank ID as an autonumber I think, and not repeating.

In the UK bank branches have a unique code that looks like mm-nn-xx

The first two designate the bank, but this does restrict the coding to 99 banks, so whether some smaller banks share the mm designation, I am not sure. Anyway, I would be inclined to store an autonumber, the bank name (probably a lookup id to a bank name table), the branch sort code, and a location/address. I don't know whether this would work if you need non-UK banks, but I can't imagine it's a lot different.
 

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
Thanks gemma-the-husky for your valuable feedback. Will certainly look at it at later stage.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:54
Joined
May 21, 2018
Messages
8,463
It was easier for me to demo a working solution than explain all the fixes. This is how you would design your tables. For simplicity I separated data entry from data viewing.
 

Attachments

  • combobox_MajP.mdb
    724 KB · Views: 109

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
Thank you MajP,
Amazing, solution. Honestly this solution has opened new doors where i can replicate this in my other projects

Thank you once again.

Thanks to Minty for the dlookup option and your valuable time.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:54
Joined
May 21, 2018
Messages
8,463
I personally seperate a lot of things in my databases. I usually have a list view like the first form to see a long list of items. This usually has "Outlook" like features to sort and filter on any columns. Then like Outlook you click on an item to see the details or add new.

I see people try to have one form do everything with multiple subforms. It is harder on the user and developer.
 

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
Thanks MajP, got the concept. I fear that creating separate tables will fail my project at the end due to lack of knowledge in queries and vba. But yes i have read that creating separate data will help in the long run

Thanks again for the knowledge, it was great.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:54
Joined
May 21, 2018
Messages
8,463
I fear that creating separate tables will fail my project at the end due to lack of knowledge
No, not doing it will make it fail. Repeating data in tables will add work, be inefficient, difficult to use, and ensure lack of data integrity. You cannot build a "spreadsheet" in a database. Just look at the amount of code in my form to try to do what you were trying. It does that and more.

Code:
Private Sub cmboBank_Enter()
  cmboBank.Requery
End Sub

Private Sub Form_Close()
  DoCmd.OpenForm "frmLog"
End Sub

Little simpler? The point is "band aid" like you had add a ton of work that makes it harder and harder. It is like building a house. If the frame is sound, plum, and square everything else is a lot easier.
 

lookforsmt

Registered User.
Local time
Today, 11:54
Joined
Dec 26, 2011
Messages
672
Very True, thanks MajP once again, will make a note of that and change some of the tables which i am working.
I will close this thread as Solved.
 

Users who are viewing this thread

Top Bottom