trouble with combo boxes

[ArielZusya]

I've got a form (frmPersonExcuse) which has two combo boxes (cmbExcusalReason
and cmbStage). cmbStage is tied to tblMainEvent.Stage and displays values
from tblStage.Stage. cmbStage is also set .visible=False. (I've got it
invisible because I don't want the user to see it or change it but I want to
be able to refer to it in code... see below. Incidentally, thinking this
might be a problem I tried making it visible and it had no impact on this
problem.) tblMainEvent is linked to tblStage in a one to many relationship
tblMainEvent.Stage to tblStage.ID_Stage. cmbReason is tied to
tblMainEvent.Reason and displays values from tblReason.Reason. cmbReason is
set .visible=True. tblMainEvent is linked to tblReason in a one to many
relationship tblMainEvent.Reason to tblReason.ID_Reason.

tblStage has three values:
1 Pre
2 Individual
3 Group

tblReason has 5 values:
1 N/A
2 Hardship
3 Cause
4 Pros
5 Def

When the form loads I run the following script:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) " & vbCrLf & _
"OR (((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 & strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 & strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****

Note: I added that vbCrLf to strSQLMiddle1 so it would wrap nicely in this
question. In my code I have that all on one line.

When the form loads cmbReason displays blanks where there should be values
in the dropdown list. The number of blanks corresponds to the correct number
of values based on the above queries and cases but no values actually appear.
Further, if I select the blank from the dropdown list that corresponds to
where the value I want should be and then I try to close the form I get the
following error:

"The value you entered isn't valid for this field. For example, you may have
entered text in a numberic field or a number that is larger than the
FieldSize setting permits."

and then when I click OK I get the following error:

"You can't save this record at this time. MyDB may have encounted an error
while trying to save a record. If you close this object now, the data changes
you made will be lost. Do you want to close the database object anyway?"

What have I done now? I thought I was finally getting the hang of all this.
Why is my cmbReason not showing me values? Why can't I select a value and
have it save to tblMainEvent? What little hair I have left is falling out.
Your help is greatly appreciated!

 

[Ken Snell \(MVP\)]

Get rid of the vbCrLf from the SQL statement text. It's completely unneeded,
and can cause some SQL parsing issues.

Are you running ACCESS 2003 SP3? If the field to which the combo box is
bound has a Format property setting for the field in the table, then yes,
this is a bug that has been reported by many ACCESS users during past weeks.
It apparently is a bug in Office 2003 SP3, which was released by MS a short
time ago. MS is aware of the bug and has released a hotfix for it.

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003 Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
--

Ken Snell
<MS ACCESS MVP>

 

[ArielZusya]

Thanks for your message. I'm using 2007... any chance this problem exists in
2007 as well? As for the vbCrLf.. the vbCrLf is not in my code... I only
added it to the code in my question here so my code wouldn't wrap in a
strange way. I tried to explain that in that note just after the code snippet
below... guess I didn't explain that very well... sorry bout that... at any
rate... rest assured... no vbCrLf in my code and so the query seems to be ok.

So... all that said... assuming the problem didn't carry over to 2007 from
2003, is my code otherwise messed up? Is there some way to make all this
work? It's not the end of the world if I can't filter the values in the
combo box based on the other combo box but data integrity would be far
superiour if the user didn't have the ability to enter something erroneous
and so I'd love to be able to limit it here. Thanks for your help!

 

[Ken Snell \(MVP\)]

Thanks for your message. I'm using 2007... any chance this problem exists
in
2007 as well?

No, this bug does not exist in 2007.

As for the vbCrLf.. the vbCrLf is not in my code... I only
added it to the code in my question here so my code wouldn't wrap in a
strange way. I tried to explain that in that note just after the code
snippet
below... guess I didn't explain that very well... sorry bout that... at
any
rate... rest assured... no vbCrLf in my code and so the query seems to be
ok.
Ok.




So... all that said... assuming the problem didn't carry over to 2007 from
2003, is my code otherwise messed up? Is there some way to make all this
work? It's not the end of the world if I can't filter the values in the
combo box based on the other combo box but data integrity would be far
superiour if the user didn't have the ability to enter something erroneous
and so I'd love to be able to limit it here. Thanks for your help!

The code looks fine to me. The error that you're getting suggests a problem
with the design/setup of the form and not a problem with the code. What is
the control source of the cmbReason combo box? What is the bound column of
this combo box? What is the column count property of this combo box? What is
the column widths property of the combo box? What is the row source query of
this combo box? What is the form's recordsource query?
--

Ken Snell
<MS ACCESS MVP>

 

[ArielZusya]

Thanks for your help.

To answer your questions:
The control source of cmbReason is tblMainEvent.ExcusalReason.
The bound column of cmbReason is 1.
The column count property of cmbReason is set to 2.
The column widths property of cmbReason is set to 0";1".
The row source query of cmbReason is dynamically set by the code and depends.
The form's recordsource query is tblMainEvent.

This form is a subform. The main form's recordsouce is tblMain and the
LinkMasterField is tblMain.ID_Main and the LinkChildField is
tblMainEvent.Person.

Incidentally, when I don't use the code and just let the row source query
for cmbReason be:

SELECT [tblReason].[ID_Reason], [tblReason].[Reason] FROM tblReason ORDER BY
[ID_Reason];

I get no errors... but I also lose the control over data integrity (limiting
the list based on the value in that other combo box). Any thoughts on where
I'm going wrong? Thanks!

 

[Ken Snell \(MVP\)]

Post the code that you're using to set the Row Source of the cmbReason combo
box. It would appear that that code is not returning the correct
fields/values for the combo box's list.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your help.

To answer your questions:
The control source of cmbReason is tblMainEvent.ExcusalReason.
The bound column of cmbReason is 1.
The column count property of cmbReason is set to 2.
The column widths property of cmbReason is set to 0";1".
The row source query of cmbReason is dynamically set by the code and
depends.
The form's recordsource query is tblMainEvent.

This form is a subform. The main form's recordsouce is tblMain and the
LinkMasterField is tblMain.ID_Main and the LinkChildField is
tblMainEvent.Person.

Incidentally, when I don't use the code and just let the row source query
for cmbReason be:

SELECT [tblReason].[ID_Reason], [tblReason].[Reason] FROM tblReason ORDER
BY
[ID_Reason];

I get no errors... but I also lose the control over data integrity
(limiting
the list based on the value in that other combo box). Any thoughts on
where
I'm going wrong? Thanks!

No, this bug does not exist in 2007.



The code looks fine to me. The error that you're getting suggests a
problem
with the design/setup of the form and not a problem with the code. What
is
the control source of the cmbReason combo box? What is the bound column
of
this combo box? What is the column count property of this combo box? What
is
the column widths property of the combo box? What is the row source query
of
this combo box? What is the form's recordsource query?

 

[ArielZusya]

Sorry... typo... cmbReason should have been cmbExcusalReason... that code is
called in the form load event as follows:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) OR
(((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 & strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 & strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****


Thanks for your help!

Post the code that you're using to set the Row Source of the cmbReason combo
box. It would appear that that code is not returning the correct
fields/values for the combo box's list.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your help.

To answer your questions:
The control source of cmbReason is tblMainEvent.ExcusalReason.
The bound column of cmbReason is 1.
The column count property of cmbReason is set to 2.
The column widths property of cmbReason is set to 0";1".
The row source query of cmbReason is dynamically set by the code and
depends.
The form's recordsource query is tblMainEvent.

This form is a subform. The main form's recordsouce is tblMain and the
LinkMasterField is tblMain.ID_Main and the LinkChildField is
tblMainEvent.Person.

Incidentally, when I don't use the code and just let the row source query
for cmbReason be:

SELECT [tblReason].[ID_Reason], [tblReason].[Reason] FROM tblReason ORDER
BY
[ID_Reason];

I get no errors... but I also lose the control over data integrity
(limiting
the list based on the value in that other combo box). Any thoughts on
where
I'm going wrong? Thanks!

Thanks for your message. I'm using 2007... any chance this problem
exists
in
2007 as well?

No, this bug does not exist in 2007.


As for the vbCrLf.. the vbCrLf is not in my code... I only
added it to the code in my question here so my code wouldn't wrap in a
strange way. I tried to explain that in that note just after the code
snippet
below... guess I didn't explain that very well... sorry bout that... at
any
rate... rest assured... no vbCrLf in my code and so the query seems to
be
ok.

Ok.




So... all that said... assuming the problem didn't carry over to 2007
from
2003, is my code otherwise messed up? Is there some way to make all
this
work? It's not the end of the world if I can't filter the values in
the
combo box based on the other combo box but data integrity would be far
superiour if the user didn't have the ability to enter something
erroneous
and so I'd love to be able to limit it here. Thanks for your help!

The code looks fine to me. The error that you're getting suggests a
problem
with the design/setup of the form and not a problem with the code. What
is
the control source of the cmbReason combo box? What is the bound column
of
this combo box? What is the column count property of this combo box? What
is
the column widths property of the combo box? What is the row source query
of
this combo box? What is the form's recordsource query?
--

Ken Snell
<MS ACCESS MVP>






:

Get rid of the vbCrLf from the SQL statement text. It's completely
unneeded,
and can cause some SQL parsing issues.

Are you running ACCESS 2003 SP3? If the field to which the combo box
is
bound has a Format property setting for the field in the table, then
yes,
this is a bug that has been reported by many ACCESS users during past
weeks.
It apparently is a bug in Office 2003 SP3, which was released by MS a
short
time ago. MS is aware of the bug and has released a hotfix for it.

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003 Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
--

Ken Snell
<MS ACCESS MVP>




I've got a form (frmPersonExcuse) which has two combo boxes
(cmbExcusalReason
and cmbStage). cmbStage is tied to tblMainEvent.Stage and displays
values
from tblStage.Stage. cmbStage is also set .visible=False. (I've got
it
invisible because I don't want the user to see it or change it but I
want
to
be able to refer to it in code... see below. Incidentally, thinking
this
might be a problem I tried making it visible and it had no impact on
this
problem.) tblMainEvent is linked to tblStage in a one to many
relationship
tblMainEvent.Stage to tblStage.ID_Stage. cmbReason is tied to
tblMainEvent.Reason and displays values from tblReason.Reason.
cmbReason
is
set .visible=True. tblMainEvent is linked to tblReason in a one to
many
relationship tblMainEvent.Reason to tblReason.ID_Reason.

tblStage has three values:
1 Pre
2 Individual
3 Group

tblReason has 5 values:
1 N/A
2 Hardship
3 Cause
4 Pros
5 Def

When the form loads I run the following script:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) " & vbCrLf & _
"OR (((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 &
strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 &
strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****

Note: I added that vbCrLf to strSQLMiddle1 so it would wrap nicely
in
this
question. In my code I have that all on one line.

When the form loads cmbReason displays blanks where there should be
values
in the dropdown list. The number of blanks corresponds to the
correct
number
of values based on the above queries and cases but no values
actually
appear.
Further, if I select the blank from the dropdown list that
corresponds
to
where the value I want should be and then I try to close the form I
get
the
following error:

"The value you entered isn't valid for this field. For example, you
may
have
entered text in a numberic field or a number that is larger than the
FieldSize setting permits."

and then when I click OK I get the following error:

"You can't save this record at this time. MyDB may have encounted an
error
while trying to save a record. If you close this object now, the
data
changes
you made will be lost. Do you want to close the database object
anyway?"

What have I done now? I thought I was finally getting the hang of
all
this.
Why is my cmbReason not showing me values? Why can't I select a
value
and
have it save to tblMainEvent? What little hair I have left is
falling
out.
Your help is greatly appreciated!

 

[Ken Snell \(MVP\)]

What are the fields in tblReason? Are those fields in the order that you
want the combo box to be populated? You're using * instead of listing the
field names explicitly.

Your code likely should run in the form's Current event procedure, not the
form's Load event procedure.

Also, is the logic in the strSQLMiddle2 clause correct? Not equal to 1? How
is that different from the logic in strSQLMiddle1?

--

Ken Snell
<MS ACCESS MVP>

Sorry... typo... cmbReason should have been cmbExcusalReason... that code
is
called in the form load event as follows:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) OR
(((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 & strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 & strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****


Thanks for your help!

Post the code that you're using to set the Row Source of the cmbReason
combo
box. It would appear that that code is not returning the correct
fields/values for the combo box's list.
--

Ken Snell
<MS ACCESS MVP>

Thanks for your help.

To answer your questions:
The control source of cmbReason is tblMainEvent.ExcusalReason.
The bound column of cmbReason is 1.
The column count property of cmbReason is set to 2.
The column widths property of cmbReason is set to 0";1".
The row source query of cmbReason is dynamically set by the code and
depends.
The form's recordsource query is tblMainEvent.

This form is a subform. The main form's recordsouce is tblMain and the
LinkMasterField is tblMain.ID_Main and the LinkChildField is
tblMainEvent.Person.

Incidentally, when I don't use the code and just let the row source
query
for cmbReason be:

SELECT [tblReason].[ID_Reason], [tblReason].[Reason] FROM tblReason
ORDER
BY
[ID_Reason];

I get no errors... but I also lose the control over data integrity
(limiting
the list based on the value in that other combo box). Any thoughts on
where
I'm going wrong? Thanks!


:

Thanks for your message. I'm using 2007... any chance this problem
exists
in
2007 as well?

No, this bug does not exist in 2007.


As for the vbCrLf.. the vbCrLf is not in my code... I only
added it to the code in my question here so my code wouldn't wrap in
a
strange way. I tried to explain that in that note just after the
code
snippet
below... guess I didn't explain that very well... sorry bout that...
at
any
rate... rest assured... no vbCrLf in my code and so the query seems
to
be
ok.

Ok.




So... all that said... assuming the problem didn't carry over to
2007
from
2003, is my code otherwise messed up? Is there some way to make all
this
work? It's not the end of the world if I can't filter the values in
the
combo box based on the other combo box but data integrity would be
far
superiour if the user didn't have the ability to enter something
erroneous
and so I'd love to be able to limit it here. Thanks for your help!

The code looks fine to me. The error that you're getting suggests a
problem
with the design/setup of the form and not a problem with the code.
What
is
the control source of the cmbReason combo box? What is the bound
column
of
this combo box? What is the column count property of this combo box?
What
is
the column widths property of the combo box? What is the row source
query
of
this combo box? What is the form's recordsource query?
--

Ken Snell
<MS ACCESS MVP>






:

Get rid of the vbCrLf from the SQL statement text. It's completely
unneeded,
and can cause some SQL parsing issues.

Are you running ACCESS 2003 SP3? If the field to which the combo
box
is
bound has a Format property setting for the field in the table,
then
yes,
this is a bug that has been reported by many ACCESS users during
past
weeks.
It apparently is a bug in Office 2003 SP3, which was released by MS
a
short
time ago. MS is aware of the bug and has released a hotfix for it.

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003
Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
--

Ken Snell
<MS ACCESS MVP>




message
I've got a form (frmPersonExcuse) which has two combo boxes
(cmbExcusalReason
and cmbStage). cmbStage is tied to tblMainEvent.Stage and
displays
values
from tblStage.Stage. cmbStage is also set .visible=False. (I've
got
it
invisible because I don't want the user to see it or change it
but I
want
to
be able to refer to it in code... see below. Incidentally,
thinking
this
might be a problem I tried making it visible and it had no impact
on
this
problem.) tblMainEvent is linked to tblStage in a one to many
relationship
tblMainEvent.Stage to tblStage.ID_Stage. cmbReason is tied to
tblMainEvent.Reason and displays values from tblReason.Reason.
cmbReason
is
set .visible=True. tblMainEvent is linked to tblReason in a one
to
many
relationship tblMainEvent.Reason to tblReason.ID_Reason.

tblStage has three values:
1 Pre
2 Individual
3 Group

tblReason has 5 values:
1 N/A
2 Hardship
3 Cause
4 Pros
5 Def

When the form loads I run the following script:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) " & vbCrLf
& _
"OR (((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 &
strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 &
strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****

Note: I added that vbCrLf to strSQLMiddle1 so it would wrap
nicely
in
this
question. In my code I have that all on one line.

When the form loads cmbReason displays blanks where there should
be
values
in the dropdown list. The number of blanks corresponds to the
correct
number
of values based on the above queries and cases but no values
actually
appear.
Further, if I select the blank from the dropdown list that
corresponds
to
where the value I want should be and then I try to close the form
I
get
the
following error:

"The value you entered isn't valid for this field. For example,
you
may
have
entered text in a numberic field or a number that is larger than
the
FieldSize setting permits."

and then when I click OK I get the following error:

"You can't save this record at this time. MyDB may have encounted
an
error
while trying to save a record. If you close this object now, the
data
changes
you made will be lost. Do you want to close the database object
anyway?"

What have I done now? I thought I was finally getting the hang
of
all
this.
Why is my cmbReason not showing me values? Why can't I select a
value
and
have it save to tblMainEvent? What little hair I have left is
falling
out.
Your help is greatly appreciated!

 

[ArielZusya]

The table looks like this:

tblReason
.ID_Reason, AutoNumber, PrimaryKey
.Reason, text, holds the various reasons

The reasons in tblReason are:
1 N/A
2 Hardship
3 Cause
4 Pros
5 Def


They are in the table in the order in which I want them. in terms of the
logic in the SQL statments... For this particular form in case 1 and 2 I want
it to show me Hardship and Cause and in case 3 I want it to show me Hardship,
Cause, Pros, and Def. There are other forms that use this table and for
those forms I need all 5 reasons. In terms of the tblReason.* ... should I
change that to tblReason.Reason or tblReason.ID_Reason, tblReason.Reason?

I've tried putting the code into oncurrent instead of onload... but no luck
there either. I noticed over the weekend that the copy of office on my
computer was in dire need of updates (though I'm not authorized to perform
such updates on my own). The tech staffers came in this morning and manually
pushed the updates onto my office. I wonder if some of the trouble I've been
having is as a result of that. That said, this morning when I tried this
again I got nowhere. UG! Anyhow... I'm open to suggestion. Thanks for all
your help.

What are the fields in tblReason? Are those fields in the order that you
want the combo box to be populated? You're using * instead of listing the
field names explicitly.

Your code likely should run in the form's Current event procedure, not the
form's Load event procedure.

Also, is the logic in the strSQLMiddle2 clause correct? Not equal to 1? How
is that different from the logic in strSQLMiddle1?

--

Ken Snell
<MS ACCESS MVP>

Sorry... typo... cmbReason should have been cmbExcusalReason... that code
is
called in the form load event as follows:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) OR
(((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 & strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 & strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****


Thanks for your help!

Post the code that you're using to set the Row Source of the cmbReason
combo
box. It would appear that that code is not returning the correct
fields/values for the combo box's list.
--

Ken Snell
<MS ACCESS MVP>



Thanks for your help.

To answer your questions:
The control source of cmbReason is tblMainEvent.ExcusalReason.
The bound column of cmbReason is 1.
The column count property of cmbReason is set to 2.
The column widths property of cmbReason is set to 0";1".
The row source query of cmbReason is dynamically set by the code and
depends.
The form's recordsource query is tblMainEvent.

This form is a subform. The main form's recordsouce is tblMain and the
LinkMasterField is tblMain.ID_Main and the LinkChildField is
tblMainEvent.Person.

Incidentally, when I don't use the code and just let the row source
query
for cmbReason be:

SELECT [tblReason].[ID_Reason], [tblReason].[Reason] FROM tblReason
ORDER
BY
[ID_Reason];

I get no errors... but I also lose the control over data integrity
(limiting
the list based on the value in that other combo box). Any thoughts on
where
I'm going wrong? Thanks!


:

Thanks for your message. I'm using 2007... any chance this problem
exists
in
2007 as well?

No, this bug does not exist in 2007.


As for the vbCrLf.. the vbCrLf is not in my code... I only
added it to the code in my question here so my code wouldn't wrap in
a
strange way. I tried to explain that in that note just after the
code
snippet
below... guess I didn't explain that very well... sorry bout that...
at
any
rate... rest assured... no vbCrLf in my code and so the query seems
to
be
ok.

Ok.




So... all that said... assuming the problem didn't carry over to
2007
from
2003, is my code otherwise messed up? Is there some way to make all
this
work? It's not the end of the world if I can't filter the values in
the
combo box based on the other combo box but data integrity would be
far
superiour if the user didn't have the ability to enter something
erroneous
and so I'd love to be able to limit it here. Thanks for your help!

The code looks fine to me. The error that you're getting suggests a
problem
with the design/setup of the form and not a problem with the code.
What
is
the control source of the cmbReason combo box? What is the bound
column
of
this combo box? What is the column count property of this combo box?
What
is
the column widths property of the combo box? What is the row source
query
of
this combo box? What is the form's recordsource query?
--

Ken Snell
<MS ACCESS MVP>






:

Get rid of the vbCrLf from the SQL statement text. It's completely
unneeded,
and can cause some SQL parsing issues.

Are you running ACCESS 2003 SP3? If the field to which the combo
box
is
bound has a Format property setting for the field in the table,
then
yes,
this is a bug that has been reported by many ACCESS users during
past
weeks.
It apparently is a bug in Office 2003 SP3, which was released by MS
a
short
time ago. MS is aware of the bug and has released a hotfix for it.

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003
Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
--

Ken Snell
<MS ACCESS MVP>




message
I've got a form (frmPersonExcuse) which has two combo boxes
(cmbExcusalReason
and cmbStage). cmbStage is tied to tblMainEvent.Stage and
displays
values
from tblStage.Stage. cmbStage is also set .visible=False. (I've
got
it
invisible because I don't want the user to see it or change it
but I
want
to
be able to refer to it in code... see below. Incidentally,
thinking
this
might be a problem I tried making it visible and it had no impact
on
this
problem.) tblMainEvent is linked to tblStage in a one to many
relationship
tblMainEvent.Stage to tblStage.ID_Stage. cmbReason is tied to
tblMainEvent.Reason and displays values from tblReason.Reason.
cmbReason
is
set .visible=True. tblMainEvent is linked to tblReason in a one
to
many
relationship tblMainEvent.Reason to tblReason.ID_Reason.

tblStage has three values:
1 Pre
2 Individual
3 Group

tblReason has 5 values:
1 N/A
2 Hardship
3 Cause
4 Pros
5 Def

When the form loads I run the following script:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) " & vbCrLf
& _
"OR (((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 &
strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 &
strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****

Note: I added that vbCrLf to strSQLMiddle1 so it would wrap
nicely
in
this
question. In my code I have that all on one line.

When the form loads cmbReason displays blanks where there should
be
values
in the dropdown list. The number of blanks corresponds to the
correct
number
of values based on the above queries and cases but no values
actually
appear.
Further, if I select the blank from the dropdown list that
corresponds
to
where the value I want should be and then I try to close the form
I
get

 

[ArielZusya]

Like magic, being more specific about the SELECT fixed everything... I
changed tblReason.* to tblReason.ID_Reason, tblReason.Reason and suddenly
everything worked like a charm. Not sure why the wildcard didn't do it for
me but at least everything works now. Thanks so much for all your help!

What are the fields in tblReason? Are those fields in the order that you
want the combo box to be populated? You're using * instead of listing the
field names explicitly.

Your code likely should run in the form's Current event procedure, not the
form's Load event procedure.

Also, is the logic in the strSQLMiddle2 clause correct? Not equal to 1? How
is that different from the logic in strSQLMiddle1?

--

Ken Snell
<MS ACCESS MVP>

Sorry... typo... cmbReason should have been cmbExcusalReason... that code
is
called in the form load event as follows:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) OR
(((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 & strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 & strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****


Thanks for your help!

Post the code that you're using to set the Row Source of the cmbReason
combo
box. It would appear that that code is not returning the correct
fields/values for the combo box's list.
--

Ken Snell
<MS ACCESS MVP>



Thanks for your help.

To answer your questions:
The control source of cmbReason is tblMainEvent.ExcusalReason.
The bound column of cmbReason is 1.
The column count property of cmbReason is set to 2.
The column widths property of cmbReason is set to 0";1".
The row source query of cmbReason is dynamically set by the code and
depends.
The form's recordsource query is tblMainEvent.

This form is a subform. The main form's recordsouce is tblMain and the
LinkMasterField is tblMain.ID_Main and the LinkChildField is
tblMainEvent.Person.

Incidentally, when I don't use the code and just let the row source
query
for cmbReason be:

SELECT [tblReason].[ID_Reason], [tblReason].[Reason] FROM tblReason
ORDER
BY
[ID_Reason];

I get no errors... but I also lose the control over data integrity
(limiting
the list based on the value in that other combo box). Any thoughts on
where
I'm going wrong? Thanks!


:

Thanks for your message. I'm using 2007... any chance this problem
exists
in
2007 as well?

No, this bug does not exist in 2007.


As for the vbCrLf.. the vbCrLf is not in my code... I only
added it to the code in my question here so my code wouldn't wrap in
a
strange way. I tried to explain that in that note just after the
code
snippet
below... guess I didn't explain that very well... sorry bout that...
at
any
rate... rest assured... no vbCrLf in my code and so the query seems
to
be
ok.

Ok.




So... all that said... assuming the problem didn't carry over to
2007
from
2003, is my code otherwise messed up? Is there some way to make all
this
work? It's not the end of the world if I can't filter the values in
the
combo box based on the other combo box but data integrity would be
far
superiour if the user didn't have the ability to enter something
erroneous
and so I'd love to be able to limit it here. Thanks for your help!

The code looks fine to me. The error that you're getting suggests a
problem
with the design/setup of the form and not a problem with the code.
What
is
the control source of the cmbReason combo box? What is the bound
column
of
this combo box? What is the column count property of this combo box?
What
is
the column widths property of the combo box? What is the row source
query
of
this combo box? What is the form's recordsource query?
--

Ken Snell
<MS ACCESS MVP>






:

Get rid of the vbCrLf from the SQL statement text. It's completely
unneeded,
and can cause some SQL parsing issues.

Are you running ACCESS 2003 SP3? If the field to which the combo
box
is
bound has a Format property setting for the field in the table,
then
yes,
this is a bug that has been reported by many ACCESS users during
past
weeks.
It apparently is a bug in Office 2003 SP3, which was released by MS
a
short
time ago. MS is aware of the bug and has released a hotfix for it.

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003
Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
--

Ken Snell
<MS ACCESS MVP>




message
I've got a form (frmPersonExcuse) which has two combo boxes
(cmbExcusalReason
and cmbStage). cmbStage is tied to tblMainEvent.Stage and
displays
values
from tblStage.Stage. cmbStage is also set .visible=False. (I've
got
it
invisible because I don't want the user to see it or change it
but I
want
to
be able to refer to it in code... see below. Incidentally,
thinking
this
might be a problem I tried making it visible and it had no impact
on
this
problem.) tblMainEvent is linked to tblStage in a one to many
relationship
tblMainEvent.Stage to tblStage.ID_Stage. cmbReason is tied to
tblMainEvent.Reason and displays values from tblReason.Reason.
cmbReason
is
set .visible=True. tblMainEvent is linked to tblReason in a one
to
many
relationship tblMainEvent.Reason to tblReason.ID_Reason.

tblStage has three values:
1 Pre
2 Individual
3 Group

tblReason has 5 values:
1 N/A
2 Hardship
3 Cause
4 Pros
5 Def

When the form loads I run the following script:

'***Start Code ***

Private Sub Form_Load()
Dim strSQLHolder As String
Dim strSQLStarter As String
Dim strSQLMiddle1 As String
Dim strSQLMiddle2 As String
Dim strSQLEnder As String

strSQLStarter = "SELECT tblReason.* FROM tblReason "
strSQLMiddle1 = "WHERE (((tblReason.ID_Reason)=2)) " & vbCrLf
& _
"OR (((tblReason.ID_Reason)=3)) "
strSQLMiddle2 = "WHERE (((tblReason.ID_Reason)<>1)) "
strSQLEnder = "ORDER BY tblReason.ID_Reason;"

Select Case Me.cmbStage
Case 1, 2 'Pre & Individual
strSQLHolder = strSQLStarter & strSQLMiddle1 &
strSQLEnder
Case 3 'Group
strSQLHolder = strSQLStarter & strSQLMiddle2 &
strSQLEnder
End Select
Me.cmbExcusalReason.RowSource = strSQLHolder
End Sub

'***End Code ****

Note: I added that vbCrLf to strSQLMiddle1 so it would wrap
nicely
in
this
question. In my code I have that all on one line.

When the form loads cmbReason displays blanks where there should
be
values
in the dropdown list. The number of blanks corresponds to the
correct
number
of values based on the above queries and cases but no values
actually
appear.
Further, if I select the blank from the dropdown list that
corresponds
to
where the value I want should be and then I try to close the form
I
get