hihi... little help on adding records pleeeease <3

[Madmental]

hi... right fairly new to Access... (tho i have been using it now for about 6 months)... im alright at VB coding and didnt think this would be much of a problem... but hmmm it is microsoft....

Right, what i want to do is:
I have a customer rent form which runs in this order:
Pick a building. Pick a Room. Pick a company and date.

that all works fine... BUT...
the way i would hopeufilly like it to work is:

once youv chosen your date it calculates your length of renting (does that already)

so... I would like it to then take say the 12 months of renting and create a record for everymonth..
e.g
LeaseID = 334
Month = 1
LeaseID = 334
Month = 2
and soooo on..
i thought it would be just a simple loop, but im running into problems with the fact Access clears my values on the form everytime i use a new or next record etc so i just cnat get it to work with a while/until or for

can this be done? iv googled everything i can imagine this comming under but all im gettin is crazy .asp code

all help is much appreciated... even if its a "dont be stuped noob this cant be done"... least then i know where i stant

 

[The Stoat]

You should have a chat with this guy looks like he's doing the same project as you.

http://www.access-programmers.co.uk/forums/showthread.php?t=99829

 

[ScottGem]

Assuming you want to the records to be added with the first month of the lease, I would do it like this:

Dim strSQL As String
Dim dteCurrmth As Date

dteCurrMth = Me.txtLeaseStart
strSQL = "INSERT INTO table (LeaseID, LeaseMonth) "

For i = 1 to Me.txtNumMonths
dteCurrMth = DateAdd("m",i,Me.txtStartDate)
strSQL = strSQL & "VALUES(" & Me.txtLeaseID & ", #" & dteCurrMth & "#);"
CurrentDb.Execute strSQL
Next i

This will add a record to the table for each month in the lease term (txtNumMonths) for each month.

 

[Madmental]

thanks a lot will get on it riiiight now even tho my brains fried.. ta much.

 

[Sergeant]

strSQL = "INSERT INTO table (LeaseID, LeaseMonth) "

For i = 1 to Me.txtNumMonths
dteCurrMth = DateAdd("m",i,Me.txtStartDate)
strSQL = strSQL & "VALUES(" & Me.txtLeaseID & ", #" & dteCurrMth & "#);"
CurrentDb.Execute strSQL
Next i

This strSQL is gonna be quite long by the time it reaches Me.txtNumMonths, no? This routine keeps appending "VALUES(" & Me.txtLeaseID & ", #" & dteCurrMth & "#);" onto the string.

Perhaps:
CurrentDb.Execute strSQL & "VALUES(" & Me.txtLeaseID & ", #" & dteCurrMth & "#);"

 

[Madmental]

Hmmmz.... iv used PHP and SQL to create a pretty extensive website... but this is causing real problems with the date...

removing the date from that SQL staement works grand... but once added it starts giving the "INSERT INTO" statement error.... do i need to replace the # with the chr() code?

can anyone see this error without you having to go to the hassle of creating/testing it?

it says strSQL = "INSERT INTO Billing (LeaseID, Date) VALUES (333, #11/12/2004#);"

On my table, Date is a... Date

 

[ScottGem]

First Thanks to Seargeant for catching that error. He offers one solution. I would have chosen to Add

Dim strSQL1 As String

Then change

strSQL1 = "INSERT INTO table (LeaseID, LeaseMonth) "

and

strSQL = strSQL1 & "VALUES(" & Me.txtLeaseID & ", #" & dteCurrMth & "#);"

As do the problem you are having, don't use Date as a field name. Its a reserved word in Access and shouldn't be used for object names.

 

[Madmental]

oki dokie... should have knowen that in all honesty... since i spent a year learning that VB doesnt like its own variables being used......

thx for the fast replies... much appreciated.