Unexpected nulls in combo box

[Laurel]

I have a form with a combo box whose row source is as follows.

SELECT DISTINCT tblAcademics.Description, tblAcademics.Sequence FROM
tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND ((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from tblAcademics ORDER
BY tblAcademics.Sequence;

Whenever this retrieves a result set where the first row has an empty string
("") in the tblAcademics.Description column (as verified by executing the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up when
is_PreviousDescription is set to txtDescription. txtDescription and
txtSequence are text boxes. Note that in those cases where Description is
an empty string, Sequence is always 1 or 2. There are no rows in
tblAcademics where Description is null. There are no rows in tblAcademics
where Sequence is null. The code works fine when there is data in the
description column. Any ideas?

ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence

 

[rquintal]

I have a form with a combo box whose row source is as follows.

SELECT DISTINCT tblAcademics.Description, tblAcademics.Sequence FROM
tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND ((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from tblAcademics ORDER
BY tblAcademics.Sequence;

Whenever this retrieves a result set where the first row has an empty string
("") in the tblAcademics.Description column (as verified by executing the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up when
is_PreviousDescription is set to txtDescription. txtDescription and
txtSequence are text boxes. Note that in those cases where Description is
an empty string, Sequence is always 1 or 2. There are no rows in
tblAcademics where Description is null. There are no rows in tblAcademics
where Sequence is null. The code works fine when there is data in the
description column. Any ideas?

ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence

The first thing to do is clean up the upper portion of your query.
There is no join, you are using where clauses to simulate the join.
You have unqualified references to txtGrade_Date, cboClass_Code,
cboSubject, cboType. Qualify them by prefixing the name of the form:
Forms!FormName!txtGrade_Date

You will aslo want to bulletproof the code by wrapping the
combobox.Column() references with a nz(_ function, e.g. txtDescription
= nz( cboDescAndSeq.Column(0),"")

 

[Laurel]

Well, using the NZ lets me trap the problem, but it doesn't solve my
problem. The first row in the select has a 1 in the sequence column when it
is executed in the query window. But it is null in my dropdown. It's not
just Description which is null, it's the Sequence column as well. I have
107 rows where there's an empty description, and they all have good numbers
in the Sequence column. How can I get that sequence number to show up in
the dropdown Column(2)????????

As for your other comments, could you explain why using the Where clause
instead of a join is a bad idea? Also, what is the down side of referencing
the controls wiithout all the extra verbiage if the code is part of the
form, not in a separate module?

I have a form with a combo box whose row source is as follows.

SELECT DISTINCT tblAcademics.Description, tblAcademics.Sequence FROM
tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND
((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from tblAcademics
ORDER
BY tblAcademics.Sequence;

Whenever this retrieves a result set where the first row has an empty
string
("") in the tblAcademics.Description column (as verified by executing the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up when
is_PreviousDescription is set to txtDescription. txtDescription and
txtSequence are text boxes. Note that in those cases where Description
is
an empty string, Sequence is always 1 or 2. There are no rows in
tblAcademics where Description is null. There are no rows in
tblAcademics
where Sequence is null. The code works fine when there is data in the
description column. Any ideas?

ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence

The first thing to do is clean up the upper portion of your query.
There is no join, you are using where clauses to simulate the join.
You have unqualified references to txtGrade_Date, cboClass_Code,
cboSubject, cboType. Qualify them by prefixing the name of the form:
Forms!FormName!txtGrade_Date

You will aslo want to bulletproof the code by wrapping the
combobox.Column() references with a nz(_ function, e.g. txtDescription
= nz( cboDescAndSeq.Column(0),"")

 

[Laurel]

I "solved" the problem by adding the following kluge. I'd still like to
know if this has happened to anyone else. (Note - I re-created the dropdown
from scratch, in case it was corrupted somehow, but that didn't help.)

The dropdown is correctly loaded with all expected rows, including the one
with the empty description.

If txtSequence = "" Then 'This happens when the description is
empty - don't know why
Set lrst_dropdown = Recordset
ls_sql = "SELECT DISTINCT tblAcademics.Description,
tblAcademics.Sequence " _
& " FROM tblAcademics, tblStudents " _
& " WHERE (((tblAcademics.Student_id)=
[tblstudents].[Student_id]" _
& ") AND ((tblAcademics.Assessment_Date)= #" & [txtGrade_Date] _
& "#) AND ((tblStudents.Class_Code)= '" & [cboClass_code] _
& "') AND ((tblAcademics.Subject)= '" & [cboSubject] _
& "') AND ((tblAcademics.Type)= '" & [cboType] _
& "')) UNION select 'New' as NewChoice, 100 as NewSequence from
tblAcademics " _
& " ORDER BY tblAcademics.Sequence"
Set lrst_dropdown = CurrentDb.OpenRecordset(ls_sql)
lrst_dropdown.MoveLast
lrst_dropdown.MoveFirst
txtSequence = lrst_dropdown!Sequence
End If

 

[rquintal]

Well, using the NZ lets me trap the problem, but it doesn't solve my
problem. The first row in the select has a 1 in the sequence column when it
is executed in the query window. But it is null in my dropdown. It's not
just Description which is null, it's the Sequence column as well. I have
107 rows where there's an empty description, and they all have good numbers
in the Sequence column. How can I get that sequence number to show up in
the dropdown Column(2)????????



As for your other comments, could you explain why using the Where clause
instead of a join is a bad idea? Also, what is the down side of referencing
the controls wiithout all the extra verbiage if the code is part of the
form, not in a separate module?

Using a where clause instead of a join is not bad, it's that a join is
more efficient.
As to referencing the form, if you paste your SQL into a query, it
won't work. The query will not assume which form is to supply the
data, even if the query is bound to the form. The way you presented
the query implied that the query was set up that way, not dynamically
built in code and using a recordset object as you show in your other
post. To be honest, you are not passing the control's name at all,
your code embeds the actual value into the query. set a debug.print
ls_sql before opening the recordset and you will see.

Q

I have a form with a combo box whose row source is as follows.
SELECT DISTINCT tblAcademics.Description, tblAcademics.Sequence FROM
tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND
((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from tblAcademics
ORDER
BY tblAcademics.Sequence;
Whenever this retrieves a result set where the first row has an empty
string
("") in the tblAcademics.Description column (as verified by executing the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up when
is_PreviousDescription is set to txtDescription. txtDescription and
txtSequence are text boxes. Note that in those cases where Description
is
an empty string, Sequence is always 1 or 2. There are no rows in
tblAcademics where Description is null. There are no rows in
tblAcademics
where Sequence is null. The code works fine when there is data in the
description column. Any ideas?
ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence

The first thing to do is clean up the upper portion of your query.
There is no join, you are using where clauses to simulate the join.
You have unqualified references to txtGrade_Date, cboClass_Code,
cboSubject, cboType. Qualify them by prefixing the name of the form:
Forms!FormName!txtGrade_Date

You will aslo want to bulletproof the code by wrapping the
combobox.Column() references with a nz(_ function, e.g. txtDescription
= nz( cboDescAndSeq.Column(0),"")

 

[Laurel]

Using a where clause instead of a join is not bad, it's that a join is
more efficient.
As to referencing the form, if you paste your SQL into a query, it
won't work. The query will not assume which form is to supply the
data, even if the query is bound to the form. The way you presented
the query implied that the query was set up that way, not dynamically
built in code and using a recordset object as you show in your other
post. To be honest, you are not passing the control's name at all,
your code embeds the actual value into the query. set a debug.print
ls_sql before opening the recordset and you will see.

When I pasted the query into the query window I substituted the values that
were in the form for the references to the controls.
I actually did look at the rowsource. That's why I have this line of
code... so I could look at it in the debugger.
ls_debug = cboDescAndSeq.RowSource

Do you have any ideas why the sequence value is null when the description is
empty? Even though in the tblAcademics table, the sequence has a value of
1????

Q

I have a form with a combo box whose row source is as follows.

SELECT DISTINCT tblAcademics.Description, tblAcademics.Sequence FROM
tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND
((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from tblAcademics
ORDER
BY tblAcademics.Sequence;

Whenever this retrieves a result set where the first row has an empty
string
("") in the tblAcademics.Description column (as verified by executing
the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up when
is_PreviousDescription is set to txtDescription. txtDescription and
txtSequence are text boxes. Note that in those cases where
Description
is
an empty string, Sequence is always 1 or 2. There are no rows in
tblAcademics where Description is null. There are no rows in
tblAcademics
where Sequence is null. The code works fine when there is data in the
description column. Any ideas?

ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence

The first thing to do is clean up the upper portion of your query.
There is no join, you are using where clauses to simulate the join.
You have unqualified references to txtGrade_Date, cboClass_Code,
cboSubject, cboType. Qualify them by prefixing the name of the form:
Forms!FormName!txtGrade_Date

You will aslo want to bulletproof the code by wrapping the
combobox.Column() references with a nz(_ function, e.g. txtDescription
= nz( cboDescAndSeq.Column(0),"")

 

[Bob Quintal]

When I pasted the query into the query window I substituted the
values that were in the form for the references to the controls.
I actually did look at the rowsource. That's why I have this line
of code... so I could look at it in the debugger.
ls_debug = cboDescAndSeq.RowSource

So you set a variable, that does not mean that you actually look at
it, and if the rowsource is set to a querydef and not a sql
stattement, all it will return is the name of that query, which
won't tell us much.

Do you have any ideas why the sequence value is null when the
description is empty? Even though in the tblAcademics table, the
sequence has a value of 1????

Without a full data set, I can only guess that something in table
students or one of the textboxes is forcing a null record.

Q

Q

I have a form with a combo box whose row source is as
follows.

SELECT DISTINCT tblAcademics.Description,
tblAcademics.Sequence FROM tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND
((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from
tblAcademics ORDER
BY tblAcademics.Sequence;

Whenever this retrieves a result set where the first row has
an empty string
("") in the tblAcademics.Description column (as verified by
executing the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up
when is_PreviousDescription is set to txtDescription.
txtDescription and txtSequence are text boxes. Note that in
those cases where Description
is
an empty string, Sequence is always 1 or 2. There are no
rows in tblAcademics where Description is null. There are no
rows in tblAcademics
where Sequence is null. The code works fine when there is
data in the description column. Any ideas?

ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence

The first thing to do is clean up the upper portion of your
query. There is no join, you are using where clauses to
simulate the join. You have unqualified references to
txtGrade_Date, cboClass_Code, cboSubject, cboType. Qualify
them by prefixing the name of the form:
Forms!FormName!txtGrade_Date

You will aslo want to bulletproof the code by wrapping the
combobox.Column() references with a nz(_ function, e.g.
txtDescription = nz( cboDescAndSeq.Column(0),"")

 

[rquintal]

When I pasted the query into the query window I substituted the values that
were in the form for the references to the controls.
I actually did look at the rowsource. That's why I have this line of
code... so I could look at it in the debugger.
ls_debug = cboDescAndSeq.RowSource

So you set a variable, if the rowsource is set to a querydef and not
a sql
stattement, all it will return is the name of that query, which
won't tell us much.

Do you have any ideas why the sequence value is null when the description is
empty? Even though in the tblAcademics table, the sequence has a value of
1????

Without a full data set, I can only guess that something in table
students or one of the textboxes is forcing a null record.

Q

I have a form with a combo box whose row source is as follows.
SELECT DISTINCT tblAcademics.Description, tblAcademics.Sequence FROM
tblAcademics, tblStudents WHERE
(((tblAcademics.Student_id)=[tblStudents].[Student_id]) AND
((tblAcademics.Assessment_Date)=[txtGrade_Date]) AND
((tblStudents.Class_Code)=[cboClass_Code]) AND
((tblAcademics.Subject)=[cboSubject]) AND
((tblAcademics.Type)=[cboType]))
UNION select "New" as NewChoice, 100 as NewSequence from tblAcademics
ORDER
BY tblAcademics.Sequence;
Whenever this retrieves a result set where the first row has an empty
string
("") in the tblAcademics.Description column (as verified by executing
the
rowsource SQL in as a query, bot cboDescAndSeq.Column(0) and
cboDescAndSeq.Column(1) are null, and the script blows up when
is_PreviousDescription is set to txtDescription. txtDescription and
txtSequence are text boxes. Note that in those cases where
Description
is
an empty string, Sequence is always 1 or 2. There are no rows in
tblAcademics where Description is null. There are no rows in
tblAcademics
where Sequence is null. The code works fine when there is data in the
description column. Any ideas?
ls_debug = cboDescAndSeq.RowSource
cboDescAndSeq.Requery
cboDescAndSeq = cboDescAndSeq.ItemData(0)
txtDescription = cboDescAndSeq.Column(0)
txtSequence = cboDescAndSeq.Column(1)
is_PreviousDescription = txtDescription
ii_PreviousSequence = txtSequence
The first thing to do is clean up the upper portion of your query.
There is no join, you are using where clauses to simulate the join.
You have unqualified references to txtGrade_Date, cboClass_Code,
cboSubject, cboType. Qualify them by prefixing the name of the form:
Forms!FormName!txtGrade_Date
You will aslo want to bulletproof the code by wrapping the
combobox.Column() references with a nz(_ function, e.g. txtDescription
= nz( cboDescAndSeq.Column(0),"")