How do you hide Columns in a SubForm

[Ayo]

I am trying to hide some columns in a subform that I have on my form based
on the select in an Option Group Frame. I tried the visible property of the
textboxes in the subform but that doesn't work.
Any ideas.
Thanks

 

[PJFry]

Can you post the code you are using?

 

[Ayo]

Private Sub frFunctionalArea_Click()
Dim ctrl As Control
Select Case Me.frFunctionalArea
Case 1
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
ctrl.Visible = False
End If
Next
Case 2
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "SAC" Then
ctrl.Visible = False
End If
Next
Case 3
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Permitting" Then
ctrl.Visible = False
End If
Next
Case 4
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Pre-Con" Then
ctrl.Visible = False
End If
Next
Case 5
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Construction" Then
ctrl.Visible = False
End If
Next
Case 6
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Commission" Then
ctrl.Visible = False
End If
Next
Case 7
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Telco/MW" Then
ctrl.Visible = False
End If
Next
End Select
Call psBuildMilestoneSubformSQL

End Sub

 

[Dale_Fye via AccessMonster.com]

Is this subform a DataSheet?

I am trying to hide some columns in a subform that I have on my form based
on the select in an Option Group Frame. I tried the visible property of the
textboxes in the subform but that doesn't work.
Any ideas.
Thanks

 

[Dale_Fye via AccessMonster.com]

Is this subform a DataSheet?

I am trying to hide some columns in a subform that I have on my form based
on the select in an Option Group Frame. I tried the visible property of the
textboxes in the subform but that doesn't work.
Any ideas.
Thanks

 

[Dale_Fye via AccessMonster.com]

Is this subform a DataSheet?

I am trying to hide some columns in a subform that I have on my form based
on the select in an Option Group Frame. I tried the visible property of the
textboxes in the subform but that doesn't work.
Any ideas.
Thanks

 

[Ayo]

Yes it is.

Is this subform a DataSheet?

 

[Dale_Fye via AccessMonster.com]

Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale

Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]

 

[Ayo]

I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale

Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]

Any ideas.
Thanks

 

[JimBurke via AccessMonster.com]

Try using 'Me!' instead of 'Me.'

Private Sub frFunctionalArea_Click()
Dim ctrl As Control
Select Case Me.frFunctionalArea
Case 1
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
ctrl.Visible = False
End If
Next
Case 2
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "SAC" Then
ctrl.Visible = False
End If
Next
Case 3
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Permitting" Then
ctrl.Visible = False
End If
Next
Case 4
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Pre-Con" Then
ctrl.Visible = False
End If
Next
Case 5
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Construction" Then
ctrl.Visible = False
End If
Next
Case 6
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Commission" Then
ctrl.Visible = False
End If
Next
Case 7
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "Telco/MW" Then
ctrl.Visible = False
End If
Next
End Select
Call psBuildMilestoneSubformSQL

End Sub

Can you post the code you are using?

[quoted text clipped - 3 lines]

 

[JimBurke via AccessMonster.com]

Here's a good link that has the 'rules' for referencing subform properties
and controls...

http://www.mvps.org/access/forms/frm0031.htm

 

[Dale Fye]

Ayo,

It sure looks like your code should work. What version of Access are you
using?

Try loading the Form with subform, and then just playing around with the
syntax in the immediate window.

HTH
Dale

I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth
= 0
End If
Next
It didn't work for me. The code ran with out any problem except the
columns
are still in the sibform.

Well,

You can obviously drag the margins of the column in the subform, but to
do it
via code you are going to have to do something like the following. When
I
opened a datasheet as the form, I was able to go to the immediate window
and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform
control
the name "sub_datasheet" I was able to hide the column with the
following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale

Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[John W. Vinson]

Yes it is.

Then use a Continous Form instead. Datasheet cells don't have a "visible"
property (you can shrink them to zero width), continuous form Textboxes do.

 

[tina]

well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth

I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale

Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[Ayo]

I get an "Object required" error on ctl.ColumnHidden = (ctl.Tag = "General")
line.

well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth

I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[Ayo]

Now I am getting "Object doesn't support this property or method" error

well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth

I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth = 0
End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

Well,

You can obviously drag the margins of the column in the subform, but to do it
via code you are going to have to do something like the following. When I
opened a datasheet as the form, I was able to go to the immediate window and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform control
the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[tina]

hmm, yeah, me too. i'd never tried this before, so i'm surprised to see that
apparently Access doesn't recognize that the subform is in Datasheet view.
both ColumnHidden and ColumnWidth properties are specific to form Datasheet
view - they're not valid in SingleForm view or ContinuousForms view. let me
play with this a bit, and see if i can come up with a solution. i'll post
back today.

Now I am getting "Object doesn't support this property or method" error

well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the mainform*. to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the Other tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth

I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then

Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth
= 0

End If
Next
It didn't work for me. The code ran with out any problem except the columns
are still in the sibform.

:

Well,

You can obviously drag the margins of the column in the subform, but

to
do it

via code you are going to have to do something like the following.

When
I

opened a datasheet as the form, I was able to go to the immediate

window
and

type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the

subform
control

the name "sub_datasheet" I was able to hide the column with the following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[tina]

well, of course, the problem wasn't Access - it recognized the Datasheet
view just fine. the problem was that i was having a stupid attack (not so
unusual for me!). the error in the loop was caused by the fact the the loop
goes through *all* the controls on the form, which includes the labels
attached to the textbox controls. and labels don't have a ColumnHidden
property, at any time. so let's try the following code instead, as

Dim ctl As Control, frm As Form, str As String

Select Case Me!frFunctionalArea
Case 1
str = "General"
Case 2
str = "SAC"
Case 3
str = "Permitting"
Case 4
str = "Pre-Con"
Case 5
str = "Construction"
Case 6
str = "Commission"
Case 7
str = Telco/MW"
Case Else
str = "show column"
End Select

Set frm = Me!SubformControlName.Form
For Each ctl In frm.Controls
If Not TypeOf ctl Is Label Then ctl.ColumnHidden = (ctl.Tag = str)
Next

again, replace SubformControlName with the correct name of the subform
control within the mainform, as i explained earlier in the thread. the code
skips labels, so that takes care of the error we both experienced.

also, note that instead of setting the ColumnHidden property explicitly to
True, i used "toggle" code to hide/show each column as appropriate. this is
an important difference from your posted code - even if ctrl.Visble had been
a valid property reference in Datasheet view, your code would have hidden
controls *but would not have UNhidden them where appropriate*. when working
with properties that change conditionally, remember that you not only want
to change a property when a certain condition is met, you also usually want
to set the property *back* to its' "default" setting when the condition
isn't met.

hth

hmm, yeah, me too. i'd never tried this before, so i'm surprised to see that
apparently Access doesn't recognize that the subform is in Datasheet view.
both ColumnHidden and ColumnWidth properties are specific to form Datasheet
view - they're not valid in SingleForm view or ContinuousForms view. let me
play with this a bit, and see if i can come up with a solution. i'll post
back today.

Now I am getting "Object doesn't support this property or method" error

mainform*.

to

Other

tab

but

to

do it
via code you are going to have to do something like the following. When
I
opened a datasheet as the form, I was able to go to the immediate window
and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform
control
the name "sub_datasheet" I was able to hide the column with the
following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[Ayo]

Thanks for all your help Tina. I already got it to work but I like your idea
better so I am going to use it. I ahve another problem that I hope you might
be able to shed some light on. THis sub works great:
Private Sub cmbSiteId_AfterUpdate()
Call psBuildComboSQL
end sub

Private Sub psBuildComboSQL()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Shopping Cart] " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId.Value & """) AND " & _
"([Market] = """ & Me.Market.Value & """) AND " & _
"([NLP] = """ & Me.NLP.Value & """) " & _
"ORDER BY [Shopping Cart]"
Me.cmbShoppingCart.RowSource = strSQL
Me.cmbShoppingCart = Null
Me.cmbShoppingCart.Requery
End Sub
Except for the fact that when I go to desihn view and come back the form
view, the previous first item in the combobox is gone and a new one is there
instead. Furthermore the same thing as happen to the record in datasheet
view. The record is no longer the first record. It's disappeared somewhere in
the middle of the datasheet. I can't figure out why its doing this.

My other issue is I can't get the following SQL statement to display a
result in the subform. Any ideas why?
Dim strSubformSQL As String, siteID As String
strSubformSQL = "SELECT * " & _
"FROM tblMilestones " & _
"WHERE ([Candidate Id] = """ & Me.cmbSiteId.Value & """) AND " & _
"([Market Name] = """ & Me.Market.Value & """) AND " & _
"([UMTS NLP Status] = """ & Me.NLP.Value & """)"
Me.tblMilestones_subform.Form.RecordSource = strSubformSQL

Thanks again for all the help. Really appreciated.

well, of course, the problem wasn't Access - it recognized the Datasheet
view just fine. the problem was that i was having a stupid attack (not so
unusual for me!). the error in the loop was caused by the fact the the loop
goes through *all* the controls on the form, which includes the labels
attached to the textbox controls. and labels don't have a ColumnHidden
property, at any time. so let's try the following code instead, as

Dim ctl As Control, frm As Form, str As String

Select Case Me!frFunctionalArea
Case 1
str = "General"
Case 2
str = "SAC"
Case 3
str = "Permitting"
Case 4
str = "Pre-Con"
Case 5
str = "Construction"
Case 6
str = "Commission"
Case 7
str = Telco/MW"
Case Else
str = "show column"
End Select

Set frm = Me!SubformControlName.Form
For Each ctl In frm.Controls
If Not TypeOf ctl Is Label Then ctl.ColumnHidden = (ctl.Tag = str)
Next

again, replace SubformControlName with the correct name of the subform
control within the mainform, as i explained earlier in the thread. the code
skips labels, so that takes care of the error we both experienced.

also, note that instead of setting the ColumnHidden property explicitly to
True, i used "toggle" code to hide/show each column as appropriate. this is
an important difference from your posted code - even if ctrl.Visble had been
a valid property reference in Datasheet view, your code would have hidden
controls *but would not have UNhidden them where appropriate*. when working
with properties that change conditionally, remember that you not only want
to change a property when a certain condition is met, you also usually want
to set the property *back* to its' "default" setting when the condition
isn't met.

hth

hmm, yeah, me too. i'd never tried this before, so i'm surprised to see that
apparently Access doesn't recognize that the subform is in Datasheet view.
both ColumnHidden and ColumnWidth properties are specific to form Datasheet
view - they're not valid in SingleForm view or ContinuousForms view. let me
play with this a bit, and see if i can come up with a solution. i'll post
back today.

Now I am getting "Object doesn't support this property or method" error

:

well, first of all, make sure you're using the right reference for the
subform. you need the name of the *subform control within the

mainform*.

to
get that, open the mainform in Design view. within Design view, click *once*
on the subform, to select it. in the Properties box, click on the

Other

tab
and look at the Name property. that's the name of the subform control. then
try the following, as

Dim ctl As Control frm As Form

Set frm = Me!SubformControlName.Form

For Each ctl In frm.Controls
ctl.ColumnHidden = (ctl.Tag = "General")
Next

replace SubformControlName with the correct name that you got from the
exercise in the previous paragraph, of course.

hth


I just tried it using:
For Each ctrl In Me.tblMilestones_subform.Form.Controls
If ctrl.Tag = "General" Then
Me.tblMilestones_subform.Form.Controls(ctrl.Name).ColumnWidth
= 0
End If
Next
It didn't work for me. The code ran with out any problem except the
columns
are still in the sibform.

:

Well,

You can obviously drag the margins of the column in the subform,

but

to
do it
via code you are going to have to do something like the following. When
I
opened a datasheet as the form, I was able to go to the immediate window
and
type:

forms("frm_Datasheet").Controls("txt_Field1").columnwidth = 0

and it hid the column.

When I put the datasheet in a form as a subform, and gave the subform
control
the name "sub_datasheet" I was able to hide the column with the
following:

forms("frm_Datasheet_Parent").sub_Datasheet.controls("txt_Field1").
ColumnWidth = 0

To make it visible again, give the ColumnWidth property a value in twips
(inches * 1440)

HTH
Dale



Ayo wrote:
Yes it is.

Is this subform a DataSheet?

[quoted text clipped - 3 lines]
Any ideas.
Thanks

 

[tina]

comments inline.

Thanks for all your help Tina. I already got it to work but I like your idea
better so I am going to use it. I ahve another problem that I hope you might
be able to shed some light on. THis sub works great:
Private Sub cmbSiteId_AfterUpdate()
Call psBuildComboSQL
end sub

Private Sub psBuildComboSQL()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Shopping Cart] " & _
"FROM tblMaterialsDatabase " & _
"WHERE ([Site ID] = """ & Me.cmbSiteId.Value & """) AND " & _
"([Market] = """ & Me.Market.Value & """) AND " & _
"([NLP] = """ & Me.NLP.Value & """) " & _
"ORDER BY [Shopping Cart]"
Me.cmbShoppingCart.RowSource = strSQL
Me.cmbShoppingCart = Null
Me.cmbShoppingCart.Requery
End Sub
Except for the fact that when I go to desihn view and come back the form
view, the previous first item in the combobox is gone and a new one is there
instead. Furthermore the same thing as happen to the record in datasheet
view. The record is no longer the first record. It's disappeared somewhere in
the middle of the datasheet. I can't figure out why its doing this.

well, it's not clear what the problem is. you're setting criteria on the
RowSource, using a SQL statement, so i wouldn't be surprised that the
RowSource changes. isn't that the point? the question is 1) is the SQL
statement returning the correct items to populate the combobox control
droplist? and 2) are the items in ascending order on the ShoppingCart
column?

if the answer to either question is no, i'd move the SQL statement into a
query and troubleshoot it there; when it's working as desired, then move it
back into the RowSource and test it again.

My other issue is I can't get the following SQL statement to display a
result in the subform. Any ideas why?
Dim strSubformSQL As String, siteID As String
strSubformSQL = "SELECT * " & _
"FROM tblMilestones " & _
"WHERE ([Candidate Id] = """ & Me.cmbSiteId.Value & """) AND " & _
"([Market Name] = """ & Me.Market.Value & """) AND " & _
"([UMTS NLP Status] = """ & Me.NLP.Value & """)"
Me.tblMilestones_subform.Form.RecordSource = strSubformSQL

the first thing i'd check would be the data type of the criteria fields. you
have the criteria for three fields written as Text data type, surrounded by
double quotes. so go back to tblMilestones and check the data type of fields
CandidateID, MarketName, and UMTSNLPStatus. if any of those are NOT Text
data type, then change the SQL statement to reflect the appropriate data
type for the appropriate criteria. and btw, there is no need to refer to the
..Value property of controls in the form, since that is the default control
property in VBA. you can write the code as

"WHERE ([Candidate Id] = """ & Me.cmbSiteId & """) AND " & _
"([Market Name] = """ & Me.Market & """) AND " & _
"([UMTS NLP Status] = """ & Me.NLP & """)"

the same syntax holds true for the SQL statement used in the combobox
control's RowSource, above.

having said all the above, i'm wondering why you're going to the trouble of
writing criteria into the subform's RecordSource, rather than just assigning
the relevant fieldnames to the subform control's LinkChildFields and
LinkMasterFields properties, within the mainform.

hth