Combo Box Help

  • Thread starter Thread starter Mike Slater
  • Start date Start date
M

Mike Slater

I know this is wrong, and I'm really sorry, but I'm kinda desperate. I
posted this code last week in Access Forms but I'm under some pressure for an
answer.

I have a form that searches data in a table (not for adding, deleting, or
editing). I have two combo boxes. The first one is for selecting a State
Name. The second combo box shows Agencies in each state.

The first combo box seems to work fine for selecting the state. The second
combo box then shows the relevent entries for each state. The problem is
that I can only select the first record in the second combo box. All records
are shown, but no matter which I select, only the first record shows in that
combo box and in the related text boxes.

This is what I have:

(cboStateName):

Row Source: tblStateName (only column used is StateName)
Type: Table/Query
Event (After Update):
Private Sub cboStateName_AfterUpdate()
Me![cboAgencyName] = Null
Me![cboAgencyName].Requery
End Sub


(cboAgencyName)

Row Source: tblOutOfStateAgencies
1st Field: StateName
Criteria: [Forms]![frmSearchByState]![cboStateName]

2nd Field: AgencyName
3rd Field: PrimaryTelephone
4th Field: DispatchTelephone
5th Field: AlternateTelephone
6th Field: FaxNumber
7th Field: ORI

(tbxPrimaryTelephone)
Control Source: =[cboAgencyName].Column(2)

(tbxDispatchTelephone)
Control Source: =[cboAgecyName].Column(3)

(tbxAlternateTelephone)
Control Source: =[cboAgecyName].Column(4)

(tbxFaxNumber)
Control Source: =[cboAgecyName].Column(5)

(tbxORI)
Control Source: =[cboAgecyName].Column(6)

That's all I have. It works fine for the first record in (cboAgencyName),
but if I select the next record, it stays on the first record. I'm a newb at
this (obviously).

Again, really, really sorry about the double post!!!

Mike
 
Is it a bound combo box? If so, try making it unbound. In general a search
combo box, which is essentially what you have, is unbound.
 
Hi Bruce,

Both combo boxes are unbound. I thought it might be a problem with the
second combo box. I just can't figure out why it displays all the records,
but can only select the first.

Mike


BruceM said:
Is it a bound combo box? If so, try making it unbound. In general a search
combo box, which is essentially what you have, is unbound.

Mike Slater said:
I know this is wrong, and I'm really sorry, but I'm kinda desperate. I
posted this code last week in Access Forms but I'm under some pressure for
an
answer.

I have a form that searches data in a table (not for adding, deleting, or
editing). I have two combo boxes. The first one is for selecting a State
Name. The second combo box shows Agencies in each state.

The first combo box seems to work fine for selecting the state. The
second
combo box then shows the relevent entries for each state. The problem is
that I can only select the first record in the second combo box. All
records
are shown, but no matter which I select, only the first record shows in
that
combo box and in the related text boxes.

This is what I have:

(cboStateName):

Row Source: tblStateName (only column used is StateName)
Type: Table/Query
Event (After Update):
Private Sub cboStateName_AfterUpdate()
Me![cboAgencyName] = Null
Me![cboAgencyName].Requery
End Sub


(cboAgencyName)

Row Source: tblOutOfStateAgencies
1st Field: StateName
Criteria: [Forms]![frmSearchByState]![cboStateName]

2nd Field: AgencyName
3rd Field: PrimaryTelephone
4th Field: DispatchTelephone
5th Field: AlternateTelephone
6th Field: FaxNumber
7th Field: ORI

(tbxPrimaryTelephone)
Control Source: =[cboAgencyName].Column(2)

(tbxDispatchTelephone)
Control Source: =[cboAgecyName].Column(3)

(tbxAlternateTelephone)
Control Source: =[cboAgecyName].Column(4)

(tbxFaxNumber)
Control Source: =[cboAgecyName].Column(5)

(tbxORI)
Control Source: =[cboAgecyName].Column(6)

That's all I have. It works fine for the first record in (cboAgencyName),
but if I select the next record, it stays on the first record. I'm a newb
at
this (obviously).

Again, really, really sorry about the double post!!!

Mike
Click to expand...
Click to expand...
 
Is the row source for cboAgancyName literally tblOutOfStateAgencies, or is
it a query based on the table? If the former, where is the criteria
applied? If the latter, post the SQL (more below about how to do that).

I would probably use the After Update event of the first combo box to set
the Row Source for the second one:

Private Sub cboStateName_AfterUpdate()

Dim strState as String, strRow as String

strState = Me.cboStateName.[State]

strRow = "SELECT OOSA.AgencyName, OOSA.StateName, " & _
"OOSA.PrimaryTelephone, OOSA.DispatchTelephone, " & _
"OOSA.AlternateTelephone, OOSA.FaxNumber, OOSA.ORI " & _
"FROM tblOutOfStateAgencies AS OOSA " & _
"WHERE OOSA.StateName = """ & strState & _
""" ORDER BY OOSA.AgencyName"

Me.[cboAgencyName].RowSource = strRow

End Sub

The syntax with the multiple quote marks in a row assumes you are using the
StateName text field in the SQL rather than a numeric key field.
I used an alias for the table because the name was so long. It is not
required that you do so. You can use query design view to put together the
query, then click View >> SQL to see the SQL, if you would rather not type
it all out. The automatic SQL may have more parentheses than is absolutely
necessary, but that shouldn't do any harm.

I expect you could do it the way you are now, in which case there may be a
difficulty with the Row Source SQL. If you post the Row Source it may be
possible to see something amiss.

This link has more information about putting together a cascading combo box:
http://www.fontstuff.com/access/acctut10.htm

Mike Slater said:
Hi Bruce,

Both combo boxes are unbound. I thought it might be a problem with the
second combo box. I just can't figure out why it displays all the
records,
but can only select the first.

Mike


BruceM said:
Is it a bound combo box? If so, try making it unbound. In general a
search
combo box, which is essentially what you have, is unbound.

Mike Slater said:
I know this is wrong, and I'm really sorry, but I'm kinda desperate. I
posted this code last week in Access Forms but I'm under some pressure
for
an
answer.

I have a form that searches data in a table (not for adding, deleting,
or
editing). I have two combo boxes. The first one is for selecting a
State
Name. The second combo box shows Agencies in each state.

The first combo box seems to work fine for selecting the state. The
second
combo box then shows the relevent entries for each state. The problem
is
that I can only select the first record in the second combo box. All
records
are shown, but no matter which I select, only the first record shows in
that
combo box and in the related text boxes.

This is what I have:

(cboStateName):

Row Source: tblStateName (only column used is StateName)
Type: Table/Query
Event (After Update):
Private Sub cboStateName_AfterUpdate()
Me![cboAgencyName] = Null
Me![cboAgencyName].Requery
End Sub


(cboAgencyName)

Row Source: tblOutOfStateAgencies
1st Field: StateName
Criteria: [Forms]![frmSearchByState]![cboStateName]

2nd Field: AgencyName
3rd Field: PrimaryTelephone
4th Field: DispatchTelephone
5th Field: AlternateTelephone
6th Field: FaxNumber
7th Field: ORI

(tbxPrimaryTelephone)
Control Source: =[cboAgencyName].Column(2)

(tbxDispatchTelephone)
Control Source: =[cboAgecyName].Column(3)

(tbxAlternateTelephone)
Control Source: =[cboAgecyName].Column(4)

(tbxFaxNumber)
Control Source: =[cboAgecyName].Column(5)

(tbxORI)
Control Source: =[cboAgecyName].Column(6)

That's all I have. It works fine for the first record in
(cboAgencyName),
but if I select the next record, it stays on the first record. I'm a
newb
at
this (obviously).

Again, really, really sorry about the double post!!!

Mike
Click to expand...
Click to expand...
Click to expand...
 
Bruce,

This is the SQL View from the Row Source of cboAgencyName:

SELECT tblOutofStateAgencies.StateName, tblOutofStateAgencies.AgencyName,
tblOutofStateAgencies.PrimaryTelephone,
tblOutofStateAgencies.DispatchTelephone,
tblOutofStateAgencies.AlternateTelephone, tblOutofStateAgencies.FaxNumber,
tblOutofStateAgencies.ORI
FROM tblOutofStateAgencies

I set the criteria under the StateName field in this query.

Meanwhile, I will try to plug the code you provided in and see what happens.

Thanks!
WHERE
(((tblOutofStateAgencies.StateName)=[Forms]![frmSearchByState]![cboStateName]));


BruceM said:
Is the row source for cboAgancyName literally tblOutOfStateAgencies, or is
it a query based on the table? If the former, where is the criteria
applied? If the latter, post the SQL (more below about how to do that).

I would probably use the After Update event of the first combo box to set
the Row Source for the second one:

Private Sub cboStateName_AfterUpdate()

Dim strState as String, strRow as String

strState = Me.cboStateName.[State]

strRow = "SELECT OOSA.AgencyName, OOSA.StateName, " & _
"OOSA.PrimaryTelephone, OOSA.DispatchTelephone, " & _
"OOSA.AlternateTelephone, OOSA.FaxNumber, OOSA.ORI " & _
"FROM tblOutOfStateAgencies AS OOSA " & _
"WHERE OOSA.StateName = """ & strState & _
""" ORDER BY OOSA.AgencyName"

Me.[cboAgencyName].RowSource = strRow

End Sub

The syntax with the multiple quote marks in a row assumes you are using the
StateName text field in the SQL rather than a numeric key field.
I used an alias for the table because the name was so long. It is not
required that you do so. You can use query design view to put together the
query, then click View >> SQL to see the SQL, if you would rather not type
it all out. The automatic SQL may have more parentheses than is absolutely
necessary, but that shouldn't do any harm.

I expect you could do it the way you are now, in which case there may be a
difficulty with the Row Source SQL. If you post the Row Source it may be
possible to see something amiss.

This link has more information about putting together a cascading combo box:
http://www.fontstuff.com/access/acctut10.htm

Mike Slater said:
Hi Bruce,

Both combo boxes are unbound. I thought it might be a problem with the
second combo box. I just can't figure out why it displays all the
records,
but can only select the first.

Mike


BruceM said:
Is it a bound combo box? If so, try making it unbound. In general a
search
combo box, which is essentially what you have, is unbound.

I know this is wrong, and I'm really sorry, but I'm kinda desperate. I
posted this code last week in Access Forms but I'm under some pressure
for
an
answer.

I have a form that searches data in a table (not for adding, deleting,
or
editing). I have two combo boxes. The first one is for selecting a
State
Name. The second combo box shows Agencies in each state.

The first combo box seems to work fine for selecting the state. The
second
combo box then shows the relevent entries for each state. The problem
is
that I can only select the first record in the second combo box. All
records
are shown, but no matter which I select, only the first record shows in
that
combo box and in the related text boxes.

This is what I have:

(cboStateName):

Row Source: tblStateName (only column used is StateName)
Type: Table/Query
Event (After Update):
Private Sub cboStateName_AfterUpdate()
Me![cboAgencyName] = Null
Me![cboAgencyName].Requery
End Sub


(cboAgencyName)

Row Source: tblOutOfStateAgencies
1st Field: StateName
Criteria: [Forms]![frmSearchByState]![cboStateName]

2nd Field: AgencyName
3rd Field: PrimaryTelephone
4th Field: DispatchTelephone
5th Field: AlternateTelephone
6th Field: FaxNumber
7th Field: ORI

(tbxPrimaryTelephone)
Control Source: =[cboAgencyName].Column(2)

(tbxDispatchTelephone)
Control Source: =[cboAgecyName].Column(3)

(tbxAlternateTelephone)
Control Source: =[cboAgecyName].Column(4)

(tbxFaxNumber)
Control Source: =[cboAgecyName].Column(5)

(tbxORI)
Control Source: =[cboAgecyName].Column(6)

That's all I have. It works fine for the first record in
(cboAgencyName),
but if I select the next record, it stays on the first record. I'm a
newb
at
this (obviously).

Again, really, really sorry about the double post!!!

Mike
Click to expand...
Click to expand...
Click to expand...
 
I should have mentioned that probably you will need either to requery the
combo box at each record (Current Event) if you are using your query, or you
will need to re-set the RowSource at each new record if you use my system.

I don't see what is preventing the combo box from working as intended. It
is as if you are requerying after making the selection in the cboAgencyName.
Or perhaps you inadvertently changed a setting. If there is no code
requerying the combo box you could just start over with a new combo box.

Mike Slater said:
Bruce,

This is the SQL View from the Row Source of cboAgencyName:

SELECT tblOutofStateAgencies.StateName, tblOutofStateAgencies.AgencyName,
tblOutofStateAgencies.PrimaryTelephone,
tblOutofStateAgencies.DispatchTelephone,
tblOutofStateAgencies.AlternateTelephone, tblOutofStateAgencies.FaxNumber,
tblOutofStateAgencies.ORI
FROM tblOutofStateAgencies

I set the criteria under the StateName field in this query.

Meanwhile, I will try to plug the code you provided in and see what
happens.

Thanks!
WHERE
(((tblOutofStateAgencies.StateName)=[Forms]![frmSearchByState]![cboStateName]));


BruceM said:
Is the row source for cboAgancyName literally tblOutOfStateAgencies, or
is
it a query based on the table? If the former, where is the criteria
applied? If the latter, post the SQL (more below about how to do that).

I would probably use the After Update event of the first combo box to set
the Row Source for the second one:

Private Sub cboStateName_AfterUpdate()

Dim strState as String, strRow as String

strState = Me.cboStateName.[State]

strRow = "SELECT OOSA.AgencyName, OOSA.StateName, " & _
"OOSA.PrimaryTelephone, OOSA.DispatchTelephone, " & _
"OOSA.AlternateTelephone, OOSA.FaxNumber, OOSA.ORI " &
_
"FROM tblOutOfStateAgencies AS OOSA " & _
"WHERE OOSA.StateName = """ & strState & _
""" ORDER BY OOSA.AgencyName"

Me.[cboAgencyName].RowSource = strRow

End Sub

The syntax with the multiple quote marks in a row assumes you are using
the
StateName text field in the SQL rather than a numeric key field.
I used an alias for the table because the name was so long. It is not
required that you do so. You can use query design view to put together
the
query, then click View >> SQL to see the SQL, if you would rather not
type
it all out. The automatic SQL may have more parentheses than is
absolutely
necessary, but that shouldn't do any harm.

I expect you could do it the way you are now, in which case there may be
a
difficulty with the Row Source SQL. If you post the Row Source it may be
possible to see something amiss.

This link has more information about putting together a cascading combo
box:
http://www.fontstuff.com/access/acctut10.htm

Mike Slater said:
Hi Bruce,

Both combo boxes are unbound. I thought it might be a problem with
the
second combo box. I just can't figure out why it displays all the
records,
but can only select the first.

Mike


:

Is it a bound combo box? If so, try making it unbound. In general a
search
combo box, which is essentially what you have, is unbound.

I know this is wrong, and I'm really sorry, but I'm kinda desperate.
I
posted this code last week in Access Forms but I'm under some
pressure
for
an
answer.

I have a form that searches data in a table (not for adding,
deleting,
or
editing). I have two combo boxes. The first one is for selecting a
State
Name. The second combo box shows Agencies in each state.

The first combo box seems to work fine for selecting the state. The
second
combo box then shows the relevent entries for each state. The
problem
is
that I can only select the first record in the second combo box.
All
records
are shown, but no matter which I select, only the first record shows
in
that
combo box and in the related text boxes.

This is what I have:

(cboStateName):

Row Source: tblStateName (only column used is StateName)
Type: Table/Query
Event (After Update):
Private Sub cboStateName_AfterUpdate()
Me![cboAgencyName] = Null
Me![cboAgencyName].Requery
End Sub


(cboAgencyName)

Row Source: tblOutOfStateAgencies
1st Field: StateName
Criteria: [Forms]![frmSearchByState]![cboStateName]

2nd Field: AgencyName
3rd Field: PrimaryTelephone
4th Field: DispatchTelephone
5th Field: AlternateTelephone
6th Field: FaxNumber
7th Field: ORI

(tbxPrimaryTelephone)
Control Source: =[cboAgencyName].Column(2)

(tbxDispatchTelephone)
Control Source: =[cboAgecyName].Column(3)

(tbxAlternateTelephone)
Control Source: =[cboAgecyName].Column(4)

(tbxFaxNumber)
Control Source: =[cboAgecyName].Column(5)

(tbxORI)
Control Source: =[cboAgecyName].Column(6)

That's all I have. It works fine for the first record in
(cboAgencyName),
but if I select the next record, it stays on the first record. I'm
a
newb
at
this (obviously).

Again, really, really sorry about the double post!!!

Mike
Click to expand...
Click to expand...
Click to expand...
 
Back
Top