473,320 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

need help with cascading combo/list boxes

18
Hey i need help with setting up this on ACCESS 2007
i need step by step

i need a form...
i select a name (drop list)...then gives me more choice (in next drop list) ....then when i select that choice give another list (drop list)..

so for example
i select canada, from list of contries, then i would get list of the provinces and then i choose my province and then i get list of cities

if someone let me know step by step how to do that i would appericiate it.
Aug 15 '07 #1
26 4776
Rabbit
12,516 Expert Mod 8TB
You have posted in the wrong forum, I am moving this post the the appropriate forum.

Please use a clearer title next time. I will change it for you this time.
Aug 15 '07 #2
Rabbit
12,516 Expert Mod 8TB
Take a look at the Cascading Combo/List Box Tutorial.
Aug 15 '07 #3
pouj
18
thank you so much you saved my life!!!!! also i didnt know it was called cascading combo box.
Aug 15 '07 #4
Rabbit
12,516 Expert Mod 8TB
thank you so much you saved my life!!!!! also i didnt know it was called cascading combo box.
You don't have to know what it's called but the title should be descriptive of the question.
Aug 15 '07 #5
pouj
18
okay so i read over cascading combo/list boxes tutorial and my projct doesnt work.

i will tell you exactly what i have done...

my first table called tblcontractor..
has 2 field
ID: Automatic
ContractorIDNumber: Number (which gets its data from another table)

my next table called tblcontractorproject
has 3 fields
ID: autonumber
Contractorprojectname: text
contractoridnumber: Number (which gets i data from same table that tblcontracot contractoridnumber gets it data)

then i create a form and then form i drop the the tables in there..and i went to properties and i went to afterupdate and i put in [Event Procedure] and i went to VB editor and i type this in there...
Expand|Select|Wrap|Line Numbers
  1. Private Sub ContractorIDNumber_AfterUpdate()
  2. With Me![ContractorIDNumber]
  3. If IsNull(Me!ContractorIDNumber) Then
  4. .RowSource = ""
  5. Else
  6. .RowSource = "SELECT [Contractorprojectname] " & _
  7. "FROM Tblcontractproject " & _
  8. "WHERE [ID]=" & Me!ContractorIDNumber
  9. End If
  10. Call .Requery
  11. End With
  12. End Sub
  13.  
also keep in mind that i have no data in the tables yet. so if you tell me what i am doing wrong here, i would apperciate it.
Aug 15 '07 #6
Rabbit
12,516 Expert Mod 8TB
okay so i read over cascading combo/list boxes tutorial and my projct doesnt work.

i will tell you exactly what i have done...

my first table called tblcontractor..
has 2 field
ID: Automatic
ContractorIDNumber: Number (which gets its data from another table)

my next table called tblcontractorproject
has 3 fields
ID: autonumber
Contractorprojectname: text
contractoridnumber: Number (which gets i data from same table that tblcontracot contractoridnumber gets it data)

then i create a form and then form i drop the the tables in there..and i went to properties and i went to afterupdate and i put in [Event Procedure] and i went to VB editor and i type this in there...

Private Sub ContractorIDNumber_AfterUpdate()
With Me![ContractorIDNumber]
If IsNull(Me!ContractorIDNumber) Then
.RowSource = ""
Else
.RowSource = "SELECT [Contractorprojectname] " & _
"FROM Tblcontractproject " & _
"WHERE [ID]=" & Me!ContractorIDNumber
End If
Call .Requery
End With
End Sub

also keep in mind that i have no data in the tables yet. so if you tell me what i am doing wrong here, i would apperciate it.
When you're doing a cascading combo box you use more than 1 combo box. You're only using one combo box here. Read the tutorial more closely.

You're also attempting to return records based what looks to be a primary key when you should be using a foreign key.
Aug 15 '07 #7
pouj
18
When you're doing a cascading combo box you use more than 1 combo box. You're only using one combo box here. Read the tutorial more closely.

You're also attempting to return records based what looks to be a primary key when you should be using a foreign key.
how do i use foreign key....and i am using 2 combo box.
Aug 15 '07 #8
Rabbit
12,516 Expert Mod 8TB
how do i use foreign key....and i am using 2 combo box.
But your code is only using one combo box.

You use a foreign key by using the foreign key field's name instead of the primary key field's name.
Aug 15 '07 #9
pouj
18
okay...so now my code looks like this....
Expand|Select|Wrap|Line Numbers
  1. Private Sub cpyname_AfterUpdate()
  2.     With Me![Employee]
  3.         If IsNull(Me!company) Then
  4.             .RowSource = ""
  5.         Else
  6.             .RowSource = "select [empname] " & _
  7.             "from tblemployees " & _
  8.             "where [id] =" & Me!company
  9.         End If
  10.         Call .Requery
  11.         End With
  12. End Sub
  13.  
but i am getting debug on With Me![Employee]...so what i am suppose to do there...and also...like i tried to set up FK but it doesnt work (thats what i am assuming) because in relationship field i just get a line from ID to EID..and can you refresh my head with the steps on how to set up FK keys? or i am doing it right...
Aug 15 '07 #10
Rabbit
12,516 Expert Mod 8TB
You're going have to give me the relevant information.

What tables are involved.
What fields are in those tables.
What are the primary keys.
How and on what fields are the tables related.

What are the relevant controls on your form.
What are the properties of those controls, i.e. control source, row source, column widths, columns, bound column, name.

The tutorial provides all of these and how to set up the example. So I can't guide you along any better than was laid out in the tutorial. I'll need to know that information if I'm to figure out where you diverted from the tutorial.
Aug 16 '07 #11
pouj
18
i did exactly step by step of what you did. my whole problem is with

With Me![Employee]
Aug 16 '07 #12
Rabbit
12,516 Expert Mod 8TB
i did exactly step by step of what you did. my whole problem is with

With Me![Employee]
I don't know what's wrong with it, you'll need to tell me what Employee is.
Aug 16 '07 #13
pouj
18
okay i will tell you step by step what i have done

i have 2 tables
one is called
tblcompany
and other is called tblemployee

in tblcompany
i got 2 fields
one is ID which is automatic and primary key
and other one is cpyname

then in tblemployee
i got 3 fields
ID which is automatic number and primary key
empname which is text
and EID which is i did lookup wizard to make relationship with ID from tblcompany (i might no idea how to set up foreign key)

then i created a blank form
and drop the fields cpyname and empname

and i changed them both dropbox.
and i put in all the control source and row source and column count a you stated.

and then i went to after update and i put in event procedure and in visual basic i put in this code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cpyname_AfterUpdate()
  2.     With Me![Employee]
  3.         If IsNull(Me!company) Then
  4.             .RowSource = ""
  5.         Else
  6.             .RowSource = "select [empname] " & _
  7.             "from tblemployees " & _
  8.             "where [id] =" & Me!company
  9.         End If
  10.         Call .Requery
  11.         End With
  12. End Sub
  13.  
so what i am doing wrong and where?

i gaved as much details as i could. so i hope this helps
Aug 16 '07 #14
Rabbit
12,516 Expert Mod 8TB
okay i will tell you step by step what i have done

i have 2 tables
one is called
tblcompany
and other is called tblemployee

in tblcompany
i got 2 fields
one is ID which is automatic and primary key
and other one is cpyname

then in tblemployee
i got 3 fields
ID which is automatic number and primary key
empname which is text
and EID which is i did lookup wizard to make relationship with ID from tblcompany (i might no idea how to set up foreign key)

then i created a blank form
and drop the fields cpyname and empname

and i changed them both dropbox.
and i put in all the control source and row source and column count a you stated.

and then i went to after update and i put in event procedure and in visual basic i put in this code


Private Sub cpyname_AfterUpdate()
With Me![Employee]
If IsNull(Me!company) Then
.RowSource = ""
Else
.RowSource = "select [empname] " & _
"from tblemployees " & _
"where [id] =" & Me!company
End If
Call .Requery
End With
End Sub

so what i am doing wrong and where?

i gaved as much details as i could. so i hope this helps
You're using the wrong fields. The main combo box's, cpyname at the moment, control source should be a key field, in this case ID/EID. The second combo box's control source doesn't have to be a key field but if you are storing this data in a related table then it should also be a key field. In this case tblemployee.ID. The code should make reference to these combo boxes, which is usually the same as the name of the field. Which you didn't do.

I also need to know the values for "and i put in all the control source and row source and column count a you stated"

I also need to know what the form is bound to, if anything, and the fields of that table if different from the two you have described.
Aug 16 '07 #15
pouj
18
i did all that as you said..but once again..is being the same...would it be possble if i send you the file so you look at it?
Aug 16 '07 #16
Rabbit
12,516 Expert Mod 8TB
I can't open your file, it says unrecognized database format.

Just answer these two questions and I should be able to fix the problem.

I also need to know the values for "and i put in all the control source and row source and column count a you stated"

I also need to know what the form is bound to, if anything, and the fields of that table if different from the two you have described.
Aug 16 '07 #17
pouj
18
is probaly cause is access 2007

okay
here is the information...
for cpyname
name: cpyname
controlsource: cpyname
colum count: 2
colum width: 1
rowsource: tblcompany
rowsource type: table/query
bound colum: 2

for empname
name: empname
controlsource: empname
colum count: 1
colum width: N/A
rowsource:
rowsource type: table/query
bound colum: 2

i will try sending you the db in access 97-2003 format
Aug 16 '07 #18
Rabbit
12,516 Expert Mod 8TB
1) Your form's record source uses a join. Joins are not updateable. As your form is setup now, there's no need for the form at all. The whole purpose of having the 2 lookup tables and cascading combo boxes is to populate the fields of a third table. Not to populate themselves.

2) The settings should be
name: cpyname (This should probably be CompanyID)
controlsource: (Whatever field you want to save this to, a third table, not your lookup tables, that defeats the purpose of cascading combo boxes)
colum count: 2
colum width: 0
rowsource: tblcompany
rowsource type: table/query
bound colum: 1

name: empname (This should probably be EmployeeID)
controlsource: (See Above)
colum count: 2
colum width: 0
rowsource:
rowsource type: table/query
bound colum: 1

3) The Code
Expand|Select|Wrap|Line Numbers
  1. Private Sub cpyname_AfterUpdate()
  2.    With Me![empname]
  3.       If IsNull(Me!cpyname) Then
  4.          .RowSource = ""
  5.       Else
  6.          .RowSource = "select id, [empname] " & _
  7.          "from tblemployees " & _
  8.          "where [id] =" & Me!cpyname
  9.       End If
  10.       Call .Requery
  11.    End With
  12. End Sub
  13.  
Aug 16 '07 #19
pouj
18
hey
i just did all that..but now it doesnt requery.....like when i choose company nothing happens in employee....whats the suggestion?
Aug 16 '07 #20
Rabbit
12,516 Expert Mod 8TB
What's the record source of your form. Delete whatever's in the record source property of the form.
Aug 16 '07 #21
pouj
18
i did that..and that didnt do any good! still same thing...it shows nothing in employee list.
Aug 16 '07 #22
Rabbit
12,516 Expert Mod 8TB
Delete whatever's in the control sources from both combo boxes.
Aug 16 '07 #23
pouj
18
did that, no work...
Aug 17 '07 #24
Rabbit
12,516 Expert Mod 8TB
I don't know what you could be doing wrong, send it to me again. I was able to get it working.
Aug 17 '07 #25
pouj
18
okay i finally got your tutorial to work.
okay but now...this is what i am want to do for school project.

I have to set up this cascadog combo/list boxs
basically
they way you would say it english would be like this

if value in combobox1 is Available (combo box choices are available, on project, vacatio)
then search the table1 for anyone with Available status and list them in combobox.

thats how it should be done....if not possible what is my choice.
Aug 22 '07 #26
Rabbit
12,516 Expert Mod 8TB
okay i finally got your tutorial to work.
okay but now...this is what i am want to do for school project.

I have to set up this cascadog combo/list boxs
basically
they way you would say it english would be like this

if value in combobox1 is Available (combo box choices are available, on project, vacatio)
then search the table1 for anyone with Available status and list them in combobox.

thats how it should be done....if not possible what is my choice.
You can do this with the cascading combo boxes. The first combo box holds the status values. The second combo box's row source is changed accordingly in the after update event of the first combo box. It's no different than a cascading combo box save for the exact syntax you use. But the concept is the same.
Aug 22 '07 #27

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Cameron | last post by:
Hi, For the database I am currently working on, my employer would like the ability to use multiple combo boxes in order to filter the database. For instance the structure of the company is based...
6
by: visionstate | last post by:
Hi there, I am building a database that requires cascading lists on a form. I currently have (I may be adding more later) 3 combo boxes on my form - Department, Surname and Forename. The user...
2
by: Dave | last post by:
I have 3 tables of information feeding into 4 combo boxes on my main form (DR Form). I have as many list boxes (acting as text boxes) as there are fields in each one of the 3 tables. Once...
1
kcdoell
by: kcdoell | last post by:
Good Morning: I have a form where I am trying to create cascading combo boxes. I have done this before but I am getting the following error that is throwing me off: Procedure declaration...
11
by: jgoodnight | last post by:
Hi, I have a form with three combo boxes: Area, Sub-Area, and Factor. I've set up the Sub-Area combo box to list only those areas that are part of the selected area. I've set up the Factor combo...
12
by: azalea45 | last post by:
Hi all I am a newbie when it comes to access. My company requires that all Databases run on the company SQL back-end as a result i have a Access project that connects to the server. I have two...
7
by: Toireasa | last post by:
Hi, Newbie Access developer here, and my first post on this forum, so I might not get everything right - thanks in advance for your help and your patience! I'm using Access 2007, in XP. I'm...
3
by: buddyr | last post by:
Hello, I was able to use the example on Cascading /List boxes on the site here for 2 combo boxes. http://bytes.com/topic/access/insights/605958-cascading-combo-list-boxes Is there a way to add...
3
by: Outback | last post by:
Hi. Windows XP + Access 2002. I have three tables. tblMakes ======= MakeKey (PK) Make tblModels
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.