Hi,

I have an unbound form [Frm Volunteer Opportunities] with a sub form [SubFrm
Vol Opportunities]. The subform has the fields [Event ID] and [Event Date].
My unbound form has a command button on it.

Basically, what I’m trying to do is have Access automatically create 31 new
records in [SubFrm Vol Opportunities] whenever I click the button. I want
each new record to be dated 5/1/2010, 5/2/2010, 5/3/2010, and so on starting
and ending with the dates that I provide in pop-up parameter boxes.
Unfortunately, I don't know code although I can probably figure out where to
cut/paste it.

Yes, I will use each new record. This will greatly decrease the amount of
time spent entering data since I would typically be doing it every single
month.

Thank you for your help!

=?Utf-8?B?RmxvcGJvdA==?= wrote:

>
> Hi,
>
> I have an unbound form [Frm Volunteer Opportunities] with a sub form [SubFrm
> Vol Opportunities]. The subform has the fields [Event ID] and [Event Date].
> My unbound form has a command button on it.
>
> Basically, what I’m trying to do is have Access automatically create 31 new
> records in [SubFrm Vol Opportunities] whenever I click the button. I want
> each new record to be dated 5/1/2010, 5/2/2010, 5/3/2010, and so on starting
> and ending with the dates that I provide in pop-up parameter boxes.
> Unfortunately, I don't know code although I can probably figure out where to
> cut/paste it.
>
> Yes, I will use each new record. This will greatly decrease the amount of
> time spent entering data since I would typically be doing it every single
> month.
>
> Thank you for your help!
>

A code for this could look like:

Dim EventCount As Byte

For EventCount = 1 To 31
DoCmd.RunSQL "INSERT INTO YourTable (EventDate) VALUES(#" & _
DateAdd("m", EventCount - 1, Me.StartDate) & "#)"
Next

I use an unbound field (Me.StartDate) as input.

--
Groeten,

Peter
http://access.xps350.com

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through 31.
Use this in your append query --
New_Dates: DateAdd("d", [CountNumber].[CountNUM],
CVDate([Forms]![YourForm]![Start_Date]))

Date_Spread: [CountNumber].[CountNUM]
with criteria --
<=DateDiff("d", CVDate([Forms]![YourForm]![Start_Date]),
CVDate([Forms]![YourForm]![End_Date]))


SELECT
DateAdd("d",[CountNumber].[CountNUM],CVDate([Forms]![YourForm]![Start_Date]))
AS New_Dates, [CountNumber].[CountNUM] AS Date_Spread
FROM CountNumber
WHERE
((([CountNumber].[CountNUM])<=DateDiff("d",CVDate([Forms]![YourForm]![Start_Date]),CVDate([Forms]![YourForm]![End_Date]))));

--
Build a little, test a little.


"Flopbot" wrote:

> Hi,
>
> I have an unbound form [Frm Volunteer Opportunities] with a sub form [SubFrm
> Vol Opportunities]. The subform has the fields [Event ID] and [Event Date].
> My unbound form has a command button on it.
>
> Basically, what I’m trying to do is have Access automatically create 31 new
> records in [SubFrm Vol Opportunities] whenever I click the button. I want
> each new record to be dated 5/1/2010, 5/2/2010, 5/3/2010, and so on starting
> and ending with the dates that I provide in pop-up parameter boxes.
> Unfortunately, I don't know code although I can probably figure out where to
> cut/paste it.
>
> Yes, I will use each new record. This will greatly decrease the amount of
> time spent entering data since I would typically be doing it every single
> month.
>
> Thank you for your help!
>

Thank you Groeten & Karl for sharing your knowledge of Access with others!

I’m playing around with Groeten’s suggestion (since his was first) and I
think it’s definitely in line with what I’m looking for (although it didn’t
work). I did some more searching on the forums and found something about
InputBoxes which sounds ideal. I tried one and it popped up a box to enter
my date. Since I don’t know code, I’m sure I’ve butchered the following (it
doesn’t work), but am I at all close?


Private Sub Add_One_Month_DblClick()

Dim EventCount As Byte
Dim StartDate As String

StartDate = InputBox("Please Select a StartDate", "CUSTOMER SELECTION")
For EventCount = 1 To 31
DoCmd.RunSQL "INSERT INTO [SubFrm Vol Opportunities] (Event Date)
VALUES(#" & DateAdd("m", EventCount - 1, [StartDate]) & "#)"
Next

End Sub


"XPS35" wrote:

> =?Utf-8?B?RmxvcGJvdA==?= wrote:
>
> >
> > Hi,
> >
> > I have an unbound form [Frm Volunteer Opportunities] with a sub form [SubFrm
> > Vol Opportunities]. The subform has the fields [Event ID] and [Event Date].
> > My unbound form has a command button on it.
> >
> > Basically, what I’m trying to do is have Access automatically create 31 new
> > records in [SubFrm Vol Opportunities] whenever I click the button. I want
> > each new record to be dated 5/1/2010, 5/2/2010, 5/3/2010, and so on starting
> > and ending with the dates that I provide in pop-up parameter boxes.
> > Unfortunately, I don't know code although I can probably figure out where to
> > cut/paste it.
> >
> > Yes, I will use each new record. This will greatly decrease the amount of
> > time spent entering data since I would typically be doing it every single
> > month.
> >
> > Thank you for your help!
> >
>
> A code for this could look like:
>
> Dim EventCount As Byte
>
> For EventCount = 1 To 31
> DoCmd.RunSQL "INSERT INTO YourTable (EventDate) VALUES(#" & _
> DateAdd("m", EventCount - 1, Me.StartDate) & "#)"
> Next
>
> I use an unbound field (Me.StartDate) as input.
>
> --
> Groeten,
>
> Peter
> http://access.xps350.com
>
> .
>

Karl,

I'm looking into Append Queries and will let you know what I find out
tomorrow. I guess MS must have 12 ways of doing everything.


"KARL DEWEY" wrote:

> Create a table named CountNumber with field CountNUM containing numbers from
> 0 (zero) through 31.
> Use this in your append query --
> New_Dates: DateAdd("d", [CountNumber].[CountNUM],
> CVDate([Forms]![YourForm]![Start_Date]))
>
> Date_Spread: [CountNumber].[CountNUM]
> with criteria --
> <=DateDiff("d", CVDate([Forms]![YourForm]![Start_Date]),
> CVDate([Forms]![YourForm]![End_Date]))
>
>
> SELECT
> DateAdd("d",[CountNumber].[CountNUM],CVDate([Forms]![YourForm]![Start_Date]))
> AS New_Dates, [CountNumber].[CountNUM] AS Date_Spread
> FROM CountNumber
> WHERE
> ((([CountNumber].[CountNUM])<=DateDiff("d",CVDate([Forms]![YourForm]![Start_Date]),CVDate([Forms]![YourForm]![End_Date]))));
>
> --
> Build a little, test a little.
>
>
> "Flopbot" wrote:
>
> > Hi,
> >
> > I have an unbound form [Frm Volunteer Opportunities] with a sub form [SubFrm
> > Vol Opportunities]. The subform has the fields [Event ID] and [Event Date].
> > My unbound form has a command button on it.
> >
> > Basically, what I’m trying to do is have Access automatically create 31 new
> > records in [SubFrm Vol Opportunities] whenever I click the button. I want
> > each new record to be dated 5/1/2010, 5/2/2010, 5/3/2010, and so on starting
> > and ending with the dates that I provide in pop-up parameter boxes.
> > Unfortunately, I don't know code although I can probably figure out where to
> > cut/paste it.
> >
> > Yes, I will use each new record. This will greatly decrease the amount of
> > time spent entering data since I would typically be doing it every single
> > month.
> >
> > Thank you for your help!
> >

On 13 mei, 00:33, Flopbot <Flop...@discussions.microsoft.com> wrote:
> Thank you Groeten & Karl for sharing your knowledge of Access with others! *
>
> I’m playing around with Groeten’s suggestion (since his was first) and I
> think it’s definitely in line with what I’m looking for (although it didn’t
> work). *I did some more searching on the forums and found something about
> InputBoxes which sounds ideal. *I tried one and it popped up a box to enter
> my date. *Since I don’t know code, I’m sure I’ve butchered the following (it
> doesn’t work), but am I at all close?
>
> Private Sub Add_One_Month_DblClick()
>
> * * Dim EventCount As Byte
> * * Dim StartDate As String
>
> * * StartDate = InputBox("Please Select a StartDate", "CUSTOMER SELECTION")
> * * For EventCount = 1 To 31
> * * DoCmd.RunSQL "INSERT INTO [SubFrm Vol Opportunities] (Event Date)
> VALUES(#" & DateAdd("m", EventCount - 1, [StartDate]) & "#)"
> * * Next
>
> End Sub
>

I think you are close. Looking at your code it looks like you try to
insert data into a (sub) form. That is not possible. Forms show data
that is stored in tables. So you have to store data into a table
first. That is what DoCmd.RunSQL "INSERT INTO..." is doing. Where youy
have [SubFrm Vol Opportunities] there should be the name of a table.

One more thing about the inputbox. You cannot be sure that what is
entered is a (valid) date. So you have check that (function IsDate).
That is why I prefer to use a form field. You can define it as a date
and thus make sure a valid date is returned.


Greetings (or "Groeten" in dutch)

PETER

http://access.xps350.com