Hi guys,
i have tried both options but nothing seems to be working so i will try and give a greater detail with an example-
i need to check between 2 dates every month.
17th of previous month & 16th of current month
if the detail is completed, a record is entered with a date say 14th of current month.
then, someone else not looking properly also tries to complete the detail. i need to stop double entry data so,
i am trying to
1.check the current month
2.check that month against the table records MonthlyTbl
3. if there is a record in the table with the same month, exit otherwise, make the record.
current code ( though it changed a lot recently ! )
Public Sub CompleteMonth()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intX As Integer
Dim strCurrPerd As String
Dim strChkCurrPerd As String
strCurrPerd = Format(Now(), "m")
'check for record in the table
strChkCurrPerd = Nz(DLookup("ProcessDate", "MonthlyTbl", strCurrPerd))
'if record is there, exit.
If strChkCurrPerd = strCurrPerd Then
MsgBox ("You have already completed this period!")
Exit Sub
Else
'if record is not there, check for current activity. if activity is pending, exit
intX = DCount("*", "FullPaymentPendingQry")
If intX <> 0 Then
MsgBox ("You already have Payments in the period." & vbCrLf & "You cannot register a completed month with Pending Payments"), vbOKOnly,
Exit Sub
Else
'otherwise process the completed month
MsgBox ("You have chosen to process a completed month for this period"), vbOKOnly,
'place the resposibility on the user
If MsgBox("Do you agree that you have completed ALL requirements for this period?", vbYesNo), = vbYes Then
MsgBox ("You will now complete the month in your records")vbOKOnly,
'create the record
Set db = CurrentDb
Set rs = db.OpenRecordset("MonthlyTbl", dbOpenDynaset)
rs.AddNew
rs!ProcessDate.Value = Date
rs!Completed.Value = True
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
'advise completion
MsgBox (" your month is complete available for printing via reports"), vbOKOnly,
End If
End If
End If
End Sub
the difficulty is getting the current date to look up the table record. also, the between dates has just been added. i looked at a "between" scenario but havent quite figured that one out yet but working on it. something like-
'set the dd date
strStartDte = "17"
'set the dd date
strEndDte = "16"
'set current month
strCurrMnth = Format(Now(), "m")
'set previous month by month - 1 ( month???? but its probably wrong! )
strPrvMnth = Format(Now(), "m") - 1
'set current year
strCurrYr = Format(Now(), "yy")
'build Current month
strBldCurrMnth = strStartDte & "/" & strCurrMnth & "/" & strCurrYr
'build Previous Month
strBldPrvMnth = strEndDte & "/" & strPrvMnth & "/" & strCurrYr
'Build Period
strPeriod = Between strBldPrvMnth & strCurrMnth
like i say, its probably completely wrong and never likely to work but it gives me a starting point and hopefully, an insight to anyone who feels generous enough to offer advice.
in the meantime,
thanks for you help so far
Nigel
)
