Re: Unexpected nulls in combo box

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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?

"rquintal@xxxxxxxxxxxx" <bob.quintal@xxxxxxxxx> wrote in message
news:1192746878.420081.241000@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Oct 18, 5:11 pm, "Laurel" <FakeM...@xxxxxxxxxxx> wrote:
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),"")



.

Relevant Pages

  • Re: Cohens paper on byte order
    ... > pertain specifically to the order within the input/ ... > output bit sequence. ... Do you mean by 'index' the first row of Fig. 2? ... the value of AES indices had some inherent meaning ...
    (sci.crypt)
  • Re: Writing several images into a single binary file
    ... just with the image size on the first row and then all the pixel values sequentially, through the read/write functions. ... is arbitrary data and can include any arbitrary sequence of bytes. ... But if for some reason you have a compelling reason to invent your own ...
    (comp.programming)
  • Re: how do I quickly insert numbers 1 to 100 in a column in Excel?
    ... - all rows are to be numbered in sequence 1 to 100,,,. ... type "1" in this cell at the first row, then drag down to the last ... from the bottom right corner handle of the first cell. ...
    (microsoft.public.excel.misc)
  • Re: Meaning of the Geological Column
    ... Your first row is a six-letter sequence. ... The organising assumption was that upper layers ... be a logical arrangement of layers, and of how igneous rock might line ...
    (talk.origins)
  • Re: How to populate a Sequence field
    ... You say you want to populate a sequence column but to do that you ... information - the sequence column itself is redundant by definition. ... why the usual recommendation for generating sequences in SQL is either i) do ... it in your client application or reporting tool; ii) do it when you query the ...
    (microsoft.public.sqlserver.programming)