I'm having a similar issue with synchronizing/cascading combo boxes. I am a new Access 2007 user and have almost no VB knowledge. I have followed MANY, MANY tutorials and tips, hints, and tricks, but they've all been unsuccessful (even the downloadable Sampe DB from Microsoft...). Unfortunately I'm not able to post the database, but here goes...
I have four tables:
tblComputers
------------
Device Type -- Number (FK for PK_Device_ID)
Make -- Number (FK for PK_Make_ID)
Model -- Number
tblMake
-------
PK_Make_ID -- AutoNumber
Make -- Text
tblModel
--------
PK_Model_ID -- AutoNumber
Model -- Text
MakeID -- Number (FK for PK_Make_ID)
tblDeviceType
-------------
PK_Device_ID -- AutoNumber
Device Type -- Text
ModelID -- (FK for PK_Model_ID)
The "Model" field in "tblComputers" is a number b/c it had been a FK for PK_Model_ID, but I have since broken "tblModel"'s relationship with "tblComputers" and plugged it straight into "tblMake."
I am trying to do a (supposedly) simple synchronized combo box setup. A user selects "Device Type" from its combo box (cboDevice), and the models are limited to a certain item (i.e. laptop, printer, etc.). Then the user selects "Make" in a combo box (cboMake), and the choices in the "Model" combo box (cboModel) are limited to that particular manufacturer.
I have struggled for over 12 hours to get this thing to work without success. Can someone please help me out here? I'd greatly appreciate it!
Chris
John W. Vinson wrote:
Re: synchronize combo boxes
28-Aug-09
wrote:
I am confused about your fieldnames. Do you have a *FIELD* in tblCity named
cboZip? You should not; a combo box is not a field, it is a *control* on a form,
displaying a field's value. And your query is selecting those records where
Zip is equal to the value of cboCity... and the Sub is for a DIFFRENT control
named City...!?
Assuming that you have table fields named City and Zip, and combo boxes on
your form named cboCity and cboZip; and that you want to have cboCity display
the city (or cities, there are multicity zipcodes) after you select a zipcode,
try
Private Sub cboZip_AfterUpdate()
Me.cboCity.RowSource = "SELECT City FROM tblCity " _
& "WHERE Zip = '" & Me!cboZip & "' ORDER BY City;"
Me.cboCity.Requery
End Sub
If, on the other hand (or in addition!) you want to be able to select a city
name from cboCity and have cboZip now reflect the zipcodes in that city,
Private Sub cboCity_AfterUpdate()
Me.cboZip.RowSource = "SELECT Zip FROM tblCity " _
& "WHERE City = """ & Me!cboCity & """ ORDER BY Zip;"
Me.cboZip.Requery
End Sub
--
John W. Vinson [MVP]
Previous Posts In This Thread:
synchronize combo boxes
I think i may be totally stupid, but.. I have read all the previous answers
on this but i still do not understand. This is my first try with access, and
i am hoping to get it to work. I have 2 combo boxes, that pull the
information from a linked list. I would like cbozip to pull automatically
from cbocity. the following is what I have that is not working, I do not get
an error just no change on form. I an not sure now where my basic setting
should be for cbozip, i have changed so many times.
Private Sub City_AfterUpdate()
Me.CboCity.RowSource = "SELECT CboZip FROM" & _
" tbl City WHERE Zip = " & Me.CboCity & _
" ORDER BY City"
Me.CboCity = Me.CboCity.ItemData(0)
End sub
Any help would be appreciated, believe it or not, I have tried for 6 hours
to get this right, using every help box on the web i could find.
thanks.
Please provide a list of the fields in TblCity.Thanks!
Please provide a list of the fields in TblCity.
Thanks!
Steve
(e-mail address removed)
Re: synchronize combo boxes
wrote:
I am confused about your fieldnames. Do you have a *FIELD* in tblCity named
cboZip? You should not; a combo box is not a field, it is a *control* on a form,
displaying a field's value. And your query is selecting those records where
Zip is equal to the value of cboCity... and the Sub is for a DIFFRENT control
named City...!?
Assuming that you have table fields named City and Zip, and combo boxes on
your form named cboCity and cboZip; and that you want to have cboCity display
the city (or cities, there are multicity zipcodes) after you select a zipcode,
try
Private Sub cboZip_AfterUpdate()
Me.cboCity.RowSource = "SELECT City FROM tblCity " _
& "WHERE Zip = '" & Me!cboZip & "' ORDER BY City;"
Me.cboCity.Requery
End Sub
If, on the other hand (or in addition!) you want to be able to select a city
name from cboCity and have cboZip now reflect the zipcodes in that city,
Private Sub cboCity_AfterUpdate()
Me.cboZip.RowSource = "SELECT Zip FROM tblCity " _
& "WHERE City = """ & Me!cboCity & """ ORDER BY Zip;"
Me.cboZip.Requery
End Sub
--
John W. Vinson [MVP]
EggHeadCafe - Software Developer Portal of Choice
Auto-download a file and execute it Client side.
http://www.eggheadcafe.com/tutorial...b65-3198d4bbac1a/autodownload-a-file-and.aspx