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.
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.
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.
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
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.
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.
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?
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 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;
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
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.
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?
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.