Code for Append Query 2

[lars7]

Hi,
On my form I have a subform that groups together members of staff by a locationId so that I can record staff activity i.e.; annual leave, overtime, sickness. The subforms record source is a query made up of my staff table "tbltruststaff" and a table with no records "tbltemp" the SQL is:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS EmployeeName, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL
FROM tbltruststaff LEFT JOIN tbltemp ON tbltruststaff.Paynum = tbltemp.PayNum;

To send the records to the records table "tblrecords" I have an append query controlled by the on click of a command button, the SQL is:

INSERT INTO tblRecords ( LocationId, PayNum, Grade, DateId, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT forms!frmmain!list2 AS F_locationId, tbltemp.PayNum, tbltruststaff.Grade, forms!frmmain!list0 AS F_DateId, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp INNER JOIN tbltruststaff ON tbltemp.PayNum = tbltruststaff.Paynum;

Can anyone help me with the 2 problems I have.

1)When I add data to the subform the append query will only add the records that have the data to the records table but I need to have a record for each staff member even if there is no data. So if there is 5 staff in a location and only 2 have activity that week I still want 5 records for that location for that weekending.

2)and how do I add some code to the append query that would show a message box if the records already exist in the records table. My knowledge of coding is still very basis so any help with either of these problems would be very appreciated.

 

[lars7]

Yes records are appending fine to the table if they are not already there.
LocationId is Number Double and DateId is Date/Time Short date (uk)

 

[PHV]

So, you may try this:
If DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!List2 & " AND DateID=#" & Forms!frmmain!List0 & "#") Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi,
I tried it but still the same no message when the records are definetly there:

Private Sub Command34_Click()
On Error GoTo Command34_Err
If DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!List2 & " AND DateID=#" & Forms!frmmain!List0 & "#") Then
MsgBox "Record already exists"
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryaddrecords"
DoCmd.SetWarnings True
Me.List2.Value = ""
strSQL = "DELETE * FROM tblTemp"
CurrentDb.Execute strSQL
Command34_Exit:
Exit Sub
Command34_Err:
MsgBox Error$
Resume Command34_Exit
End Sub

 

[stix4t2]

lars,

you need to do the compare to qrytest like you were doing originally. You may want to goto variables to make the code all little cleaner.

dim cntAppend as integer, cntAll as integer
cntAppend = DCount("*", "qryTest")
cntAll =  DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!List2 & " AND DateID=#" & Forms!frmmain!List0 & "#")

if( cntAppend < cntAll ) then
  MsgBox "Record already exists"
  Exit Sub
End If

This is the kind of stuff that happens when we fix the tree but loose sight of the forest. If you set a break point, F9, at the if statement you can do put your cursor over the cntAppend and cntAll variables to see their values. Then F5 will finish the routine.

This is good stuff for you, you are learning a lot of good stuff here. That lars, he's going to be a good programmer.

 

[lars7]

Ok here's what I got,

cntAppend = 7
cntAll = 0
Forms!frmmain!List0 "10/03/2006"
Forms!frmmain!List2 = 1

The date and locationId from the list boxes are what I should have. I tried to append 7 records to the record table that I knew where there. I could get no reading from LocationId or DateId. But I'm still getting no message. Why is it not counting tblrecords?

 

[PHV]

OK, regional settings issue:
DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!List2 & " AND DateID=#" & Format(Forms!frmmain!List0, "yyyy-mm-dd") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi,
Ok here's what It is now,

cntAppend = 7
cntAll = 7
Forms!frmmain!List0 "10/03/2006"
Forms!frmmain!List2 = 1

But still no message.


Private Sub Command34_Click()
On Error GoTo Command34_Err
Dim cntAppend As Integer, cntAll As Integer
cntAppend = DCount("*", "qryTest")
cntAll = DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!List2 & " AND DateID=#" & Format(Forms!frmmain!List0, "yyyy-mm-dd") & "#")

If (cntAppend < cntAll) Then
MsgBox "Record already exists"
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryaddrecords"
DoCmd.SetWarnings True
Me.List2.Value = ""
strSQL = "DELETE * FROM tblTemp"
CurrentDb.Execute strSQL
Command34_Exit:
Exit Sub
Command34_Err:
MsgBox Error$
Resume Command34_Exit
End Sub

 

[stix4t2]

qrytest should be returning no records. Are you sure you are using the right joins, check this query, it shouldn't have any records.

 

[lars7]

Hi,
I do get records in qrytest:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, forms!frmmain!list0 AS F_DateId, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL
FROM tbltruststaff LEFT JOIN tbltemp ON tbltruststaff.Paynum = tbltemp.PayNum
ORDER BY tbltruststaff.Paynum;

It's only cntAll that changes, it goes to nothing if the records are not in the records table.

 

[lars7]

Hi,
Would anyone like to have a look at for me, I'd be happy to Zip it up and send it. The code from PHV seems to be fine maybe I'm not seeing something.

 

[stix4t2]

lars,
Paste the the other sql statements, please label which is which.

 

[lars7]

Hi Stix,
This is the append query:

INSERT INTO tblRecords ( LocationId, PayNum, Employee, Grade, DateId, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, tbltruststaff.Grade, forms!frmmain!list0 AS F_DateId, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp RIGHT JOIN tbltruststaff ON tbltemp.PayNum = tbltruststaff.Paynum
WHERE (((tbltruststaff.S_LocationId)=[forms]![frmmain]![list2]));

This is the Subform:

SELECT qrytblrecords.LocationId, qrytblrecords.PayNum, qrytblrecords.Employee, qrytblrecords.Grade, qrytblrecords.DateId, qrytblrecords.HRS, qrytblrecords.OT, qrytblrecords.EX, qrytblrecords.LTS, qrytblrecords.STS, qrytblrecords.AL, qrytblrecords.ML, qrytblrecords.SL, qrytblrecords.CL, qrytblrecords.UL, qrytblrecords.OL
FROM qrytblrecords;

This is the temp query:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, forms!frmmain!list0 AS F_DateId, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL
FROM tbltruststaff LEFT JOIN tbltemp ON tbltruststaff.Paynum = tbltemp.PayNum
ORDER BY tbltruststaff.Paynum;

And from the command button:


Private Sub Command34_Click()
On Error GoTo Command34_Err
Dim cntAppend As Integer, cntAll As Integer
cntAppend = DCount("*", "qryTest")
cntAll = DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!List2 & " AND DateID=#" & Format(Forms!frmmain!List0, "yyyy-mm-dd") & "#")
If (cntAppend < cntAll) Then
MsgBox "Record already exists"
Exit Sub
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryaddrecords"
DoCmd.SetWarnings True
Me.List2.Value = ""
strSQL = "DELETE * FROM tblTemp"
CurrentDb.Execute strSQL
Command34_Exit:
Exit Sub
Command34_Err:
MsgBox Error$
Resume Command34_Exit
End Sub

If you need more information let me know.

 

[stix4t2]

lars,

temp query reads to me all records in tbltemp and matching records in tbltruststaff. I thought we had discussed wanting the other way, all staff and matching temp. This cntAll, possible append records.

copy temp query and add tblrecords. Join locationid and dateid( or is this paynum), all temp query and matching tblrecords. Test tblrecords for null, for appendable records.

 

[lars7]

Hi Stix,
"qrytemp" is to show all staff in the staff table it cannot work the other way there is no records in "tbltemp" untill I saved a location. I redone the qry and here is the new SQL for "qrytemp"

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, forms!frmmain!list0 AS F_DateId, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp RIGHT JOIN tbltruststaff ON tbltemp.PayNum=tbltruststaff.Paynum;


The information from the query gets stored in tbltemp(for milliseconds) and then the append query "qryaddrecords" sends it to "tblrecords" Here is the SQL for "qryaddrecords"

INSERT INTO tblRecords ( LocationId, PayNum, Employee, Grade, DateId, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, tbltruststaff.Grade, forms!frmmain!list0 AS F_DateId, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp RIGHT JOIN tbltruststaff ON tbltemp.PayNum = tbltruststaff.Paynum
WHERE (((tbltruststaff.S_LocationId)=[forms]![frmmain]![list2]));

I will try the second part of your post.

 

[stix4t2]

lars,

Lets get on the same page so you can get this done. Without the extra fields, i.e. WTE, OT...

tblTemp is just for adding records. What main fields are in it that match tblRecords, just Paynum.

And what else are you entering on the form, Location ID and

So is this right, you want to take the temp data and append it to tblRecords. But need to test if the paynum and location id already exist in tblRecords?

To use the same terms, qryAddTest will be appendable records and qryAddAll will attempted appendable records.

 

[lars7]

hi Stix,
I have removed "tbltemp" from the append query so that I am going from "qrytemp" below:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, forms!frmmain!list0 AS F_DateId, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp RIGHT JOIN tbltruststaff ON tbltemp.PayNum = tbltruststaff.Paynum;

straight to the append query "qryaddrecords" below:

INSERT INTO tblRecords ( LocationId, PayNum, Employee, Grade, DateId, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, tbltruststaff.Grade, forms!frmmain!list0 AS F_DateId, tbltruststaff.WTE, qrytemp.OT, qrytemp.EX, qrytemp.LTS, qrytemp.STS, qrytemp.AL, qrytemp.ML, qrytemp.SL, qrytemp.CL, qrytemp.UL, qrytemp.OL, qrytemp.ED
FROM tbltruststaff LEFT JOIN qrytemp ON tbltruststaff.Paynum = qrytemp.Paynum
WHERE (((tbltruststaff.S_LocationId)=[forms]![frmmain]![list2]));


I hope this makes it a little easier to follow it does for me I think.

 

[stix4t2]

Lars,

So qrytemp has all the records that can to be appended to tbl records. You probably need to add the where locationid clause to the qryTemp. This query represents cntAll.

Build another query that links qrytemp to tblrecords to get cntAppend. Link the locationid and dateid's with all records from qrytemp and matches of tblrecords. Add one test for null to a column with tblrecords.locationid for missing records that will be appended, if there is a value then it already exists..

Use the second query in the append query because it represents only the records that need to be added. You won't need fields from tbltruststaff because they are already in qryTemp.

 

[lars7]

Hi Stix,
Sorry but I have gone and changed things again. I have removed "tbltruststaff" from the append query I now only have "qrytemp" and it works fine. Here is both SQL's now:

Qrytemp:

SELECT tbltruststaff.S_LocationId, tbltruststaff.Paynum, [S_Name] & "," & [F_Name] AS Employee, forms!frmmain!list0 AS F_DateId, tbltruststaff.Grade, tbltruststaff.WTE, tbltemp.OT, tbltemp.EX, tbltemp.LTS, tbltemp.STS, tbltemp.AL, tbltemp.ML, tbltemp.SL, tbltemp.CL, tbltemp.UL, tbltemp.OL, tbltemp.ED
FROM tbltemp RIGHT JOIN tbltruststaff ON tbltemp.PayNum=tbltruststaff.Paynum;

Qryaddrecords:

INSERT INTO tblRecords ( LocationId, PayNum, Employee, DateId, Grade, HRS, OT, EX, LTS, STS, AL, ML, SL, CL, UL, OL, ED )
SELECT qrytemp.S_LocationId, qrytemp.Paynum, qrytemp.Employee, qrytemp.F_DateId, qrytemp.Grade, qrytemp.WTE, qrytemp.OT, qrytemp.EX, qrytemp.LTS, qrytemp.STS, qrytemp.AL, qrytemp.ML, qrytemp.SL, qrytemp.CL, qrytemp.UL, qrytemp.OL, qrytemp.ED
FROM qrytemp
WHERE (((qrytemp.S_LocationId)=[forms]![frmmain]![list2]));

I hope this makes it clearer,

 

[stix4t2]

I am having troubles understanding why the locationid where clause is in the qryaddrecords and the qrytemp. Also what query are you using to test the actual number of records being appended, cntAppend.

 

[lars7]

Hi,
The where clause is only in "qryaddrecords" otherwise I would be saving all records when I press the save button instead of the staff in each indiviual location, I thought that was the best way to go rather than waiting till all the locations had been processed.

For "cntappend" I am using a select query, you told me to copy the append query and I called it qrytest.