Solved Why setting the backstyle in vba fails?

[KitaYama]

Trying to use the old way of hiding the arrow of combo boxes.

I pass the name of the textboxes to cover the combo boxes in form's open event:

Private Sub Form_Open(Cancel As Integer)
    Dim Ctrls As String
    Ctrls = "txtOrderID"
    HideComboArrows Me, Ctrls
End Sub

Then I try to control the text box by :

Public Sub HideComboArrows(frm As Access.Form, Ctrls As String)
    Dim Ctrl As Variant
    Dim Expression As String
    Dim cmb As String
 
    With frm
        For Each Ctrl In Split(Ctrls, ",")
            cmb = Mid(Ctrl, 4)
            Expression = "=ShowCombo ('" & frm.Name & "', '" & cmb & "')"
            .Controls(Ctrl).Left = .Controls(cmb).Left
            .Controls(Ctrl).Width = .Controls(cmb).Width
            .Controls(Ctrl).Top = .Controls(cmb).Top
            .Controls(Ctrl).Height = .Controls(cmb).Height
         
            .Controls(Ctrl).OnEnter = Expression
            .Controls(Ctrl).BackColor = .Controls(cmb).BackColor
            .Controls(Ctrl).ControlSource = "=[" & cmb & "].[Column](1)"
            .Controls(Ctrl).LeftMargin = .Controls(cmb).LeftMargin
            .Controls(Ctrl).Locked = True
            .Controls(Ctrl).BackStyle = acNormal
            .Controls(cmb).TabStop = False
''            .Controls(Ctrl).ZOrder (0)
        Next
    End With
 
End Sub

Opening the form, still the arrow is visible.
If I remove the following line and manually set the backStyle in property sheet of textbox to normal, it works.
.Controls(Ctrl).BackStyle = acNormal

My question:
Why setting the backstyle in vba causes the process to fail and should be set manually?

Thanks.

PS:
Sample file attached.

 

[June7]

If you want "Normal", the value is 1, "Transparent" is 0. https://learn.microsoft.com/en-us/office/vba/api/Access.TextBox.BackStyle
acNormal returns 0.

 

[KitaYama]

If you want "Normal", the value is 1, "Transparent" is 0. https://learn.microsoft.com/en-us/office/vba/api/Access.TextBox.BackStyle
acNormal returns 0.

You mean I can't use
.Controls(Ctrl).BackStyle = acNormal

Is the value of acNormal set by MS, wrong?
If so, what is acNormal all about?

thanks.

 

[June7]

acNormal = 0

If you want "Normal", use 1, otherwise 0 for "Transparent".

.Controls(Ctrl).BackStyle = 1

acNormal serves purpose as argument parameter for various commands, such as OpenForm.

Docmd.OpenForm "FormName", acNormal

 

[KitaYama]

acNormal = 0

If you want "Normal", use 1, otherwise 0 for "Transparent".

.Controls(Ctrl).BackStyle = 1

I get your point. But I still can't understand why acNormal returns a wrong value.
Is there any where else it(acNormal) can be used with the value of 0?

thanks again.

 

[June7]

See my edited earlier post.

 

[KitaYama]

Million thanks.
I feel so stupid.
I should have checked the value of acNormal before posting.

Thanks again for the lesson.

 

[AHeyne]

You could also search in the VBEs 'Object 'Browser' for 'acNormal'.
There you can see that it belongs to the enumeration 'AcFormView', which makes clear that it's not to be used with the 'BackStyle' property.

 

[KitaYama]

You could also search in the VBEs 'Object 'Browser' for 'acNormal'.
There you can see that it belongs to the enumeration 'AcFormView', which makes clear that it's not to be used with the 'BackStyle' property.

Thanks.

 

[MajP]

FYI. Do not hold me to this but I am pretty sure most of the properties have indices in the order shown. At lest that is the rule I try.

.
So transparent is 0, normal is 1

The tricky one is RowSourceType. This is one of the few that I know that actually is a string example "Table/Query". The yes/no and true/false I think are all True and False.

which makes clear that it's not to be used with the 'BackStyle' property.

I would rephrase that. You can use any enumeration constants anywhere in your code. In fact I often try to use these in my UDFs because they are understood by the user.

If you use Enumeration constants that are not explicitly for a given object ensure they align in value. Is not that you should not use it, but you need to verify it aligns. In this case the values did not align.

Some vba constants make intuitive sense and do align and no reason not to use them. Example you can use vbHide (=0) for something like
me.cmboOne.visible = vbHide
where 0 represents false.

 

[The_Doc_Man]

Since you mention that 0 represents False, something tripped me up some years ago when I was still relatively new to Access. True and False work perfectly in SQL but not at all in VBA. There are constants for vbYes and vbNo (as responses to a MsgBox with the appropriate box style) but TRUE and FALSE are not intrinsic constants. You have to define them first. What a bugger. But now I always define them in a support module that contains my color constants with names for various RGB component intensities.

I wouldn't be surprised to find that this oversight had since been remedied, but when I first discovered it, it explained why my code was having such issues for comparisons and logic tests.

 

[isladogs]

True/False are predefined in VBA & have been for at least 20 years. They do not need any special treatment for English Office users.
In SQL Server, bit fields are used with values 0 and +1, In Access / VBA its 0 and -1
The only caveat with Access Booleans is that if your apps are run in another language such as Spanish, it will not recognize True/False. Localized equivalents apply instead.

That is one of the reasons why I now rarely use Boolean fields. Instead, I either use bit fields using values of 0/1 or Integer fields value of 0/-1.
The latter method also allows you to use checkboxes

 

[The_Doc_Man]

True/False are predefined in VBA & have been for at least 20 years. They do not need any special treatment for English Office users.
In SQL Server, bit fields are used with values 0 and +1, In Access / VBA its 0 and -1
The only caveat with Access Booleans is that if your apps are run in another language such as Spanish, it will not recognize True/False. Localized equivalents apply instead.

That is one of the reasons why I now rarely use Boolean fields. Instead, I either use bit fields using values of 0/1 or Integer fields value of 0/-1.
The latter method also allows you to use checkboxes

I stand by the statement that I ran into the problem of TRUE and FALSE not being defined in VBA when I was active early in my career - but they WERE defined for SQL (probably JET at the time). It is possible that I was missing a particular library reference that would have covered that, but at the time, a DEBUG.PRINT of TRUE returned 0 and otherwise, everything compiled. As I said, what it does now is not the intent of the comment. It was merely to point out that predefined symbols aren't always what you think.

 

[isladogs]

That's certainly something I've never seen or heard about
From the beginning, Access defined Boolean values True/False as -1/0.

This is from Access v1.0 (1992) when it was still Access Basic with no external reference libraries

And this is from Allen Browne: http://allenbrowne.com/NoYesNo.html as part of his article where he recommends using an Integer field instead of Booleans. My emphasis in RED

When the Microsoft team designed version 1, they made two fundamental mistakes with Yes/No fields:

1. They decided Yes/No fields could be Yes or No only -- never Null.
2. They wrote JET assuming that a Yes/No field would never contain a Null.

(A) violates relational theory, but the real problem is with (B). As any first year computer student knows, all fields on the outer side of join can be Null. So, even basic queries regularly do return Null in Yes/No fields! And since JET was never designed handle this data type with Null, when you operate on this column (apply criteria, group, sort, join, ...) Access crashes completely or generates a nonsense error. For a demonstration, see Outer Join Queries Fail on Yes/No fields.

18 years later, these fundamental design flaws have never been addressed. Clearly, we cannot expect Microsoft to address this problem any time soon.

If you know enough to create an outer-join query, this issue affects you. Most users just know Access crashes, without knowing why.

Use Number fields instead​

Once you understand the cause, the workaround is obvious. JET is quite happy working with Nulls in Number fields. Since Access uses 0 for False, and -1 for True, use a Number field in place of the Yes/No field, and JET will process these queries correctly.

 

[isladogs]

@KitaYama
Out of interest, why do you want to hide the combo dropdown arrow?
You are obviously aware that it reappears as soon as you click in the combo . . so why hide it at other times?

 

[KitaYama]

@KitaYama
Out of interest, why do you want to hide the combo dropdown arrow?
You are obviously aware that it reappears as soon as you click in the combo . . so why hide it at other times?

Sorry for the late reply.
I have a table with several FKs to other tables. A continuous form shows the result of a search on this table.
Showing numbers (PartFK, SupplierFK, UnitFK,.....) tells me nothing. I use combo boxes with relative rowsources to these parent tables to show the actual names.
Just as a matter of appearance, I'm sure you can imagine how terrible a continuous form with a lot of combo boxes looks like.
So hiding the arrows, gives me a better look. So I was simply trying to write a function that covers the necessary setting for these text boxes instead of manually change them one by one.

I know that I can add the tables to the query that is used as the record source of the form, but the query is a complicated query with several joins and sub queries. I just prefer didn't want to put more heat the query.

Any other suggestion (if any) can be helpful.

Thanks.

 

[MajP]

A continuous form shows the result of a search on this table.

If the form is for view and not selecting, why not simply include the display values in the query? Why have combo boxes and not textboxes showing the actual values?

 

[CJ_London]

Use a dlookup? Tho’ probably not as efficient?

 

[isladogs]

Agree with @MajP
Using lots of lookup row sources in a continuous form isn't normally a good idea.
I would recommend changing the form record source to get rid of the issue.

Although I don't like the idea of what you are doing, your code solution is a good way of handling it (with the correct backstyle value).

By contrast, there is another user at UA who has literally thousands of forms with lots of combos on every form.
He covers each of them with a small rectangle. That really is completely stupid!

 

[KitaYama]

For this particular search, I don't use a saved query.
I have a form with a lot of controls (textboxes, combos, option boxes, checkboxes,...) that is used to create a dynamic sql for the search I need. I was simply trying to make the creating process of the sql easier by omitting the necessary joins for these FKs in the query.

I'll follow the given advices and will add the tables to a saved query and see how it goes.

Thanks again for your time and help.