Solved Help on Select code on combobox

[MajP]

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]

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.

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]

Looking at your data
tbl_Org tbl_Org

ORG	BankId	BankName	CBKACName	CBKAC	CBKACName_cbk	CBKAC_cbk
ABC	001	ABC Bank	ABC_cbAC	1234567890	PQR_cbAC	3712345678
ABC	002	PQR Bank	ABC_cbAC	1234567890	NA	NA
ABC	003	XYZ Bank	ABC_cbAC	1234567890	NA	NA
ABC	004	NA	NA	NA	NA	NA
PQR	001	ABC Bank	PQR_cbAC	3712345678	NA	NA
PQR	002	PQR Bank	PQR_cbAC	3712345678	ABC_cbAC	1234567890
PQR	003	XYZ Bank	PQR_cbAC	3712345678	NA	NA
PQR	004	NA	NA	NA	NA	NA

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]

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]

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

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]

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]

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]

Thanks gemma-the-husky for your valuable feedback. Will certainly look at it at later stage.

 

[MajP]

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.

 

[lookforsmt]

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]

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]

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]

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.

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]

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.