automaticly adding a set of records to a form

[RickR]

Hello Everyone,

I have a time sheet DB that adds up daily totals for a weekly commission. To do this I have set up three tables to handle this:

EmploeeTable:
EmployeeID
Lname
Fname
Payrate

CommTable:
HoursWorked
CommissionRate

WeeklyInput:
WeeklyInputID
EmployeeID
StartDate
Mon
Tue
Wed
Thu
Fri
Hours

Ive a query built on the Weekly Input table, which joins to the Employee table and the CommTable, then a form built off of this query.

This works fine for the first week, but the putting in subsequent weeks is a problem.

Question 1, Am I approaching this in the right manner, meaning my table layout and all?

Question 2, I thought maybe some sort of loop might be the answer, the first part of the form ask the user for the Startdate, and create a new record with that date for each employee.
If this is the answer, how do I do that? I’ve been pouring over several books and going though the FAQs here and cant seem to find what I need, or at least don’t know it if I did!

Any help is greatly appreciated.

Rick

 

[David R]

I did something similar to this with an Append Query behind a command button. There were more fields than yours but perhaps this will help...

Button:

Private Sub buttonAddDates_Click()
On Error GoTo Err_buttonAddDates_Click
    If (Me.textDateEntry = "" Or IsNull(Me.textDateEntry)) Or IsNull(Me.comboGradeType) Then
        MsgBox "You must fill in the Date and type of Grade you wish to fill in."
        Me.textDateEntry.SetFocus
    Exit Sub
    End If
    
    If MsgBox("Do you want to add " & Me.comboGradeType.Column(1) & "s for " & Me.textDateEntry & "?", vbYesNo, "Warning") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "EnterDailyRecords", acViewNormal
        DoCmd.SetWarnings True
    Else
        MsgBox "Use the View button to view a day's records for your class."
        Exit Sub
    End If
    
    [subformGradeDates].Form.RecordSource = "QueryGradeDates"
       
Exit_buttonAddDates_Click:
    Exit Sub

Err_buttonAddDates_Click:
    MsgBox Err.Description
    Resume Exit_buttonAddDates_Click
    
End Sub

Query:
INSERT INTO tableGrades ( StudentID, DateField, GradeTypeID, Grade )
SELECT tableStudents.StudentID, [Forms]![Enter Grades for Day]![textDateEntry] AS Expr1,
 [Forms]![Enter Grades for Day]![comboGradeType] AS Expr2, [Forms]![Enter Grades for Day]![textDefaultGrade] AS Expr3
FROM tableStudents;

[This message has been edited by David R (edited 04-26-2002).]

 

[RickR]

Thanks for the reply David. First I must admit that I don't know much codeing, with that said;

I see the first part as being a button with error checking to make sure something is inputed.

Then Im a bit lost thru the next part of the code, I dont understand what its doing so I dont know how to change it for my database.

Last part being the actual append query, I alomst understand, but how would I get a new record with a date for each of the employees?

The reason I am trying to do that is to set up a criteria by StartDate in my form, there by giving the user just that week to be put in.

I do hope I'm coming accross clearer to everyone then it sounds to me

[This message has been edited by RickR (edited 04-25-2002).]

 

[David R]

The Append Query at the bottom is the really important part. The form you can design to your own needs. Here's what it does:
INSERT INTO tableGrades ( StudentID, DateField, GradeTypeID, Grade )

INSERT INTO is SQL for 'append'. Then it lists the fields we're appending. In your case, it might just be EmployeeID, DateField.

SELECT tableStudents.StudentID, [Forms]![Enter Grades for Day]![textDateEntry] AS Expr1, [Forms]![Enter Grades for Day]![comboGradeType] AS Expr2, [Forms]![Enter Grades for Day]![textDefaultGrade] AS Expr3

Where do we get the data from to append? In the same order as the fields were listed in the INSERT INTO line, we define what the values are going to be:
StudentID is going to be tableStudents.StudentID
DateField is going to be the value of the unbound field that we put today's date into. [Forms]![Enter Grades for Day]![textDateEntry] refers to that field (use the Expression Builder to get the syntax right). You can ignore the AS Expr1, that's only there because I built the query in design view and has no effect on the result.
GradeTypeID is goiing to come from [Forms]![Enter Grades for Day]![comboGradeType], similarly.
Ditto for the Grade field.

FROM tableStudents;
Where are we getting this data from? Oh right, tableStudents.
You might/probably will have to include a line like
WHERE [tableEmployees].[EmployeeActiveStatus] = -1 AND [tableEmployees].[Department] = [Forms]![YourForm]![comboDepartmentName]
to narrow down your Employee selection.

Now when you do this, this query working properly depends on you setting the Primary Key for your timesheet table to be multipart: EmployeeID and DateField. Then only one record can ever be input for one date for one employee. Otherwise your data will become highly erroneous in a hurry.

Make sure you have your Append Query working correctly before you start worrying too much about error checking your form. Then have another look at the code I used above.

Good luck,
David R

 

[RickR]

David,

I think I understand, your quite good as an instructor!

Now this is the part where I'm the bonehead student:
I made a test form, made a button, and at the On Click event I make an event procedure.

I put the following in:

Private Sub Command4_Click()

INSERT INTO tblWeeklyInput(EmployeeID, StartDate)

SELECT tblWeeklyInput.EmployeeID,[Forms]![frmsetup]![StartDate]

FROM tblWeeklyInput

End Sub

The INSERT INTO and SELECT statement are both red and when I attempt to use the button I get a syntax error.

Where have I gone wrong??

Sorry to be a bother

 

[David R]

No problem. You're trying to put SQL (Structured Query Language) into a code module. That's why it's throwing up at you. SQL is what you use in the query window, and certain other circumstances.

Steps to fix:
1) Go into a new query in design view. Change to SQL view (the Query View/Design View/SQL View button in the top left).
2) Paste in exactly what you have now in your code window (from INSERT to just before End Sub).
3) Change to Design View and see if it makes sense. Save this query under a name.
4) Back to your code window. Erase just the SQL that you just moved and put
DoCmd.OpenQuery "QueryNameYouJustSaved" in place of it all. Make sure you leave the Private Sub.. and End Sub... lines.
4b) Note this will work best if your query never changes (the Form field value that you're putting in can change, of course, I'm talking about the SQL bits). If it can change, which doesn't seem likely, you'll have to do more coding.
5) Now you can test your query. Make sure it runs properly off the form once or twice, THEN go back into the code window and surround your DoCmd.OpenQuery statement with these lines:
DoCmd.SetWarnings False
DoCmd.OpenQuery...
DoCmd.SetWarnings True

This turns off the 'Are you sure you want to...?' yadda yadda messages, now that you're sure the query works correctly. You can put your own custom MsgBox that's a little nicer if you like, i.e.
If MsgBox("Do you want to add records for " & [Forms]![frmsetup]![StartDate] & "?",vbYesNo) = vbNo Then Exit Sub

Good luck. Post back if you have further problems.

 

[RickR]

David,

That works so much better when you put it there! LOL

Now how would I referance the employee table to get all of my employees? Right now insert into query only uses the employees that are currently in the WeeklyInput table. Which is fine for now, but wont be when employes change, either coming or going.
I'm guessing I need to do some sort of loop, either prior to the Insert or during.

Again many thanks,

Rick

 

[David R]

Hmmm, only problem I can see is when a new employee has no records in the Weekly Input table. That's why I used tableStudents (Employees table in your case) to get the SELECT values, not the actual WeeklyValues table. This should work though I haven't played with it today. If it gives you trouble post back the Append Query you are using and maybe someone can troubleshoot it.

 

[RickR]

David,
Thanks for pointing me in the right direction, I tweaked the query and came up with this:

INSERT INTO tblWeeklyInput ( EmployeeID, StartDate )
SELECT tblEmployee.EmployeeID, [Forms]![frmsetup]![StartDate]
FROM tblEmployee LEFT JOIN tblWeeklyInput ON tblEmployee.EmployeeID = tblWeeklyInput.EmployeeID;

And it works like a charm!!

Thanks again for all your help.

Rick