Form

[bdehning]

Now a harder question.

If I have an option group with selections Monthly, Bi-Monthly, Quarterly and
Semi-Annually on a form.

If any of these are selected I have a subform [Service Calls Update] opened
which works fine. On the main form is a field call [Inception Date]. This
Subform will have 12 rows of fields. Each line will have a MM/YY Field, a
check box with label yes and a function field where up to 12 functions can be
selected.

What codes do I need such that if Quarterly is chosen in the option group
and the [Inception Date] is 10/01/04 and the 12 date fields should start with
October 2004 and show 12 months with an X in the check boxes for Oct 04, Jan
05, Apr 05, and Jul 05. Users need the ability to modify the pre-selected
choices and choose what they want.

Selecting Semi-Annually would cause check box X in October 04 and April 05

 

[Steve Schapel]

Brian,

Untested "air code" follows...

Private Sub YourOptionGroup AfterUpdate()
' (set the Option Value of the 4 butons to 1, 2, 3, 6)
Dim i As Integer
Dim MonthRef As String
Dim CheckRef As Boolean
For 1 = 1 To 12
MonthRef = Format(DateAdd("m",i-1,Me.Inception_Date),"mm/yy")
CheckRef = (i MOD Me.YourOptionGroup) = 0
CurrentDb.Execute "INSERT INTO YourSubformTable ( LabelYes,
MonthField )" & _
" VALUES ( " & CheckRef & ", '" &
MonthRef & "' )", dbFailOnError
Next i
With Me.YourSubForm
.Requery
.Visible = True
End With
End Sub

 

[bdehning]

Steve, I will give this a shot.

Brian,

Untested "air code" follows...

Private Sub YourOptionGroup AfterUpdate()
' (set the Option Value of the 4 butons to 1, 2, 3, 6)
Dim i As Integer
Dim MonthRef As String
Dim CheckRef As Boolean
For 1 = 1 To 12
MonthRef = Format(DateAdd("m",i-1,Me.Inception_Date),"mm/yy")
CheckRef = (i MOD Me.YourOptionGroup) = 0
CurrentDb.Execute "INSERT INTO YourSubformTable ( LabelYes,
MonthField )" & _
" VALUES ( " & CheckRef & ", '" &
MonthRef & "' )", dbFailOnError
Next i
With Me.YourSubForm
.Requery
.Visible = True
End With
End Sub

--
Steve Schapel, Microsoft Access MVP

Now a harder question.

If I have an option group with selections Monthly, Bi-Monthly, Quarterly and
Semi-Annually on a form.

If any of these are selected I have a subform [Service Calls Update] opened
which works fine. On the main form is a field call [Inception Date]. This
Subform will have 12 rows of fields. Each line will have a MM/YY Field, a
check box with label yes and a function field where up to 12 functions can be
selected.

What codes do I need such that if Quarterly is chosen in the option group
and the [Inception Date] is 10/01/04 and the 12 date fields should start with
October 2004 and show 12 months with an X in the check boxes for Oct 04, Jan
05, Apr 05, and Jul 05. Users need the ability to modify the pre-selected
choices and choose what they want.

Selecting Semi-Annually would cause check box X in October 04 and April 05

 

[Steve Schapel]

Brian,

I have realised that the Append Query I gave you will also need to
include the foreign key field in the SubformTable, for which you will
presumably use the value of the linking primary key field from the main
form. Sorry for this oversight.

 

[bdehning]

Steve,finally had time to start on this!!! Here is what I have but it fails
right now on the CurrentDb.Execute line and of course being a code rookie I
have questions.

Private Sub Frame99_AfterUpdate()
'(Set the option group value of the 4 buttons to 2,4,6,12)
Dim i As Integer
Dim MonthRef As String
Dim CheckRef As Boolean
For i = 1 To 12
MonthRef = Format(DateAdd("m", i - 1, Me.Inception_Date), "mm/yy")
CheckRef = (i Mod Me.Frame99) = 0
CurrentDb.Execute "Insert Into Service Calls (LabelYes,MonthField )"
&_"Values (" & CheckRef & ",'"& MonthRef & "')", dbFailOn Error
Next i
With Me.Service_Calls_Update_01 - 1 - 5
.Requery
.Visible = True
End With
End Sub

Do I use MonthRef as my Date Field Names and use 1-12 at the end of each?
Do I use CheckRef as my Check Box Field and use 1-12 at the end of each?
Not sure what you mean by include the foreign key field in the SubformTable,
for wich you wil presumably use the values of the linking primary key field
from the main form.
Does the code address the other Function Field that comes after the Check
box field? I will probably use a combo box for that field 12 times but need
the field to pop us with the rest of the line.

I will definitely need help to get through this so bear with me as I build
the fields.

 

[Steve Schapel]

Brian,

I'm not able to look at this thoroughly right now, but a quick browse of
your code shows spaces where there shouldn't be, and no spaces where
there should be. Try it like this...

CurrentDb.Execute "INSERT INTO [Service Calls] ( LabelYes, MonthField )" & _
" VALUES ( " & CheckRef & ",'" & MonthRef & "' )", dbFailOnError

 

[bdehning]

Steve, need the help when you get the time to answer those questions.

Takes code but obviously hits run-time error now when I click the option
group.

I get The INSERT INTO statement does not contain the following unknown field
name: 'LabelYes'. Make sure name is type correctly? What is LabelYes?

Brian,

I'm not able to look at this thoroughly right now, but a quick browse of
your code shows spaces where there shouldn't be, and no spaces where
there should be. Try it like this...

CurrentDb.Execute "INSERT INTO [Service Calls] ( LabelYes, MonthField )" & _
" VALUES ( " & CheckRef & ",'" & MonthRef & "' )", dbFailOnError

--
Steve Schapel, Microsoft Access MVP

Steve,finally had time to start on this!!! Here is what I have but it fails
right now on the CurrentDb.Execute line and of course being a code rookie I
have questions.

Private Sub Frame99_AfterUpdate()
'(Set the option group value of the 4 buttons to 2,4,6,12)
Dim i As Integer
Dim MonthRef As String
Dim CheckRef As Boolean
For i = 1 To 12
MonthRef = Format(DateAdd("m", i - 1, Me.Inception_Date), "mm/yy")
CheckRef = (i Mod Me.Frame99) = 0
CurrentDb.Execute "Insert Into Service Calls (LabelYes,MonthField )"
&_"Values (" & CheckRef & ",'"& MonthRef & "')", dbFailOn Error
Next i
With Me.Service_Calls_Update_01 - 1 - 5
.Requery
.Visible = True
End With
End Sub

Do I use MonthRef as my Date Field Names and use 1-12 at the end of each?
Do I use CheckRef as my Check Box Field and use 1-12 at the end of each?
Not sure what you mean by include the foreign key field in the SubformTable,
for wich you wil presumably use the values of the linking primary key field
from the main form.
Does the code address the other Function Field that comes after the Check
box field? I will probably use a combo box for that field 12 times but need
the field to pop us with the rest of the line.

I will definitely need help to get through this so bear with me as I build
the fields.

 

[Steve Schapel]

Brian,

Replace LabelYes with the name of the field that the checkbox on the
subform is bound to. You forgot to mention the name of this field
before, so I had to improvise

 

[Steve Schapel]

Brian,

I assumed you meant that there were 12 records in the subform, and that
the Function field would have the data added manually. Is this right?
I also assume that the table that the subform is based on, which it now
appears is called 'Service Calls', has a field which is the basis to
relate the subform to the main form. Is this right? This is what I
meant... this field will need to be included into the Append Query so
the resulting subform data will relate to the main form's ID. Make sense?

 

[bdehning]

The way it works manually now is that the last subform[Service Calls] is
continous and each time a new Scheduled Service Month is entered the Service
Call Number field creates a new record. So the plan to use one field for
each of 4 items but appear 12 times for the 12 Months. Then if user likes
predefined choices or changes manually, when they close the form it will ask
if they want to save changes and the records would be created, correct? So
yes, my plan is to list the same fields for the 12 rows on the form now to do
this.

Yes, field now called Call Type will be a combo box and be selected manually
for each of the rows.

I never did like this form manually but it worked. Each form is based on
the table that the fields refer to. Would I be better off and do I need to
now base these on queries so the code will work correctly.

Regarding the Append Query, is this the code or do I need to create this? I
am still confused on the append query. My forms are related by primary and
child keys in the tables.

I made the changes and the code does run but does not create fields
correctly yet but you are helping me progress obviously so thank you and keep
providing assistance when you get a chance

 

[Steve Schapel]

Brian,

.... So the plan to use one field for
each of 4 items but appear 12 times for the 12 Months.

Can you give an example, I have no idea what this means.

... Would I be better off and do I need to
now base these on queries so the code will work correctly.

No, I see no reason why the Record Source of the forms needs to change,
if they are currently functioning correctly.

Regarding the Append Query, is this the code or do I need to create this? I
am still confused on the append query.

The "INSERT INTO ..." in the code is what I am referring to as the
Append Query.

My forms are related by primary and
child keys in the tables.

Can you give an example.

I made the changes and the code does run but does not create fields
correctly yet ...

What does "does not create fields correctly" mean?

 

[bdehning]

Right now manually entered fields exist for the fields that users enter into
which fills the fields and creates the Service Call Number which is a unique
Auto Number. They can continue to create these service calls and when an
entry is made on the first blank field line another row appears with the
Service Call Number assigned automatically and how we reference these calols
in queries, reports, etc. On a new Account there is only the first row of
blank fields and the form expands as neede for as many calls are entered.

Since we work on a year basis, I am trying to have all 12 Months(12 Rows of
possible Service Calls appear). The actual record would not be saved unless
the user clicked ok by the last prompt.

 

[bdehning]

Account Information Table - PK Policy Number
Location Table PK Location ID, FK Policy Number
Service Calls - FK Location ID

Check Box field does not show a check but it is not greyed out either.
Service Call Month Field does not appear to be affected from what I can tell.


The subform wants to bring up previous scheduled calls and not create new
ones.

 

[Steve Schapel]

Brian,

Do I understand that the Service Calls subform is a subform on a
Locations form? In that case, first of all the subform's Link Child
Fields and Link Master Fields properties probably needs to be the
Location ID field, so that the Service Calls form only shows the records
for the current Location record. Am I right? And secondly, this would
probably be the field that I mentioned before needs to be included in
the Append Query, for example maybe it should be more like this...
CurrentDb.Execute "INSERT INTO [Service Calls] ( [Location ID],[Check
Box],[Service Call Month] )" & _
" VALUES ( " & Me.Location_ID & "," & CheckRef & ",'" &
MonthRef & "' )", dbFailOnError

 

[bdehning]

Here is what I tried after your post.

CurrentDb.Execute "INSERT INTO [Service Calls Update]([Location
ID],[Checkbox],[Scheduled Service Month])" & _
" VALUES ( " & CheckRef & ",'" & MonthRef & "' )", dbFailOnError

Number of query values and destination fields are not the same. I assume
this has to do with me having only one field for items I want to appear on
the subform. Fields I have on subform are [Scheduled Service Month],
[CheckBox], [Assigned Consultant], [Service Call Number], [Call Type].
[Assigned Consultant] and [Call Type] are Combo Boxes.

IF we get this to work there is a field [Assigned Consultant] on the
Location Update subform that could be used to feed a name to [Assigned
Consultant] on the Service Call Update subform. Then users could leave or
modify as required.

Does this help.

Main Form is Service Frequency Set-up 1-6-05. Location Update is a subform
of the main form. Service Call Update is a subform of the Location Update
Form.

Brian,

Do I understand that the Service Calls subform is a subform on a
Locations form? In that case, first of all the subform's Link Child
Fields and Link Master Fields properties probably needs to be the
Location ID field, so that the Service Calls form only shows the records
for the current Location record. Am I right? And secondly, this would
probably be the field that I mentioned before needs to be included in
the Append Query, for example maybe it should be more like this...
CurrentDb.Execute "INSERT INTO [Service Calls] ( [Location ID],[Check
Box],[Service Call Month] )" & _
" VALUES ( " & Me.Location_ID & "," & CheckRef & ",'" &
MonthRef & "' )", dbFailOnError

--
Steve Schapel, Microsoft Access MVP

Account Information Table - PK Policy Number
Location Table PK Location ID, FK Policy Number
Service Calls - FK Location ID

Check Box field does not show a check but it is not greyed out either.
Service Call Month Field does not appear to be affected from what I can tell.


The subform wants to bring up previous scheduled calls and not create new
ones.

 

[Steve Schapel]

Brian,

You forgot to include the Location ID in the VALUES clause. But then,
it looks like there is no Location ID field represented on the Service
Call Update subform... why is that?

 

[bdehning]

I guess I never needed the field on my original subform since the Child and
Master fields were Location ID and I used the tables as the source, the
Service Calls table received the Location ID and everything else worked.
Aside from connecting tabels the Location ID is not really used. It is even
invisible on the Locations Update form.

Was this a bad design on my part?

Brian,

You forgot to include the Location ID in the VALUES clause. But then,
it looks like there is no Location ID field represented on the Service
Call Update subform... why is that?

--
Steve Schapel, Microsoft Access MVP

Here is what I tried after your post.

CurrentDb.Execute "INSERT INTO [Service Calls Update]([Location
ID],[Checkbox],[Scheduled Service Month])" & _
" VALUES ( " & CheckRef & ",'" & MonthRef & "' )", dbFailOnError

Number of query values and destination fields are not the same. I assume
this has to do with me having only one field for items I want to appear on
the subform. Fields I have on subform are [Scheduled Service Month],
[CheckBox], [Assigned Consultant], [Service Call Number], [Call Type].
[Assigned Consultant] and [Call Type] are Combo Boxes.

IF we get this to work there is a field [Assigned Consultant] on the
Location Update subform that could be used to feed a name to [Assigned
Consultant] on the Service Call Update subform. Then users could leave or
modify as required.

Does this help.

Main Form is Service Frequency Set-up 1-6-05. Location Update is a subform
of the main form. Service Call Update is a subform of the Location Update
Form.

 

[bdehning]

Steve, you still out there to help?

Brian,

You forgot to include the Location ID in the VALUES clause. But then,
it looks like there is no Location ID field represented on the Service
Call Update subform... why is that?

--
Steve Schapel, Microsoft Access MVP

Here is what I tried after your post.

CurrentDb.Execute "INSERT INTO [Service Calls Update]([Location
ID],[Checkbox],[Scheduled Service Month])" & _
" VALUES ( " & CheckRef & ",'" & MonthRef & "' )", dbFailOnError

Number of query values and destination fields are not the same. I assume
this has to do with me having only one field for items I want to appear on
the subform. Fields I have on subform are [Scheduled Service Month],
[CheckBox], [Assigned Consultant], [Service Call Number], [Call Type].
[Assigned Consultant] and [Call Type] are Combo Boxes.

IF we get this to work there is a field [Assigned Consultant] on the
Location Update subform that could be used to feed a name to [Assigned
Consultant] on the Service Call Update subform. Then users could leave or
modify as required.

Does this help.

Main Form is Service Frequency Set-up 1-6-05. Location Update is a subform
of the main form. Service Call Update is a subform of the Location Update
Form.

 

[Steve Schapel]

Brian,

Thanks for the further explanation. I was just checking that the right
fields were in the right place. It is not bad design to not have the
linking child field represented on the subform, but I always put it
there as a hidden control anyway, just to help me keep track of what's
going on.

 

[Steve Schapel]

Yep. Any progress?