Loading variable in new form, from module 1

[Kib]

Hi.
I have a form that in the vba runs some calculations and gets some information. Which I have stored in a string variable.
such as strFName = "mount holly"
then I open up a form. I want a control box on the new form to start with its selection being
"Like '*" & strFName & "*'"
or something like that.
I know this is confusing, but really need help
If need more clarification on what is being asked here. let me know
thanks.

 

[Dustman]

Well, I'm not quite sure I know what your asking but I'll give it a shot. First thing you should do is to make sure you declare the variable in the module using Global and not Dim. That will make sure its accessable to all the forms. If you are wanting to use a SQL statement to load the control on the new form, open the form and set the Row Source property to your SQL string.

For example

Private Sub myForm_Load()
  mycontrol.RowSource = "SELECT ColumnName FROM TableName WHERE ColumnName LIKE '" & strFName & "*';"

End Sub

Your may have to play around with it alittle on where to put the code, depending on when you use it.. the form load may not be the place you want it.

Is that what your looking for? If not, try to explain it again and I'll give it another shot.

-Dustin
Rom 8:28

 

[Kib]

This is close to what I am looking for.
let me try to explain a little more.
The Combo box already has a source, that is a list of locations.
Such as :
Montclair, VA
Washington, DC
Seattle, WA
Los Angeles, CA

lets say strFName = "Washington"

When the form loads, I want to combobox to read "Washington, DC"
but still have the option of the user selecting something else.

Your previous post did help, but I guess it was just a part of the problem.

 

[JoeMiller]

Maybe I'm missing something but can't you just set the default value of your combo box to strFName? So your code would go:

MyComboBox.DefaultValue = strFName

HTH Joe Miller
joe.miller@flotech.net

 

[Dustman]

Hmm.. I'm pretty sure I know what your wanting to do now and I'm sure there is a way to do it. Setting the defaultvalue to strFName wouldn't help because you would only have what they searched for. ie: Washington instead of Washington, DC. I need to know a little more about your situation. How are you loading the values in the combo box? If your doing them in design time, try making a table with the values instead. This will allow you to use the sql statement to find a value. I'm going to have to play around with it a little. I'll get back to ya ASAP.

-Dustin
Rom 8:28

 

[Quehay]

If you're opening the form based on results from another form and want to have a value from one form used in the other use OpenArgs

strResults = vbaresults

DoCmd.OpenForm "myForm",,,,strResults

In the Form_Load event of the target form

If not IsNull(me.OpenArgs) then

cboState = me.OpenArgs

end if


*If forget whether or not you test for Null on the OpenArgs--if you get an "Invalid Use of Null" error pass the OpenArgs to a variant first before testing for null.

**Also me.OpenArgs is used for both transmitting and recipient forms

 

[Kib]

Dustman:
The values in the combo box right now come from a sql statement working on a table
the table has
ID LOCATION CODE
1 Montclair, VA 213
2 Washington, DC 314
3 Seattle, WA
4 Los Angeles, CA 100

The combobox queries out all records that have Null CODE

so the combobox source has that sql statement...
Hope this explains it some more.