Please look at code. I think im missing something simple.

[Greg]

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]

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.

 

[Guest]

I was intrigued by your problem and tried to ponder it out on a test db. Here's what I've got that works

tblNam
NameI
Nam

tblCit
CityI
Cit

tblAddres
AddressI
Addres

tblJun
JuncI
NameI
CityI
AddressI

tblSu
SubI
JuncI
NameI
CityI
AddressI

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?

HT

rp


----- Greg wrote: ----

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 compan
WHERE ((([company].[Division])=[Forms]
[frmlinkedcomboboxes]![cboDivision]))

I have only one table called Company

-----Original Message----
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 - Custome
cboDivision - Divisio
cboJobsite - Jobsit

I have one table - Compan

I have 3 field
CustI
Divisio
Jobsit

Here is my VB code: Option Compare Databas
Private Sub cboCust_AfterUpdate(
Dim strSearch As Strin

strSearch = "[CustID] = " & Chr$(34) & Me![cboCust] &>Chr$(34

'Find the record that matches the control

Me.RecordsetClone.FindFirst strSearc
Me.Bookmark = Me.RecordsetClone.Bookmar

'requery the Division combox box so that it shows correct

information (given in the row source
Me![cboDivision].Requer
Me.cboDivision.Enabled = Tru
Me![cboDivision].Value = "Select Division

Me![cboJobsite].Requer

Me.cboJobsite.Enabled = Tru
Me![cboJobsite].Value = "Select Jobsite

Private Sub Form_Load(

Me.cboCust.Value = "select a customer
Me.cboDivision.Enabled = Fals
Me.cboJobsite.Enabled = Fals

Thank you

Gre

 

[Guest]

On second thought, try modifying your SQL to

SELECT [company].[Jobsite
FROM compan
WHERE ((([company].[Division])=[Forms]
[frmlinkedcomboboxes]![cboDivision]) AND (([company].[CustID]) = [Forms]![frmlinkedcomboboxes]![cboCust))

ht

rp


----- Greg wrote: ----

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 compan
WHERE ((([company].[Division])=[Forms]
[frmlinkedcomboboxes]![cboDivision]))

I have only one table called Company

-----Original Message----
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 - Custome
cboDivision - Divisio
cboJobsite - Jobsit

I have one table - Compan

I have 3 field
CustI
Divisio
Jobsit

Here is my VB code: Option Compare Databas
Private Sub cboCust_AfterUpdate(
Dim strSearch As Strin

strSearch = "[CustID] = " & Chr$(34) & Me![cboCust] &>Chr$(34

'Find the record that matches the control

Me.RecordsetClone.FindFirst strSearc
Me.Bookmark = Me.RecordsetClone.Bookmar

'requery the Division combox box so that it shows correct

information (given in the row source
Me![cboDivision].Requer
Me.cboDivision.Enabled = Tru
Me![cboDivision].Value = "Select Division

Me![cboJobsite].Requer

Me.cboJobsite.Enabled = Tru
Me![cboJobsite].Value = "Select Jobsite

Private Sub Form_Load(

Me.cboCust.Value = "select a customer
Me.cboDivision.Enabled = Fals
Me.cboJobsite.Enabled = Fals

Thank you

Gre

 

[Greg]

Excellent, well done, I was debating about having
individual tables. I think it makes more sense too

Thanks,
Greg

-----Original Message-----
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 wrote: -----

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.

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

.