Append Rows using criteria from form 1

[Taff82]

Hi all,

Am a bit stuck with this one.

I have a table "tblReviews" with the following fields:-

Learn_ID
Review_PlannedDate
Review_ActualDate
Reviewer

I have an unbound form with the following txt boxes:-

txtLearn_ID
txtReviewStartDate (Format dd/mm/yyyy)
txtIntervalWks
txtAmountOfReviews (Number)
txtReviewer

What I would like to happen is after I have completed the above fields, appends the rows to my table.

So if

txtLearn_ID = Aj0982
txtReviewStartDate = 01/09/2005
txtIntervalWks = 1
txtAmountOfReviews = 4
txtReviewer = Ann

I would like to Add 4 rows to my table which should look something like the following:-

Learn_ID, Review_PlannedDate, Reviewer
Aj0982, 08/09/2005, Ann
Aj0982, 15/09/2005, Ann
Aj0982, 22/09/2005, Ann
Aj0982, 29/09/2005, Ann

I'm sure it is possible, just stumped on how to go about it.


Thanks in advance for any help.


Taff.

 

[earthandfire]

Typed - not tested - and uses DAO


dim rs as recordset
set rs = currentdb.openrecordset("tblReviews")
with rs
for a = 1 to txtAmountOfReviews
.addnew
!Learn_ID = txtLearn_ID
!Review_PlannedDate = txtReviewStartDate + (a * 7 * txtIntervalWeeks)
!Reviewer = txtReviewer
.update
next a
end with
rs.close

Hope this helps.

 

[Taff82]

Hi earthandfire,

Tried the above and got an Data Type Mismatch on the following line:


!Review_PlannedDate = txtReviewStartDate + (a * 7 * txtIntervalWeeks)

Changed it to !Review_PlannedDate = cdate(txtReviewStartDate) + (a * 7 * txtIntervalWeeks)

and now it works great.

Many thanks for your help.

Taff.

 

[earthandfire]

I was tempted to put cdate in, but without Access running to test it, I "guessed" wrongly, that it would be ok without it.

Glad you got it working.

 

[Taff82]

Hi again,

In a follow up to the above, when I run the code, if any of the records violate the Primary Key it does not add any.

i.e. If I try to add 10 records and the first one Violates the primary key the rest are not added.

Does anyone have any suggestions?

Thanks again in advance for any help.

Taff

 

[PHV]

If the code bomb out with an error message you may consider the On Error Resume Next instruction before the For loop.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Taff82]

Hi PHV,

In my error trapping added If err.number = 3022 then
resume next and it seemed to work.

Thanks for your help, appreciated.

Taff.