Help with combobox and list box link (1 Viewer)

Makivic

New member
Local time
Today, 09:03
Joined
May 21, 2019
Messages
5
Hello,

I am quite new to Access, and I`m using 2010 version. I have a school project where I need to create a link between a combobox and a list box. I have to create a form in which I have a combobox and a list, then i need to select a record from a combobox and then all details specified to that record to show in list box.
Example. I have a table with ID, Name, Last Name, Position, Date of birth.
Combobox should have only Name and Last name, and then in list box i have to display the rest of data from table.
I have been looking all over the Web, but just cant figure this one out. In Excel i can manage it, but with Access I am having problem. I cant event start the code.
If anyone could give the simplest explanation how to do this i would be most gratefull.


Thanks
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:03
Joined
Feb 19, 2013
Messages
16,553
Not sure why you need a listbox for this task since you are only going to return 1 row of data but you need something like this.

Assuming your combo is called Combo1, your listbox is called List1, your table is called myTable and you want to display in the listbox lastname, position and date of birth.

Combo1 properties
rowsource - SELECT ID, Name, LastName FROM myTable
columns 3
bound column 1
column widths 0

List1 properties
rowsource - SELECT LastName, Position, DOB FROM myTable WHERE ID=[Combo1]
columns 3

then in your Combo1 after update event put

List1.Requery

edit: note that Name is a reserved word and it is not a good idea to have spaces or non alphanumeric characters in table, field and controlnames - Access is not excel and doing so will cause unexpected issues down the line. So develop good habits from the get go
 

Makivic

New member
Local time
Today, 09:03
Joined
May 21, 2019
Messages
5
It works, thanks.
I just needed something to start with.
Now I know how to link combo to list.
I have to create a two tables, one that has id from input, name, and last name. And other that will have same id but more different data, so that I can select id from combobox and then in the list will be displayed data from table2 that has the same id.


Its like a table1 has students with id
and table2 has id, class, mark.
Table2 will hold all info about all students.
And list1 will display all info about specific student ID.
Something like Excel Vlookup, where it will show all data that has the same ID.
What i`m making is some sort of filter, but instead of subframe, its in a list
 

Makivic

New member
Local time
Today, 09:03
Joined
May 21, 2019
Messages
5
By the way, is it possible to have two tables, one to hold studend info and other table to hold some other info, and then to fill combobox with data from table 1 and use tha as a criteria to search table two and then display that data in list?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:03
Joined
Feb 19, 2013
Messages
16,553
yes - same principle - just change the SELECT and FROM parts of the list1 rowsource

what you are talking about is what is known as 'cascading combo's' and is a common requirement. Google 'cascading combo's' and you will find plenty of links on the subject. You might also want to consider learning about subforms which would meet your requirement without any code. You can also google 'search forms'

BTW VLookup will only return a) the first instance of a value found and b) only a single value, not multiple values.

Do not make the mistake of thinking of access as being a larger excel. It isn't, it is a completely different animal which works in a completely different way - very little of what you do in Excel will work in Access - and visa versa
 

Makivic

New member
Local time
Today, 09:03
Joined
May 21, 2019
Messages
5
Thank you very much. I will look into it. I will post a reply when i make a sample database, to see if I got it right. ☺
 

Makivic

New member
Local time
Today, 09:03
Joined
May 21, 2019
Messages
5
Okay, I think i got it. Here is the example of what was I needed to achieve.
If you have any advice on how to improve it or something I did wrong, please tell.
 

Attachments

  • TestStudent.accdb
    492 KB · Views: 33

CJ_London

Super Moderator
Staff member
Local time
Today, 08:03
Joined
Feb 19, 2013
Messages
16,553
well - it works! general points

1. give your field names meaningful names - ID? ID of what? There is much debate about the suffix - my preference is to use PK and FK (primary key and foreign key) i.e. StudentPK and StudentFK (to replace the StudentID field in tblProgress which is then not required) - you'll need to change your rowsources in the form

2. create table relationships on PK and FK - in your query you have done it on the studentID field - if you were to change the value in one table, your query will cease to work

3. tblProgress is not normalised - however since you don't have anything about subject, it can revised later if required

4. with regards the form you don't need to populate the student name fields with vba code - just put in the controlsource

=Select.column(1) --- or (2) for last name

5. you also don't need to enable/disable the controls to update the controls via VBA

6. you don't need qryTrack you could just use the table - see next point. Also if you want to display no results if a student doesn't have a matching record in tblProgress, your join is the wrong way round - but in this example could just be an inner join.

7. you also don't need to restate the listbox rowsource if you put

SELECT Mark1, Mark2, Mark3 FROM tblProgress WHERE StudentID = [SELECT]

in the rowsource (rather than using VBA), then your select afterupdate just needs the requery line.

8. Alternatively if you are going to populate the rowsource with vba code as you are at the moment, you don't need the requery

9. Finally, 'SELECT' is a reserved word, using reserved words can produce misleading error messages - see this link

https://support.office.com/en-us/article/learn-about-access-reserved-words-and-symbols-ae9d9ada-3255-4b12-91a9-f855bdd9c5a2

10. Back to table design - your studentID field in tblStudent is indexed (duplicates OK) - just think about the implications of allowing two records with the same StudentID

As I said, it works but do check out my pointers - they will benefit you in the long term.
 

Users who are viewing this thread

Top Bottom