auto fil records

[wraith]

If I hire out a porter cabin, it can be hired out for one week or two weeks and so on.
In a data view, I want to be able to enter the date from and to with the number of weks it is on hire and be able to get the database to automatically fill all the records and dates so the accounts can sign off each payement and print a report showing who has payed and who has not.
Any ideas on how I can do this

 

[namliam]

simply create a do while loop to insert the records...
dim myWeek as integer
myWeek = 1
Do while myWeek <= Me.FormWeeks
'insert into table using either a recordset or Append Query
myWeek = myWeek + 1
loop
As Bugs would say, Thats all folks

Regards

 

[wraith]

where would I put this code?
and when would I run the append query, after they have entered the details like a afterupdate event

 

[namliam]

Put the code inside a button which users push to confirm they have finished or indeed the after update or something or rather, Experiment with it... to see what works for you.

Regards

 

[wraith]

Sorry about this, but do I insert into table using a recordset or append query, does this create new records with the dates

 

[wraith]

sorry again I should of said how do I

 

[namliam]

dim myWeek as integer
dim rs as DAO.recordset
set rs = currentdb.openrecordset("YourTable")
myWeek = 1
Do while myWeek <= Me.FormWeeks
'insert into table using either a recordset or Append Query
    rs.addnew
    rs!YourDateField = Me.FormDate + 7*(myWeek-1) 'makes the dates go up
    rs!OtherfieldsToFIll = OtherFields
    rs.update
    myWeek = myWeek + 1
loop
rs.close
Set rs = nothing

Something like so! Does that help? 

Regards

 

[wraith]

I is coming up with an error.
User Defind type not defined, this is what I put



Private Sub Command20_Click()

Dim rs As DAO.Recordset
Dim myWeek As Integer
Set rs = CurrentDb.openrecordset("BDcontinue")
myWeek = 1
Do While myWeek <= Me.formweeks
'insert into table using either a recordset or Append Query
rs.AddNew
rs!From = Me.From + 7 * (myWeek - 1) 'makes the dates go up
rs.Update
myWeek = myWeek + 1
Loop
rs.Close
Set rs = Nothing
End Sub.


The button is on the form the user fills out

 

[namliam]

You probably get stuck on:

Dim rs As DAO.Recordset


make sure the Microsoft DAO 3.?? library is referenced (Tools=>References from a VBA window)

Regards

 

[wraith]

Thanks for that the code works fine

 

[wraith]

I nned to modify the code.
I need to be able to put three text boxes or combo boxes on the form. days, weeks , months so the user can choose.
I tried using else if but did not work.
I was going to set the value to zero for all the text boxes so I would update the records if one of the text boxes was greater than one can you help

 

[namliam]

First post back the FULL code you have now.

Then, your now changing from a weekly approach to a flexible approach changing days/weeks/months depending on user input.

Next up is what if a user wants 1 month 2 weeks and 2 days?

How about having the user input the Start and end date instead of selecting preset periods?

Regards

 

[kingsgambit]

Code so far

Private Sub Command20_Click()

Dim rs As DAO.Recordset
Dim myWeek As Integer
Set rs = CurrentDb.openrecordset("BDcontinue")
myWeek = 1

Do While myWeek <= Me.weeks
'insert into table using either a recordset or Append Query
rs.AddNew
rs!EstDate = Me.EstDate + 7 * (myWeek - 1) 'makes the dates go up
rs!BDnumber = BDnumber
rs!Month = Month
rs!InvoiceNo = InvoiceNo

rs.Update
myWeek = myWeek + 1

Loop
rs.Close
Set rs = Nothing

End Sub

They want to be able to pick a day in the week then select every week or every other week until end date, as well as select x amount of week or months from there start date.
I thought of using a combo box with two coulmns one the user would see the other would have the days i.e 1 week = 7days
but I don't know how I would do the months or the every other selection.

Any ideas

 

[namliam]

I think that your going to get the request pretty soon, "Can i do 1 month 2 week and 1 day?"
Then your running into some difficulties. eg
Date = 15-jan
+ 1 month: 15-feb
+ 2 week (14 days): 29-feb (leap year, 2004)
+ 1 day: 1-mar
Which is equal to 6 weeks and 4 days or 46 days. Its not that hard to make or do but users might get unexpected results.

Anyway you can use the dateadd function to add the months (and even weeks and days) if you feel more comfy doing that. However additional problem is your storing.
Your now storing a record per week, do you want to keep it that way? Thus having a posible problem with the 4 spare days (out of the 7 in a week).
Also would you allow users to enter 46 days or tell them NO, max = 31 or 7 and add the other days in months/weeks.

The storage question is in particular important.

 

[Surfette]

I have question regarding the dates...

Along the same line, is it possible to ask the datebase to:

(1) If cabin is occupied from March 30, 2006 to April 5, 2006, auto populate the dates on the form view to show that all those dates are occupied? If so, where do I dictate the database to show that information.

(2) If I have renters renting more than one cabin/room (let's say they want to rent three rooms) can I enter in a form that these three cabins are reserved from dates 3/30/2006 to 4/5/2006 and on a separate calendar/form see that 3 out of the 7 cabins are occupied from 3/30/2006 till 4/5/2006?

Thanks!
Surfette