Null is not 0! Null means, essentially, "unknown". Please review the
following information to be sure you are using Null in the way you
intend:
http://allenbrowne.com/casu-11.html
Note the link at the bottom of the page to "Commor Errors with Null".
Under what circumstances is cboItemNo blank *after* you update it? It
could
happen when you clear the combo box after previously having made a
selection, but is that the intention? In any case, is it really an empty
string at that time, or is it Null?
Is txtitemdesc a bound control? If not, try setting it to "" instead of
Null. I'm not sure it will make a difference, but refer back to the
information in the link before you assign a value of Null to something.
The column count for cboItemNo (on the Format tab of the combo box
property
sheet) needs to be 3. Is it? Set the Colum Widths to 1.5",0";0" if you
want to see just the first column (use whatever you want for the first
column), but you cannot reference column(2) if it is not there. Open the
Row Source query to be sure it contains the expected information. As it
stands, Item_Number is the first column (Column(0)), Item_Description the
second (Column(1)), and Total from your query (a calculated field,
right?)
is the third (Column(2)).
Remember that this will apply only after the update of cboItemNo. If you
want to see the correct information when navigating through the records
you
will need to use the code again in the form's Current event. If the text
boxes are unbound you may do better just to set their Control Source to:
=[cboItem].Column(1) and:
=[cboItem].Column(2)
In this case you don't need After Update code at all. However, if you
are
storing the values (which you probably shouldn't be), the After Update
event
is correct.
Post back if this doesn't get you clower to where you need to be.
Monica said:
You are right, I have now changed them to text boxes and have locked
them.
I
possibly do not need to check for Null except for on the Annualised
Volume
as
there is a likelihood of this being zero. The statement I am now using
is
Private Sub cboItemNo_AfterUpdate()
If Me.cboItemNo = "" Then
Me.txtitemdesc = Null
Me.txtAnnualisedVolume = 0
Else
Me.txtitemdesc = Me.cboItemNo.Column(1)
Me.txtAnnualisedVolume = Nz(Me.cboItemNo.Column(2), 0)
End If
The row source details of cboitemno are
SELECT ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.ITEM_NUMBER,
ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.ITEM_DESCRIPTION_1,
qryTotalUsedYear.Total FROM ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE LEFT
JOIN
qryTotalUsedYear ON
ACCOUNTING_SYSTEM_STOCK_CONTROL_FILE.ITEM_NUMBER=qryTotalUsedYear.ItemNumber;
Thanks for your help so far, hopefully this bit of information can
assist
further
Regards
Monica
:
I'm curious as to why you are assigning a value to a combo box in this
way.
Can the user change cboItemDesc or cboAnnualisedVolume after changing
cboItemNo? Why not assign the value to a text box? Further, if the
text
box values are dependent on the combo box selection, it may be best to
lock
the text boxes.
Assuming there is a reason for using combo boxes, the next thing is to
make
sure you know combo box column numbering starts with 0, so that 1 is
actually the second column.
When you assign a value to a combo box you are assigning the value to
the
bound column. If there are two columns in cboAnnualisedVolume, and
the
first column is the bound column, does assigning 0 to the combo box
result
in a record from the combo box row source?
Why use Nz for cboItemNo.Column(1)? Does the row source for cboItemNo
include nulls or empty strings in the second column? Also, why test
for
no
value in cboItemNo? Does your row source contain null values or empty
strings in the bound column?
If cboItemDesc contains a value, do you wish to reset it to Null if
there
is
no value in the second column (Column(1)) of your cboItemNo selection?
Again, what is the point of combo boxes if they can be reset by a
selection
in another combo box?
In general you can combine the two expressions:
If Me.cboItemNo = "" Then
Me.cboItemDesc = Null
Me.cboAnnualisedVolume = 0
Else
Me.cboItemDesc = Me.cboItemNo.Column(1)
Me.cboAnnualisedVolume = Me.cboItemNo.Column(2)
End If
If you are still having problems, and if the questions above don't
affect
how you think about this, provide some details about the row source
for
cboItemNo, and for the format of the other combo boxes (bound columns,
column count, etc.)
Hello all, I am trying to load a result into two fields once the
first
field
has been selected in a form. The first field which should return a
text
result is working fine however the second result which is a number
field
is
not working. I am sure it has something to do with this but not sure
what
I
have to write.
What I have written is
Private Sub cboitemno_afterUpdate()
If Nz(Me.cboItemNo, "") = "" Then
Me.cboitemdesc = Null
Else
Me.cboitemdesc = Nz(Me.cboItemNo.Column(1), 0)
End If
If Nz(Me.cboItemNo, "") = "" Then
Me.cboAnnualisedVolume = 0
Else
Me.cboAnnualisedVolume = Me.cboItemNo.Column(2)
End If
End Sub
