A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Add consecutive dates w/ command button & parameter boxes



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 08:35 PM posted to microsoft.public.access.forms
Flopbot
external usenet poster
 
Posts: 60
Default Add consecutive dates w/ command button & parameter boxes

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!

  #2  
Old May 11th, 2010, 09:13 PM posted to microsoft.public.access.forms
xps35
external usenet poster
 
Posts: 22
Default Add consecutive dates w/ command button & parameter boxes

=?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

  #3  
Old May 11th, 2010, 09:51 PM posted to microsoft.public.access.forms
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Add consecutive dates w/ command button & parameter boxes

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!

  #4  
Old May 12th, 2010, 11:33 PM posted to microsoft.public.access.forms
Flopbot
external usenet poster
 
Posts: 60
Default Add consecutive dates w/ command button & parameter boxes

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

.

  #5  
Old May 12th, 2010, 11:43 PM posted to microsoft.public.access.forms
Flopbot
external usenet poster
 
Posts: 60
Default Add consecutive dates w/ command button & parameter boxes

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!

  #6  
Old May 13th, 2010, 11:11 AM posted to microsoft.public.access.forms
XPS350
external usenet poster
 
Posts: 69
Default Add consecutive dates w/ command button & parameter boxes

On 13 mei, 00:33, Flopbot 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.