Auto populate field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings all. The following works very well to filter my dependent combo
lists:

SSI.RowSource = "Select distinct tblmaterials.ssi " & _
"from tblmaterials " & _
"where tblmaterials.description = '" & description.Value & "' "
& _
"order by tblmaterials.ssi;"

In most cases there is only one SSI per description. As it is now users
must choose the resulting SSI by clicking on cboSSI after choosing
description from preceding cboDescription. I would like the SSI field to
auto populate with the value if there is only one choice. I tried using
SSI.Value=select statement above, but nothing happens. Perhaps I need to try
an insert statement rather than select?Maybe it is not as easy as I think it
should be. Does anyone have any ideas? Thank you.
 
I tried cboSSI.defaultvalue = select statement in the after update event of
cboDescription, but I get #Error in the field. Maybe syntax is wrong?
 
You're right, you cannot write something like SSI.Value= "select value from
....". After setting the rowsource of the combobox, you can take a look at
it to see if it's value is now undefined and if yes, set its value to the
first value of the list if the later count is 1; something like:

If (cb.ListIndex = -1) Then
If (cb.ListCount > 1) Then
cb = cb.Column(0, 0)
end if
end if

Don't know if you may have timing problem (ie, the rowsource didn't have
time to query the server; maybe you could check with a very long source).
 
Sorry, there is a small error in my previous post:

If (cb.ListCount = 1) Then
 
Thanks again Sylvain. I could not get this to work entirely. The value
displayed in cboDescription did not correspond to the value chosen in cboSSI.
I did however achieve a solution. Being a newbie it did not at first occur
to me I do not need to have the description column in my order table, because
it is already a column in my material lookup table. To solve the issue of
users wanting to be able to look at the order form and see the discription I
added a second column to cboSSI, and added description to the code setting
its rowsource. Then I added an unbound text box and used something like the
last line of code you provided to set its control source.
=[cboSSI].column(1).
 
Not sure to understand your last problem but if you set to 0 the width of
the first column (if the first column is the bound field, of course) then
it's the second column that will be displayed to the user (in your case, the
description). This is usually the way to do with comboboxes linked to a
field.

If you want to display more than one column when the combox is rolled down
and regroup two of more of these columns to be displayed, you can add a
column in your query that will be the expression to be displayed and also be
the second column of the combobox and set its width to a very small value
different of 0, something like 0.003". Because of its small width, this new
second column won't be displayed to the user when the combobox is rolled
down but will be used as the value to be displayed to the user.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Greg Snidow said:
Thanks again Sylvain. I could not get this to work entirely. The value
displayed in cboDescription did not correspond to the value chosen in
cboSSI.
I did however achieve a solution. Being a newbie it did not at first
occur
to me I do not need to have the description column in my order table,
because
it is already a column in my material lookup table. To solve the issue of
users wanting to be able to look at the order form and see the discription
I
added a second column to cboSSI, and added description to the code setting
its rowsource. Then I added an unbound text box and used something like
the
last line of code you provided to set its control source.
=[cboSSI].column(1).

Sylvain Lafontaine said:
You're right, you cannot write something like SSI.Value= "select value
from
....". After setting the rowsource of the combobox, you can take a look
at
it to see if it's value is now undefined and if yes, set its value to the
first value of the list if the later count is 1; something like:

If (cb.ListIndex = -1) Then
If (cb.ListCount > 1) Then
cb = cb.Column(0, 0)
end if
end if

Don't know if you may have timing problem (ie, the rowsource didn't have
time to query the server; maybe you could check with a very long source).
Click to expand...
Click to expand...
 
Back
Top