need help with inserting a set of records

Hello all & I hope I'm posting in the right place..

I need to load a temporary table with a range of sequential dates,
passing it a beginning and an end date from a criteria form.

I have created the form FrmMakeDayFile with 2 unbound fields
[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
button and have a routine of some sort append records to the table
tblDates with field Ddate inserted starting from txtDateFr and adding
a new record for each date, ending at TxtdateTo. (This data gets used
in a local front-end and is then zapped... but that part's under
control - I just need to quickly seed values to the dayfile table....)

I must be having a vague day as something that shoudld so easy is
eluding me... Can anybody start me off please.

thankyou.
Dave

mvdave wrote:

Quote:

Hello all & I hope I'm posting in the right place..
>
I need to load a temporary table with a range of sequential dates,
passing it a beginning and an end date from a criteria form.
>
I have created the form FrmMakeDayFile with 2 unbound fields
[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
button and have a routine of some sort append records to the table
tblDates with field Ddate inserted starting from txtDateFr and adding
a new record for each date, ending at TxtdateTo. (This data gets used
in a local front-end and is then zapped... but that part's under
control - I just need to quickly seed values to the dayfile table....)
>
I must be having a vague day as something that shoudld so easy is
eluding me... Can anybody start me off please.
>
thankyou.
Dave

Depends on what you already know how to do and what you don't. You need...

A VBA code procedure that opens an editable Recordset against your
destination table.

A Loop within that procedure.

Within the loop you create a date value one larger than the previous loop
iteration DateAdd() function being a good mechanism here.

Within that same loop you insert the generated date into your Recordset.

You start the loop with your start date and you stop the loop after you
have inserted the value matching your end date.

Which of the above do you already know how to do and which do you need help
with?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

On Apr 14, 2:54*am, "Rick Brandt" <rickbran...@hotmail.comwrote:

Quote:

mvdave wrote:

Quote:

Hello all & I hope I'm posting in the right place..

>

Quote:

I need to load a temporary table with a range of sequential dates,
passing it a beginning and an end date from a criteria form.

>

Quote:

I have created the form FrmMakeDayFile with 2 unbound fields
[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
button and have a routine of some sort append records to the table
tblDates with field Ddate inserted starting from txtDateFr and adding
a new record for each date, ending at TxtdateTo. (This data gets used
in a local front-end and is then zapped... but that part's under
control - I just need to quickly seed values to the dayfile table....)

>

Quote:

I must be having a vague day as something that shoudld so easy is
eluding me... *Can anybody start me off please.

>

Quote:

thankyou.
Dave

>
Depends on what you already know how to do and what you don't. *You need....
>
A VBA code procedure that opens an editable Recordset against your
destination table.
>
A Loop within that procedure.
>
Within the loop you create a date value one larger than the previous loop
iteration DateAdd() function being a good mechanism here.
>
Within that same loop you insert the generated date into your Recordset.
>
You start the loop with your start date *and you stop the loop after you
have inserted the value matching your end date.
>
Which of the above do you already know how to do and which do you need help
with?
>
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com- Hide quoted text -
>
- Show quoted text -

Thanks Rick... A programmer i ain't.. but here's where i got to with
it..which has elementrs of what you've advised. But it's not working..
I'm sure there's somthing simpler.

Dim sdate As Date
Dim edate As Date
Dim Ddate As Date

Dim rstDayfile As Recordset
Dim db As Database
Dim sqldates As String

Set db = CurrentDb()
sqldates = "select * from TblDates"
Set rstDayfile = db.OpenRecordset(sqldates, dbOpenDynaset)
sdate = Me.txtFromDate
edate = Me.txtToDate
Ddate = DMax("ddate", "tbldates")

With rstDayfile
Do Until Ddate edate
Ddate = sdate
AddNew
!Ddate = sdate + 1
Update
Loop
End With

mvdave wrote:

Quote:

Hello all & I hope I'm posting in the right place..
>
I need to load a temporary table with a range of sequential dates,
passing it a beginning and an end date from a criteria form.
>
I have created the form FrmMakeDayFile with 2 unbound fields
[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
button and have a routine of some sort append records to the table
tblDates with field Ddate inserted starting from txtDateFr and adding
a new record for each date, ending at TxtdateTo. (This data gets used
in a local front-end and is then zapped... but that part's under
control - I just need to quickly seed values to the dayfile table....)
>
I must be having a vague day as something that shoudld so easy is
eluding me... Can anybody start me off please.
>
thankyou.
Dave

I might have a command button to initiate the append. Air code
Dim datFor As date
Dim rst As Recordset
set rst = Currentdb.openrecordset("TableName",dbopendynaset)
It Not IsNull(Me.FromDate) And Not IsNull(Me.ToDate) then
For datFor = Me.FromDate to Me.ToDate
rst.Addnew
rst!TableDateField = datFor
rst.Update
Next
Endif

Soon is now
http://www.youtube.com/watch?v=K2NrIALcNOw

mvdave wrote:

Quote:

On Apr 14, 2:54 am, "Rick Brandt" <rickbran...@hotmail.comwrote:

Quote:

>mvdave wrote:

Quote:

>>Hello all & I hope I'm posting in the right place..

>>

Quote:

>>I need to load a temporary table with a range of sequential dates,
>>passing it a beginning and an end date from a criteria form.

>>

Quote:

>>I have created the form FrmMakeDayFile with 2 unbound fields
>>[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
>>button and have a routine of some sort append records to the table
>>tblDates with field Ddate inserted starting from txtDateFr and
>>adding a new record for each date, ending at TxtdateTo. (This data
>>gets used in a local front-end and is then zapped... but that
>>part's under control - I just need to quickly seed values to the
>>dayfile table....)

>>

Quote:

>>I must be having a vague day as something that shoudld so easy is
>>eluding me... Can anybody start me off please.

>>

Quote:

>>thankyou.
>>Dave

>>
>Depends on what you already know how to do and what you don't. You
>need...
>>
>A VBA code procedure that opens an editable Recordset against your
>destination table.
>>
>A Loop within that procedure.
>>
>Within the loop you create a date value one larger than the previous
>loop iteration DateAdd() function being a good mechanism here.
>>
>Within that same loop you insert the generated date into your
>Recordset.
>>
>You start the loop with your start date and you stop the loop after
>you have inserted the value matching your end date.
>>
>Which of the above do you already know how to do and which do you
>need help with?
>>
>--
>Rick Brandt, Microsoft Access MVP
>Email (as appropriate) to...
>RBrandt at Hunter dot com- Hide quoted text -
>>
>- Show quoted text -

>
Thanks Rick... A programmer i ain't.. but here's where i got to with
it..which has elementrs of what you've advised. But it's not working..
I'm sure there's somthing simpler.
>
Dim sdate As Date
Dim edate As Date
Dim Ddate As Date
>
Dim rstDayfile As Recordset
Dim db As Database
Dim sqldates As String
>
Set db = CurrentDb()
sqldates = "select * from TblDates"
Set rstDayfile = db.OpenRecordset(sqldates, dbOpenDynaset)
sdate = Me.txtFromDate
edate = Me.txtToDate
Ddate = DMax("ddate", "tbldates")
>
With rstDayfile
Do Until Ddate edate
Ddate = sdate
AddNew
!Ddate = sdate + 1
Update
Loop
End With

Try...

Dim edate As Date
Dim NewDate As Date
Dim rstDayfile As Recordset
Dim db As Database
Dim sqldates As String

Set db = CurrentDb()
sqldates = "select * from TblDates"
Set rstDayfile = db.OpenRecordset(sqldates, dbOpenDynaset)
NewDate = Me.txtFromDate
edate = Me.txtToDate

With rstDayfile
Do Until NewDate edate
.AddNew
!Ddate = NewDate
.Update
NewDate = DateAdd("d", 1, NewDate)
Loop
End With

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

On Apr 14, 3:24*am, Salad <o...@vinegar.comwrote:

Quote:

mvdave wrote:

Quote:

Hello all & I hope I'm posting in the right place..

>

Quote:

I need to load a temporary table with a range of sequential dates,
passing it a beginning and an end date from a criteria form.

>

Quote:

I have created the form FrmMakeDayFile with 2 unbound fields
[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
button and have a routine of some sort append records to the table
tblDates with field Ddate inserted starting from txtDateFr and adding
a new record for each date, ending at TxtdateTo. (This data gets used
in a local front-end and is then zapped... but that part's under
control - I just need to quickly seed values to the dayfile table....)

>

Quote:

I must be having a vague day as something that shoudld so easy is
eluding me... *Can anybody start me off please.

>

Quote:

thankyou.
Dave

>
I might have a command button to initiate the append. *Air code
* * * * Dim datFor As date
* * * * Dim rst As Recordset
* * * * set rst = Currentdb.openrecordset("TableName",dbopendynaset)
* * * * It Not IsNull(Me.FromDate) And Not IsNull(Me.ToDate) then
* * * * * * * * For datFor = Me.FromDate to Me.ToDate
* * * * * * * * * * * * rst.Addnew
* * * * * * * * * * * * rst!TableDateField = datFor
* * * * * * * * * * * * rst.Update
* * * * * * * * Next
* * * * Endif
>
Soon is nowhttp://www.youtube.com/watch?v=K2NrIALcNOw- Hide quoted text -
>
- Show quoted text -

OK - thankyou Salad!!

Here' the tidied version of your air code... Worked a treat! Thankyou
very much. It's 3:40 am here... time for some zzzzz's. Much
appreciated. Dave.

Private Sub CmdMakeDayFile_Click()

Dim datFor As Date
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)

If Not IsNull(Me.txtFromDate) And Not IsNull(Me.txtToDate) Then
For datFor = Me.txtFromDate To Me.txtToDate
rst.AddNew
rst!Ddate = datFor
rst.Update
Next
End If

End Sub

On Sun, 13 Apr 2008 09:33:11 -0700 (PDT), mvdave wrote:

Quote:

Hello all & I hope I'm posting in the right place..
>
I need to load a temporary table with a range of sequential dates,
passing it a beginning and an end date from a criteria form.
>
I have created the form FrmMakeDayFile with 2 unbound fields
[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
button and have a routine of some sort append records to the table
tblDates with field Ddate inserted starting from txtDateFr and adding
a new record for each date, ending at TxtdateTo. (This data gets used
in a local front-end and is then zapped... but that part's under
control - I just need to quickly seed values to the dayfile table....)
>
I must be having a vague day as something that shoudld so easy is
eluding me... Can anybody start me off please.
>
thankyou.
Dave

Copy and paste the below code into a Module.
Open your form and enter the 2 dates.
Run the Code.

Public Sub FillDates()
' Will fill a Date Field with consecutive date values.

Dim db As DAO.Database
Dim rs As Recordset
Dim dteFrom As Date
Dim dteTo As Date
Set db = CurrentDb
Set rs = db.OpenRecordset("tblDates")
dteFrom = Forms!FrmMakeDayFile![txtDateFr]
dteTo = Forms!FrmMakeDayFile![txtDateTo]

With rs
Do Until dteFrom = dteTo + 1
.AddNew
!Ddate = dteFrom
.Update
dteFrom = dteFrom + 1
Loop
End With
Set db = Nothing
Set rs = Nothing
End Sub
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

mvdave wrote:

Quote:

On Apr 14, 3:24 am, Salad <o...@vinegar.comwrote:
>

Quote:

>>mvdave wrote:
>>

Quote:

>>>Hello all & I hope I'm posting in the right place..

>>

Quote:

>>>I need to load a temporary table with a range of sequential dates,
>>>passing it a beginning and an end date from a criteria form.

>>

Quote:

>>>I have created the form FrmMakeDayFile with 2 unbound fields
>>>[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
>>>button and have a routine of some sort append records to the table
>>>tblDates with field Ddate inserted starting from txtDateFr and adding
>>>a new record for each date, ending at TxtdateTo. (This data gets used
>>>in a local front-end and is then zapped... but that part's under
>>>control - I just need to quickly seed values to the dayfile table....)

>>

Quote:

>>>I must be having a vague day as something that shoudld so easy is
>>>eluding me... Can anybody start me off please.

>>

Quote:

>>>thankyou.
>>>Dave

>>
>>I might have a command button to initiate the append. Air code
> Dim datFor As date
> Dim rst As Recordset
> set rst = Currentdb.openrecordset("TableName",dbopendynaset)
> It Not IsNull(Me.FromDate) And Not IsNull(Me.ToDate) then
> For datFor = Me.FromDate to Me.ToDate
> rst.Addnew
> rst!TableDateField = datFor
> rst.Update
> Next
> Endif
>>
>>Soon is nowhttp://www.youtube.com/watch?v=K2NrIALcNOw- Hide quoted text -
>>
>>- Show quoted text -

>
>
OK - thankyou Salad!!
>
Here' the tidied version of your air code... Worked a treat! Thankyou
very much. It's 3:40 am here... time for some zzzzz's. Much
appreciated. Dave.

It appears you are a bit aways from the US. Australia?

I think you can either go to bed now or get up for the Monday commute.

Quote:

>
Private Sub CmdMakeDayFile_Click()
>
Dim datFor As Date
Dim rst As Recordset
>
Set rst = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)
>
If Not IsNull(Me.txtFromDate) And Not IsNull(Me.txtToDate) Then
For datFor = Me.txtFromDate To Me.txtToDate
rst.AddNew
rst!Ddate = datFor
rst.Update
Next
End If
>
End Sub

On Apr 14, 4:25*am, Salad <o...@vinegar.comwrote:

Quote:

mvdave wrote:

Quote:

On Apr 14, 3:24 am, Salad <o...@vinegar.comwrote:

>

Quote:

>mvdave wrote:

>

Quote:

>>Hello all & I hope I'm posting in the right place..

>

Quote:

>>I need to load a temporary table with a range of sequential dates,
>>passing it a beginning and an end date from a criteria form.

>

Quote:

>>I have created the form FrmMakeDayFile with 2 unbound fields
>>[txtDateFr] and [TxtDateTo]. I simply want to be able to click a
>>button and have a routine of some sort append records to the table
>>tblDates with field Ddate inserted starting from txtDateFr and adding
>>a new record for each date, ending at TxtdateTo. (This data gets used
>>in a local front-end and is then zapped... but that part's under
>>control - I just need to quickly seed values to the dayfile table....)

>

Quote:

>>I must be having a vague day as something that shoudld so easy is
>>eluding me... *Can anybody start me off please.

>

Quote:

>>thankyou.
>>Dave

>

Quote:

>I might have a command button to initiate the append. *Air code
* * * *Dim datFor As date
* * * *Dim rst As Recordset
* * * *set rst = Currentdb.openrecordset("TableName",dbopendynaset)
* * * *It Not IsNull(Me.FromDate) And Not IsNull(Me.ToDate) then
* * * * * * * *For datFor = Me.FromDate to Me.ToDate
* * * * * * * * * * * *rst.Addnew
* * * * * * * * * * * *rst!TableDateField = datFor
* * * * * * * * * * * *rst.Update
* * * * * * * *Next
* * * *Endif

>

Quote:

>Soon is nowhttp://www.youtube.com/watch?v=K2NrIALcNOw-Hide quoted text-

>

Quote:

>- Show quoted text -

>

Quote:

OK - thankyou Salad!!

>

Quote:

Here' the tidied version of your air code... Worked a treat! Thankyou
very much. It's 3:40 am here... time for some zzzzz's. Much
appreciated. Dave.

>
It appears you are a bit aways from the US. *Australia?
>
I think you can either go to bed now or get up for the Monday commute.
>
>
>
>
>

Quote:

Private Sub CmdMakeDayFile_Click()

>

Quote:

Dim datFor As Date
Dim rst As Recordset

>

Quote:

Set rst = CurrentDb.OpenRecordset("tblDates", dbOpenDynaset)

>

Quote:

If Not IsNull(Me.txtFromDate) And Not IsNull(Me.txtToDate) Then
* * For datFor = Me.txtFromDate To Me.txtToDate
* * * * rst.AddNew
* * * * rst!Ddate = datFor
* * * * rst.Update
* * Next
End If

>

Quote:

End Sub- Hide quoted text -

>
- Show quoted text -- Hide quoted text -
>
- Show quoted text -

Yes, I'm in Sydney and thankyou to all for your quick and varied
suggestions. I've tried posting on other help forums before and gone
days without a response, so I really appreciate the way you've all
jumped in and helped.

Now.. I owe I owe... so off to work i go..

Dave

need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

re: need help with inserting a set of records

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about