How do you hide Columns in a SubForm

[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

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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