Auto Create Records

[nick1408]

A bit of a funny one. I have a form where number of hours forecast (hoursF) and number of hours used (hoursU) needs to be added to a table each month. What I would like to do is have Access add the records for me automatically. Ideally, each month a record would be created with two fields (hoursF and hoursU) then both fields are cleared ready for new data to be entered for the coming month.

hoursU is a calculated field of every job closed in a month. hoursF is entered by the database user from the forecast hours by Head office.

Thanks for the help

 

[sneuberg]

I think you could create a separate Access application to do that. The AutoExec would open that database with this table and add a new record. Then you use the Windows Task Scheduler to run this Access application at the appropriate time.

But since the user has to add the hours anyway this seems a little silly. What might be easier and more practical is some code in the form open event (of the form adds this data) that checks to see if a record for the current month has already been added and if not opens with the form at a new record with perhaps a label stating; "Ready for this months input" If you give me details of the table and form involved I'll see if I can outline the code for you. Of course it make I a lot easier if you can upload the database.

 

[sneuberg]

If your users have Outlook what might be interest to try is a Outlook calendar event that would launch a macro that would pop up a form and prompt for the data. When the user entered the data it would do some validity checks (make sure the input is number) and then open the database and add the hours.

I have no idea if this can be done at all but I'd be willing to give it a shot if it's something you would want.

 

[nick1408]

I think you could create a separate Access application to do that. The AutoExec would open that database with this table and add a new record. Then you use the Windows Task Scheduler to run this Access application at the appropriate time.

But since the user has to add the hours anyway this seems a little silly. What might be easier and more practical is some code in the form open event (of the form adds this data) that checks to see if a record for the current month has already been added and if not opens with the form at a new record with perhaps a label stating; "Ready for this months input" If you give me details of the table and form involved I'll see if I can outline the code for you. Of course it make I a lot easier if you can upload the database.

Thanks for the help. What you said in the second paragraph is exactly what I want to do.

I haven't created the table yet, I am still in planning. Essentially it will be like:

 | ID | | MMM YYYY | | hoursF | | hoursU |

I honestly need no more than that.

hoursF as a total will be on a form called FrontPage in a field called hoursF.

The rest is purely for reporting which I can sort myself. I'm a bit reluctant to upload the db as I have (unwisely?) put corporate logos all through it.

 

[sneuberg]

I've attached a database that has code that I believe does what you want. I set up the table in this a little differently than what you had in mind. Instead of a field MMM YYYY I put in a field named DateEntered which defaults to Now(), the current date and time. I don't like spaces in field names and you can alway use DatePart (and I do) to get the part of the date you want in this case the month and year.

I think you will find the code in the form open pretty straight forward. I you don't understand it let me know.

I used a label to signal the need for the entry of data. It would be easier to use a message box for this but I really hate it when that's done to be so I try to avoid them. The tricky part with using labels is clearing them when they shouldn't be displayed any longer. I think I have that covered in the form current event. Let me know if I missed a case.

I also put some code in the beforeupdate that you might be interested in. It checks to see if the hours for the month have already be added, i.e., it doesn't allow duplicates.

 

[nick1408]

I've attached a database that has code that I believe does what you want. I set up the table in this a little differently than what you had in mind. Instead of a field MMM YYYY I put in a field named DateEntered which defaults to Now(), the current date and time. I don't like spaces in field names and you can alway use DatePart (and I do) to get the part of the date you want in this case the month and year.

I think you will find the code in the form open pretty straight forward. I you don't understand it let me know.

I used a label to signal the need for the entry of data. It would be easier to use a message box for this but I really hate it when that's done to be so I try to avoid them. The tricky part with using labels is clearing them when they shouldn't be displayed any longer. I think I have that covered in the form current event. Let me know if I missed a case.

I also put some code in the beforeupdate that you might be interested in. It checks to see if the hours for the month have already be added, i.e., it doesn't allow duplicates.

Thanks mate. I'm pretty sure that's what I am after. Looking at the VBA seems to show it will do what I am after.

What I am adding to hoursU is data from a query that draws all the closed tasks and place the appropriate hours against the correct part of the table. Again, I haven't created the query but am pretty sure I can create an autoexec query to fill in the table each time it is opened as well as also call the query each time a task is closed. The key will be to get the right hoursU to go against the right hoursF. I will need to work this part out.

 

[nick1408]

Apologies to bring this topic back up. The following code gives me a "method or data member not found" error:

 Private Sub Form_Current()
'clears message to add hours
Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If Not Me.NewRecord Then
    Me.lblMessage.Caption = ""
Else
    If DatePart("yyyy", LastEntryDate) = DatePart("yyyy", Date) And DatePart("m", LastEntryDate) = DatePart("m", Date) Then
         Me.lblMessage.Caption = ""
    End If
End If
 End Sub

The full code on form FrontPage is:

 Option Compare Database
Option Explicit
 Private Sub Form_BeforeUpdate(Cancel As Integer)
'check if this month's hours have already been entered
Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If DatePart("yyyy", LastEntryDate) = DatePart("yyyy", Me.DateEntered) And DatePart("m", LastEntryDate) = DatePart("m", Me.DateEntered) Then
    Me.DateEntered.SetFocus
    MsgBox "Hours have already been adding for this month"
    Cancel = True
End If
 
End Sub
 Private Sub Form_Current()
'clears message to add hours
Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If Not Me.NewRecord Then
    Me.lblMessage.Caption = ""
Else
    If DatePart("yyyy", LastEntryDate) = DatePart("yyyy", Date) And DatePart("m", LastEntryDate) = DatePart("m", Date) Then
         Me.lblMessage.Caption = ""
    End If
End If
 End Sub
 Private Sub Form_Open(Cancel As Integer)
 Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If IsNull(LastEntryDate) Then 'empty table
        PromptForHours
     If DatePart("yyyy", LastEntryDate) >= DatePart("yyyy", Date) And DatePart("m", LastEntryDate) > DatePart("m", Date) Then
        PromptForHours
    End If
End If
 
End Sub
 Sub PromptForHours()
 DoCmd.GoToRecord , , acNewRec
Me.hoursF.SetFocus
Me.lblMessage.Caption = "Please input this months hours"
 End Sub

All above is from Sneuberg's help before. It will all work I feel (I'm pretty sure the code checks to see if data has been entered for this month on table Table1, if it has there is a popup saying so (I am going to change this to

exit sub

), if not, then a form comes up asking you to enter the data for this month.)

 

[sneuberg]

Does your form have a label named lblMessage?

 

[nick1408]

Does your form have a label named lblMessage?

Nah, no labels are actually named (all are 'Label 29' for example)

 

[sneuberg]

Nah, no labels are actually named (all are 'Label 29' for example)

The code requires a label on the form with that name (lblMessage) or renamed the identifier in red below to the label you are using for the message.

 Private Sub Form_BeforeUpdate(Cancel As Integer)
'check if this month's hours have already been entered
Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If DatePart("yyyy", LastEntryDate) = DatePart("yyyy", Me.DateEntered) And DatePart("m", LastEntryDate) = DatePart("m", Me.DateEntered) Then
    Me.DateEntered.SetFocus
    MsgBox "Hours have already been adding for this month"
    Cancel = True
End If
 
End Sub
 Private Sub Form_Current()
'clears message to add hours
Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If Not Me.NewRecord Then
    Me[COLOR="Red"].lblMessage[/COLOR].Caption = ""
Else
    If DatePart("yyyy", LastEntryDate) = DatePart("yyyy", Date) And DatePart("m", LastEntryDate) = DatePart("m", Date) Then
         Me.[COLOR="red"]lblMessage[/COLOR].Caption = ""
    End If
End If
 End Sub
 Private Sub Form_Open(Cancel As Integer)
 Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If IsNull(LastEntryDate) Then 'empty table
        PromptForHours
     If DatePart("yyyy", LastEntryDate) >= DatePart("yyyy", Date) And DatePart("m", LastEntryDate) > DatePart("m", Date) Then
        PromptForHours
    End If
End If
 
End Sub
 Sub PromptForHours()
 DoCmd.GoToRecord , , acNewRec
Me.hoursF.SetFocus
Me.[COLOR="red"]lblMessage[/COLOR].Caption = "Please input this months hours"
 End Sub

 

[nick1408]

Cool. That I would be why. On second thoughts I think I have done this wrong. FrontPage is an autoexec so I need the checker sub (first one) on that. If true, I need to call the form you created and then let the rest run. If false, continue as normal (exit sub).

Thanks for the help!

 

[nick1408]

I've added the following to call frmHours if the data isn't entered:

 Private Sub Form_Open(Cancel As Integer)
'check if this month's hours have already been entered
Dim LastEntryDate As Variant
LastEntryDate = DMax("[DateEntered]", "[Table1]")
If DatePart("yyyy", LastEntryDate) = DatePart("yyyy", Date) And DatePart("m", Date) = DatePart("m", Date) Then
 '    MsgBox "Hours have already been adding for this month"
'    Cancel = True
Exit Sub
Else
    DoCmd.OpenForm "frmHours"
 End If
End Sub

Seems to work a treat now