Hide Columns in a datasheet Subform based on a another field value

Hi
Can anyone provide me with some much needed guidance. I am VB challenged!!!

I have a form in datasheet view that displays many columns based on a query

Basically the users first fill out a form with information about an upcoming
advertising campaign.

They enter a campaign start date and the number of weeks for it to run.
There are many queries that run to select Radio Station and them to a
Prospective Buy Table.

Then they use another form where they fill out buy and post information for
each week of the campaign.

Some stations agree to run the ads for the entire campaign
Some stations agree to run some of the weeks up fron depending on their
schedule
Some stations decide each week if they will participate in the Campaign for
the next week.

So the users wanted check boxes for each week, so they could check them
ahead of time if they know upfront that a station is confirming for future
weeks.

On my form I have 6 checkboxes ( Wk1Chkbox, Wk2ChkBox, Wk3ChkBox, .....)
because the max a campaign can run is 6 weeks.

I hide all but Wk1 when the form is opened

Then I have a checkbox called WkChkBox that will unhide the remaining 5
weeks if they Check it.

My problem is,, if the total number of weeks the campaign is to run is less
than 6, (lets say 3 weeks) Then, I only want to show check boxes Wk1, Wk2,
Wk3 when they check the Box to Unhide.

Below is my code for hiding the checkboxes on Entering the Subform
the field that I need to use for comparing is NumWeeksTB

Anybody have suggestion on how to code it so I only show the checkboxes if
they are not greater than NumbWeeksTB?


Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))

End Sub


--
Nurse Nancy

Nurse Nancy -

Try this for your subroutine. It will also check the number of weeks:

Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 1)
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 2)
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 3)
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 4)
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 5)

End Sub

--
Daryl S


"Nurse Nancy" wrote:

Hi
Can anyone provide me with some much needed guidance. I am VB challenged!!!

I have a form in datasheet view that displays many columns based on a query

Basically the users first fill out a form with information about an upcoming
advertising campaign.

They enter a campaign start date and the number of weeks for it to run.
There are many queries that run to select Radio Station and them to a
Prospective Buy Table.

Then they use another form where they fill out buy and post information for
each week of the campaign.

Some stations agree to run the ads for the entire campaign
Some stations agree to run some of the weeks up fron depending on their
schedule
Some stations decide each week if they will participate in the Campaign for
the next week.

So the users wanted check boxes for each week, so they could check them
ahead of time if they know upfront that a station is confirming for future
weeks.

On my form I have 6 checkboxes ( Wk1Chkbox, Wk2ChkBox, Wk3ChkBox, .....)
because the max a campaign can run is 6 weeks.

I hide all but Wk1 when the form is opened

Then I have a checkbox called WkChkBox that will unhide the remaining 5
weeks if they Check it.

My problem is,, if the total number of weeks the campaign is to run is less
than 6, (lets say 3 weeks) Then, I only want to show check boxes Wk1, Wk2,
Wk3 when they check the Box to Unhide.

Below is my code for hiding the checkboxes on Entering the Subform
the field that I need to use for comparing is NumWeeksTB

Anybody have suggestion on how to code it so I only show the checkboxes if
they are not greater than NumbWeeksTB?


Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))

End Sub


--
Nurse Nancy

Nurse Nancy -

Try this for your subroutine. It will also check the number of weeks:

Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 1)
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 2)
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 3)
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 4)
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 5)

End Sub

--
Daryl S


"Nurse Nancy" wrote:

Hi
Can anyone provide me with some much needed guidance. I am VB challenged!!!

I have a form in datasheet view that displays many columns based on a query

Basically the users first fill out a form with information about an upcoming
advertising campaign.

They enter a campaign start date and the number of weeks for it to run.
There are many queries that run to select Radio Station and them to a
Prospective Buy Table.

Then they use another form where they fill out buy and post information for
each week of the campaign.

Some stations agree to run the ads for the entire campaign
Some stations agree to run some of the weeks up fron depending on their
schedule
Some stations decide each week if they will participate in the Campaign for
the next week.

So the users wanted check boxes for each week, so they could check them
ahead of time if they know upfront that a station is confirming for future
weeks.

On my form I have 6 checkboxes ( Wk1Chkbox, Wk2ChkBox, Wk3ChkBox, .....)
because the max a campaign can run is 6 weeks.

I hide all but Wk1 when the form is opened

Then I have a checkbox called WkChkBox that will unhide the remaining 5
weeks if they Check it.

My problem is,, if the total number of weeks the campaign is to run is less
than 6, (lets say 3 weeks) Then, I only want to show check boxes Wk1, Wk2,
Wk3 when they check the Box to Unhide.

Below is my code for hiding the checkboxes on Entering the Subform
the field that I need to use for comparing is NumWeeksTB

Anybody have suggestion on how to code it so I only show the checkboxes if
they are not greater than NumbWeeksTB?


Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))

End Sub


--
Nurse Nancy

Nurse Nancy -

Try this for your subroutine. It will also check the number of weeks:

Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 1)
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 2)
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 3)
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 4)
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = (Not
(Nz(Me.WkChkBox, 0)) AND Me.NumWeeksTB 5)

End Sub

--
Daryl S


"Nurse Nancy" wrote:

Hi
Can anyone provide me with some much needed guidance. I am VB challenged!!!

I have a form in datasheet view that displays many columns based on a query

Basically the users first fill out a form with information about an upcoming
advertising campaign.

They enter a campaign start date and the number of weeks for it to run.
There are many queries that run to select Radio Station and them to a
Prospective Buy Table.

Then they use another form where they fill out buy and post information for
each week of the campaign.

Some stations agree to run the ads for the entire campaign
Some stations agree to run some of the weeks up fron depending on their
schedule
Some stations decide each week if they will participate in the Campaign for
the next week.

So the users wanted check boxes for each week, so they could check them
ahead of time if they know upfront that a station is confirming for future
weeks.

On my form I have 6 checkboxes ( Wk1Chkbox, Wk2ChkBox, Wk3ChkBox, .....)
because the max a campaign can run is 6 weeks.

I hide all but Wk1 when the form is opened

Then I have a checkbox called WkChkBox that will unhide the remaining 5
weeks if they Check it.

My problem is,, if the total number of weeks the campaign is to run is less
than 6, (lets say 3 weeks) Then, I only want to show check boxes Wk1, Wk2,
Wk3 when they check the Box to Unhide.

Below is my code for hiding the checkboxes on Entering the Subform
the field that I need to use for comparing is NumWeeksTB

Anybody have suggestion on how to code it so I only show the checkboxes if
they are not greater than NumbWeeksTB?


Private Sub Weekly_Buy_Query_subform_Enter()

Me.Weekly_Buy_Query_subform.Form.Wk2ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk3ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk4ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk5ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))
Me.Weekly_Buy_Query_subform.Form.Wk6ChkBox.ColumnH idden = Not
(Nz(Me.WkChkBox, 0))

End Sub


--
Nurse Nancy