Solved Why setting the backstyle in vba fails?

[June7]

I have forms with lots of comboboxes and never thought it looked terrible.

You have a search form that opens another form to display data (not edit) and using comboboxes on this form to display related data? I agree, just use a query that includes lookup tables and bind to textboxes, eliminate code.

 

[MajP]

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 do not see that being any easier. I think it would be way easier to bring in the extra fields.
Now if you name your PKs and FKs the same name you will likely want to alias the FKs. If not when you build your filter and say
"where SomeID = " & 1"
It is going to error with something like "SomeID can refer to a field in more than one table"
if not you will need to specify
"where tableOne.SomeID = " & 1
This is why I always name my fields SomeID and SomeID_FK (in the other table)

 

[KitaYama]

I do not see that being any easier. I think it would be way easier to bring in the extra fields.

I'm not trying to prove you're wrong. I Know about your (And others) abilities and experiences, specially in Access.
I'm just trying to show what I meant by "Making the process of creating the sql easier".

This is a very very simplified relationship of a given database.

Now if I want to search for a specific order, I use a form to do my choices and let's say the sql looks like :

SELECT
    o.OrderPK, o.OrderNo, o.UserFK, o.CustomerFK, o.ProductFK
FROM
    tblOrders AS o INNER JOIN tblOperations AS p ON o.OrderPK = p.OrderFK
WHERE
    o.CustomerFK = 128
    AND (o.OrderPK In (SELECT OrderFK FROM tblOperations WHERE ProcessFK=20 AND OperatedOn IS NULL)
    And (o.OrderPK) In (SELECT OrderFK FROM tblOperations WHERE ProcessFK=18 AND OperatedOn IS NOT NULL)
    And (o.OrderPK) In (SELECT OrderFK FROM tblOperations WHERE ProcessFK=13 AND UserFK=3));

The form that shows the result of above query, uses several combos to show the name of FKs used in query.
But if I want to bring in names instead of FKs, the sql changes to:

SELECT
    o.OrderPK, o.OrderNo, u.UserName, c.CustomerName, d.ProductName
FROM
    tblProducts AS d INNER JOIN
        (tblUsers AS u INNER JOIN
            (tblCustomers AS c INNER JOIN
                 (tblOrders AS o INNER JOIN tblOperations AS p ON o.OrderPK = p.OrderFK)
             ON c.CustomerPK = o.CustomerFK) ON u.UserPK = o.UserFK)
        ON d.ProductPK = o.ProductFK
WHERE
    o.CustomerFK = 128
    AND (o.OrderPK In (SELECT OrderFK FROM tblOperations WHERE ProcessFK=20 AND OperatedOn IS NULL)
    And (o.OrderPK) In (SELECT OrderFK FROM tblOperations WHERE ProcessFK=18 AND OperatedOn IS NOT NULL)
    And (o.OrderPK) In (SELECT OrderFK FROM tblOperations WHERE ProcessFK=13 AND UserFK=3));

Well, it's not a huge difference, but still is a little more complicated than the first approach.
The problem starts when even tblUsers has its subqueries (selecting between night Shifts/Day shifts or Emplyee/PartTime, ....)
or when tblCustomer may need some specific shipments that needs the tblShipments being added or using another subquery.

There are still some other tables that I haven't mentioned (tblUsedMachines, tblUsedTools, tblMaterials, ....) and the list goes up.
At times the sql gets so huge as the result of selected search terms.

My point is when creating the sql, I was trying to summarize the sql as much as possible by not adding all joins and using combos to show the relevant data from other tables.

Again, I know you and others are absolutely correct that adding the necessary items to the query and not using lots of lookup row sources is the best practice. I'll try to follow your and others suggestion to add these fields as much as possible.

Thanks again for all the advices I received here.

 

[MajP]

Not sure if it helps any but no reason you cannot include the fks in your select also. If you think it's easier put dlookups in instead of combos and joined tables.

 

[Josef P.]

Note:
To ensure that your code can also be used as a subform without much effort, you could pass the control reference to the ShowCombo procedure instead of the form name and control name.

Public Function ShowCombo(frmName As String, obj As String)
    Forms(frmName).Form.Controls(obj).SetFocus
End Function

=>

Public Function ShowCombo(ByVal objRef As Control)
    objRef.SetFocus
End Function

=> Expression = "=ShowCombo([" & cmb & "])"

 

[KitaYama]

to ensure that your code can also be used as a subform without much effort,

For the functions that I need to be available for sub forms I use something like:
Expression = "=MyFunction([Form], '" & Ctrl & "')"

Seems that your suggestion is shorter.
Will give it a try.

Thanks for the advice.

 

[MajP]

Seems complicated in both cases. Making a string and then parsing it. Why not simpy pass the controls? Works with subforms and forms. You may need some extra code to return the parent form if this is on a tab control.

Public Sub HideComboArrows(ParamArray Ctrls() As Variant)
    Dim Ctrl As Access.TextBox
    Dim cmb As ComboBox
    Dim Expression As String
    Dim i As Integer
    
    For i = 0 To UBound(Ctrls)
        Set Ctrl = Ctrls(i)
        Set cmb = Ctrl.Parent.Controls(Mid(Ctrl.Name, 4))
        With Ctrl
              
             '.Expression = "=ShowCombo ('" & Ctrl.Parent.Name & "', '" & cmb.Name & "')"
             .Left = cmb.Left
             .Width = cmb.Width
             .Top = cmb.Top
             .Height = cmb.Height
             .OnEnter = Expression
             .BackColor = cmb.BackColor
             .ControlSource = "=[" & cmb.Name & "].[Column](1)"
             .LeftMargin = cmb.LeftMargin
             .Locked = True
             .BackStyle = 0
             cmb.TabStop = False
         End With
        Next i
End Sub

Example
HideComboArrows txt1, txt2, txt3, txt4

Works for me and a lot less lines of code you need to get perfect syntax for. If you pass a misformed string it will never get caught. Since I pass the real controls it will get caught at design time.