How do you hide Columns in a SubForm

[Ayo]

I want to be able to control it with the cmbShoppingCart combobox. In the
tblMaterialsDatabase table, there are multiple Shopping Carts with multiple
items in each. I wanted to use the Shopping Cart combobox to display the
items in the subform.

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

 

[tina]

okay, i assume you're responding to my comment about using the
LinkChildFields and LinkMasterFields properties of the subform control
within the mainform. there's no reason why you can't name a combobox control
in the LinkMasterFields property. you can even name a mainform calculated
control in the LinkMasterFields property, and name a calculated field in the
LinkChildFields property, as long as that calculated field is included in
the subform's RecordSource. suggest you read up on the
LinkMasterFields/LinkChildFields topic in Access Help so you'll understand
their use and potential better.

hth

I want to be able to control it with the cmbShoppingCart combobox. In the
tblMaterialsDatabase table, there are multiple Shopping Carts with multiple
items in each. I wanted to use the Shopping Cart combobox to display the
items in the subform.

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

 

[Ayo]

Thanks. I will read more about it and see what I can get from there. You have
been a great help. Thank you very much.

okay, i assume you're responding to my comment about using the
LinkChildFields and LinkMasterFields properties of the subform control
within the mainform. there's no reason why you can't name a combobox control
in the LinkMasterFields property. you can even name a mainform calculated
control in the LinkMasterFields property, and name a calculated field in the
LinkChildFields property, as long as that calculated field is included in
the subform's RecordSource. suggest you read up on the
LinkMasterFields/LinkChildFields topic in Access Help so you'll understand
their use and potential better.

hth

I want to be able to control it with the cmbShoppingCart combobox. In the
tblMaterialsDatabase table, there are multiple Shopping Carts with multiple
items in each. I wanted to use the Shopping Cart combobox to display the
items in the subform.

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

 

[tina]

you're very welcome

Thanks. I will read more about it and see what I can get from there. You have
been a great help. Thank you very much.

okay, i assume you're responding to my comment about using the
LinkChildFields and LinkMasterFields properties of the subform control
within the mainform. there's no reason why you can't name a combobox control
in the LinkMasterFields property. you can even name a mainform calculated
control in the LinkMasterFields property, and name a calculated field in the
LinkChildFields property, as long as that calculated field is included in
the subform's RecordSource. suggest you read up on the
LinkMasterFields/LinkChildFields topic in Access Help so you'll understand
their use and potential better.

hth

I want to be able to control it with the cmbShoppingCart combobox. In the
tblMaterialsDatabase table, there are multiple Shopping Carts with multiple
items in each. I wanted to use the Shopping Cart combobox to display the
items in the subform.

:

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

 

[STEVE FRY]

Hi, I have a very similar problem, but instead hiding based on column name, I need to hide based on data in the subform. So far I have this, which works, but it doe snot cycle through the data (it simply takes the first value and hides the column based of that).

I would appreciate any help,

Steve

Me!Child86.Form.[Concrete Accessories].ColumnHidden = IsNull(Child86.Form.[Concrete Accessories]) = True



JimBurke via AccessMonster.com wrote:

Re: How do you hide Columns in a SubForm
22-May-09

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

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

Ayo wrote:

--
Jim Burke




EggHeadCafe - Software Developer Portal of Choice
WPF DataGrid Custom Paging and Sorting
http://www.eggheadcafe.com/tutorial...f-32b2d802ae17/wpf-datagrid-custom-pagin.aspx

 

[John Spencer]

Since a data sheet is homogeneous you can't show or hide a column in each row
depending on the value of the column in that row. The only time this could
work would be if you had a single record.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County