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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|