using selection from combo box as input to a 2d combo box

[tonyaims]

I have materials that can be put into a PO grouped into
classe - doors; windows; sealants; etc. In a PO form, I
want the user to select a material group from a combo box
and use that selection in a 2d combo box to list all of
the materials in the selected group.
I used a wizard in the first combo box and selected:
"Remember the value for later use"

But I cannot figure out how to specify to Access to use
that value in the query that access builds to get the
values for the second combo box.

Can anyone help?

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[tonyaims]

To: Allen Browne
First of all thanks for your suggestion on Saturday(9/4).
I modelled the sub below after the suggestions in:

Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

Private Sub Combo26_AfterUpdate()
Dim strSQL As String
strSQL = "Select tblMaterialDesc.MaterialDesc"
strSQL = strSQL & " FROM tblMaterialDesc"
strSQL = strSQL & " WHERE tblMaterialDesc.MaterialGrp
= " & Me!Combo26
strSQL = strSQL & " ORDER BY
tblMaterialDesc.MaterialDesc"
Me!Combo28.RowSourceType = "Table/Query"
Me!Combo28.RowSource = strSQL
End Sub

In my form Combo26 is the same as Combo1 and Combo28
is the same as Combo2. When I run the form, no data(ie: no
material descriptions) appear in Combo28. I checked the
properties of Combo28. The following query was in
RowSource:

SELECT tblMaterialDesc.MaterialDesc
FROM tblMaterialDesc
WHERE (((tblMaterialDesc.MaterialGrpID)=1))
ORDER BY tblMaterialDesc.MaterialDesc;

The query is consistent with strSQL that was built in
Combo26. Me!Combo26 has been replaced with 1 which is the
value for the Material Group that I chose. MaterialGrpID
is an autonumbered index. Index value 1 represents the
material group "glass" so I expected all materials in the
material group "glass" to appear as choices in Combo28.

Just to keep it simple, I changed strSQL to exclude the
WHERE and the ORDER BY clauses. When I reran the form I
expected to get all materials listed but Combo28 was still
blank. The query in rowsource was:

SELECT tblMaterialDesc.MaterialDesc
FROM tblMaterialDesc

Any suggestions as to what I am doing wrong?

Thanks,

Tony

 

[Allen Browne]

Hi Tony

Are we aiming to:
a) limit the drop-down choices in combo28, so the user is only offered the
choices appropriate to the MaterialGrp chosen in Combo26, or
b) assign a value to Combo28, when the user choices a MaterialGrp in
Combo26.

If a), I can't see much wrong with what you did (though assigning the
RowSourceType again is not necessary).

If b), you need to assign a value to the combo instead of setting its
RowSource. In the AfterUpdate event of Combo26, do something like this:
Me.Combo28 = ...
where the "..." may involve some kind of DLookup() to get the desired value,
or might refer to .ItemData(0) if you want to assign the first value.

I assumed you were aiming for a), which does not put a value into the combo,
but limits the choices available to the user.

 

[Guest]

Alan -

My aim is a) Only those specific materials that are in
the group selected in combo26 are intended to appear in
the drop down list of combo28. The selection the user
makes from the limited list offered in combo28 is
intended to be saved as the specific material selected
for a PO.

FYI. I copied(to the clipboard) the query that is built
in combo26 and placed in rowsource of combo28 and pasted
it into a test query. When I run the test query, it
generates the expected result. I must have something set
wrong in my form since it does not produce any result at
all.

Since nothing appeared in the drop down list for combo28,
is there some way I can turn debugging on to find out why
nothing appeared?

Thanks for your continued suggestions,

Tony

 

[Allen Browne]

At the top of the procedure, add:
Debug.Print "Running Combo26_AfterUpdate at " & Now()

At the end of the procedure, add:
Debug.Print strSQL

Open your form, and change combo26.
Open the Immediate Window (Ctrl+G).
If the code is running, you should see 2 lines.
Check that the 2nd one is correct.

If that appears to work, enter this into the Immediate Window to check that
combo28 had its RowSource updated correctly:
? Forms("WhateverYourFormIsCalledHere").Combo28.RowSource

 

[tonyaims]

Alan - Forgot to include my name on my previous post. My
answers to your questions are below...

-----Original Message-----
Alan -

My aim is a) Only those specific materials that are in
the group selected in combo26 are intended to appear in
the drop down list of combo28. The selection the user
makes from the limited list offered in combo28 is
intended to be saved as the specific material selected
for a PO.

FYI. I copied(to the clipboard) the query that is built
in combo26 and placed in rowsource of combo28 and pasted
it into a test query. When I run the test query, it
generates the expected result. I must have something set
wrong in my form since it does not produce any result at
all.

Since nothing appeared in the drop down list for combo28,
is there some way I can turn debugging on to find out why
nothing appeared?

Thanks for your continued suggestions,

Tony

-----Original Message-----
Hi Tony

Are we aiming to:
a) limit the drop-down choices in combo28, so the user is only offered the
choices appropriate to the MaterialGrp chosen in Combo26, or
b) assign a value to Combo28, when the user choices a MaterialGrp in
Combo26.

If a), I can't see much wrong with what you did (though assigning the
RowSourceType again is not necessary).

If b), you need to assign a value to the combo instead of setting its
RowSource. In the AfterUpdate event of Combo26, do something like this:
Me.Combo28 = ...
where the "..." may involve some kind of DLookup() to get the desired value,
or might refer to .ItemData(0) if you want to assign

the
first value.

I assumed you were aiming for a), which does not put a value into the combo,
but limits the choices available to the user.

 

[tonyaims]

Alan -

All looks OK with the code. I even copied and pasted the
string strSQL into the query builder and ran the query. It
worked fine. I did this while the debugger was active so
the context would be equivalent.

I did notice something strange. In the table referenced by
the constructed query, some groups have many items(>50)
and others only a few(<5). In my testing, if I select a
large group(# items>50) in combo26, when I click to open
the drop down list in combo28 a window with a scroll bar
opens but has no items in the list(ie: the list is empty
even tho there is a scroll bar). Then if I rerun the test
and select a small group(# items<5) in combo26, when I
click to open the drop down list in combo28 a window opens
without a scroll bar. This tells me that the query is
working at least to the point of determining how many
items are in the group but not listing the text in the
drop down window that opens.

Any ideas?

Thanks,

Tony

 

[Allen Browne]

Ok, Tony, you know the code is running, is generating the correct SQL
statement, and that is works when only a few records are returned. That's
good progress.

There is a setting under:
Tools | Options | Edit/Find
that affects which lists are displayed, and how many records are read, so
you might want to check those settings. However, if it is missing with as
few as 50, I doubt is the issue.

We did see this once, where the lists did not populate reliably with say 50
or more. The scenario was an unpatched Access 2000, drawing data from a very
busy old WinNT server. There was other evidence that it was a timing issue,
e.g. forms intermittently showed #Error for calculated fields. Applying the
service packs for JET 4 and also for Office 2000 reduced the frequency of
the problem but did not eliminate it completely. We ended up moving the back
end database off the server (200m away in another building) and onto one of
the local workstations. I don't know if any of that will help, but it would
certainly be worth checking that you have Service Pack 8 for JET 8. Locate
msjet40.dll (typically in windows\system32), right-click, and choose
Properties. On the Version tab, you should see 4.0.8xxx, where the 8 is the
important number.

You could also test if the network timing is contributing to the issue by
moving the back end file onto the local hard disk, and reconnecting your
linked tables.

There is another minor issue where the selected value in the combo may not
show after you change its RowSource. That happens if the combo's bound
column is zero-width, and the selected value is no longer in the RowSource:
understandably, there is nothing Access can show in that case, so you may
want to set the Value of the combo to Null when you assign the RowSource.

 

[Guest]

Thanks for the suggestions. I have been away on a business
trip since early morning 9/7 and have not been able to try
any of your suggestions yet.

One point. There is NO data returned in either the <5
group or the >50 group. The drop down window is clear white
(no data). The difference in the 2 cases is that <5 has no
scroll bar and >50 has a scroll bar. So I conclude the
query works to the extent of knowing how many records are
returned.

I will be back in my office tomorrow and will try your
suggestions.

Thanks again,

Tony

 

[Allen Browne]

Hmm. So it is returning records, but nothing is showing up?

In that case, you may want to check the RowSource, Column Count, and Column
Widths property to see which column it is supposed to be showing.

All the best for tomorrow's troubleshoot on this.

 

[Guest]

Thanks. Will let you know how it works out...

-----Original Message-----
Hmm. So it is returning records, but nothing is showing up?

In that case, you may want to check the RowSource, Column Count, and Column
Widths property to see which column it is supposed to be showing.

All the best for tomorrow's troubleshoot on this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.




.

 

[tonyaims]

Allen,

The Column Widths property was set to 0";1". I set it to
1";0", 2";0" and 0.5";0" and they all worked. Apparently,
any non-zero value for the first Column Widths setting
works.

Another question: If I set the Limit to List property to
No and the user types in something that is not in the
list, will access add the text as a new record in the
table used to populate the combo box?

Thanks for your help. I learned a lot about how both
Access works and how VB interacts with it.

Thanks again,

Tony

 

[Allen Browne]

No, setting Limit To List to No will not automatically create an entry in
the lookup table that feeds the combo's RowSource. In fact, if you have a
relation between the 2 tables with Referential Integrity, you will not be
allowed to make that entry.

To add an entry to the lookup table, see:
NotInList: Adding values to lookup tables
at:
http://members.iinet.net.au/~allenbrowne/ser-27.html
or simply use the combo's DblClick event to open the form where the entry
can be made (and then Requery the combo in that form's AfterUpdate event.)