string variable value as default value for combo box

[Kim M.]

I am trying to set the default value for a combo box to the value of a public
string variable. (I have this code tied to the open event of the form.) But
keep getting error message. If I try to use a public integer variable, it
works fine, so I'm guessing it has something to do with the string v. number
thing. There is nothing about the combo box itself that would seem to
require a number, and the field in the underlying table that corresponds to
that combo box is a text field. When I looked at vbHelp for "DefaultValue"
it shows three sets of quotation marks around the string in the example...not
sure how this would translate when using a variable as the default value
instead.

Thanks for helping a relative newbie!

Kim M.

 

[John W. Vinson]

I am trying to set the default value for a combo box to the value of a public
string variable. (I have this code tied to the open event of the form.) But
keep getting error message. If I try to use a public integer variable, it
works fine, so I'm guessing it has something to do with the string v. number
thing. There is nothing about the combo box itself that would seem to
require a number, and the field in the underlying table that corresponds to
that combo box is a text field. When I looked at vbHelp for "DefaultValue"
it shows three sets of quotation marks around the string in the example...not
sure how this would translate when using a variable as the default value
instead.

Thanks for helping a relative newbie!

Kim M.

Are you perchance using a Lookup Field in the table? If so, then the control
source of the combo box IS in fact a number, and the lookup field misfeature
conceals this very basic fact from view.

If not please post your code.

 

[Kim M.]

Yes -- the field in question IS a lookup field! But how/when does that
convert it to a number field??

If I put in a text default value for the combo box in the Properties sheet
from within Access (rather than in vba), it works fine. And if I use vba
code to set the default variable to some specific string of text, it also
works fine. It is only when I try to set the default value to the string
*variable* that I run into problems.

Any suggestions/work-arounds?

Thanks so much!
Kim M.

 

[John W. Vinson]

Yes -- the field in question IS a lookup field! But how/when does that
convert it to a number field??

That's what a Lookup Field is: a (really stupid, IMO) tool which conceals the
actual contents of your table behind a very limited display tool. The actual
content of the field IS the numeric foreign key to the lookup table.

See http://www.mvps.org/access/lookupfields.htm for a critique.

If I put in a text default value for the combo box in the Properties sheet
from within Access (rather than in vba), it works fine. And if I use vba
code to set the default variable to some specific string of text, it also
works fine. It is only when I try to set the default value to the string
*variable* that I run into problems.

I never use lookup fields so I really don't know! Perhaps you should post a
new thread asking how.

It is certainly not *necessary* - you can use a combo box on a Form, whether
or not there's a lookup field behind it; you can default its value to the
numeric foreign key (using a query or code to identify the numeric value given
the text one) if need be.

 

[Kim M.]

I guess what is confusing me is that the foreign table that serves as the
basis for the lookup table does not have any numeric fields. I specifically
got rid of the autonumber field and made the text field in question the
(unique) key field. The only other two fields in the foreign table are also
text fields. So a bit of a puzzle...

 

[John W. Vinson]

I guess what is confusing me is that the foreign table that serves as the
basis for the lookup table does not have any numeric fields. I specifically
got rid of the autonumber field and made the text field in question the
(unique) key field. The only other two fields in the foreign table are also
text fields. So a bit of a puzzle...

What's the RowSource of the combo box? Also ColumnCount, ColumnWidths,
BoundColumn?

 

[Kim M.]

Thanks everybody. I finally figured it out. I had to do it like this (with
the extra quotation marks):

Me.cboStaffInitials.DefaultValue = "'" & pubStaffInitials & "'"

Kim M.

 

[Jack Leach]

Just to be picky, using a single quotation works great, right up until
there's an apostrophe in a string, then your up the creek, so to speak. The
"correct" way is using double quotations.

When inside a string, "" = "

therefore:

Me.cboStaffInitials.DefaultValue = """" & pubStaffInitials & """"

returns "content of pubStaffInitials" rather than 'content of
pubStaffInitials'

The problem is with apostrophe's, when they're encountered inside a literal
string vba closes the string early.


On a seperate note, you seem to be forcing the value of pubStaffInitials to
be enclosed in a quote, as if you need to specify that it's a string. Will

CStr(pubStaffInitials)

not work instead, without playing around with quotes? There's definately
some strange going-ons with datatypes here, probably via the evil lookup
fields.

hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)