Using '=' in VBA

[lorirobn]

hi...silly little question...but - is there a way in VBA to set 5 fields equal to the same value without 5 statements or a loop?
for exampe: fieldA = fieldB = fieldC = fieldD = 100

thanks...
from a reformed COBOL programmer

 

[hwkranger]

Nope. not possible. Limitations of VBA. But in C, C++, C# you can.

Randall Vollen
National City Bank Corp.

 

[JeremyNYC]

No, I don't think there is. If you happen to have named them something like
txtYadda1
txtYadda2
etc, then, if you really wanted you could do something along these lines (I'm writing this here, with no testing, so be sure to test this yourself...)

dim intCountFields as integer

for intcountfields = 1 to 5
   me("txtYadda" & intcountfields) = "blah blah blah"
next intcountfields

But, of course, this would only save you a small bit of effort if it's really only five fields.

Do you want to expand a little on what the fields are and why you're setting them to the same value? My spidey sense tells me there may be something awry in your data model--you normally wouldn't want repeating fields in your table. Clearly I don't know what these fields are and I'm going out on a limb here, but I thought I'd raise the point.

Jeremy

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[hwkranger]

After I posted a quick no, I got to thinking -- if you are using a rst, then you're using SQL. If you're using SQL then you can do assignments there..

example:

  UPDATE MYTABLE
  SET
   (
     FLD1 = VALUE,
     FLD2 = VALUE,
     FLD3 = VALUE,
     FLD...
    )
[\code]

I suspect as Jeremy does that there's something wrong with your schema.

The answer to your problem isn't what you want -- you're probably looking for a more terse language than VBA.



Randall Vollen
National City Bank Corp.

 

[lorirobn]

I knew I should have said what I was using them for in my initial post!

I have an option group on my form, with 5 fields. When one is selected, I want to change its label's color. Thus the code - after changing backstyle and backcolor on the selected item, I need to change the others back to the default backstyle/backcolor.

So far, the code I have, in the option group afterupdate event, changes all of the controls to the default, one by one, then changes the selected one to the new color. Just was wondering about options on how to do this code. Kind-of a newbie to VBA, but ready to learn some more...

By the way, is there a way to set a label back to its default, without actually referencing the color?

thanks...
Lori

 

[JeremyNYC]

Lori,

Ahhhhhh.

I would probably just make a look, sort of like what I've got, setting all of the colors to the default*, and then set the color of the one you want to highlight. Of course, this still assumes that they're named in some sort of scheme with a number at the end. If not, maybe you're lucky and they're the only controls of that type on this form? If so, you could loop through all the controls, only acting on the ones in question. But wait, these are labels, right? If so, you ought to be able to rename them to whatever you want, with no consequences, yeah?

Jeremy

* Defaults: There is, somewhere on the web, a site that lists default color values for various windows screen elements. I don't remember where it is, but I do remember that I found it without too much trouble by googling for it.

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[stix4t2]

I whipped up this code to maybe do what you want.

Couple of things to keep in mind. You have to put the corresponding label name in the tag of the option button. The backstyle is set to the label after the option button control is determined to be current or not.

The second is that you have to set all the labels to the color you want. The key here is that unselected labels are set to transparent. You could play with this code to set colors.

Also be sure to call the routine from the form_current if frame is bound or form_load if frame is unbound.

Can you replace your variable names for the ones I made up here?

Private Sub frmOptions_AfterUpdate()
Dim ctrl As Control
    For Each ctrl In frmOptions.Controls 'controls in frame
        If (ctrl.ControlType = 105) Then 'is option box
            frmOptions.Controls(ctrl.Tag).BackStyle = IIf(ctrl.OptionValue = Frame2.Value, 1, 0)
        End If
    Next ctrl
End Sub

Good luck

 

[MajP]

Here is a far easier way to do that.

Private Sub frameOne_AfterUpdate()
 Const newBackColor As Long = vbRed
 Dim myOption As Access.Control
 For Each myOption In frameOne.Controls
    If myOption.ControlType = acOptionButton Then
       myOption.Controls(0).BackStyle = 0
       If myOption.OptionValue = frameOne.Value Then
          myOption.Controls(0).BackStyle = 1
          myOption.Controls(0).BackColor = newBackColor
       End If
    End If
 Next myOption
End Sub

This assumes every option button has a label, and that there are no other controls inside the frame but option buttons or labels. I choose red and transparent

Here are the tricks
an option button has an option value, which is the value that you set at design time for the button to return. They have an optionvalue, but do not have a value. The OptionGroup has the value.

myOption.Controls(0)
returns the label that is associated with a control. Yes a control has a controls collection of one item.

 

[Tigerlili3]

Default grey in Access is -2147483633. And yes, you do need to include the minus sign.

 

[MajP]

if you do this
myOption.Controls(0).BackStyle = 0

Then you do not really have to worry about the default grey.

 

[Tigerlili3]

This is a link to a list of some Microsoft system colors...

http://www.peterssoftware.com/sysclrs.htm

 

[MajP]

TigerLili,
Thanks for the reference. Try this one for run commands, and click on full list. It is a fantastic effort they put together.

http://www.tkwickenden.clara.net/list/lista.htm

 

[Tigerlili3]

Thanks!

 

[JeremyNYC]

Yup, it was the Peter's Software link that I remembered.

---
Jeremy Wallace
METRIX Lead Developer
Fund for the City of New York
http:// metrix . fcny . org

 

[lorirobn]

Thanks for all the great suggestions and tips...it's all incredibly helpful and I've learned a lot from all of you.

I ended up using the loop that MajP suggested...and it works great with a lot less code than I had.

Couple of questions:
Is Dim myOption As Access.Control
the same as
Dim myOption As Control
???

And, just out of curiosity - when I start to type in the statement
'If myOption.ControlType = acOptionButton Then',
when I type the period after 'myOption.'
Access doesn''t give me 'ControlType' as one of the choices in the pop-up. This is interesting - does it not give all the choices? I thought if it's not an option, then it's not correct.

Thanks once again,
Lori

 

[MajP]

1.

Couple of questions:
Is Dim myOption As Access.Control
the same as
Dim myOption As Control

Maybe, maybe not. Visual basic is sloppy with some of the very common object naming conventions. For example you can have a DAO recordset or an ADO recordset and it you do not tell it which one it will find the first reference it comes across. So if I have only a reference to ADO or if ADO is listed before DAO then this throws a type mismatch error

Dim myRs as Recordset (it is dimmed as ADO by default)
set myRs = me.recorsetclone (it returns a DAO recordset)

So I always do
dim myRs as DAO.recorset
dim myRs as ADODB.recorset

If I am working with VB form controls they use the same names as Access form controls, but are not the same.
I can use these controls on an Access form, and sometimes they have greater flexibility. You can do a lot a things with a VB textbox that you can not with a Access textbox. So I am in a habit of explicitly identifying my objects if they have a name that is common.

Dim myControl As MSForms.Control
Dim myControl2 As MSForms.CheckBox
Dim myControl3 As Access.Control
Dim myControl4 As Access.CheckBox

2. I do not know the answer to your second question hopefully someone will chime in. The "controlType" is a property of a control. So you would assume that it would show up. There are other properites of other objects that do not appear in intellisense as well, but I do not know why.