compose a module to call from forms (1 Viewer)

F

Falcon88

Registered User.
Local time
Today, 12:26
Joined
Nov 4, 2014
Messages
299
hiii all dears

i have three forms , mainfrm , inside it [FONT=&quot]OrdersSubFrm , inside it [/FONT][FONT=&quot]OrdDetSubFrm .

i have a code to allert user if there are any changes , for any control in any form of this three forms. , after three days of the current date.
i use this codes in the Form_BeforeUpdate event :
for the main form i use :
[/FONT]
Code: 
[LEFT][FONT=&quot][SIZE=3]Dim MyDate
Dim LValue As Integer
Dim MnthCnt As String

MyDate = DateSerial(Year(Me!OrdersSubFrm.Form!OrderDate), Month(Me!OrdersSubFrm.Form!OrderDate), Day(Me!OrdersSubFrm.Form!OrderDate))
LValue = DateDiff("d", MyDate, Date)

If LValue < 10 Then
MnthCnt = "day"
Else
MnthCnt = "days"
End If
If LValue >= 3 Then
   If MsgBox("Dear user It has been " & " ( " & LValue & " ) " & MnthCnt & vbCrLf & _
   " from the date of this visit for patient:" & "" & Me.PtName & vbCrLf & _
     "Do you want to change the information of this patient, to confirm the changes Choose Yes, to cancel select No. ", vbInformation + vbMsgBoxRtlReading + vbMsgBoxRight + vbYesNo, " Change Confirmation " & " " & Me.Parent!PtName) = vbYes Then
     Cancel = False
     Else
     Cancel = True
     DoCmd.RunCommand acCmdUndo
     End If
End If
End If
[/SIZE][/FONT][/LEFT]
[SIZE=3]  [/SIZE]
[FONT=&quot]

in the [/FONT][FONT=&quot]OrdersSubFrm (sub form):
[/FONT]
Code: 
Dim MyDate
Dim LValue As Integer
Dim MnthCnt As String
MyDate = DateSerial(Year(Me!OrderDate), Month(Me!OrderDate), Day(Me!OrderDate))
LValue = DateDiff("d", MyDate, Date)
If LValue < 10 Then
MnthCnt = "day"
Else
MnthCnt = "days"
End If
If LValue >= 3 Then
   If MsgBox("Dear user It has been " & " ( " & LValue & " ) " & MnthCnt & vbCrLf & _
     " from the date of this visit for patient:" & "" & Me.Parent.PtName & vbCrLf & _
     " Do you want to change the information of this patient, to confirm the changes Choose Yes, to cancel select No. ", vbInformation + vbMsgBoxRtlReading + vbMsgBoxRight + vbYesNo, " Change Confirmation " & " " & Me.Parent!PtName) = vbYes Then Then
     Cancel = False
     Else
     Cancel = True
     DoCmd.RunCommand acCmdUndo
     End If

End If
[FONT=&quot]

and in the [/FONT][FONT=&quot][FONT=&quot]OrdDetSubFrm (sub sub form) :

Code: 
Dim MyDate

Dim LValue As Integer
Dim MnthCnt As String

MyDate = DateSerial(Year(Me.Parent!OrderDate), Month(Me.Parent!OrderDate), Day(Me.Parent!OrderDate))
LValue = DateDiff("d", MyDate, Date)

If LValue < 10 Then
MnthCnt = "day"
Else
MnthCnt = "days"
End If




If LValue >= 3 Then
   If MsgBox("Dear user It has been " & " ( " & LValue & " ) " & MnthCnt & vbCrLf & _
   " from the date of this visit for patient:" & " " & Me.Parent.Parent!PtName & vbCrLf & _
     " Do you want to change the information of this patient, to confirm the changes Choose Yes, to cancel select No. ", vbInformation + vbMsgBoxRtlReading + vbMsgBoxRight + vbYesNo, " Change Confirmation " & " " & Me.Parent!PtName) = vbYes Then
     Cancel = False
     Else
     Cancel = True
     DoCmd.RunCommand acCmdUndo
     End If

End If
note : i have a listbox in the [/FONT][/FONT][FONT=&quot]OrdersSubFrm (sub form) , under DblClick event the same code , but runs action queries as :
Code: 
Dim MyDate
Dim LValue As Integer
Dim MnthCnt As String
MyDate = DateSerial(Year(Me!OrderDate), Month(Me!OrderDate), Day(Me!OrderDate))
LValue = DateDiff("d", MyDate, Date)
If LValue < 10 Then
MnthCnt = "day"
Else
MnthCnt = "days"
End If
If LValue >= 3 Then
   If MsgBox("Dear user It has been" & " ( " & LValue & " ) " & MnthCnt & vbCrLf & _
     "from the date of this visit for this patient :" & "" & Me.Parent.PtName & vbCrLf & _
     "Do you want to change the information of this patient, to confirm the changes Choose Yes, to cancel select No. ", vbInformation + vbMsgBoxRtlReading + vbMsgBoxRight + vbYesNo, " Change Confirmation " & " " & Me.Parent!PtName) = vbYes Then
      Cancel = False
      DoCmd.SetWarnings False
      DoCmd.OpenQuery "Q4"
      DoCmd.SetWarnings True
      Me!OrdDetSubFrm.Form.Requery

     Else
     Cancel = True

     End If
     Else
   DoCmd.SetWarnings False
  DoCmd.OpenQuery "Q4"
   DoCmd.SetWarnings True
  Me!OrdDetSubFrm.Form.Requery
End If
Q: how to build a module [/FONT] to replace all the above codes ?
 
Minty

Minty

AWF VIP
Local time
Today, 10:26
Joined
Jul 26, 2013
Messages
10,371
To get you started - You have to remove any reference to the form objects (me.) , or pass the calling form into the function / public sub you want to build. It is probably easier to just pass the values you need into the sub or function., as they are all the same.

Don't try and run the action queries initially - leave them in your form,, as they are specific to that form.
 
F

Falcon88

Registered User.
Local time
Today, 12:26
Joined
Nov 4, 2014
Messages
299
Minty said:
To get you started - You have to remove any reference to the form objects (me.) , or pass the calling form into the function / public sub you want to build. It is probably easier to just pass the values you need into the sub or function., as they are all the same.

Don't try and run the action queries initially - leave them in your form,, as they are specific to that form.
Click to expand...

i try :
Code: 
[LEFT][FONT=&quot]Public Function LTDYS(LftDate As Date, MyPtNm As String)[/FONT]
[FONT=&quot]Dim MyDate[/FONT][/LEFT]
 [LEFT][FONT=&quot]Dim LValue As Integer[/FONT][/LEFT]
 [LEFT][FONT=&quot]Dim MnthCnt As String[/FONT]
[FONT=&quot]MyDate = DateSerial(Year(LftDate), Month(LftDate), Day(LftDate))[/FONT][/LEFT]
 [LEFT][FONT=&quot]LValue = DateDiff("d", MyDate, Date)[/FONT]
[FONT=&quot]If LValue < 10 Then[/FONT][/LEFT]
 [LEFT][FONT=&quot]MnthCnt = "day"[/FONT][/LEFT]
 [LEFT][FONT=&quot]Else[/FONT][/LEFT]
 [LEFT][FONT=&quot]MnthCnt = "days"[/FONT][/LEFT]
 [LEFT][FONT=&quot]End If[/FONT][/LEFT]
 [LEFT][FONT=&quot]If LValue >= 3 Then[/FONT][/LEFT]
 [LEFT][FONT=&quot]   If MsgBox("Dear user It has been " & " ( " & LValue & " ) " & MnthCnt & vbCrLf & _[/FONT][/LEFT]
 [LEFT][FONT=&quot]   "[/FONT] [FONT=&quot]from the date of this visit for patient:" & "" & MyPtNm & vbCrLf & _[/FONT][/LEFT]
 [LEFT][FONT=&quot]          "[/FONT] [FONT=&quot]Do you want to change the information of this patient, to confirm the changes Choose Yes, to cancel select No. ,vbInformation + vbMsgBoxRtlReading + vbMsgBoxRight + vbYesNo, " Change Confirmation " & " " & MyPtNm) = vbNo Then[/FONT][/LEFT]
 [LEFT][FONT=&quot]     DoCmd.RunCommand acCmdUndo[/FONT][/LEFT]
 [LEFT][FONT=&quot]     Else[/FONT][/LEFT]
 [LEFT][FONT=&quot]     Exit Function[/FONT][/LEFT]
 [LEFT][FONT=&quot]     End If[/FONT][/LEFT]
 [LEFT][FONT=&quot]End If[/FONT][/LEFT]
 [LEFT][FONT=&quot]Exit Function[/FONT]
[FONT=&quot]End Function[/FONT][/LEFT]

what about action query ? how to use .
 
Minty

Minty

AWF VIP
Local time
Today, 10:26
Joined
Jul 26, 2013
Messages
10,371
Probably the simplest approach - If you pass in the calling forms name you could include the queries if the correct form name is present.
 
You must log in or register to reply here.

Similar threads

B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
430
gemma-the-husky
gemma-the-husky
T
Filter Form From ComboBox and date field
Replies
4
Views
351
Josef P.
J
C
Invalid Use of Null
2
Replies
20
Views
529
chuckcoleman
C
B
Using date from a text box in a where condition
Replies
6
Views
354
Pat Hartman
P
V
Trying to have a button on a form open a report with info from a list box
Replies
18
Views
737
moke123
moke123

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom