Why is this an error?

[Marshall Barton]

Fair enough, I'm sorry. I have a user form it has total 8 comboboxes.
All of which have either a value list or a table of selections
attached to it. The form is 100% modeled after Allen Brown's Search
Sample database

http://www.allenbrowne.com/ser-62.html

You can select a selection in one combobox or two or all and when you
click the filter button, it filters out all the information and gives
you just what you selected above. I copied all of his code and put my
information in it to make the filter work. And it does. Since all the
cbox's work properly I want to take some of the combo boxes to another
level.

Currently with no modifications from this post I can select cboxBranch
and select a branch # 8103 and run filter and all records with that
branch 8103 will pop up below. If I reset the filter and start over I
can select cboxVendor only; if I use vendor # 123456 then all branches
with #123456 attached to it will come up. This works fine.

cboxbranch has a table that lists just numerical branch #'s and branch
names. i.e. 8103, Denver Bread; this is rowsource
cboxvendor has a table that lists just numerical vendor # and vendor
names i.e 123456, Tina's Bakery; this is rowsource

Like in Allen brown's example you can click one cbox to all cbox's and
run the filter, some could be null and the filter would still run.

My question is IF I choose cbox branch first i.e. 8103 and run filter
all records for 8103 come up. OK now if I go to cboxvendor now with
8103 records still filtered, I would like the vendors in cboxvendors
not to show the entire list but to know that 8103 in cboxbranch is
choosen and only give me the vendors that 8103 uses to choose from. If
I reset the filter to stratch, then I would like the cboxvendor to
list all vendors for all branches as default, because sometimes I want
to see all brances the vendors supply us. This help at all?

So, in summary, the cboxvendors I want to default to master vendor
list, if a branch is selected first before a person goes to choose
vendor then in cboxvendor a partial vendor list will come up only for
that branch selected.

If I understand all that, Doug already provided a way to to
get whant you want. As Doug said, make sure cboxBranch's
BoundColumn is set so it corresponds with the en_vend_key
field.

 

[ryan.fitzpatrick3]

Doug,

I got it to work how I wanted to, what I did is use this.

Private Sub cboxBranch_AfterUpdate()
Dim strSQL As String
If Nz(Me!cboxBranch, 0) = 0 Then
' If the combo is Null, use the whole table as the RecordSource.
Me!cboxVendor.RowSource = "tblAdageVendors"
Else

Me!cboxVendor.RowSource = "qryvendorbranch"
End If
End Sub

I saved the sql statement as a query, and i'm just using the query. in
place of a sql string.

Also i have a reset filter button so I put this in Me!
cboxVendor.RowSource = "tblAdageVendors"

and it works. I have one question though. how do I change bound column
and column's and column's widths, because between the two seperate
rowsources, one has column bound 1, width 1, column 1, and the other
has column bound 2, width 0;1 and column 2. how can i program this
into the vba code?

ryan