Autofill and second column of combo box failing

[Guest]

Hi all,

The setup is such cboVirus-->cboTest-->cboItem. The latter two combo boxes
are dependent on the preceding one, and have a column count of 2, (bound: 1),
where the second column is the test version or item version number.

When I write the code or go into the Row Source SQL statement and return to
the form, the numbers in the second column appear. And my code appears to
work. But as soon as I change the Virus chosen and/or the Test chosen, the
second column of the combo boxes are full of blanks.


I also would like the second column to autofill the Test Version and Item
Version bound objects on the form. Below is my code.

What is going on and how do I fix it?

Private Sub cboVirus_AfterUpdate()

'Sets up test combo box as being dependent on virus combo box AND limits to
RRT-PCR tests AND Status as In use'
'Note that the query in properties'
Me!cboTest.RowSource = "SELECT Test FROM lstTests WHERE Virus = '" &
cboVirus & "' AND Test Like '*RRT-PCR' AND Status Like 'In Use' "

End Sub

Private Sub cboTest_AfterUpdate()
'Sets up item combo box as being dependent on test combo box and etc'
Me!cboItem.RowSource = "SELECT lstItems.Item FROM lstItems WHERE
lstItems.Test = '" & cboTest & "' AND Status Like 'In Use'"

'Sets up autofill of Test Version'
Me!txtTestVersion.Value = cboTest.Column(1)

End Sub

Private Sub cboItem_AfterUpdate()

'Sets up autofill of Item Version'
Me!txtItemVersion.Value = cboItem.Column(1)

End Sub

Thanks all,
AG

 

[Guest]

dependent on the preceding one, and have a column count of 2, (bound: 1),

That is not what you have shown. In the SQL that you have shown,
the latter two combo boxes only have one column each:
Test,
lstItems.Item

(david)

 

[Guest]

Hi David,

I may not have explained myself well enough (or maybe I have and am
misuderstanding your comment).

The code for the cascading combo boxes is working fine. The combo boxes
dependency is based on the bound column of the preceding combo box
(column(0)) and so this code only references the bound columns.

The unbound columns TestVersion and ItemVersion are to autofill different
objects on the form and it is here that I am having issue. Are you saying I
need to list the unbound columns in the cascading combo box code for the
autofill code to work correctly?

Thanks,
AG

 

[Guest]

You have to include the unbound columns in the
SQL for the combo box.

If they aren't included, they aren't there.

(david)

 

[Guest]

I got it to work David,thanks a bunch, but I'm still a bit muddled as to why
it wasn't working before. If you could answer the following two questions
that would be great.

If you look at my posted select statement I have additional parameters
limiting the combo box, Test Like '*RRT-PCR' AND Status Like 'In Use',
although these fields aren't "included" in the statement prior to WHERE
clause, and yet they are being implemented.

So why can I not refer to the unbound columns without including them in the
SQL statement?

As it is, in the properties of the combo boxes I have the column count as 2.
I also had the same SQL statement, as used in the VBA, as a query in the row
source. This query included the unbound "version" fields, which were
positioned next to the bound field. Are you saying that the VBA overrides
the property window row source query entirely?

I have always wondered about that question--if I write one SQL statement in
the row source of the property window but define a different rowsource in VBA
which one is implemented?

In the end, I deleted the rowsource SQL statements and fleshed out the
SELECT portion of the VBA statements to include the "version" fields and all
appears to be working.

Thanks again David,
AG

 

[Guest]

When you do a select query, the database engine selects and returns
the fields in the first part of the statement.

You can use the rest of the database: you can join, sort, restrict,
count, lookup, or do lots of things with the other fields in the table,
or with fields in other tables.

But as well as doing whatever, a select query returns, as a
recordset, the fields that you listed in the first part of the select
query.

If you want your combo box to use fields selected from the
database, you have to select those fields, so that they are in
the recordset used by the combo box.

You can have a whole database available, and a select query
will select out those records and fields which you want your
combo box to use.

(david)

 

[Guest]

Thanks David, that-maka-da-sense. I did not know about the recordset stuff.

AG