Set Default Value to a ComboBox in a Continuous Form using VBA

[Shinta]

Greetings dear Access Programmers fellows:

As the title says, I'm trying to set the DefaultValue of a ComboBox in a Continuous Form on the fly, based of the Value of another ComboBox in the MainForm. I'm trying the following code without the desired result:

Private Sub Form_Current()

Forms!MyForm!MySubForm!subFormCombo1.DefaultValue = Me.configComboBox 'Does nothing
Forms!MyForm!MySubForm!subFormCombo1.DefaultValue = "MyValue2" 'Does nothing
Forms!MyForm!MySubForm!subFormCombo1.DefaultValue = 2 'With numbers works perfectly, but I need to use text
Forms!MyForm!MySubForm!subFormCombo1.Requery

End Sub

I'm attaching an Access file which presents this scenario. Could you please give me a hand with this issue?
Btw, I don't want to use the solution in which I assign a DefaultValue at the DesignView -> ControlProperties, as in my real life scenario, I need it to be solved via VBA code as mentioned. Thanks.

Thanks a lot in advanced: I'm grateful with this forum, as I've always received the best of the supports around the web. Regards,

 

[Deleted member 147267]

You realize this will set the default value to be the same for every instance of that combo in a continuous form? Is that OK?
BTW, if this is for a subform (you didn't say but it appears so) then the reference syntax is
Forms!NameOfYourForm!NameOfYourSubformCONTROL.Form.controlNameHere

I didn't download your file because you may not want this after all.

 

[Shinta]

You realize this will set the default value to be the same for every instance of that combo in a continuous form? Is that OK?
BTW, if this is for a subform (you didn't say but it appears so) then the reference syntax is
Forms!NameOfYourForm!NameOfYourSubformCONTROL.Form.controlNameHere

I didn't download your file because you may not want this after all.

Greetings:

Yes, the idea is that all the instances of the ComboBox in the SubForm will have the same DefaultValue based in the ComboBox at the MainForm. The challenge I've is to be able to do this via VBA and not the DesignViewer.

Any thoughts?

 

[jdraw]

Suggest you clarify exactly WHAT you are trying to do. Also, when seeking help you should name your tables, forms etc with meaningful names. table MyForm and form MyForm are not helpful to getting focused responses.

 

[theDBguy]

Hi. The DefaultValue property takes a String value. Also, setting the default value only affects new records. Is that what you're trying to do? Prepare the data for new records?

 

[Shinta]

Hi. The DefaultValue property takes a String value. Also, setting the default value only affects new records. Is that what you're trying to do? Prepare the data for new records?

Hello!

Yes, this is exactly what I'm aiming for... how can I make it work with Strings and not only numbers?

 

[theDBguy]

Hello!

Yes, this is exactly what I'm aiming for... how can I make it work with Strings and not only numbers?

Hi. Default values have to be the same data types as the field in the table. What I was referring to was the DefaultValue property that requires a String. For example, this should work:

Me.FieldName.DefaultValue="NewValue"

But this may not:

strValue="NewValue"
Me.FieldName.DefaultValue=strValue

If not, then I suggest doing this:

Me.FieldName.DefaultValue="""" & strValue & """"

Hope that helps...

 

[arnelgp]

check the code behind the Load event of the form and
the AfterUpdate event of the configComboBox.
removed the Parent/Child link on the subform.
make the parent form unbound.
made the subform bound.
added sample FIELD1 and FIELD2 fields to myForm table so you
can add real records.

 

[Shinta]

Hi. Default values have to be the same data types as the field in the table. What I was referring to was the DefaultValue property that requires a String. For example, this should work:

Me.FieldName.DefaultValue="NewValue"

But this may not:

strValue="NewValue"
Me.FieldName.DefaultValue=strValue

If not, then I suggest doing this:

Me.FieldName.DefaultValue="""" & strValue & """"

Hope that helps...

Greetings:

Yes! This worked!

Me.FieldName.DefaultValue="""" & strValue & """"

Could you please give the reason why this

strValue="NewValue"
Me.FieldName.DefaultValue=strValue

does not work? This will be great for learning a little bit more of the principles of Access.

Thanks again for the help.. this was giving me lots of headaches..
Regards,

 

[theDBguy]

Greetings:

Yes! This worked!

Me.FieldName.DefaultValue="""" & strValue & """"

Could you please give me the reason why

Yes, it worked because, as I said, the DefaultValue property accepts a String argument. What the above code resolves into is basically this:

Me.FieldName.DefaultValue="NewValue"

strValue="NewValue"
Me.FieldName.DefaultValue=strValue

Does not work? This will be great for learning a little bit more of the principles of Access.

Yes, this didn't work because this approach is not passing/assigning a String value to the DefaultValue property. It basically resolves to this:

Me.FieldName.DefaultValue=NewValue

Btw, it only worked with a ComboBox linked to a Table but not with an Unbound... is there any reason for such?

Default Value property should apply to either bound or unbound control. Since you were talking about a Combobox, it may be because of the "bound column" settings. The default value has to match it.

Thanks again for the help.. this was giving me lots of headaches..

Regards,

You're very welcome. We're all happy to assist. Good luck with your project.

 

[Shinta]

Yes, this didn't work because this approach is not passing/assigning a String value to the DefaultValue property. It basically resolves to this:

Me.FieldName.DefaultValue=NewValue

Mm... is there a reason why the DefaultValue property only accepts explicitly data in difference to other properties where Access assign the value in the variable instead of its name? Perhaps I'm thinking too much about the subject, bit it is quite interesting the behavior.

Default Value property should apply to either bound or unbound control. Since you were talking about a Combobox, it may be because of the "bound column" settings. The default value has to match it.

Yes, I've done my testing properly and it works with bound or unbound controls: sorry about that.

You're very welcome. We're all happy to assist. Good luck with your project.

As always, this forum responds with the most gentle, accurate and detailed answers. I really appreciate to have found you.
Best wishes as well

p.s. I'm uploading the final testing file, so if anyone else happens to face this issue, could see the example working.

 

[theDBguy]

Mm... is there a reason why the DefaultValue property only accepts explicitly data in difference to other properties where Access assign the value in the variable instead of its name? Perhaps I'm thinking too much about the subject, bit it is quite interesting the behavior.

Hi. I think you are confusing the DefaultValue property with Data Types again. Just think of the DefaultValue "property" like a "field" in a table. It's data type, let's say, is Text. So, you need to enter Text into it (when using code). The actual "value" assigned to this property "has to match" the data type of the bound field. If you weren't using code, you could simply enter a number in the Default Value property box in the Design View of the Form, and that would be okay. Cheers!

 

[Shinta]

Hi. I think you are confusing the DefaultValue property with Data Types again. Just think of the DefaultValue "property" like a "field" in a table. It's data type, let's say, is Text. So, you need to enter Text into it (when using code). The actual "value" assigned to this property "has to match" the data type of the bound field. If you weren't using code, you could simply enter a number in the Default Value property box in the Design View of the Form, and that would be okay. Cheers!

Now I get it; properties of other kind, like Value, might be VariantType, so they can receive several kind of data, but DefaultValue is Text, so, if I don't treat it like that with the data I assign to it (aka, "quotes"), the assignation will fail. Numbers, are casted automatically to String, so that's why it works.

Thanks a lot for the clarification: now I know something new

Best wishes and regards,

 

[theDBguy]

Now I get it; properties of other kind, like Value, might be VariantType, so they can receive several kind of data, but DefaultValue is Text, so, if I don't treat it like that with the data I assign to it (aka, "quotes"), the assignation will fail. Numbers, are casted automatically to String, so that's why it works.

Thanks a lot for the clarification: now I know something new

Best wishes and regards,

Hi. Good luck with your project. If you want to know what values are expected by each property, you can get a quick explanation from the Help Files. For example, the Filter and OrderBy properties accept Strings, but the FilterOn and OrderByOn properties take Boolean values. Cheers!