combo box with a general list and highlight those from recordset (1 Viewer)

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
I managed to pop a combo box with all qualifications a tutor may have by a recordset. I also need to pop that combo box with the qualifications a particular tutor has.

I have no idea on this structure. I would appreciate if you have any idea.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
Hi. By "pop," are you saying you "populated" the combobox? Are you trying to populate a second combobox based on a selection from the first? If so, it's called a "cascading combobox." There are a few examples on how to do it. Try searching on the term.
 

Micron

AWF VIP
Local time
Today, 03:44
Joined
Oct 20, 2018
Messages
3,476
a combo box with all qualifications a tutor may have
need to pop that combo box with the qualifications a particular tutor has
Can't see the difference between these 2 requirements when they apply to the same control. If you want more than one set of attributes (fields) for a given entity then make sure those fields are included in the combo row source sql or query and that you have enough combo columns visible to show them.
 

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
Hi. By "pop," are you saying you "populated" the combobox? Are you trying to populate a second combobox based on a selection from the first? If so, it's called a "cascading combobox." There are a few examples on how to do it. Try searching on the term.

Only one combo box is used. A recordset of all qualifications populates the combo box.

I need to highlight/mark the items in that combo box with another recordset of tutor
with his/her qualifications.
 

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
Can't see the difference between these 2 requirements when they apply to the same control. If you want more than one set of attributes (fields) for a given entity then make sure those fields are included in the combo row source sql or query and that you have enough combo columns visible to show them.

I'm sorry that my question is not unclear. The details are listed below

I have two tables;
tblPQual has record(s) of tutors' post qualifications
tblPQual (ID, AutoNumber; TRef, unique key of tblTutor; S_Code, Code of the Post Qualification and is foreigner key of tblSpecialty)
e.g.
TRef, S_Code
...
671, 3
671, 6
...

tblSpecialty has all post qualifications I maintain
tblSpecialty (ID, AutoNumber; S_Code, Unique specialty code; S_Abbr, Abbreciation of Specialty; S_Desc, Specialty Description; S_Type, 1..4)
e.g.
S_Code, S_Abbr, S_Desc, S_Type
...
3, DABFP, Diplomate of the American Board of Family Practice, 1
6, FRACGP, Fellow of Royal Australian College of General Practitioners, 1
...


I populate a combo box with
1) recordset of tblSpecialty and then
2) need to highlight/mark the item(s) in the same combo box which a tutor has in the table of tblPQual.

I manage to do item 1) above but no idea of how to work out item 2).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
I populate a combo box with
1) recordset of tblSpecialty and then
2) need to highlight/mark the item(s) in the same combo box which a tutor has in the table of tblPQual.

I manage to do item 1) above but no idea of how to work out item 2).
Hi. Unfortunately, Combobox rows cannot be highlighted (at least, I don't think so). On the other hand, Listbox rows can be highlighted (selected). Perhaps you could try using a Listbox?
 

isladogs

MVP / VIP
Local time
Today, 07:44
Joined
Jan 14, 2017
Messages
18,186
Listbox would be my preference as well but you would FILTER the list to those items relevant to the selection made in the first combo

Alternatively use the cascading combo idea already mentioned....but again you would filter the second combo to the selection made in the first combo
 

Micron

AWF VIP
Local time
Today, 03:44
Joined
Oct 20, 2018
Messages
3,476
I read it as more than one list item would be selected via code. AFAIK, code can drop down and select an item, but to do more than one (assuming that's even possible) would require it to be bound to a multi value field. If the field isn't multi value, forget it. If it is, forget it.
Go with a listbox and forget the combo - unless maybe you need it to populate the listbox in the first place. Regardless, forget about trying to select combo list items via code.
 

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
I changed to use listbox for populating a recordset. For example, there're 3 items highlighted in black. When I deselect one item in the populated listbox, the background color doesn't disappear, how can I turn the background color to white to indicate to the user that he/she has deselected it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
Try holding down the Ctrl key when you click on the item.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
Did you set Multiselect to Extended?
 

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
Did you set Multiselect to Extended?

Thank you the suggestion.

However, I set it as
Forms("frmTutor").Controls("lstPQual").MultiSelect = 2 ' Extended. It runs into error of Run-time error '2448' You can't assign a value to this object.


I read the nets and found and got the information that this is MS ACCESS' bug. Pls. refer the following for the details.

https://www.access-programmers.co.uk/forums/showthread.php?t=205384

How can I make this work with VBA or else.

Best.
 

isladogs

MVP / VIP
Local time
Today, 07:44
Joined
Jan 14, 2017
Messages
18,186
I don't believe that was what DBG was suggesting.
If you use Multiselect...Simple it will behave as you want.

To make that change, open the form in design view, select the listbox and go to the property sheet. The multiselect options are on the Other tab

You can't change the property whilst open in form view
 

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
Thank you, theDBguy. It works with the properties setting for Multiselect.

However, when I coded it as "Forms("frmTutor").Controls("lstPQual").MultiSelect = 1", it ran into the error code of 2448 again. How come?
 

isladogs

MVP / VIP
Local time
Today, 07:44
Joined
Jan 14, 2017
Messages
18,186
Not sure whether you were responding to me.
I've already explained why you're getting the error.
You can only set listbox multiselect type property in design view
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
Hi. Why would you want to change this property using code? I’m not in front of a computer now but check the help file to see if this property is read only or not.
 

NT100

Registered User.
Local time
Today, 15:44
Joined
Jul 29, 2017
Messages
148
Hi. Why would you want to change this property using code? I’m not in front of a computer now but check the help file to see if this property is read only or not.

Thank you for your concern. I want to put all in scripts to have a better management and maintenance instead of looking each control and inspect its properties. This could be a tedious work and prone to error.

Best.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:44
Joined
Oct 29, 2018
Messages
21,358
So, what did the help file say? Did it say you’re allowed to change the property using code? Sent from phone...
 

Users who are viewing this thread

Top Bottom