Append

[PhilipEwen]

Hi, i am filling data between dates on a form.
i.e. enter date from, date to, other data
=> fills table with
1/1/01 other data
2/1/01 other data
3/1/01 other data
etc etc

Someone ( Rich ) kindly showed me the best way of doing this with the following code.
My problem is that if data already exists for these dates, i want it to append the data, not add more data to it, as there will then be multiple 'other data' for that date.

How do i do an Append to append the data, or Add New if the dates do not exist ?

Cheers

Phil
Function FillTable()
Dim DB As Database
Dim rst As DAO.Recordset
Dim StDate As Date
Dim EndDate As Date
Dim I As Variant
Dim NoOfDys As Integer, hotelid As Integer, datefield As Date, hb As String, al As String, sc As String, bb As String
StDate = Forms!hoteldetails!txt_from
EndDate = Forms!hoteldetails!txt_to
NoOfDys = DateDiff("d", StDate, EndDate)
Set DB = CurrentDb
Set rst = DB.OpenRecordset("rates")

For I = -1 To NoOfDys - 1
rst.MoveLast
rst.AddNew

rst!datefield = DateAdd("d", I + 1, StDate)
rst!rateshotelid = Forms!hoteldetails!hotelid
'rst!hotelid = Forms!hoteldetails!hotelid
'rst!hotelname = Forms!hoteldetails!hotelname
'rst!hotelsresortid = Forms!hoteldetails!hotelsresortid
rst!hb = Forms!hoteldetails!txt_hb
rst!al = Forms!hoteldetails!txt_al
rst!sc = Forms!hoteldetails!txt_sc
rst!bb = Forms!hoteldetails!txt_bb

rst.Update


Next I


rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing

 

[PhilipEwen]

I have seen Pam Hartman's excellent advice in pointing towards the Microsoft website.
I now realise that you cannot update and append if the table has it's index as an Autonumber - i'm stuck then !!

A work around i am going to try is run a query before the data entry, which looks to see if there is data between the dates entered. it will then set a warning and delete that exisiting data....
hope it works !