Solved Item not in the collection of Combo Box but shown as default value (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
I believe there should be something wrong with my Access settings because the property settings theDBguy suggested works on his computer but it is not working in my computer. Is there anything you can suggest to solve this issue or at least track it?
Sorry, probably nothing wrong, just a bad explanation on my part. It only says "Select One" if you have not selected anything, else it shows the choice. I should have told you to look at record 5 and a new record.
 

zeroaccess

Active member
Local time
Today, 07:37
Joined
Jan 30, 2020
Messages
671
IMO, most combos are used for filtering or record navigation and shouldn't be bound anyway. Some people who do bind them wonder why field entries change when somebody plays with the combo.
Most of mine are bound, some of which are used for data entry and some of which are used for their lookup capability - disabled and locked. If you're doing data entry and need users to select from a limited list of choices, and can't afford the screen real estate for list boxes, well...there is only one game in town.
 

silversun

Registered User.
Local time
Today, 05:37
Joined
Dec 28, 2012
Messages
204
Hi. The only thing I could offer right now is, if you could post a demo version of your db, we can take a look at it. Otherwise, I'll be willing to do a web meeting with you.
I have a copy of my DB attached here. Please start with form frm_everything and enter any date and time then you will see the combo boxes.
Thank you in advance.
 

Attachments

  • copy for world access.zip
    89.7 KB · Views: 115

silversun

Registered User.
Local time
Today, 05:37
Joined
Dec 28, 2012
Messages
204
Sorry, probably nothing wrong, just a bad explanation on my part. It only says "Select One" if you have not selected anything, else it shows the choice. I should have told you to look at record 5 and a new record.
I understood. Before I select anything it shows empty combo box and that's where I want to see "Select One" on my combo box. A copy of DB is attached and if you have time please look at it.
Thank you in advance
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
The issue with the format property as far as I can tell it it only works if the Visible column and the Bound column are the same. It is not the difference between a value list and a query. I modified your combo that used a rowsource type of query and it worked. However, I am not sure if that helps if you need a bound control to a control source.
 

statsman

Active member
Local time
Today, 08:37
Joined
Aug 22, 2004
Messages
2,088
How about replacing the text in the current label to Select One?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
Since this is all unbound, you do not have to worry about the value being saved. This can be done real easy. Just make sure the bound column is the visible column. In your code to get the correct ID then you just need to use the column property of the combo. "Rs!risk_id = Me.cmb_infected2" to "Rs!risk_id = Me.cmb_infected2.column(0)". I just added a Tag property to each combo. I changed each bound column and format so that the bound and visible are the same
Code:
Private Sub Form_Load()
cleanForm
Dim ctrl As Access.Control
For Each ctrl In Me.Controls
  If ctrl.Tag <> "" Then
    Debug.Print ctrl.Tag
    ctrl.Value = ctrl.Tag
  End If
Next ctrl
End Sub

Bottom line if the bound column is numeric you cannot apply a value or a format that is a string even if the bound column is not visible. That was your whole issue.
 

Attachments

  • MajP_ for world access.zip
    154.3 KB · Views: 113

silversun

Registered User.
Local time
Today, 05:37
Joined
Dec 28, 2012
Messages
204
Since this is all unbound, you do not have to worry about the value being saved. This can be done real easy. Just make sure the bound column is the visible column. In your code to get the correct ID then you just need to use the column property of the combo. "Rs!risk_id = Me.cmb_infected2" to "Rs!risk_id = Me.cmb_infected2.column(0)". I just added a Tag property to each combo. I changed each bound column and format so that the bound and visible are the same
Code:
Private Sub Form_Load()
cleanForm
Dim ctrl As Access.Control
For Each ctrl In Me.Controls
  If ctrl.Tag <> "" Then
    Debug.Print ctrl.Tag
    ctrl.Value = ctrl.Tag
  End If
Next ctrl
End Sub

Bottom line if the bound column is numeric you cannot apply a value or a format that is a string even if the bound column is not visible. That was your whole issue.
Hi. Thank you. Your code and modification generates "Run-time error 13". Is there anything I need to do in my side?
1586874720950.png

1586874672062.png

MajP;
Which field in my table? This is not a field in my table but it is a variable used in another function called riskRate.
I am going to work and will try more when I am back.
Thanks again
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
The issue with the format property as far as I can tell it it only works if the Visible column and the Bound column are the same. It is not the difference between a value list and a query. I modified your combo that used a rowsource type of query and it worked. However, I am not sure if that helps if you need a bound control to a control source.
@MajP Nice detective work! All I had to do to get around it was set the first column width to 0.0007" Cheers!

So, from this:



to this:
combonoid.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:37
Joined
May 21, 2018
Messages
8,463
MajP;
Which field in my table? This is not a field in my table but it is a variable used in another function called riskRate.
I am going to work and will try more when I am back.
Thanks again
the column property is zero indexed. My guess is you want the first column which is 0
 

silversun

Registered User.
Local time
Today, 05:37
Joined
Dec 28, 2012
Messages
204
the column property is zero indexed. My guess is you want the first column which is 0
Hi,
I am using two columns of my combo box here. I use column (1) for a variable in riskRate function and column(0) to be in my Recordset and will be written in main table.
 

silversun

Registered User.
Local time
Today, 05:37
Joined
Dec 28, 2012
Messages
204
I am still waiting for help to arrive. Is there anyone who can help me about this?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:37
Joined
Oct 29, 2018
Messages
21,357
Yes I did. I fixed it already. Took me more time and I had to go to work but I finally finished it this morning.
Thank you for responding.
Hi. Glad to hear you got it sorted out. Just to clarify, did you mean you ended up fixing the code you were using? Or, did you end up using the "no code" approach?
 

silversun

Registered User.
Local time
Today, 05:37
Joined
Dec 28, 2012
Messages
204
Hi. Glad to hear you got it sorted out. Just to clarify, did you mean you ended up fixing the code you were using? Or, did you end up using the "no code" approach?
I actually didn't use any code. Since I had "--Select One--" as one of my items in underlying table it was displaying it in the middle of the list. I added a space at the beginning of the word " --Select One--" so that brought it to the top of the list when items were sorted ascending.
This is exactly what I wanted although it doesn't look like professional but it works.
I did test it in all different ways and always displayed the list properly.
 

Users who are viewing this thread

Top Bottom