append table from txt boxes on a form

[shutzy]

staff hours table

i have dable in this before but i dont think i didnt like the answers and thought that there must be an automated way of doing things.

i have a form with text boxes for a start and finish time for every day of the week.

i would like to populate a table with those times but in one click for a year.

i just had an idea but thought i would run it past someone before i put a lot of work in.

i thought of having many many macros on click event for a button. i would manually type a date in a textbox and it would automatically revert to the first day of the week(monday). then on click it would open query(a query that i have already set up to append a table) then on the next macro it would set value of the date txtbox to DateAdd('d',1,[txtDateStart]), next it would open query(a query for tuesday, so i would need 7 queries).

or i could just have more set value macros and just 1 querie but that would take another 2 macros for each day.(is there a limit to macros)

this process would repeat until DateAdd would be 364. i think im looking at about 700 macros on that single on click event

what do you guys think? will it work? will it crash? will it take forever? is it just outright silly?

 

[boblarson]

Re: staff hours table

700 macros - ridiculous. You must not be aware that you can do several things in a single macro. But beyond the other issues which make me question the database design structure, I dont' have time to help you deal with. You can do this simply in VBA.

So, code to get the previous monday would be:

'This code was generously donated by Bob Askew (raskew) from Access World Forums 'and modified by Bob Larson to be able to look either forward or back.
[B][COLOR=#ff0000]'NOTE:  If you use this in a QUERY or Control Source, you have to specify the 'day NUMBER instead[/COLOR][/B]
[B][COLOR=#ff0000]'of vbWednesday, vbSunday, etc. because queries and control sources do not 'know about the VBA [/COLOR][/B]
[B][COLOR=#ff0000]'constants.[/COLOR][/B]
 
Function fNextNthDay(dteStart As Date, _
                     intWeekday As Integer, Optional blnPrevious As Boolean) As Date
'************************************************* *
'Purpose: Round date up to next specified
' weekday
' The optional parameter blnPrevious specifies if you want
' the PREVIOUS date.  The default is to get the NEXT date.
 
 
'Inputs:
' 1) ? fNextNthDay(#4/18/06#, vbWednesday)  
' 2) ? fNextNthDay(#4/19/06#, vbWednesday)
' 3) ? fNextNthDay(#4/20/06#, vbWednesday)
' 4) ? fNextNthDay(#4/19/06#, vbWednesday, True)
' 5) ? fNextNthDay(#4/20/06#, vbWednesday, True)
'Output:
' 1) 4/19/06
' 2) 4/19/06
' 3) 4/26/06
' 4) 4/19/06
' 5) 4/19/06
'************************************************* *
    If blnPrevious Then
        fNextNthDay = (dteStart - Weekday(dteStart) + _
                       intWeekday + _
                       IIf(Weekday(dteStart) < intWeekday, -7, 0))
    Else
        fNextNthDay = dteStart - Weekday(dteStart) + _
                      intWeekday + _
                      IIf(Weekday(dteStart) > intWeekday, 7, 0)
    End If
End Function

So then to do the work

Function AddDatesToTable(dteStartDate As Date, lngNumberOfDates As Long)
    Dim db As DAO.Database
    Dim strSQL As String
    Dim lngDateCount  As Long
    
    Set db = CurrentDb
    
    For lngDateCount = 0 To lngNumberOfDates
       strSQL = "INSERT INTO tableNameHere ([DateFieldNameHere]) VALUES(#" & DateAdd("d", lngDateCount, dteStartDate) & "#"
       db.Execute strSQL, dbFailOnError
       lngDateCount = lngDateCount + 1
    Next
    
    
End Function

And then you need to be able to generate your start date from your input so you would call the function like this from your button's click event.

Private Sub MyCommandButtonNameHere_Click()
   AddDatesToTable fNextNthDay(Me.txtStartDate, 2, True), 365
End Sub

 

[shutzy]

Re: staff hours table

hi bob. it wasnt a serious question, but it may have become serious if i found no help. all i know is macros and i also know that vba can do it in a much quicker speed and a hell of a lot less input. but i dont know where to start with vba so i tend to stick to macro. i didnt mean litterally 700 macros, merely 700 steps. also this is how i tend to progress my ideas. i think of a way of doing it as that is the most important thing then i start to look at 'how can i do this better'. so it was a starting point.

i dont really feel that your statement of

which make me question the database design structure

is totally unfounded as you have said you dont have time.

what i am trying to do is absolutely nothing to do with table structure.

my table structure will be

UniqueID | EmployeeID | Date | StartTime | FinishTime

the reason i want to create a full year of staff rota is because i would like to be able to book appointments upto a year in the future.

if you can see another way of doing this then please tell me as i have asked this question before and an excel sheet was suggested but then i would have to import and i feel that there is a better way.

 

[jdraw]

Re: staff hours table

Does your organization also use Outlook? How about Outlook appointments?
To learn some vba here are a few links.

http://www.functionx.com/vbaccess2007/Lesson01.htm
http://www.accessmvp.com/strive4peace/VBA.htm
http://www.everythingaccess.com/tutorials.asp?Cat=VBA / Modules&Rows=10&Order=7

Good luck with your project.

 

[shutzy]

Re: staff hours table

sorry jd i have been on a website where programmers can bid for the work. you set a limit describe what you want done and they submitt their tenders.

so i need to get this done before i can take it further.

is there a way to tell a makro to loop. like you can with vba. everyone keeos telling me to learn vba but i think that you can do anything with macros. you just got to learn how.

 

[shutzy]

i am trying to create an append query. the query is to create new records in the tblStaffHours from details on a form frmStaffDetails.

ive only put 1 field in there as yet because it dosnt seem to give any results.

its blank

heres the sql

INSERT INTO tblStaffHours ( StartTime )
SELECT [Forms]![frmStaffDetails]![MondayStartTime] AS Expr1;

what am i doing wrong

 

[boblarson]

Re: staff hours table

but i think that you can do anything with macros. you just got to learn how.

That is not a correct statement. With Access 2010 it is getting closer but there are still things that macros can't do that VBA can.

Now, I gave you the actual VBA which would work. But you can ignore it if you want. Or you can work with us and you can start learning it.

 

[shutzy]

Re: staff hours table

bob i dont mean to be rude and i really do want to learn. but i really do hate it when people tell me that this is the only way. i do appreciate that i am not as experienced as you but i do feel that my ideas carry some weight. i reaaly do appreciate the code you gave me but it is not what i want. i would like to explore all other avenue before i divulge into vba. for one, i think that vba will be defunct by macro in the next 5-10 years. if i am going to be doing this for some time then i would like to start with the 'future'.

i have thought of a way to do it by macros(loop). it wont take 700 macros but it will take as many processes as vba would.

the latest idea i have had is

If [Forms]![frmStaffDetails]![txtStaffDate] <DateAdd('y',1,Date()) then
Run Macro
Macro 1(macro 1 includes the steps to complete a full weeks hours for the staff member(date+1,run query etc)
If [Forms]![frmStaffDetails]![txtStaffDate] <DateAdd('y',1,Date()) then
Run Macro
Macro 1(macro 1 includes the steps to complete a full weeks hours for the staff member(date+1,run query etc)

im not sure if you can run a macro when it is already open if not then i will just have to duplicate the same macro and call it macro 2 and have them loop through each other.

im not sure if this will work in practice but in logic says it should.

bob once last thing. i am not a programmer and i will never pretend i am but i am logical. i believe that if the above suggestion works then that will be far less work than the code you supplied(at least for someone like me).

 

[pr2-eugin]

I do not understand why you have a SELECT statement... try this instead..

INSERT INTO tblStaffHours (StartTime) VALUES ([Forms]![frmStaffDetails]![MondayStartTime])

 

[shutzy]

neither do i. i just put the sql into my query, navigated to design view. went back to sql view and it is at it was. SELECT. ill try it with a fresh query.

 

[shutzy]

it dosnt like it. ive uploaded an image of the query in design view.

what have i done wrong?

 

[pr2-eugin]

Where are you trying to run the query from?? Why do you have to use Query Design wizard?

 

[shutzy]

i dont use query design wizzard. i go straight into query design. i want to run the query from a macro. on click event in frmStaffDetails

 

[boblarson]

Re: staff hours table

i do appreciate that i am not as experienced as you but i do feel that my ideas carry some weight. i reaaly do appreciate the code you gave me but it is not what i want. i would like to explore all other avenue before i divulge into vba.

Your choice. So I will make sure to not answer your questions from this point on so you don't get the answers you don't like.

for one, i think that vba will be defunct by macro in the next 5-10 years.

Yeah, right. Of course, as a Microsoft MVP, I had direct contact with the Access development team at Microsoft and, I don't see that happening based on the interactions I had with them. But that's totally up to you to believe if you want.

I'm outta here.

 

[shutzy]

Re: staff hours table

its really nice to get a mature conversation. i suspect that it is you that does not like 'my replies'. my way or no way?

i have just looked into the Run Macro and could you believe it, it has a Repeat Count or Repeat Expression. i think that this one is solved.

 

[pr2-eugin]

Why not use VBA? that would be incredibly simple. I am not sure if there is a RunSQL in Macro.. I never use macros.. but if you want to give VBA a try code will be like..

Dim myQry As String
myQry = "INSERT INTO tblStaffHours ( StartTime ) VALUES (" & [Forms]![frmStaffDetails]![MondayStartTime] & ")"
DoCmd.SetWarnings (False)
DoCmd.RunSQL
DoCmd.SetWarnings (True)

This code will go into the OnClick event of the button on your form.

 

[shutzy]

sorry i think i may have misunderstood your previous post. i am using query design like you asked. the reason for this is that i want to create a query and save it. then run macro to open query. thus having the table appended. could you give me your honest opinion on my input into this thread http://www.access-programmers.co.uk/forums/showthread.php?t=234946 because i feel that bob is suggesting i should move over to vba like yourself did. where my responses 'out of order' or does it ome down to a difference of opinion. i do feel that you can do anything with a macro dispite what bob and many others say. the reason i feel this is that everyone tells me to do it in vba when i post a question of how to do it in a macro. its like no-one understands macros and they are telling me to do it in vba because it is better.

sorry about this but bob has possibly struck a chord.

 

[jdraw]

Re: staff hours table

sorry jd i have been on a website where programmers can bid for the work. you set a limit describe what you want done and they submitt their tenders.

so i need to get this done before i can take it further.

is there a way to tell a makro to loop. like you can with vba. everyone keeos telling me to learn vba but i think that you can do anything with macros. you just got to learn how.

I think you missed the meaning of my post

Does your organization also use Outlook? How about Outlook appointments?
To learn some vba here are a few links.

http://www.functionx.com/vbaccess2007/Lesson01.htm
http://www.accessmvp.com/strive4peace/VBA.htm
http://www.everythingaccess.com/tuto...ows=10&Order=7

Good luck with your project.

You were looking for a way to handle appointments. If you use Outlook in your organization, you can tie Access and Outlook together and take advantage of Outlook's Appointments.

You also mentioned you don't know vba.
I gave you links to 3 well established sites for vba tutorials and examples.

 

[shutzy]

sorry jd. i didnt misread the post. its just that it is a few more steps ahead at the minute. i needed to sort out a table that records the times that the staff members are in. i am thankful for the links and i will be making use of them. ive heard before about integrating outlook calendar with access so it is definately an avenue i can go down. i dont want to pay for it(diary) as i like to do as much myself as possible so i will definately be exploring this option.

i need to do a couple mre things to get to that point. the closer i get to it i think of something else that i think needs to be there to make the data extraction better.

thanks again jd