Create X number of records with a button

[GrandMasterTuck]

I know this has probably been gone over, but I'm just looking for a super-simple way to put a button on my form that will create a number of records equal to the number of days in the month listed in a field. For instance, if MyField is "4/1/2015", I'd like the system to create one record for each date between 4/1/15 and 4/30/15.

I have scoured the forums, and I find many threads on creating multiple records, but none of them deal with the same type of thing I need. I have a field, called MyField, and I have a table called MyTable with a column called MyDate. I want to enter a date into MyField, then click a button, and the button will run code/macro/whatever that will create multiple rows in MyTable, one for each date in Month([MyField])

And yeah, I know this is probably really simple. Still learning! Thanks for the help!

 

[JLCantara]

Hi GrandMasterTuck!

Here is the solution:

1 - Linked to MyField, create an AfterUpdate event.

2 - After proper check do the following:
Y = DatePart("yyyy",Myfield) ' to fetch the year
M = DatePart("m",MyField) ' to fetch the month

3 - NewDate = DateSerial(Y,M,1) ' To start at the first day

4 - while DatePart("m",MyField)=M
'create a record using NewDate
NewDate = DateAdd("d", 1, NewDate)
Loop

5 - This way you don't need to now the number of days in month M.

Good luck, JLCan.

 

[GrandMasterTuck]

Hi JL...

I am going to need a little better description of the steps along the way. I don't quite follow you:

After proper check do the following:

What, exactly, is a 'proper check'? What field does this check, and what am I looking for?

Y = DatePart("yyyy",Myfield) ' to fetch the year
M = DatePart("m",MyField) ' to fetch the month

3 - NewDate = DateSerial(Y,M,1) ' To start at the first day

4 - while DatePart("m",MyField)=M
'create a record using NewDate
NewDate = DateAdd("d", 1, NewDate)

I think I understand this, this gets pasted into the VBA editor, or the "VIEW CODE" screen, right? Don't I have to remove those numbers you wrote in there? Or do I actually paste "3 - NewDate = " etc..?

 

[MarkK]

Here are a few subroutines that might be handy in this pursuit . . .

Function LengthOfMonth(d1 As Date) As Integer
[COLOR="Green"]'   the length of the month is defined by its last day number[/COLOR]
    LengthOfMonth = Day(LastDayOfMonth(d1))
End Function

Function LastDayOfMonth(d1 As Date) As Date
[COLOR="Green"]'   The last day of this month is the zero'th day of next month[/COLOR]
    LastDayOfMonth = DateSerial(Year(d1), Month(d1) + 1, 0)
End Function

Sub LoopForEveryDayOfMonth(d1 As Date)
[COLOR="Green"]'   runs a loop, once for each day of the month in d1[/COLOR]
    Dim i As Integer
    For i = 1 To LengthOfMonth(d1)
[COLOR="Green"]        'do something for each day[/COLOR]
        Debug.Print DateSerial(Year(d1), Month(d1), i)
    Next
End Sub

Everything is easier with simple, clear, subroutines.

 

[GrandMasterTuck]

I assume, in that example, that "d1" is my date field, correct? This does seem easy...

 

[MarkK]

But keep in mind, time is a scalar value, just like dollars or inches, and in construction software or accounting software, you never create a record for each dollar, or a record for each foot.

Unless you are doing something quite specialized, it seems very unlikely to be useful to have a record that stores a single date. A date is an attribute of something else, like, a person has a birth date. A person also has a shoe size, but you'd never store each possible shoe size in a table. A shoe size belongs to a person or to a shoe. It's meaningless as a row in a table. Similarly, what do you do with April 23, 2015 as a single value in a row in a table? What does it mean, all isolated like that?

 

[JLCantara]

Hi M. Tuck!

The AfterUpdate event is triggered what ever you do in the object; so make sure first that there is a date:

Dim Y as Integer, M as Integer
Dim NewDate as Long

If txtDate is null then
goto ExitProc ' A label that exits the sub
Else
Y = DatePart("yyyy",Myfield) ' to fetch the year
M = DatePart("m",MyField) ' to fetch the month
NewDate = DateSerial(Y,M,1) ' To start at the first day
while DatePart("m",MyField)=M
'create a record using NewDate
NewDate = DateAdd("d", 1, NewDate)
Loop
End if

That should do the job...
JLCantara

 

[GrandMasterTuck]

Unless you are doing something quite specialized, it seems very unlikely to be useful to have a record that stores a single date. A date is an attribute of something else, like, a person has a birth date. A person also has a shoe size, but you'd never store each possible shoe size in a table. A shoe size belongs to a person or to a shoe. It's meaningless as a row in a table. Similarly, what do you do with April 23, 2015 as a single value in a row in a table? What does it mean, all isolated like that?

I'd call it specialized. Each date record in the table has the primary key (the date), the name of the desk person, the number of units on hand (for pet food bags, in case you're curious), and the number of units used. Then there is an associated table that's linked to the date column of the first table, wherein a number of employee records are stored, depending on how many employees are in that day. Sometimes we have two, sometimes we have five, and each one of them has their own record that's created on that day (they create the record by clicking a button and filling out some fields). The employees complete several jobs and indicate the time the job was completed on their individual record.

So, yes, I need to have the individual dates in the first table, because there are records associated ONLY to that date (the other columns) and then there are subrecords associated to that date that are different quantities each day, and each of THOSE has columns associated ONLY to that employee (other columns in the employee table).

The AfterUpdate event is triggered what ever you do in the object

I don't want the system to do anything until I click the BUTTON. The form has a single date field and a single button. I want to enter a date into the field, then click the button, and THAT causes the system to create new records for every date in the month that appears in that box.

so make sure first that there is a date:

Dim Y as Integer, M as Integer
Dim NewDate as Long

If txtDate is null then
goto ExitProc ' A label that exits the sub
Else
Y = DatePart("yyyy",Myfield) ' to fetch the year
M = DatePart("m",MyField) ' to fetch the month
NewDate = DateSerial(Y,M,1) ' To start at the first day
while DatePart("m",MyField)=M
'create a record using NewDate
NewDate = DateAdd("d", 1, NewDate)
Loop
End if

Argh. You are still talking a little bit over my head, or I'm failing to understand what you're saying. HOW am I 'making sure there is a date'? I mean, I see the code you posted, but I don't know WHERE to put it. Am I putting this code in the MyDate field? If so, into which Event am I placing this? If you're telling me to put it in MyDate's After Update, it looks like the code is executing the act of creating records as soon as the field is updated, and I don't want it to do that until I click MyButton. Or does this code go on the BUTTON? And if so, where in that code ame I referencing which table I want these records created?

I'm sorry to be a pain, but I was hoping one of you could explain that in a little more detail. I understand how the code works (I think), but I haven't a clue where to PUT it.

If I have frmCreateMonth hooked to tblDates, with MyDate field and MyButton, and I open frmCreateMonth and enter "4/1/15" into MyDate and click MyButton, I want the system to create 30 new records in tblDates with the first record's primary key set to "4/1/15" and the last record (the thirtieth record) primary key to be "4/30/15", and each of the records in between those two are, in numerical order, the other dates in that month. 4/2/15, 4/3/15, 4/4/15, and so on. Then my employees can get in there and each create records for themselves for the days they are working that month.

Looking at that code you posted, I see nowhere to reference the fact that these new records must appear in tblDates. Maybe I'm not being clear with my questions... I really appreciate your willingness to post a suggestion for me, but I'm not quite there with you yet... Any ideas?

 

[MarkK]

The date is the primary key???