Inserting a series of dates -PLEASE HeLp !!

[PhilipEwen]

Hi,
I am trying to insert a set of data which is entered from an unbound form into a table.
I want to enter a From: date and a To: date and the by calculating the difference in days between the 2 dates, loop through an insert statement to enter the same data, but just adding 1 day to the date each time.

I may have gone arse over tit on this, but it seems the way to do it !! :0)

I have 2 problems...
1. the insert command is fine if i use the Forms!hoteldetails!hotelsresortid as the data, but i need to be able to add a day to that date - therefore i have assigned a variable 'entrydate' and would normally just add a day.
Unfortunately i keep getting an 'Enter parameter Value' for entrydate if i put this in the SQL statement
Surely this is just a syntax thing...but i have tried all sorts of methods and am still stuck.

PROBLEM 2:
I have calculated the difference in days between the dates using DateDiff - no problem.
I then start my loop, insert the data, add a day ( if i can get prob.1 sorted ! ), add 1 to the count for the loop and loop again until all days are filled.
The problem is that it seems to add 2 to the loop count variable each time, therefore if i have a date diff of 6 days, it'll only loop 3 times, not 6 !!!!!!!!!!!

Could someone PLEASE PLEASE PLEASE check my code and tell me why i am being an idiot !

Many thanks

Private Sub btn_enter_new_prices_Click()
' check if text boxes are filled If [txt_from] <> "" And [txt_to] <> "" And [txt_hb] <> "" And [txt_al] <> "" And [txt_sc] <> "" And [txt_bb] <> "" Then

Dim ss As String
Dim dateinterval As Integer
Dim firstdate As Date
Dim lastdate As Date
Dim counter As Integer
Dim entrydate As Date

'set up variables
entrydate = Forms!hoteldetails!txt_from 'set the entry date to enter the correct data to the From field
counter = 1 ' counts the loops


firstdate = Forms!hoteldetails!txt_from 'firstdate for calculation
lastdate = Forms!hoteldetails!txt_to ' last date for calculation

dateinterval = DateDiff("d", [firstdate], [lastdate]) 'sets the number of days between the dates
dateinterval = dateinterval + 1 ' add 1 to include last day

'start loop
For counter = 1 To dateinterval
ss = "" 'clears any old data
ss = "INSERT INTO [hotels] ( hotelid,hotelname,hotelsresortid,[date],hb,al,sc,bb ) VALUES (Forms!hoteldetails!hotelid,Forms!hoteldetails!hotelname,Forms!hoteldetails!hotelsresortid,[entrydate],Forms!hoteldetails!txt_hb,Forms!hoteldetails!txt_al,Forms!hoteldetails!txt_sc ,Forms!hoteldetails!txt_bb);"

'DoCmd.SetWarnings (False)
DoCmd.RunSQL ss
'DoCmd.SetWarnings (True)
Me.Requery
entrydate = entrydate + 1

If counter > dateinterval Then Exit For
counter = counter + 1
Next counter

End Sub

 

[Rich]

Why do you need to add dates to a table in this way without any other data?

 

[PhilipEwen]

I am inserting other data...
ss = "INSERT INTO [hotels] ( hotelid,hotelname,hotelsresortid,[date],hb,al,sc,bb ) VALUES (Forms!hoteldetails!hotelid,Forms!hoteldetails!hotelname,Forms!hoteldetails!hotelsresortid,[entrydate],Forms!hoteldetails!txt_hb,Forms!hoteldetails!txt_al,Forms!hoteldetails!txt_sc ,Forms!hoteldetails!txt_bb);"

all that data.
I just need to post the same data with different dates marked on it - the from: and to: dates and all dates in between

 

[Rich]

Something like this, if your replacing all the existing data then delete the table contents first, and change the rst.MoveLast to MoveFirst although you'll have to leave one dummy record in the table, I also see you have a field named [Date] you should change this to something else, it's a reserved word in access and will cause you problems, I've called it txtDate for this.
Function FillTable()
Dim DB As DATABASE
Dim rst As Recordset
Dim StDate As Date
Dim EndDate As Date
Dim I As Variant
Dim NoOfDys As Integer, hotelid As Integer,hotelname As String, hotelsresortid As Integer , txtDate 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("YourTable")
rst.MoveLast
For I = -1 To NoOfDys - 2
rst.AddNew
rst!txtDate = DateAdd("m", I + 1, StDate)
rst!hotelid = Forms!hoteldetails!hotelid
rst!hotelname = Forms!hoteldetails!hotelname
rst!hotelsresortid = Forms!hoteldetails!hotelsresortid
rst!hb = Forms!hoteldetails!txt_hb
rst!alt = Forms!hoteldetails!txt_al
rst!sc=Forms!hoteldetails!txt_sc
rst!bb=Forms!hoteldetails!txt_bb


rst.Update
rst.MoveNext

Next I


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


End Function

 

[PhilipEwen]

Thanks for your help on this - i am trying to test it but i keep getting 'Compile error - userdefined type not defined' for the line Dim dbs As Database

What does this mean ??
I will search the forum and post back if i have figured it out !!

Cheers

 

[PhilipEwen]

figured....ADO 3.6 not installed, so i installed that.
I then had a problem with the Dim rst = Recordset etc etc. I had to Dim rst as ADO.Recordset

then it all worked fine.

Thanks very much for that Rich - really appreciated. I'm now not quite as bald as i thought i'd be from pulling my hair out !!

Cheers again

( for those who want the correct code... )
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, hotelname As String, hotelsresortid 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("hotels")
rst.MoveLast
For I = -1 To NoOfDys - 2
rst.AddNew
rst!datefield = DateAdd("m", I + 1, StDate)
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
rst.MoveNext

Next I


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


End Function

 

[Rich]

Yes sorry I have that inferior 97 product, I don't have to dim dao ado etc. Should I now "upgrade"?

 

[PhilipEwen]

?? if you want

 

[Rich]

Not for all the tea in China!

 

[PhilipEwen]

I'm afraid you've got me there....
I don't know enough about Access to know if oyu are being serious or sarcastic.
Either way are you trying to make a point? as to be honest i wouldn't know which version is better than the other, so not really sure where your response sprang from ?

Thanks for your help though - it was appreciated.

 

[PhilipEwen]

have since read through the whole ocnversation and i now understand where the comment came from.
Did you per chance take offence 'cos i said "(for those of you who want the correct code...)"
If so, i mearly meant 'correct' in the contaxt of what i had previously dicussing which was issues with MS2000 and the errors i had found + how i had resolved the errors - NOTHING more.
I surely didn't mean to cause offence by infering that you code was 'incorrect' as it was not 2000. As i said, i don't know enough about it to really formulate an opinion of which is better or 'correct' - i just think you took it out of context.

 

[Rich]

No offence Phillip, just having a go at MS, there are many bugs in A2k that weren't in 97, I've just bought a new notebook with Win Mill pre-installed, it's a pile of rap!

 

[PhilipEwen]

is it possible to do a similar looping process as you have mentioned, but by using 'Insert into ....' instead of all this rst.blah ??
It has to loop through the rates field BETWEEN txt_date AND txt_date_to, inserting the same data with the only change is the date= date + 1 ( day )

The reason i ask is that if all the records are deleted, i immediately get an 'not found error' as in the code it says move to last record, which it can't if there are no records....

or is there another way around this ??