Maybe I am just looking at this too long...
I would like to refresh the database after the function is run. I put the Refresh at the end of my function and here is the error that I received:
Compile error:
Sub for Function not defined
Without the refresh the module work fine.
Here is my module:
Function PrintStickingLabel()
Dim i As Integer
Dim db As Database, tdf As TableDef
Set db = CurrentDb
i = MsgBox("You are about to print sticking label; do you want to continue?", vbYesNo, "Import Cutting")
If i = vbNo Then
Exit Function
Else
DoCmd.SetWarnings False
'''''Check if table "tblStickingLabelProppck" exist, if does then delete
For Each tdf In db.TableDefs
If tdf.Name = "tblStickingLabel" Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next tdf
'''Creating table for printing sticking label
CurrentDb.Execute " SELECT Master.ItemCode, tblHarvestingSchedule.PNam
e, tblHarvestingSchedule.SowD
at, " _
& " tblHarvestingSchedule.Prin
tTag, Master.SowYr, Master.SowWk, Master.SowSize, Master.EventType, " _
& " Master.Location, Sum(Master.Qty) AS TotalQty, Master.CDate, AllClockNo([ItemCode],[Sow
Yr],[SowWk
], " _
& " [SowSize],[EventType],[Loc
ation],[Cd
ate]) AS ClockNos, tblHarvestingSchedule.Note
, " _
& " tblHarvestingSchedule.StkQ
ty INTO tblStickingLabel " _
& " FROM Master INNER JOIN tblHarvestingSchedule ON Master.ItemCode = tblHarvestingSchedule.Item
No " _
& " GROUP BY Master.ItemCode, tblHarvestingSchedule.PNam
e, tblHarvestingSchedule.SowD
at, " _
& " tblHarvestingSchedule.Prin
tTag, Master.SowYr, Master.SowWk, Master.SowSize, " _
& " Master.EventType, Master.Location, Master.CDate, " _
& " AllClockNo([ItemCode],[Sow
Yr],[SowWk
],[SowSize
],[EventTy
pe],[Locat
ion],[Cdat
e]), " _
& " tblHarvestingSchedule.Note
, tblHarvestingSchedule.StkQ
ty " _
& " HAVING (((tblHarvestingSchedule.P
rintTag)=T
rue) AND ((Master.EventType)=""HV""
) AND " _
& " ((tblHarvestingSchedule.No
te)<>""dis
card"") AND ((tblHarvestingSchedule.St
kQty)>0));
"
End If
Refresh >>>this is where I like it to be.
End Function
Thanks
perennial
Start Free Trial