Creating values in a table in code

[keri]

Hi,

I have a table with 2 fields - flddate and category.

The values of flddate are all working day dates (monday to friday
dates between jan 2007 and 2010 (although the end date could change).

I would like to write code to insert the numbers 1 to 6 in repetition
into the category field until every row is filled.

For example I would like the finished table to be;

flddate category
1/jan/07 1
2/jan/07 2
3/jan/07 3
4/jan/07 4
5/jan/07 5
8/jan/07 6
9/jan/07 1
10/jan/.07 2

etc, etc. As I said the flddate values are already completed.

Any help or where to start is appreciated. Thanks,

[engles@ridesoft.com]

Re: Creating values in a table in code

On Mar 24, 10:28 am, "keri" <keridow...@hot mail.comwrote:

Hi,
>
I have a table with 2 fields - flddate and category.
>
The values of flddate are all working day dates (monday to friday
dates between jan 2007 and 2010 (although the end date could change).
>
I would like to write code to insert the numbers 1 to 6 in repetition
into the category field until every row is filled.
>
For example I would like the finished table to be;
>
flddate category
1/jan/07 1
2/jan/07 2
3/jan/07 3
4/jan/07 4
5/jan/07 5
8/jan/07 6
9/jan/07 1
10/jan/.07 2
>
etc, etc. As I said the flddate values are already completed.
>
Any help or where to start is appreciated. Thanks,

I can't imagine why you want that rather than day of week, but ...

dim db as dao.database, r as dao.recordset
Dim i as integer

set db=currentdb
set r=db.openrecord set ("Select * from table ORDER BY flddate;",
dbopendynaset)
i = 1
do while not r.eof
r.edit
r!Category = i
r.update
i = i + 1
if (i 6) then i=1
r.movenext
end do
r.close
set r=nothing
set db=nothing

This is one way and it's simple.

-- Larry Engles

[keri]

Re: Creating values in a table in code

Larry,

Many thanks for the above. I presume by the day of the week comment
you are referring to Mon being a 1 and Tue being a 2 etc etc, in which
case me needing 6 numbers on Mon - Fri wouldn't work. However if I am
missing the point please correct me as I may be making this difficult
for myself!

Anyway the code is super, thanks,

[Don Leverton]

Re: Creating values in a table in code

Hi Keri,
Have you looked at the Weekday() function?
Weekday(date, [firstdayofweek])
You can set the "firstdayofweek " to 2, and it should display the results you
want.

"keri" <keridowson@hot mail.comwrote in message
news:1174767346 .824874.32130@d 57g2000hsg.goog legroups.com...

Larry,
>
Many thanks for the above. I presume by the day of the week comment
you are referring to Mon being a 1 and Tue being a 2 etc etc, in which
case me needing 6 numbers on Mon - Fri wouldn't work. However if I am
missing the point please correct me as I may be making this difficult
for myself!
>
Anyway the code is super, thanks,
>

[Don Leverton]

Re: Creating values in a table in code

Sorry Keri ... I hadn't absorbed what you had said. You want to populate a
table with these values, not use existing data ... right?

If this is the case, then I believe that Larry's code won't do the job
either ... he too is attempting to work with existing data.

Let me ponder on this for a while, and I'll post some new code.

Don.
"Don Leverton" <leveriteNoJunk Mail@telusplane t.netwrote in message
news:4pyNh.1771 9$x9.2041@edtnp s89...

Hi Keri,
Have you looked at the Weekday() function?
Weekday(date, [firstdayofweek])
You can set the "firstdayofweek " to 2, and it should display the results

you

want.
>
"keri" <keridowson@hot mail.comwrote in message
news:1174767346 .824874.32130@d 57g2000hsg.goog legroups.com...

Larry,

Many thanks for the above. I presume by the day of the week comment
you are referring to Mon being a 1 and Tue being a 2 etc etc, in which
case me needing 6 numbers on Mon - Fri wouldn't work. However if I am
missing the point please correct me as I may be making this difficult
for myself!

Anyway the code is super, thanks,

>
>

[Don Leverton]

Re: Creating values in a table in code

Ok Keri, try this:

Using Access97, I created an unbound form that contains:
=============== =============== =========
A calendar control ("MyCalendar ")
2 un-bound text-boxes ("txtStartDa te" and "txtEndDate ") both "Enabled", but
"Locked", and formatted "Short Date".
(This forces the user to use the calendar control to set the dates ... which
WILL be formatted correctly.)

A Command Button ("cmdAddRecords ")
A subform ("sbfFldDate ") which is linked to the table ("tblFldDate s")

Also... "flddate" in tblFldDates is indexed, Allow duplicates = No
This provision is so that you can not accidentally duplicate existing
entries, yet still allows newer dates.
(ie. if you entered a start date of "01/01/2007" again and changed the end
date to "01/01/2011", it wil not duplicate (Errorcode 3022) the 2007 to 2010
dates , but it WILL add records from 01/02/2010 to 01/01/2011.)
=============== =============== =========

Here is the entire code that drives this operation.

=============== =============== =========
Option Compare Database
Option Explicit
Dim ctl As Variant
Dim MyStartDate As Date
Dim MyDate As Date
Dim MyEndDate As Date
Dim MyCategory



Private Sub cmdAddRecords_C lick()
On Error GoTo ErrHandler

Dim rstAddDates As DAO.Recordset
Set rstAddDates = Me.sbfFldDate.F orm.RecordsetCl one
MyStartDate = Me.txtStartDate
MyEndDate = Me.txtEndDate

'Let's make sure we have a Start Date and an End Date before proceeding.
If IsDate(MyStartD ate) And IsDate(MyEndDat e) Then

With rstAddDates ' Doing this will save us a bunch of typing.
'Add the initial values
.AddNew
!flddate = MyStartDate
MyDate = MyStartDate
MyCategory = WeekDay(MyDate, vbMonday)
!Category = MyCategory
.Update

Do Until MyDate = MyEndDate 'Set up a Loop that will continue until
we want it to end.
MyDate = DateAdd("d", 1, MyDate) 'Now add one day at a time
MyCategory = WeekDay(MyDate, vbMonday) 'Gets a "day of week
value"(1 to 5) for the date

If MyCategory 0 And MyCategory < 6 Then 'We only want to
add values 1 thru 5
.AddNew 'This adds the data into the subform's
recordset.
!flddate = MyDate
!Category = MyCategory
.Update
.Bookmark = .LastModified
Me.sbfFldDate.F orm.Bookmark = .Bookmark 'This will show
the new records as they are created.
End If

Loop

.Close

End With
Set rstAddDates = Nothing 'Release the recordset object

Else

MsgBox ("Please make sure that both Start and End Dates are provided.")

End If

ErrHandler:
If Err.Number = 3022 Then
Resume Next
Else
Dim Msg
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, , "Error"
Exit Sub
End If

End Sub

Private Sub MyCalendar_Afte rUpdate()


Select Case ctl
Case 1
Me.txtStartDate .SetFocus
Me![txtStartDate] = Me![MyCalendar]
MyStartDate = Me![MyCalendar]

Case 2
Me.txtEndDate.S etFocus
Me![txtEndDate] = Me![MyCalendar]
MyEndDate = Me![MyCalendar]
Case Else

Exit Sub
End Select


End Sub
Private Sub txtStartDate_En ter()
ctl = 1

End Sub

Private Sub txtEndDate_Ente r()
ctl = 2

End Sub

Private Sub txtStartDate_Ke yDown(KeyCode As Integer, Shift As Integer)
ctl = 1
MsgBox ("Please Select a date from the Calendar.")
Me.MyCalendar.S etFocus
Me![txtStartDate] = Me.MyCalendar
End Sub

Private Sub txtEndDate_KeyD own(KeyCode As Integer, Shift As Integer)
ctl = 2
MsgBox ("Please Select a date from the Calendar.")
Me.MyCalendar.S etFocus
Me![txtEndDate] = Me.MyCalendar

End Sub


I have tested this, and found that it does what I think you are trying to
do.
HTH,
Don


"Don Leverton" <leveriteNoJunk Mail@telusplane t.netwrote in message
news:xwyNh.1774 7$x9.101@edtnps 89...

Sorry Keri ... I hadn't absorbed what you had said. You want to populate a
table with these values, not use existing data ... right?
>
If this is the case, then I believe that Larry's code won't do the job
either ... he too is attempting to work with existing data.
>
Let me ponder on this for a while, and I'll post some new code.
>
Don.
"Don Leverton" <leveriteNoJunk Mail@telusplane t.netwrote in message
news:4pyNh.1771 9$x9.2041@edtnp s89...

Hi Keri,
Have you looked at the Weekday() function?
Weekday(date, [firstdayofweek])
You can set the "firstdayofweek " to 2, and it should display the results

you

want.

"keri" <keridowson@hot mail.comwrote in message
news:1174767346 .824874.32130@d 57g2000hsg.goog legroups.com...

Larry,
>
Many thanks for the above. I presume by the day of the week comment
you are referring to Mon being a 1 and Tue being a 2 etc etc, in which
case me needing 6 numbers on Mon - Fri wouldn't work. However if I am
missing the point please correct me as I may be making this difficult
for myself!
>
Anyway the code is super, thanks,
>

>
>