multi combo boxes

[Robson8112]

i just need to know how:

When i select an option on Combo box 1 it displays cetain info in combo box 2. I cant seem to retrieve it.

The info in combo 1 and combo 2 both read from different tables, but both are linked?

so, when i select an option in combo 1, it gives me certain options in combo 2.

any suggestons?

 

[Newposter]

Here's how I do it:

Cobmbo box 1 is unbound. Data Row Source Type is set to Table/Query and Row Source is "Select [field1] From [tbl1]. Properties set so that After Update it runs a macro. The macro first runs SetValue on combo box 2 to Expression Null (clears the contents), then the macro does a Requery.

Combo box 2 is unbound. Data Row Source Type is set to Table/Query and Row Source is "qryField1-Filter". This consists of SELECT DISTINCTROW tbl2.field2, tbl2.field1
FROM tbl2
WHERE ((tbl2.field1=[Forms]![frmSearchfield1]![field1]))
ORDER BY tbl2.field2;

field1 is the field in the first combo box, and field2 is the field for the 2nd combo box. I made tbl1 to have just the one field1, and tbl2 has both field1 and field2, such that field1 is replicated for each different corresponding field2.
Newposter
"Good judgment comes from experience. Experience comes from bad judgment."

 

[Robson8112]

This is how mine looks, and it dont work

SELECT DISTINCTROW TblMediaServers.Media Servers, TblMediaServers.Masters Servers
FROM TblMediaServers
WHERE ((TblMediaServers.Masters Servers=[Forms]![Tapes_Main]![cboServerSelect]))
ORDER BY TblMediaServer.Media Servers;

 

[SgtJarrow]

Try:

SELECT DISTINCTROW [TblMediaServers].[Media Servers], [TblMediaServers].[Masters Servers]
FROM [TblMediaServers]
WHERE (([TblMediaServers].[Masters Servers]=[Forms]![Tapes_Main]![cboServerSelect]))
ORDER BY [TblMediaServer].[Media Servers];

Notice the inclusion of the []'s. With spaces in the names of fields, you must enclose them in brackets for access to properly recognize them. If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Robson8112]

no errors this time, however its not showing any things in the combo 2 box

 

[SgtJarrow]

In the afterupdate event for Combobox1, you should have:

Me![combobox2] = ""
Me![combobox2].Requery

This will populate combobox2 after combobox1 has been chosen.....

Also, check to make sure combobox1 has the correct data... If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Robson8112]

It gives me a "Cant find Macro Error" this is what i put in the after update:

Me![cboMedia] = ""
Me![cboMedia].Requery

 

[SgtJarrow]

In the little afterupdate box in the property window, click the little arrow to the right of the field and select eventprocedure. Then click the elipse (three dots at the end of the box) to the right of the box. The code window will come up.....here is where you put those two lines of code.... If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Robson8112]

Still no joy, below is my code (CboSelectServer is Combo1)

Private Sub CboSelectServer_AfterUpdate()
Requery

Me![CboMedia] = ""
Me![CboMedia].Requery

End Sub

 

[SgtJarrow]

Are you still getting the "Can't Find Macro Error"??? Or just not getting anything displayed in CboMedia???

You should now be working....

If you want (and can), zip up the database (less than 1 MB or my server won't accept it) and send it to my address in my sig block. I will take a look. I use Access97 at the office, so if you are using 2000 or 2002, please save as previous version first.... If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Robson8112]

theres no errors, its just not showing anything..

 

[SgtJarrow]

My guess then, without see the database and all, is there is something wrong in the SQL staement for the second combobox....

SELECT DISTINCTROW [TblMediaServers].[Media Servers], [TblMediaServers].[Masters Servers]
FROM [TblMediaServers]
WHERE (([TblMediaServers].[Masters Servers]=[Forms]![Tapes_Main]![cboServerSelect]))
ORDER BY [TblMediaServer].[Media Servers];

But again, without seeing the structure and setup, I cannot see the error.... If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[Robson8112]

i'll send u the data base, i've removed quite a alot of forms and tables to cut the size down, but i've left the ones i've got the problem with.

Will be with in a bit..