Subform control reference in Function

B

bymarce

Hi and thanks for your help! I have a subform that I use on several forms.
The subform is called subfrmConditions. I want to call this function in
cboCondition_AfterUpdate() to update the record source for cboUnits. On the
If statement I'm getting the error "Can't find Field refered to in your
expression". Thanks.
Marcie

Public Function ConditionsUpdateUnits(frmName As Form, FormName As String)
Dim sqlUnits As String
sqlUnits = "SELECT DISTINCT Units.Units " & _
"FROM Properties RIGHT JOIN Units ON
Properties.[UnitsType]=Units.[UnitsType] "
"WHERE (((Properties.Property)=Forms!" & FormName &
"!subfrmConditions.Form!cboCondition)) " & _
"ORDER BY Units.Units;"

If IsNull(frmName!subfrmConditions.Form!cboCondition) Then
frmName!subfrmConditions.Form!cboUnits.RowSource = "SELECT DISTINCT
Units.Units FROM Units ORDER BY Units.Units"
Else
frmName!subfrmConditions.Form!cboUnits.RowSource = sqlUnits
End If
End Function
 
D

Dirk Goldgar

bymarce said:
Hi and thanks for your help! I have a subform that I use on several
forms.
The subform is called subfrmConditions. I want to call this function in
cboCondition_AfterUpdate() to update the record source for cboUnits. On
the
If statement I'm getting the error "Can't find Field refered to in your
expression". Thanks.
Marcie

Public Function ConditionsUpdateUnits(frmName As Form, FormName As String)
Dim sqlUnits As String
sqlUnits = "SELECT DISTINCT Units.Units " & _
"FROM Properties RIGHT JOIN Units ON
Properties.[UnitsType]=Units.[UnitsType] "
"WHERE (((Properties.Property)=Forms!" & FormName &
"!subfrmConditions.Form!cboCondition)) " & _
"ORDER BY Units.Units;"

If IsNull(frmName!subfrmConditions.Form!cboCondition) Then
frmName!subfrmConditions.Form!cboUnits.RowSource = "SELECT DISTINCT
Units.Units FROM Units ORDER BY Units.Units"
Else
frmName!subfrmConditions.Form!cboUnits.RowSource = sqlUnits
End If
End Function
Click to expand...


Verify that the name of the subform control (on the main form) is actually
"subfrmConditions". It could be that, while the subform's SourceObject is
"subfrmConditions", the subform control itself is named something else.

Out of curiosity, are you passing both the form object and the form object's
name to this function, as "frmName" and "FormName" respectively? Wouldn't
it be simpler to pass just the form object, and use its Name property when
you want to build the name into your SQL string?
 
B

bymarce

Thanks for your help and especially for the tip about the name property of
objects!! I figured something like that should be available but I didn't
know what it was. I checked the names and they were correct. I ended up
getting the code to work like this. It seams Access 07 doesn't require the
subform be included in the reference.

Public Function ConditionsUpdateUnits(frmName As Form)
If IsNull(frmName.cboCondition) Then
frmName.cboUnits.RowSource = "SELECT DISTINCT Units.Units FROM Units
ORDER BY Units.Units"
Else
Dim sqlUnits As String
Dim strQ As String
Dim strCND As String
strQ = """"
strCND = frmName.cboCondition
sqlUnits = "SELECT DISTINCT Units.Units " & _
"FROM Properties RIGHT JOIN Units ON
Properties.[UnitsType]=Units.[UnitsType] " & _
"WHERE (((Properties.Property)=" & strQ & strCND &
strQ & ")) " & _
"ORDER BY Units.Units;"
frmName.cboUnits.RowSource = sqlUnits
End If
End Function

Dirk Goldgar said:
bymarce said:
Hi and thanks for your help! I have a subform that I use on several
forms.
The subform is called subfrmConditions. I want to call this function in
cboCondition_AfterUpdate() to update the record source for cboUnits. On
the
If statement I'm getting the error "Can't find Field refered to in your
expression". Thanks.
Marcie

Public Function ConditionsUpdateUnits(frmName As Form, FormName As String)
Dim sqlUnits As String
sqlUnits = "SELECT DISTINCT Units.Units " & _
"FROM Properties RIGHT JOIN Units ON
Properties.[UnitsType]=Units.[UnitsType] "
"WHERE (((Properties.Property)=Forms!" & FormName &
"!subfrmConditions.Form!cboCondition)) " & _
"ORDER BY Units.Units;"

If IsNull(frmName!subfrmConditions.Form!cboCondition) Then
frmName!subfrmConditions.Form!cboUnits.RowSource = "SELECT DISTINCT
Units.Units FROM Units ORDER BY Units.Units"
Else
frmName!subfrmConditions.Form!cboUnits.RowSource = sqlUnits
End If
End Function
Click to expand...


Verify that the name of the subform control (on the main form) is actually
"subfrmConditions". It could be that, while the subform's SourceObject is
"subfrmConditions", the subform control itself is named something else.

Out of curiosity, are you passing both the form object and the form object's
name to this function, as "frmName" and "FormName" respectively? Wouldn't
it be simpler to pass just the form object, and use its Name property when
you want to build the name into your SQL string?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

bymarce said:
Thanks for your help and especially for the tip about the name property of
objects!! I figured something like that should be available but I didn't
know what it was. I checked the names and they were correct. I ended up
getting the code to work like this. It seams Access 07 doesn't require
the
subform be included in the reference.

Public Function ConditionsUpdateUnits(frmName As Form)
If IsNull(frmName.cboCondition) Then
frmName.cboUnits.RowSource = "SELECT DISTINCT Units.Units FROM
Units
ORDER BY Units.Units"
Else
Dim sqlUnits As String
Dim strQ As String
Dim strCND As String
strQ = """"
strCND = frmName.cboCondition
sqlUnits = "SELECT DISTINCT Units.Units " & _
"FROM Properties RIGHT JOIN Units ON
Properties.[UnitsType]=Units.[UnitsType] " & _
"WHERE (((Properties.Property)=" & strQ & strCND &
strQ & ")) " & _
"ORDER BY Units.Units;"
frmName.cboUnits.RowSource = sqlUnits
End If
End Function
Click to expand...


Great! If, as I understood from before, cboUnits and cboCondition are
controls on the subform, then I guess you are passing the subform, not the
main form, as frmName. That's a very good solution.
 
B

bymarce

This function is being called from the cboConditions_AfterUpdate on the
subform as Call ConditionsUpdateUnits(Me). So I guess that does refer to the
subform.

Dirk Goldgar said:
bymarce said:
Thanks for your help and especially for the tip about the name property of
objects!! I figured something like that should be available but I didn't
know what it was. I checked the names and they were correct. I ended up
getting the code to work like this. It seams Access 07 doesn't require
the
subform be included in the reference.

Public Function ConditionsUpdateUnits(frmName As Form)
If IsNull(frmName.cboCondition) Then
frmName.cboUnits.RowSource = "SELECT DISTINCT Units.Units FROM
Units
ORDER BY Units.Units"
Else
Dim sqlUnits As String
Dim strQ As String
Dim strCND As String
strQ = """"
strCND = frmName.cboCondition
sqlUnits = "SELECT DISTINCT Units.Units " & _
"FROM Properties RIGHT JOIN Units ON
Properties.[UnitsType]=Units.[UnitsType] " & _
"WHERE (((Properties.Property)=" & strQ & strCND &
strQ & ")) " & _
"ORDER BY Units.Units;"
frmName.cboUnits.RowSource = sqlUnits
End If
End Function
Click to expand...


Great! If, as I understood from before, cboUnits and cboCondition are
controls on the subform, then I guess you are passing the subform, not the
main form, as frmName. That's a very good solution.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

bymarce said:
This function is being called from the cboConditions_AfterUpdate on the
subform as Call ConditionsUpdateUnits(Me). So I guess that does refer to
the
subform.
Click to expand...


Yep. As I said, that's a nice, tidy solution.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Set dynamic reference to a control in a public function 1
Iterating subform on Load/Open gives error 1
Assign recordset - subform 1
Duplicate record in Form and Subform 0
Position subform control in relation to another 7
change subform label backcolors function 1
value in subform 9
reference in recordset.findfirst 3

Top