Please Help! VB being arsey!!

[benbenson]

I am trying to create some code that, when command button clicked, records created in an existing table. I have two text boxes, startdate and enddate. In the table there are two fields, date and roomno. I want the code to enter a record for every date between the two text boxes, and for each room. However, for some reason, it tries to enter a null value in the roomNo field.
Here is the code:

Private Sub CmdDates_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim temp1 As Integer
Dim count As Integer

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("roomdate", dbOpenDynaset)

With rs
Do While StartDate < EndDate
count = 1
Do While count < 10
.AddNew
Date = StartDate
roomno = count
.Update
count = count + 1
Loop
StartDate = StartDate + 1
Loop
End With

End Sub

The roomNo and date are the composite key for the table RoomDate. Its probably something really obvious, but Ive been staring at it for too long!!
Please Help!

Ben.

 

[Howlsta]

you need to refer to table field names like this:

rs!roomno etc

I noticed your trying to add 1 to the date. You cannot to a date in this way, it will cause a type mismatch.

change this

StartDate = StartDate + 1

to

StartDate = DateAdd ("d",1,StartDate)

and change the format of the two text boxes to short date.

Watch out though that code still has some errors and you might go into an infinite loop.

BTW do you want the code to add 10 rooms for each date, that's what the first loop seems to be doing.

Rich

 

[benbenson]

Rich, You're a star! The date increment works ok. I was just making up the code from other fragments. Thanks for the help!!!

 

[Howlsta]

Ben,

Glad to help. So is the second loop working OK then?

Must have been my computer not responding before, I thought it had got stuck in the loop and had to shut down the application.