Combo Box displaying wrong stored value (1 Viewer)

Gigitty

Member
Local time
Today, 12:46
Joined
Mar 29, 2007
Messages
52
Hi guys. Had a look but can't find anything in the forums on this issue. I have a combo box referencing a table via the query builder. There are 7 columns in the lookup table and I want to store the value from column 3. I have set the 'Bound Column' to column 3. When viewing the data in the raw table the correct value is stored however viewing the data on the form in which is was entered displays the data from column 1. I've set up Combo Boxes like this in the past and never had this issue. Any help or comments greatly appreciated.

Gigitty.
 

bob fitz

AWF VIP
Local time
Today, 19:46
Joined
May 23, 2011
Messages
4,717
The column count is zero-based, so the third column would e column(2)
Perhaps you just need to set the widths of columns that you don't need to see to 0cm
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
I normally use a query for my combos, and always have the first field, the ID and hidden?
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
The column count is zero-based, so the third column would e column(2)
Perhaps you just need to set the widths of columns that you don't need to see to 0cm
I'd have to disagree there Bob
The bound column starts at 1?

As you have mentioned. O/P has not hidden first two fields?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:46
Joined
Jul 9, 2003
Messages
16,245
Maybe this will help:-

 

bob fitz

AWF VIP
Local time
Today, 19:46
Joined
May 23, 2011
Messages
4,717
I'd have to disagree there Bob
The bound column starts at 1?
For the sake of clarity, I was referring to the "Column" property that one might use like this:
=Combo1.Column(2)
to return the third column from Combo1.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
For the sake of clarity, I was referring to the "Column" property that one might use like this:
=Combo1.Column(2)
to return the third column from Combo1.
Ah OK, sorry.
 

Gigitty

Member
Local time
Today, 12:46
Joined
Mar 29, 2007
Messages
52
The column count is zero-based, so the third column would e column(2)
Perhaps you just need to set the widths of columns that you don't need to see to 0cm
Hi Bob. Thanks for your response. What's weird is that changing the Bound Column does change what gets stored in the table, but not what is displayed in the Combo Box in the Form. I've attached the database so you can see what's happening. It only contains a couple of records with test data so feel free to add or delete as you wish.
 

Attachments

  • FSH Cath Lab Procedure Billing.accdb
    896 KB · Views: 298

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:46
Joined
May 21, 2018
Messages
8,463
What's weird is that changing the Bound Column does change what gets stored in the table, but not what is displayed in the Combo Box in the Form. I've attached the database so you can see what's happening. It only contains a couple of records with test data so feel free to add or delete as you wish.
Not sure what you are expecting to happen, but that is not how it works. The first visible column will be displayed and not the bound column. You normally would hide the ID and the Category (0 width) so that you code is the first visible. Not sure of the issue since you did it correct on the other ones. What is different about the first combo.
 

Gigitty

Member
Local time
Today, 12:46
Joined
Mar 29, 2007
Messages
52
Not sure what you are expecting to happen, but that is not how it works. The first visible column will be displayed and not the bound column. You normally would hide the ID and the Category (0 width) so that you code is the first visible. Not sure of the issue since you did it correct on the other ones. What is different about the first combo.
Hi Maj.

I added the Category to the lookup table after I had created the form. Then I went back to edit the Combo Boxes. When it wasn't behaving as I expected I re-created the first Combo Box which is why it's different to the others. Trying to get it to behave properly before I updated the remaining Combo Boxes. I need the Category column to be visible when the Combo Box expands to view as i'm initially sorting the list by the Category then by the Procedure.
 

Gigitty

Member
Local time
Today, 12:46
Joined
Mar 29, 2007
Messages
52
Created a work around to overcome the issue. Thanks to everyone for their responses.
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
Hi Bob. Thanks for your response. What's weird is that changing the Bound Column does change what gets stored in the table, but not what is displayed in the Combo Box in the Form. I've attached the database so you can see what's happening. It only contains a couple of records with test data so feel free to add or delete as you wish.
That is how it works? The Bound column is the value that gets stored.?
You are showing ALL the fields in the combo?
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:46
Joined
Sep 21, 2011
Messages
14,046
Created a work around to overcome the issue. Thanks to everyone for their responses.
There was no 'issue', you just had the incorrect setup for the combo?
In your patient table you should be storing the ID of the relevant procedures?

I won't even try to get you to change the structure, which does not appear normalised.? What happens if you need a 5th code for your procs?

1623398367394.png


Good luck with the project.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:46
Joined
May 21, 2018
Messages
8,463
I won't even try to get you to change the structure, which does not appear normalised.?
I will, however, suggest you change the structure. You are wasting you time if not. This is about as bad of a table design that I have seen. It is like de-normalization on top of denormalization. This will make date entry, form design, data integrity, and reporting near impossible. Keep it if you want, but I would not ask for much help if you decide to build on this. Most people here will shy away to try to put a band-aid on a sucking chest wound. I would put the effort in to fixing it now before wasting your time and others. Trust me you will end up will super complicated Rube Goldberg solutions to performing trivial tasks.
patient.jpg

e
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 19:46
Joined
Jul 9, 2003
Messages
16,245
Most people here will shy away to try to put a band-aid on a sucking chest

Love that terminology!

Some people may help you because the challenge is good fun, but it doesn't make it right!

I refer you to a post I made in this thread:-



where I demonstrated my Transpose Tool, to an AWF member in a similar situation. There are other examples on my website. If you would like a free copy of my transpose tool, contact me for details in a pm private message and I will explain how you can get a free copy...
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:46
Joined
May 21, 2018
Messages
8,463
I went ahead an normalized this for you. Should make data management far easier.
TblPatients TblPatients

PatientIDUMRNFirst NameSurnameStreetAddressPostCodeID_FK
1​
A1234566ElizabethMontgomery1 East Willoughby Pde
590​
3​
C7894561JosephElliott55 Cantrell Circuit
11​
tblDrDetails tblDrDetails

Dr_IDDr_Name
1​
Dr Alan Whelan
2​
Dr Jamie Rankin
3​
Dr Matthew Erickson
4​
Dr Thato Mabote
5​
Dr Anthony Putrino
6​
Dr Richard Clugston
7​
Dr Gerald Yong
8​
Dr Sharad Shetty
9​
Dr Abdul-Rahman Ihdayhid
10​
Dr Chris Judkins
11​
Dr Michael Nguyen
tblProcedure tblProcedure

tblProcedureCodes tblProcedureCodes


ProcedureIDProcedureCategoryProcedure
1​
PCIPOBA: One Vessel
2​
PCIPOBA: Two or more Vessels
6​
PCIStent - State Lesion 1
7​
PCIStent – State Lesion 2
8​
PCIStent – State Lesion 3
9​
PCIStent – State Lesion 4
ProcedureCodeIDProcedureID_FKProcedureCodeCodePriority
72​
1​
38300
1​
73​
2​
38303
1​
74​
6​
38306
1​
75​
7​
38306
1​
76​
8​
38306
1​
.......
tblPatient_Procedures tblPatient_Procedures


PatientProcedure_IDPatientID_FKProcedureID_FKProcedureDateConsultantID
1​
1​
32​
6/1/2021​
1​
2​
1​
2​
6/15/2021​
2​
3​
1​
55​
6/14/2021​
3​
4​
1​
37​
6/13/2021​
4​
5​
1​
41​
6/3/2021​
5​
6​
1​
6​
6/11/2021​
6​
7​
3​
7​
6/2/2021​
7​
8​
3​
8​
6/2/2021​
8​
9​
1​
23​
6/15/2021​
8​
10​
3​
20​
6/21/2021​
7​
11​
14​
6/8/2021​
4​
12​
3​
63​
6/22/2021​
0​
13​
1​
51​
6/8/2021​
7​


The above table is the key. This is a Many to Many. 1 patient can have many procedures, and many patients can have the same procedure.
I added one piece of code to concatenate the codes

frmProcedureSubForm frmProcedureSubForm

ProcedureProcedure DateConsultantCodes
Cath: before planned PCI (incl LV and/or: RHC aortogram6/8/2021Dr Thato Mabote38246
New atrial lead or New RV lead6/1/2021Dr Alan Whelan38350
Pulmonary angiogram6/3/2021Dr Anthony Putrino60927
Stent - State Lesion 16/11/2021Dr Richard Clugston38306
Pericardiocentesis6/13/2021Dr Thato Mabote38359
Venogram6/14/2021Dr Matthew Erickson35200
CRT-D no atrial lead6/15/2021Dr Sharad Shetty38390, 38365, 38393
POBA: Two or more Vessels6/15/2021Dr Jamie Rankin38303
Stent – State Lesion 36/2/2021Dr Sharad Shetty38306
Stent – State Lesion 26/2/2021Dr Gerald Yong38306
Coronary angiogram: with SVG and LIMA6/21/2021Dr Gerald Yong38231
1-3 wire study6/22/202138209
 

Attachments

  • MajP_FSH_CathLabBilling.accdb
    3.5 MB · Views: 286
Last edited:

Users who are viewing this thread

Top Bottom