synchronize combo boxes

reesa · External Since: Aug 28, 2009 Posts: 1

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.

John W. Vinson · External Since: Jan 29, 2004 Posts: 2505

On Fri, 28 Aug 2009 13:34:01 -0700, reesa <reesa.TakeThisOut@discussions.microsoft.com>
wrote:

>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.

I'm confused about your fieldnames. Do you have a *FIELD* in tblCity named
cboZip? You shouldn't; a combo box is not a field, it's 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]

Steve · External Since: Aug 04, 2009 Posts: 47

Please provide a list of the fields in TblCity.

Thanks!

Steve
santus.DeleteThis@penn.com

"reesa" <reesa.DeleteThis@discussions.microsoft.com> wrote in message
news:598E2FF4-FC5B-4616-98CE-E113D5306057@microsoft.com...
>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.

Chris Alexander · External Since: Nov 19, 2009 Posts: 1

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:

On Friday, August 28, 2009 4:34 PM
reesa wrote:

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.

On Friday, August 28, 2009 4:58 PM
Steve wrote:

Please provide a list of the fields in TblCity.Thanks!
Please provide a list of the fields in TblCity.

Thanks!

Steve
santus RemoveThis @penn.com

On Friday, August 28, 2009 5:49 PM
John W. Vinson wrote:

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/tutorials/aspnet/f3c535b5-70df-4e98-ab65-31...4bbac1a

KenSheridan via AccessMon · External Since: May 16, 2009 Posts: 68

Chris:

The RowSource for cboDevice is:

SELECT PK_Device_ID, [Device Type] FROM tblDeviceType ORDER BY [Device Type];

Its other properties (and this applies to the other combo boxes too) are as
follows:

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box. In its AfterUpdate event procedure put:

Me.cboMake.Requery

Note that the tblDeviceType table should not have a ModelID column.

The RowSource for cboMake is:

SELECT DISTINCT PK_Make_ID, Make FROM tblMake INNER JOIN tblModel ON tblMake.
PK_Make_ID = tblModel.MakeID WHERE tblModel.Device_ID = Form!cboDevice ORDER
BY Make;

Note that tblModel does now require a Device_ID column.

In its AfterUpdate event procedure put:

Me.cboModel.Requery

The RowSource for cboModel is:

SELECT PK_Model_ID, Model FROM tblModel WHERE MakeID = Form!cboMake AND
Device_ID = Form!cboDevice ORDER BY Model;

Your problem was that you had tblDeviceType referencing tblModel, not the
other way round. Your tblComputers table's Model column should be a foreign
key referencing the key of tblModel. In fact you only really need this
foreign key, not the Make and Device Type columns as the model maps to the
make and device type via the relationships, so these columns are actually
redundant. Normalizing the table by removing them does make the interface
more difficult to construct, however, as you need to use unbound controls fro
the make and device type. It can be done, but it's a little tricky. There's
a demo of mine showing how it can be done at the following link if you want
to take a look:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn...tid=236

One other thing to note is that as you are using numeric 'surrogate' keys
correlated combo boxes like this don't work well in continuous form view as
if an item selected in one row is no longer represented in the combo box's
list when its requeried by a selection in another row, the value in the first
row will disappear. The underlying numeric value of the control is still the
same, you just don't see the corresponding text value. This is not a problem
in single form view provided that you requery all of the correlated combo
boxes in the form's Current event procedure. If 'natural' keys are used
rather than 'surrogate' numeric keys then the problem with continuous forms
goes away.

Another thing to note is that when referencing the controls as parameters in
the RowSource queries, as the controls are all on the same form, you can
simply use the Form property as above rather than a full reference to the
form as a member of the Forms collection. In fact you can even omit the
Form! altogether and just put the control name, but I prefer to leave in the
Form! to avoid any possible ambiguity as to what is being referenced.

Ken Sheridan
Stafford, England

Chris Alexander wrote:
>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
>
>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:
>
>On Friday, August 28, 2009 4:34 PM
>reesa wrote:
>
>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.
>
>On Friday, August 28, 2009 4:58 PM
>Steve wrote:
>
>Please provide a list of the fields in TblCity.Thanks!
>Please provide a list of the fields in TblCity.
>
>Thanks!
>
>Steve
>santus@penn.com
>
>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/tutorials/aspnet/f3c535b5-70df-4e98-ab65-3198d4bbac1a/autodownload-a-file-and.aspx

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1