Create records via code

[Guest]

Hi, I have a table of records which I want to use to add data to a seperate
itinerary table. I have a yes/no field and I know I can create an append
query to add all records which have been selected (yes chosen), but my
question is how to deal with the dates.

Currently I have a field which states number of days the activity is to take
place, for the purposes of the itinerary and other reports I think I need to
have one record per day though, eg if an audit lasts 4 days I need to create
four records with all data the same except for the date ? I presume I will
have to add the records via code but I am not sure on how to do this. Would I
open the itinerary recordset and then create a loop using the number of days
as the count and just keeping adding one to the date field?

Thanks in advance for any help.
Sue

 

[Alex White MCDBA MCSE]

*** Air Code ***

dim adoTest as new adodb.recordset
dim intCounter as integer
intCounter = 0
with adoTest
.open "Select * from TblTest",adopenkeyset,adlockdynamic
do While intCounter < 4
.addnew
.fields("myDate").value = dateAdd("d",intCounter,Now())
.... other fields
.update
intCounter = intCounter + 1
loop
.close
end with

the above code is completely untested and is just an example.

 

[Alex White MCDBA MCSE]

Sorry I have an error in the code.

.open "Select * from TblTest",currentproject.connection,
adopenkeyset,adlockdynamic

Sorry about that.

 

[adsl]

Hi, I have a table of records which I want to use to add data to a
seperate
itinerary table. I have a yes/no field and I know I can create an append
query to add all records which have been selected (yes chosen), but my
question is how to deal with the dates.

Currently I have a field which states number of days the activity is to
take
place, for the purposes of the itinerary and other reports I think I need
to
have one record per day though, eg if an audit lasts 4 days I need to
create
four records with all data the same except for the date ? I presume I will
have to add the records via code but I am not sure on how to do this.
Would I
open the itinerary recordset and then create a loop using the number of
days
as the count and just keeping adding one to the date field?

Thanks in advance for any help.
Sue

 

[adsl]

*** Air Code ***

dim adoTest as new adodb.recordset
dim intCounter as integer
intCounter = 0
with adoTest
.open "Select * from TblTest",adopenkeyset,adlockdynamic
do While intCounter < 4
.addnew
.fields("myDate").value = dateAdd("d",intCounter,Now())
.... other fields
.update
intCounter = intCounter + 1
loop
.close
end with

the above code is completely untested and is just an example.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

 

[adsl]

Sorry I have an error in the code.

.open "Select * from TblTest",currentproject.connection,
adopenkeyset,adlockdynamic

Sorry about that.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

 

[Guest]

Thanks, I will try this. One query though - the days are not always 4 so I
would need to read the contents of the AuditDays field in as a variable for
intCounter?

 

[Alex White MCDBA MCSE]

Yes, maybe what you need is this,

if you have different types of audits,

create a table for audit definitions

in that table, have an Audit_Duration field and use that value for the loop.