2 combo box, 2 txt boxes - Populate problem

[S Jackson]

On my form, I have a combo box that looks up Region from a table. In the
Afterupdate event of this combo box, I have the following code:

'Code for Program Manager Information
Dim strSQL as String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
MsgBox strSQL

The above code provides the data for the 2nd combo box, cmbFieldofc which is
bound to the Fieldofc field in table tblCaseInfo. The two text boxes are
setup like so:
=[cmbFieldofc].Column(3)
=[cmbFieldofc].Column(6)

The problem I am having is that when I input the information for the first
time, everything works great. However, when I move to a new record and then
go back, the information is not correct - the 2nd combo box reverts to its
original query information, which messes up the two txt boxes.

So, the question is, what event on which control do I need to add code to?
Is it an event on the Form? If so, I may have a problem as I already
declared "strSQL as String" for a different process on the OnCurrent and
AfterUpdate events on the form control. So, if I have to add the above code
for the two combo boxes, do I just give it a different name (strSQL2)?

Any help is greatly appreciated. I always get lost with regard to which
event to assign code!
TIA

 

[Guest]

Hi, S.

Yes, the code can go in the On Current event. And, yes, just use another
variable name, like strCBox2SQL. And check out a third-party text written
for Access developers. They should treat the sequence of events thoroughly
there.

HTH
Sprinks

 

[S Jackson]

Thanks, but I am still having problems. I tried making a new simplied form
just for testing purposes. I have included the two relevant tables in the
query the form is based on. Combo Box 1 (cboRegion) allows the user to
select the Region which is stored in the Region field of Table 1. Then the
strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user
select a field office within the Region they just selected. When they do
this, it stores the information in the FieldOfc field in Table One and then
displays the matching information in text boxes 1 and 2.

The problem I am having is that the information in the cboFieldOfc keeps
disappearing when you either:

1. reopen the form; or
2. make a change to the cboRegion in any of the other records.

Currently, I have the code to populate cboFieldofc in the Afterupdate Event
of cboRegion. I have tried putting it elsewhere (Form-OnCurrent,
Form-OnOpen, Form-Afterupdate), but nothing works.

I'm lost here. This is really eating my lunch!

Hi, S.

Yes, the code can go in the On Current event. And, yes, just use another
variable name, like strCBox2SQL. And check out a third-party text written
for Access developers. They should treat the sequence of events thoroughly
there.

HTH
Sprinks

On my form, I have a combo box that looks up Region from a table. In the
Afterupdate event of this combo box, I have the following code:

'Code for Program Manager Information
Dim strSQL as String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
MsgBox strSQL

The above code provides the data for the 2nd combo box, cmbFieldofc which is
bound to the Fieldofc field in table tblCaseInfo. The two text boxes are
setup like so:
=[cmbFieldofc].Column(3)
=[cmbFieldofc].Column(6)

The problem I am having is that when I input the information for the first
time, everything works great. However, when I move to a new record and then
go back, the information is not correct - the 2nd combo box reverts to its
original query information, which messes up the two txt boxes.

So, the question is, what event on which control do I need to add code to?
Is it an event on the Form? If so, I may have a problem as I already
declared "strSQL as String" for a different process on the OnCurrent and
AfterUpdate events on the form control. So, if I have to add the above code
for the two combo boxes, do I just give it a different name (strSQL2)?

Any help is greatly appreciated. I always get lost with regard to which
event to assign code!
TIA

 

[Guest]

The Old Disappearing Data! Is this a continuous form? If so, the combo
box cannot simultaneously hold the different populations required for each
chosen Region, so if the selection is not contained within the combo box'
*only* list, it disappears! Confused the hell out of me, too, when I first
encountered it.

The solution is to bind a textbox to your FieldOfc field, and use its
OnGotFocus event to pop up a modal form that contains a single unbound combo
box populated by your SQL string. Use its OnUpdate event to write the
selection to your textbox on the main form, and close the modal form.

Here's an example from one of my applications, where the user selects a
steel size based on the steel type:

' Text box on main form
Private Sub txtSteelSize_GotFocus()
On Error Resume Next

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

End Sub

' Unbound combo box on mini-form
Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub


HTH
Sprinks

Thanks, but I am still having problems. I tried making a new simplied form
just for testing purposes. I have included the two relevant tables in the
query the form is based on. Combo Box 1 (cboRegion) allows the user to
select the Region which is stored in the Region field of Table 1. Then the
strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user
select a field office within the Region they just selected. When they do
this, it stores the information in the FieldOfc field in Table One and then
displays the matching information in text boxes 1 and 2.

The problem I am having is that the information in the cboFieldOfc keeps
disappearing when you either:

1. reopen the form; or
2. make a change to the cboRegion in any of the other records.

Currently, I have the code to populate cboFieldofc in the Afterupdate Event
of cboRegion. I have tried putting it elsewhere (Form-OnCurrent,
Form-OnOpen, Form-Afterupdate), but nothing works.

I'm lost here. This is really eating my lunch!

Hi, S.

Yes, the code can go in the On Current event. And, yes, just use another
variable name, like strCBox2SQL. And check out a third-party text written
for Access developers. They should treat the sequence of events thoroughly
there.

HTH
Sprinks

On my form, I have a combo box that looks up Region from a table. In the
Afterupdate event of this combo box, I have the following code:

'Code for Program Manager Information
Dim strSQL as String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
MsgBox strSQL

The above code provides the data for the 2nd combo box, cmbFieldofc which is
bound to the Fieldofc field in table tblCaseInfo. The two text boxes are
setup like so:
=[cmbFieldofc].Column(3)
=[cmbFieldofc].Column(6)

The problem I am having is that when I input the information for the first
time, everything works great. However, when I move to a new record and then
go back, the information is not correct - the 2nd combo box reverts to its
original query information, which messes up the two txt boxes.

So, the question is, what event on which control do I need to add code to?
Is it an event on the Form? If so, I may have a problem as I already
declared "strSQL as String" for a different process on the OnCurrent and
AfterUpdate events on the form control. So, if I have to add the above code
for the two combo boxes, do I just give it a different name (strSQL2)?

Any help is greatly appreciated. I always get lost with regard to which
event to assign code!
TIA

 

[S Jackson]

Thanks for your quick response! Glad to hear I was not alone in the
"confused all-to-hell" place I was in. I have the exact same premise
working on a different form and I have studied and studied it and I can't
see what is different from what I am trying to do now! I guess it will
remain a mystery. (Also, fyi, this is not a continuous form, but is a
single form.)

Your idea to use a textbox bound to the Fieldofc field and the pop-up is
great! That was what I was thinking about trying next, but I wasn't ready
to tackle the coding yet. I'll give your example a whirl and see if I can
get it working.

Many Thanks.
S. Jackson

The Old Disappearing Data! Is this a continuous form? If so, the combo
box cannot simultaneously hold the different populations required for each
chosen Region, so if the selection is not contained within the combo box'
*only* list, it disappears! Confused the hell out of me, too, when I first
encountered it.

The solution is to bind a textbox to your FieldOfc field, and use its
OnGotFocus event to pop up a modal form that contains a single unbound combo
box populated by your SQL string. Use its OnUpdate event to write the
selection to your textbox on the main form, and close the modal form.

Here's an example from one of my applications, where the user selects a
steel size based on the steel type:

' Text box on main form
Private Sub txtSteelSize_GotFocus()
On Error Resume Next

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

End Sub

' Unbound combo box on mini-form
Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub


HTH
Sprinks

Thanks, but I am still having problems. I tried making a new simplied form
just for testing purposes. I have included the two relevant tables in the
query the form is based on. Combo Box 1 (cboRegion) allows the user to
select the Region which is stored in the Region field of Table 1. Then the
strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user
select a field office within the Region they just selected. When they do
this, it stores the information in the FieldOfc field in Table One and then
displays the matching information in text boxes 1 and 2.

The problem I am having is that the information in the cboFieldOfc keeps
disappearing when you either:

1. reopen the form; or
2. make a change to the cboRegion in any of the other records.

Currently, I have the code to populate cboFieldofc in the Afterupdate Event
of cboRegion. I have tried putting it elsewhere (Form-OnCurrent,
Form-OnOpen, Form-Afterupdate), but nothing works.

I'm lost here. This is really eating my lunch!

Hi, S.

Yes, the code can go in the On Current event. And, yes, just use another
variable name, like strCBox2SQL. And check out a third-party text written
for Access developers. They should treat the sequence of events thoroughly
there.

HTH
Sprinks

:

On my form, I have a combo box that looks up Region from a table.

In
the

Afterupdate event of this combo box, I have the following code:

'Code for Program Manager Information
Dim strSQL as String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyTitle, tblSurveyors.Region,

tblSurveyors.SvyPhone
" _

& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
MsgBox strSQL

The above code provides the data for the 2nd combo box, cmbFieldofc which is
bound to the Fieldofc field in table tblCaseInfo. The two text

boxes
are

setup like so:
=[cmbFieldofc].Column(3)
=[cmbFieldofc].Column(6)

The problem I am having is that when I input the information for the first
time, everything works great. However, when I move to a new record

and
then

go back, the information is not correct - the 2nd combo box reverts

to
its

original query information, which messes up the two txt boxes.

So, the question is, what event on which control do I need to add

code
to?

Is it an event on the Form? If so, I may have a problem as I already
declared "strSQL as String" for a different process on the OnCurrent and
AfterUpdate events on the form control. So, if I have to add the

above
code

for the two combo boxes, do I just give it a different name (strSQL2)?

Any help is greatly appreciated. I always get lost with regard to which
event to assign code!
TIA

 

[S Jackson]

I thought I would give you an update since you were trying to help. I gave
up. My initial reason for trying all of this was because I think I have a
design flaw in my database. My central table is tblCaseInfo. I have a 2nd
table called tblSurveyors. The link table b/t them is called
tblCaseSurveyors. tblCaseInfo and tblCaseSurveyors have a one-to-many
relationship. tblCaseInfo also has a one-to-one relationship with tblRegion
which contains the names and addresses of program managers. There can be
several program managers per Region. Originally, I had my form set up with
a combo box to select Region from a separate lookup table, a 2nd combo box
was then populated based on the selection for Region with the corresponding
program managers from tblRegion.

The design flaw I saw in my database was that the information about program
managers contained in tblRegion is also stored in tblSurveyors. So, when a
user wants to update information related to a program manager, they have to
update it in two different places. So, I thought I would try to change my
form by having the 2nd combo box populate with just program managers
(SvyTitle field in tblSurveyors) from tblSurveyors. The first problem was
the empty 2nd combo box when you newly opened the form, or changed the
Region selection in another record. The second problem was it did not
update the txtboxes for name, title, phone number of the program manager.

I am not sure how to fix this. Do you think I am having a problem because
of the relationships already established b/t tblCaseInfo, tblCaseSurveyors
and tblSurveyors? Maybe I need a link table for program managers?.

I'm so confused. I can't imagine how confusing this must be to you!
Therefore, I give up.

Thanks anyway

Thanks for your quick response! Glad to hear I was not alone in the
"confused all-to-hell" place I was in. I have the exact same premise
working on a different form and I have studied and studied it and I can't
see what is different from what I am trying to do now! I guess it will
remain a mystery. (Also, fyi, this is not a continuous form, but is a
single form.)

Your idea to use a textbox bound to the Fieldofc field and the pop-up is
great! That was what I was thinking about trying next, but I wasn't ready
to tackle the coding yet. I'll give your example a whirl and see if I can
get it working.

Many Thanks.
S. Jackson

The Old Disappearing Data! Is this a continuous form? If so, the combo
box cannot simultaneously hold the different populations required for each
chosen Region, so if the selection is not contained within the combo box'
*only* list, it disappears! Confused the hell out of me, too, when I first
encountered it.

The solution is to bind a textbox to your FieldOfc field, and use its
OnGotFocus event to pop up a modal form that contains a single unbound combo
box populated by your SQL string. Use its OnUpdate event to write the
selection to your textbox on the main form, and close the modal form.

Here's an example from one of my applications, where the user selects a
steel size based on the steel type:

' Text box on main form
Private Sub txtSteelSize_GotFocus()
On Error Resume Next

DoCmd.OpenForm _
FormName:="frmSelectSteelSize", _
View:=acNormal, _
WindowMode:=acDialog
Me!txtResult.SetFocus

End Sub

' Unbound combo box on mini-form
Private Sub cboSteelSize_AfterUpdate()
Forms!frmSteelTakeoff!sbfSteelTakeoff.Form!txtSteelSizeID = Me!cboSteelSize
DoCmd.Close
End Sub


HTH
Sprinks

Then

the

strSQL code runs which populates a 2nd combo box (cboFieldofc) so that user
select a field office within the Region they just selected. When they do
this, it stores the information in the FieldOfc field in Table One and then
displays the matching information in text boxes 1 and 2.

The problem I am having is that the information in the cboFieldOfc keeps
disappearing when you either:

1. reopen the form; or
2. make a change to the cboRegion in any of the other records.

Currently, I have the code to populate cboFieldofc in the Afterupdate Event
of cboRegion. I have tried putting it elsewhere (Form-OnCurrent,
Form-OnOpen, Form-Afterupdate), but nothing works.

I'm lost here. This is really eating my lunch!

Hi, S.

Yes, the code can go in the On Current event. And, yes, just use another
variable name, like strCBox2SQL. And check out a third-party text written
for Access developers. They should treat the sequence of events
thoroughly
there.

HTH
Sprinks

:

On my form, I have a combo box that looks up Region from a table. In
the
Afterupdate event of this combo box, I have the following code:

'Code for Program Manager Information
Dim strSQL as String
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone
" _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
MsgBox strSQL

The above code provides the data for the 2nd combo box, cmbFieldofc
which is
bound to the Fieldofc field in table tblCaseInfo. The two text boxes
are
setup like so:
=[cmbFieldofc].Column(3)
=[cmbFieldofc].Column(6)

The problem I am having is that when I input the information for the
first
time, everything works great. However, when I move to a new

record

and

reverts

to

OnCurrent

and