|
|
|
Please look at code. I think im missing something simple. |
|---|
| message from Greg on 19 May 2004 |
Hi,
Thanks for the help on my previous question, it worked
very well.
I cannot get my third linked (dynamic) combo box to
refresh. I believe its in my code. The first two combo
boxes refresh everytime, but after I have gone through my
dropdowns more than once, nothing appears in the Jobsite
combo box.
I have 3 combo boxes in this order:
cboCust - Customer
cboDivision - Division
cboJobsite - Jobsite
I have one table - Company
I have 3 fields
CustID
Division
Jobsite
Here is my VB code:
Option Compare Database
Private Sub cboCust_AfterUpdate()
Dim strSearch As String
'For text IDs
strSearch = "[CustID] = " & Chr$(34) & Me![cboCust] &
Chr$(34)
'Find the record that matches the control.
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark
'requery the Division combox box so that it shows correct
information (given in the row source)
Me![cboDivision].Requery
Me.cboDivision.Enabled = True
Me![cboDivision].Value = "Select Division"
Me![cboJobsite].Requery
Me.cboJobsite.Enabled = True
Me![cboJobsite].Value = "Select Jobsite"
End Sub
Private Sub Form_Load()
Me.cboCust.Value = "select a customer"
Me.cboDivision.Enabled = False
Me.cboJobsite.Enabled = False
End Sub
Thank you,
Greg
|
| Greg replied to Greg on 19 May 2004 |
Hi, Actually, when I select a Jobsite from the 3rd
dropdown it brings up all jobsites in a division for every
company, no matter which company I choose, so its just
going off of the Division dropdown. Therefore I think its
in my SQL statenent.
Heres my sql statement:
SELECT [company].[Jobsite]
FROM company
WHERE ((([company].[Division])=[Forms]!
[frmlinkedcomboboxes]![cboDivision]));
I have only one table called Company.
|
| =?Utf-8?B?cnB3?= replied to Greg on 19 May 2004 |
SELECT [company].[Jobsite]
FROM company
WHERE ((([company].[Division])=[Forms]!
[frmlinkedcomboboxes]![cboDivision]) AND (([company].[CustID]) = [Forms]![frmlinkedcomboboxes]![cboCust));
hth
rpw
|
| =?Utf-8?B?cnB3?= replied to Greg on 19 May 2004 |
I was intrigued by your problem and tried to ponder it out on a test db. Here's what I've got that works.
tblName
NameID
Name
tblCity
CityID
City
tblAddress
AddressID
Address
tblJunc
JuncID
NameID
CityID
AddressID
tblSub
SubID
JuncID
NameID
CityID
AddressID
I then built a form on tblSub and made three combos for Name, City, and Address. Whatever name selected then limited the list in the City combo, and a selection there limited the list in the Address combo.
The SQL for the City combo is this:
SELECT tblCity.CityID, tblCity.City FROM tblCity INNER JOIN tblJunc ON tblCity.CityID=tblJunc.CityID WHERE (((tblJunc.NameID)=forms!frmSub!NameID));
The SQL on the Address combo is this:
SELECT tblAddress.AddressID, tblAddress.Address, tblJunc.CityID FROM tblAddress INNER JOIN tblJunc ON tblAddress.AddressID=tblJunc.AddressID WHERE (((tblJunc.CityID)=forms!frmSub!CityID) And ((tblJunc.NameID)=forms!frmSub!NameID));
In the After Update event of the Name combo, I put "Me.CityID.Requery". In the after update of the City combo I put "Me.AddressID.Requery"
Works like a charm.
Maybe you can use this and ponder out the problem with your form.
Hmmm. Maybe my tblName is the same as your company, tblCity = division, tblAddress = jobsite, tblJunc = assignments??, tblSub = customer order??
HTH
rpw
|
| Greg replied to =?Utf-8?B?cnB3?= on 20 May 2004 |
Excellent, well done, I was debating about having
individual tables. I think it makes more sense too
Thanks,
Greg
out on a test db. Here's what I've got that works.
Name, City, and Address. Whatever name selected then
limited the list in the City combo, and a selection there
limited the list in the Address combo.
JOIN tblJunc ON tblCity.CityID=tblJunc.CityID WHERE
(((tblJunc.NameID)=forms!frmSub!NameID));
tblJunc.CityID FROM tblAddress INNER JOIN tblJunc ON
tblAddress.AddressID=tblJunc.AddressID WHERE
(((tblJunc.CityID)=forms!frmSub!CityID) And
((tblJunc.NameID)=forms!frmSub!NameID));
put "Me.CityID.Requery". In the after update of the City
combo I put "Me.AddressID.Requery"
your form.
tblCity = division, tblAddress = jobsite, tblJunc =
assignments??, tblSub = customer order??
every
just
think its
combo
through my
Jobsite
[cboCust] &>Chr$(34)
|
| =?Utf-8?B?cnB3?= replied to Greg on 21 May 2004 |
You may wish to check out this link that Jeff Conrad suggested to me once. It's about normalizing your tables.
http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding
Normalization")
rpw
|
|
