Editing Existing records

[hughess7]

Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[strive4peace]

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[hughess7]

Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

 

[strive4peace]

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[hughess7]

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[hughess7]

Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

:

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[strive4peace]

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

:

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[hughess7]

Hi again

Sorry you are getting my two tasks mixed up. I don't want to create a new
table for the virtual records. I want to append to the same recordset
amending existing records.

My recordset looks like this for example (see below). The spec and
ReviewDates are already populated when sent to the user. The user has entered
Bank Holiday 1 day, Audit 4 days. As soon as they have entered the 4, I want
the Activity (and other fields) copied to the records for 03/01/07, 04/01/07
and 05/01/07.

Spec ReviewDate Activity ReviewDays
1 01/01/07 Bank Holiday 1
1 02/01/07 Audit 4
1 03/01/07
1 04/01/07
1 05/01/07

Sue

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

:

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

:

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[strive4peace]

Hi sue,

I have created a Word document with all the information you have
provided -- I will print it out and see if I can get a better grasp on
what you need. I will not be able to do this until later -- just wanted
to let you know.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi again

Sorry you are getting my two tasks mixed up. I don't want to create a new
table for the virtual records. I want to append to the same recordset
amending existing records.

My recordset looks like this for example (see below). The spec and
ReviewDates are already populated when sent to the user. The user has entered
Bank Holiday 1 day, Audit 4 days. As soon as they have entered the 4, I want
the Activity (and other fields) copied to the records for 03/01/07, 04/01/07
and 05/01/07.

Spec ReviewDate Activity ReviewDays
1 01/01/07 Bank Holiday 1
1 02/01/07 Audit 4
1 03/01/07
1 04/01/07
1 05/01/07

Sue

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue


:

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

:

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

:

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[hughess7]

Thanks....

Sue

Hi sue,

I have created a Word document with all the information you have
provided -- I will print it out and see if I can get a better grasp on
what you need. I will not be able to do this until later -- just wanted
to let you know.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi again

Sorry you are getting my two tasks mixed up. I don't want to create a new
table for the virtual records. I want to append to the same recordset
amending existing records.

My recordset looks like this for example (see below). The spec and
ReviewDates are already populated when sent to the user. The user has entered
Bank Holiday 1 day, Audit 4 days. As soon as they have entered the 4, I want
the Activity (and other fields) copied to the records for 03/01/07, 04/01/07
and 05/01/07.

Spec ReviewDate Activity ReviewDays
1 01/01/07 Bank Holiday 1
1 02/01/07 Audit 4
1 03/01/07
1 04/01/07
1 05/01/07

Sue

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue


:

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

:

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

:

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[strive4peace]

Hi Sue,

I have been reviewing what you have written and need to know more about
your data structure. Here is something you can do to document that for us:

create a new general module*

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

*How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

'~~~~~~~~~~~~~~~~~~

I am a little confused why you are copying data that is already stored
to another record, it sounds like your tables need to be structured
differently.

why not a main table with SpecID and the other relevant information
including a start and stop date? Why do you feel you need to make a
record for each day and duplicate what is already in the first record?

If you do want to have a daily record, it should be kept in another
table and linked to the main table using an ID field (is SpecID unique
to the first table?). Then, information in that table would be specific
to that day, not a repeat of anything else already stored.

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks....

Sue

Hi sue,

I have created a Word document with all the information you have
provided -- I will print it out and see if I can get a better grasp on
what you need. I will not be able to do this until later -- just wanted
to let you know.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hi again

Sorry you are getting my two tasks mixed up. I don't want to create a new
table for the virtual records. I want to append to the same recordset
amending existing records.

My recordset looks like this for example (see below). The spec and
ReviewDates are already populated when sent to the user. The user has entered
Bank Holiday 1 day, Audit 4 days. As soon as they have entered the 4, I want
the Activity (and other fields) copied to the records for 03/01/07, 04/01/07
and 05/01/07.

Spec ReviewDate Activity ReviewDays
1 01/01/07 Bank Holiday 1
1 02/01/07 Audit 4
1 03/01/07
1 04/01/07
1 05/01/07

Sue

:

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue


:

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

Hope this makes sense?
Thanks
Sue

:

Hi Sue,

sounds like ReviewDays is a calculated field. You should not be storing
calculated fields as you can display them with an equation anytime.
What does ReviewDays represent?

Also, why are you changing CustID and Activity?

Can you explain a bit more about your data and the purpose of your
application?

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Thanks for the reply, think you've maybe slightly misunderstood what I am
trying to do though unless I don't understand your code? I do not want to
append/add records as the record with each relevant date already exists. I
want to find the record with the date and update (add data) to the rest of
the fields in the record. I want to use ReviewDays as a loop count to update
the correct number of days.

I thought something like this ? ....

set rst = db.openrecordset("select * from QuarterlyPlan)

IntDays = ReviewDays
CheckDate = ReviewDate

Do while IntDays > 1
If Weekday(CheckDate) = vbFriday then
CheckDate = CheckDate + 3
Else
CheckDate = CheckDate + 1
End If

rst.findfirst "[ReviewDate] = #" & CheckDate & "#"

rst.edit

rst.Activity = Me![Activity]
rst. CustID = Me![custid]
etc

rst. Update

IntDays = IntDays - 1

Loop

:

Hi Sue,

one way to do this would be to use code to loop through the dates and
append a record or records to the underlying table using SQL

in code behind the form, try something like this:

'~~~~~~~~~~~~~~~~~~
dim mDate as date _
, s as string

for mDate = me.Date_controlname +1 to me.Date2
s = "INSERT INTO Tablename (datefield, textfield, numberfield ) " _
& "SELECT #" & mDate & "#" _
& ", '" & me.text_controlname & "'"
& ", " & me.number_controlname
& ";"
currentdb.execute s
next mDate

doEvents

me.requery
'~~~~~~~~~~~~~~~~~~`

assuming Date2 is an unbound control containing the date to end

since you are already making a record for the first date, you would add
1 to that date for appending records using SQL


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi all

I have a continuous form displaying data, it has the fields SpecID and
ReviewDate populated and the rest of the information is entered by a user. If
an activity lasts more than one day, I want the record details to copy to the
next date for as long as the activity lasts. ie if an activity is entered on
01/01/07 and lasts 5 days, I want to programatically update 2 - 5 Jan with
the same data. I want the code to stop if it can't find a date, but to skip
weekend dates. I then want the form to refresh to show the new data.

Can someone offer some help with the best way to do this please? Would it be
with a findfirst recordset using ReviewDate then rs.edit and rs.update?

Thanks
Sue

 

[hughess7]

Thanks, I've sorted it now anyway. You don't need to worry about my table
structure, it is correct. I can't store end date as like I said before the
days may not be consecutive so start - end date would not be able to
calculate the length of the activity, hence I have a field ReviewDays which
stores the number of days the activity lasts. The reason why I need to
populate a table with the actual daily events is purely so I can plot them
properly on a calendar report. I produce a quarterly plan via crosstab query
and print in an access report. I have created code to loop through the dates
and written all records to a temporary Schedule table which gets populated
each time the report runs.

I could store the dates for activities that last more than one day in
another table with SpecID and ItineraryID but not sure I need to.

Thanks for all your efforts in trying to understand and help with this!

Sue

Hi Sue,

I have been reviewing what you have written and need to know more about
your data structure. Here is something you can do to document that for us:

create a new general module*

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

*How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

'~~~~~~~~~~~~~~~~~~

I am a little confused why you are copying data that is already stored
to another record, it sounds like your tables need to be structured
differently.

why not a main table with SpecID and the other relevant information
including a start and stop date? Why do you feel you need to make a
record for each day and duplicate what is already in the first record?

If you do want to have a daily record, it should be kept in another
table and linked to the main table using an ID field (is SpecID unique
to the first table?). Then, information in that table would be specific
to that day, not a repeat of anything else already stored.

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks....

Sue

Hi sue,

I have created a Word document with all the information you have
provided -- I will print it out and see if I can get a better grasp on
what you need. I will not be able to do this until later -- just wanted
to let you know.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi again

Sorry you are getting my two tasks mixed up. I don't want to create a new
table for the virtual records. I want to append to the same recordset
amending existing records.

My recordset looks like this for example (see below). The spec and
ReviewDates are already populated when sent to the user. The user has entered
Bank Holiday 1 day, Audit 4 days. As soon as they have entered the 4, I want
the Activity (and other fields) copied to the records for 03/01/07, 04/01/07
and 05/01/07.

Spec ReviewDate Activity ReviewDays
1 01/01/07 Bank Holiday 1
1 02/01/07 Audit 4
1 03/01/07
1 04/01/07
1 05/01/07

Sue

:

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue


:

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.

 

[strive4peace]

you're welcome, Sue glad you got it

by the way, for plotting purposes, you could use a query with multiple
tables for your source. Storing redundant data is not necessary since
you can repeat data from one table...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks, I've sorted it now anyway. You don't need to worry about my table
structure, it is correct. I can't store end date as like I said before the
days may not be consecutive so start - end date would not be able to
calculate the length of the activity, hence I have a field ReviewDays which
stores the number of days the activity lasts. The reason why I need to
populate a table with the actual daily events is purely so I can plot them
properly on a calendar report. I produce a quarterly plan via crosstab query
and print in an access report. I have created code to loop through the dates
and written all records to a temporary Schedule table which gets populated
each time the report runs.

I could store the dates for activities that last more than one day in
another table with SpecID and ItineraryID but not sure I need to.

Thanks for all your efforts in trying to understand and help with this!

Sue

Hi Sue,

I have been reviewing what you have written and need to know more about
your data structure. Here is something you can do to document that for us:

create a new general module*

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

*How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

'~~~~~~~~~~~~~~~~~~

I am a little confused why you are copying data that is already stored
to another record, it sounds like your tables need to be structured
differently.

why not a main table with SpecID and the other relevant information
including a start and stop date? Why do you feel you need to make a
record for each day and duplicate what is already in the first record?

If you do want to have a daily record, it should be kept in another
table and linked to the main table using an ID field (is SpecID unique
to the first table?). Then, information in that table would be specific
to that day, not a repeat of anything else already stored.

to help you understand how to structure data in Access, send me an email
and request my 30-page Word document on Access Basics (for Programming)
-- it doesn't cover VBA, but prepares you for it because it covers
essentials in Access. Be sure to put "Access Basics" in the subject
line so that I see your message...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks....

Sue


:

Hi sue,

I have created a Word document with all the information you have
provided -- I will print it out and see if I can get a better grasp on
what you need. I will not be able to do this until later -- just wanted
to let you know.

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Hi again

Sorry you are getting my two tasks mixed up. I don't want to create a new
table for the virtual records. I want to append to the same recordset
amending existing records.

My recordset looks like this for example (see below). The spec and
ReviewDates are already populated when sent to the user. The user has entered
Bank Holiday 1 day, Audit 4 days. As soon as they have entered the 4, I want
the Activity (and other fields) copied to the records for 03/01/07, 04/01/07
and 05/01/07.

Spec ReviewDate Activity ReviewDays
1 01/01/07 Bank Holiday 1
1 02/01/07 Audit 4
1 03/01/07
1 04/01/07
1 05/01/07

Sue

:

Hi sue,

"simply trying to create a new table with certain
field information copied into it from another table."

why not use a Make-Table query then, instead of looping?

~~~~~~~~ MAKE TABLE ~~~~~~~~
SELECT fieldlist
INTO tablename
IN c:\path\anotherdatabase.mdb
FROM tablename
WHERE conditions to process before recordset is made
GROUP BY fieldlist
HAVING conditions for fields that are grouped
ORDER BY fieldlist;

once you have copied the records, you can use an Update query to set
ReviewDays to 0

~~~~~~~~~~~ UPDATE ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#


I am a little confused, though, why you are creating a new table for
this information as opposed to using a temporary Flag (yes/no) field in
your table to mark records...

~~~~~~~~~~~ Calendar Report ~~~~~~~~~~~

I have some code that outputs information to an HTML file in a Calendar
format. If you are interested, email me and I'll send it to you. Put
"HTML Calendar Report" in the subject so I don't miss your message...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



hughess7 wrote:
Sorry had a few days off and got out of sync. I just realised I explained
stage 1 of the process to you - DOH! Which I do also need help with though!

Stage 2, which I was asking for advice on in this post, is done at the
Specialists end. I want them to fill out entries for every date in the
quarter that they are sent, so if they enter an activity of greater than 1
ReviewDays, I want all the data in the record to copy to the record(s) for
the next day. eg 1 Jan they enter details of a 5 day audit, when they enter 5
days in the ReviewDays field I want 2, 3, 4 and 5 Jan records to be
automatically updated with the same information as the fields in 1 Jan
record. It is purely just to show on a calendar form I have, I will set
ReviewDays to equal 0 for all these records and they will not get imported
back into the live system.

Sue


:

Hi again

I'll try to explain it to you...

ReviewDays is not a calculated field. In my data I have [ReviewDate] which
is the start date of an activity then [ReviewDays] stores the number of days
the activity lasts. I do not have an end date field as the activities are not
always on consecutive days!

I am not changing any data, simply trying to create a new table with certain
field information copied into it from another table. It will be used to send
out to our specialists who amend the data in their own copy of a standalone
database and send back to us when completed. Kind of like a transfer file, we
do this currently as they do not have access to our servers and live data in
the office. Long term this is being looked at, with me getting our systems on
the web somehow, but for now we manage by using these Access 'transfer files'
(small MDBs).

The specialists have to complete a quarterly Itinerary Plan and send to us
each quarter. I want to keep their task as simple and easy for them as
possible. So I want to send them blank records for every Mon-Frid for the
next quarter and they have to fill out the blanks and return. For some dates
at the beginning of the quarter and for certain activities eg bank holidays
we will already know the information so the plan needs to be populated with
any information we already have before sending to them. eg for next quarter
we would already know the Easter bank holiday dates.

The Table [Itinerary] contains the live data we already know, the plan needs
to include data from this table and just a Date and SpecID for those days no
entry exists. I know how to append the data from [Itinerary] but I just need
some code to create the missing working (Mon-Frid) dates and SpecID. I want
to do it this way to make it easier to detect if they have missed an entry
for any day in the required quarter.