Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-21-2019, 02:23 AM   #1
Makivic
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Makivic is on a distinguished road
Post Help with combobox and list box link

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

Makivic is offline   Reply With Quote
Old 05-21-2019, 02:51 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,761
Thanks: 40
Thanked 3,488 Times in 3,375 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Help with combobox and list box link

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Makivic (05-22-2019)
Old 05-21-2019, 03:50 AM   #3
Makivic
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Makivic is on a distinguished road
Re: Help with combobox and list box link

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 is offline   Reply With Quote
Old 05-21-2019, 04:21 AM   #4
Makivic
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Makivic is on a distinguished road
Re: Help with combobox and list box link

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?
Makivic is offline   Reply With Quote
Old 05-21-2019, 05:10 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,761
Thanks: 40
Thanked 3,488 Times in 3,375 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Help with combobox and list box link

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 05-21-2019, 09:32 AM   #6
Makivic
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Makivic is on a distinguished road
Thumbs up Re: Help with combobox and list box link

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 is offline   Reply With Quote
Old 05-22-2019, 03:36 AM   #7
Makivic
Newly Registered User
 
Join Date: May 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
Makivic is on a distinguished road
Re: Help with combobox and list box link

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.
Attached Files
File Type: accdb TestStudent.accdb (492.0 KB, 6 views)

Makivic is offline   Reply With Quote
Old 05-22-2019, 05:54 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,761
Thanks: 40
Thanked 3,488 Times in 3,375 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Help with combobox and list box link

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.

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Tags
combo box , form , list box , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
2 combobox link each other.. please help cikwan82 Forms 4 12-17-2013 05:19 PM
Link combobox and textbox andy_25 Forms 1 11-06-2009 03:58 AM
Link between Combobox Agamemnon Forms 1 09-18-2003 12:40 AM
Need my combobox to accept new data (not in list) and update the list. ??? SomeGuy Forms 2 11-16-2000 06:59 PM




All times are GMT -8. The time now is 08:49 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World