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.

 

[stix4t2]

lars,

1. The append query joins can be set to include all records from one table and matches in the other. Edit the relationship to include all staff members.

2. If you index the fields so duplicates are not allowed, when the append query runs, instead of 5 records added, access will report key failures for the duplicates. This will keep Access from adding the duplicates and you get the warning.

Let us know if you want to add the duplicates and just be warned, or if you are turning warnings off but still want a message.

 

[lars7]

Thanks Stix4t2,
I had the left join from the staff table in the subform query but not the append query so I altered that and now it's saving all.
I'm sorry I should have made myself clear with the second point and said that I had set warnings false and that is why I wanted some code to give a message box only if a weekending date is already present in the records table. I had tried to alter the query SQL myself to run straight from the button but had no success.

 

[stix4t2]

lars,

Can you build a select query that emulates the append query and then use a DCOUNT = 0 to see if records are going to be appended?

 

[lars7]

Hi stix,
I could do the first part but I haven't used Dcount before,
if you told me what to do I'm sure I could do it.

 

[stix4t2]

lars,
The Dcount returns the number of records in a query or table. So if your select query is emulating the append query and there are no records, then the process has no records to append. If you click on DCount in your code and hit F1, you can learn more.

if( DCOUNT( "*", "YourSelectQueryName") = 0)then
     msgbox "No Records to Append"
end if

if you want to compare the number of records in your staff table to the select query then

if( DCOUNT( "*", "YourQueryName") < DCOUNT( "*", "YourStaffTable"))then
     msgbox "Can only append " & DCOUNT( "*", "YourQueryName")  & " records of " & DCOUNT( "*", "YourStaffTable") - 
end if

Of course you have to use your own query and table names

This DCount and DLookup are pretty cool tools to use when programming. If you learn them, then people can say, "That lars, he has a lot of weapons in his arsenal"

 

[lars7]

Hi stix,
I don't know if I have put the code in the right place the msgbox appears weather I can or cant append the records table I only need it to appear if the LocationId and the DateId are the same as records already in the table. This is the code:

Private Sub Command12_Click()
On Error GoTo Command12_Err
Dim strSQL As String
If (DCount("*", "qrytest") < DCount("*", "tblrecords")) Then
MsgBox "Can only append " & DCount("*", "qrytest") & " records of " & DCount("*", "tblrecords")
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryaddrecords"
DoCmd.SetWarnings True
'Me.List0.Value = ""
'Me.List1.Value = ""
'Me.List2.Value = ""
strSQL = "DELETE * FROM tblTemp"
CurrentDb.Execute strSQL
Command12_Exit:
Exit Sub
Command12_Err:
MsgBox Error$
Resume Command12_Exit
End Sub

I commented out the value bit while testing.

 

[stix4t2]

lars,

Do you know how to set a break point and watch variables? The qrytest should return the number of records being appended, tblRecords should return the number of records attempted to be appended. If you break your code at the if(dcount(... statement, you can switch back to Access and view the two queries. They might need adjustment. Try to set up tests for when the append works completely, partly and not at all.

 

[lars7]

Hi Stix,
Sorry I don't know how to set a break point and watch variables. The qrytest is shown the number of records I am trying to append I am getting a msgbox like " you can only append 7 of 100 records" when I'm trying to append 7 records if they are in the records table or not. I was wondering if this could look up the two primary key fields LocationId and DateId and stop the append query running if it found the records already existed.

 

[stix4t2]

lars,

Part of your query for appending

forms!frmmain!list2 AS F_locationId, forms!frmmain!list0 AS F_DateId

You can add a filter to the dcount for filtering records

DCount("*", "tblrecords", "[LocationID] = " & forms!frmmain!list2 AS F_locationId & " And [F_DateID] = " &  forms!frmmain!list0 AS F_DateId )

The third optional parameter for DCount is a filter. I tried to build it to your needs, but you may have to make some changes. Keep in mind that tblrecords must contain a LocationID field and a F_DateID field. Change the filter if the field names are not correct.

To set a break point is pretty easy. Put your cursor on the line where you want to place a break point and it the F9 key. The line will turn the background red. When you the code hits the break point it pauses execution. At this point you can inspect variable to view their values, or step through the code to watch the process. In the visual basic IDE for Access, take a look at the Debug menu. It has items for setting breakpoints, stepping into or over lines of code and setting watches to view values in varaibles. Kind of cool tools for checking code, and good weapons to add to your arsenal.

 

[lars7]

Hi Stix,
I'm getting a syntax error message:

DCount("*", "tblrecords", "[LocationID] = " & forms!frmmain!list2 AS F_locationId & " And [F_DateID] = " & forms!frmmain!list0 AS F_DateId )

with the (As) and the field in the records table is DateId.

 

[stix4t2]

lars,

Sorry, I guess my human side is showing, take the "as" statements out. I cut and pasted too much.

DCount("*", "tblrecords", "[LocationID] = " & forms!frmmain!list2 & " And [F_DateID] = " & forms!frmmain!list0 )

 

[PHV]

Haven't read the whole thread, so just the syntax issue:
DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!list2 & " AND F_DateID=" & Forms!frmmain!list0)

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 got a msgbox saying it was missing = so I put it in and it is still red and giving the missing syntax message.

Have I got this right, this is to replace the if Dcount like below.

Private Sub Command34_Click()
On Error GoTo Command34_Err
Dim strSQL As String
DCount("*", "tblrecords", "[LocationID] = " & forms!frmmain!list2 & " And [F_DateID] = " & forms!frmmain!list0
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryaddrecords"
DoCmd.SetWarnings True
'Me.List0.Value = ""
'Me.List1.Value = ""
Me.List2.Value = ""
strSQL = "DELETE * FROM tblTemp"
CurrentDb.Execute strSQL
Command34_Exit:
Exit Sub
Command34_Err:
MsgBox Error$
Resume Command34_Exit
End Sub

 

[lars7]

Hi PHV,
I just saw yours code and that one is red too.

Private Sub Command34_Click()
On Error GoTo Command34_Err
Dim strSQL As String
DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!list2 & " AND F_DateID=" & Forms!frmmain!list0)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryaddrecords"
DoCmd.SetWarnings True
'Me.List0.Value = ""
'Me.List1.Value = ""
Me.List2.Value = ""
strSQL = "DELETE * FROM tblTemp"
CurrentDb.Execute strSQL
Command34_Exit:
Exit Sub
Command34_Err:
MsgBox Error$
Resume Command34_Exit
End Sub

 

[PHV]

I quite don't understand what you want to do, something like this ?
If DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!list2 & " AND F_DateID=" & Forms!frmmain!list0) Then
' some stuff here
End If

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 PHV,
What I'm looking for is an append query but with a message box saying that If the records already exist in the records table them the records cannot be saved. This is the code:

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;

I have this being run from a command button with set warnings false but I would like a message box saying the records already exist or a bettter way of doing it if you know how.

 

[PHV]

So, you wanted this ?
If DCount("*", "tblrecords", "LocationID=" & Forms!frmmain!list2 & " AND DateID=" & Forms!frmmain!list0) Then
MsgBox "Record already exists"
Exit Sub
End If

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 again,
I'm not getting any message:

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

and the rest of the code is running. The primary key fields in the records table are LocationId and DateID and in List0 it's "fldweekending" and List2 it's L_LocationId if that changes anything.

 

[PHV]

What are the data type of LocationId and DateID ?
Is the record appended ?

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