Form-Subform linking in VBA

[Favri]

Hi!



I have been trying to apply a solution to my Access application where a user is picked from the combo list, then a subform lists his projects (works).





Well, everything works just fine, until I try to list projects based on position of a user. When I enter this code it works:






But when I try to insert an IIf condition it returns error:






What should I do to make this code work?

[Welshgasman]

Why the brackets around the True and False values?
Normally, (well always), one of those would be assigned to a variable/control?, so your syntax is way off.

When in doubt look up the syntax.
https://support.microsoft.com/en-gb/...0IIf%20returns.

However that is the same as yours , and only works in that form as a souce for a control.

[Gicu]

I would suggest you move your IIF() to set the value to a string variable (Dim sChildField as String) then use that to set the linking property. It makes cleaner and easier to debug...
Cheers,

[xps35]

Why do you want to do it in VBA? The fields (on main and sub) will always be the same, no matter the selected value. So use the relevant properties of the subform.

[ArviLaanemets]

So you have an unbound form as main one, where you select an user (I assume this as you refer to combo control as MasterField property of subform instead of userID of main form's source), and the list of selected users projects in subform?
And in table of users as a field which determines a position of given user (I hope it is not a description of position, but PositionID). And you want the subform to display only projects matching with position selected in another combo of main form?

On fly!
Have the RowSource of position selectin combo something like

SELECT PositionID, Position FROM tblPositions
UNION SELECT 0 AS PositionID, "No Filter" FROM tblPositions
ORdeR BY 1

For position combo, design a Change event, which:
In case the value of combo is 0, sets the subform's Filter property to "", and FilterOn property to False;
Otherwise sets Filter property to "ChangeID=" & ValueOfCombo, and FilterOn property to True.

[ArviLaanemets]

So you have an unbound form as main one, where you select an user (I assume this as you refer to combo control as MasterField property of subform instead of userID of main form's source), and the list of selected users projects in subform?
And in table of users as a field which determines a position of given user (I hope it is not a description of position, but PositionID). And you want the subform to display only projects matching with position selected in another combo of main form?

On fly!
Have the RowSource of position selectin combo something like

SELECT PositionID, Position FROM tblPositions
UNION SELECT 0 AS PositionID, "No Filter" FROM tblPositions
ORDER BY 1

For position combo, design a Change event, which:
In case the value of combo is 0, sets the subform's Filter property to "", and FilterOn property to False;
Otherwise sets Filter property to "PositionID=" & ValueOfCombo, and FilterOn property to True.

Scrap it! I assumed you want to filter subform for position of project! It looks like you want to select only users of certain position.
So instead use Change event of position combo to update the RowSource of users combo like:
In case the value of position combo is 0, sets the users combo RowSource to "SELECt UserID, User FROM tblUsers ORDER BY 2";
Otherwise sets the users combo RowSource to "SELECt UserID, User FROM tblUsers ORDER BY 2 WHERE PositionID=" & ValueOfCombo.