Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Unanswered: Quick Questions (hopefully)

    Hello,

    I have a couple of queries please ?

    1) I have an entry form of sorts (FrmEntry), and when the date loses focus, if it is past a certain date, an outlook OFT file opens.

    This is all spot on. However, can I make it so the form I am currently in (FrmEntry) closes when the OFT file is opened, and the record is deleted from the table (TblMain)

    2) Also, on the above mentioned form I have 2 buttons. One to save and close the form, and the other one is to close the form. Both of these do the same function though. What I want is for one of them to save the record and close, whereas the other one I want to close the form without saving the record.

    Can these be done.

    Thanks in advance.

    J.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    1: Need to see the code you aready have...


    2: Save and Close Form (in the On Click event):

    DoCmd.RunCommand acCmdSaveRecord
    DoEvents
    DoCmd.Close


    Close Form Without saving:

    DoCmd.RunCommand acCmdUndo
    DoEvents
    DoCmd.Close




  3. #3
    Join Date
    Jun 2004
    Posts
    26

    Thanks

    Thank you CyberLynx - that second one works fine now.

    As for the first one, here is the code I use :

    Private Sub TxtDateTo_LostFocus()
    Select Case Format(TxtDateTo, "ddd")
    Case "Mon"
    TxtCutOffLookup = DateAdd("d", 6, TxtDateTo)
    Case "Tue"
    TxtCutOffLookup = DateAdd("d", 5, TxtDateTo)
    Case "Wed"
    TxtCutOffLookup = DateAdd("d", 4, TxtDateTo)
    Case "Thu"
    TxtCutOffLookup = DateAdd("d", 3, TxtDateTo)
    Case "Fri"
    TxtCutOffLookup = DateAdd("d", 2, TxtDateTo)
    Case "Sat"
    TxtCutOffLookup = DateAdd("d", 1, TxtDateTo)
    Case "Sun"
    TxtCutOffLookup = Format(TxtDateTo, "dd mmmm yyyy")
    End Select
    On Error Resume Next
    Dim x
    x = DLookup("[CutOffDate]", "TblCutOffDate", "[WeekEnding] = #" & Format(TxtCutOffLookup, "dd mmm yyyy") & "#")
    LblCutOff2.Value = Format(x, "dd mmmm yyyy")
    Dim Msg
    If Format(x, "0") < Format(TxtSysDate, "0") Then
    Msg = MsgBox("Unfortunately the Cut Off Date for this period has passed." & vbCrLf & vbCrLf & "Please use the following template instead.", vbCritical + vbOKOnly)
    DoCmd.Close acForm, "Frm Entry"
    ShellExecute 0&, "Open", "c:\Template.oft", 0, 0, SW_NORMAL
    Else
    End If

    I hope you can help me.

    Thanks

    J.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Just real quick and off the top...try this:

    Code:
    If Format(X, "0") < Format(TxtSysDate, "0") Then
       Msg = MsgBox("Unfortunately the Cut Off Date for this period has passed." & _
       vbNewLine & vbNewLine & "Please use the following template instead.", _
       vbCritical + vbOKOnly)
       'prevent the "Are You Sure" Msg box from popping up....
       DoCmd.SetWarnings False
       'Delete the current record...
       DoCmd.RunCommand acCmdDeleteRecord
    'allow Msg box warnings again...
       DoCmd.SetWarnings True
       'close the form....you may need to hide the form (Me.Visible = False)
       'first then put the "DoCmd.Close" after the "Shell" statement...
       DoCmd.Close acForm, "Frm Entry"
       ShellExecute 0&, "Open", "c:\Template.oft", 0, 0, SW_NORMAL
    Else
       'whatever............
    End If

  5. #5
    Join Date
    Jun 2004
    Posts
    26

    Thanks

    Thank you very much CyberLynx - that has worked a treat !

  6. #6
    Join Date
    Jun 2004
    Posts
    26

    One more thing...

    Sorry to be a huge pain, but would you happen to know how to accomplish the following :

    As you know the OFT file opens dependant on the dates, and it opens when the date to loses focus.

    Is there any way to only have this OFT open when the record is created. I.e. NOT when someone just happens to edit the record to check something / have a look at it ?

    Thanks again.

    J.

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Certainly...try this:


    Code:
    If (Format(X, "0") < Format(TxtSysDate, "0")) And Me.NewRecord =True Then
       Msg = MsgBox("Unfortunately the Cut Off Date for this period has passed." & _
       vbNewLine & vbNewLine & "Please use the following template instead.", _
       vbCritical + vbOKOnly)
       'prevent the "Are You Sure" Msg box from popping up....
       DoCmd.SetWarnings False
       'Delete the current record...
       DoCmd.RunCommand acCmdDeleteRecord
    'allow Msg box warnings again...
       DoCmd.SetWarnings True
       'close the form....you may need to hide the form (Me.Visible = False)
       'first then put the "DoCmd.Close" after the "Shell" statement...
       DoCmd.Close acForm, "Frm Entry"
       ShellExecute 0&, "Open", "c:\Template.oft", 0, 0, SW_NORMAL
    Else
       'whatever............
    End If

    The key statement here is Me.NewRecord. If the current record being worked on is a new record then this statement will return True. If the current record being worked on is a old or an existing record then this statement will return false.

    If you want to bring up your template (OFT) as soon as the new record is created rather than after the date is enetered and loses focus then you would want to place code into the On Current event for you form. Perhaps something like this;

    Code:
    If Me.NewRecord = True Then
    	ShellExecute 0&, "Open", "c:\Template.oft", 0, 0, SW_NORMAL
    End if
    Now...as soon as a new record is created, the OFT is opened.

    Hope this helps...


  8. #8
    Join Date
    Jun 2004
    Posts
    26

    Thanks

    Thank you very much for that.

    I appreciate all your help.

  9. #9
    Join Date
    Jun 2004
    Posts
    26
    Hello,

    I have done the above, and it works fine the first time. However, I find that once the OFT file has opened and the FrmEntry has Closed - when I next try to open the FrmEntry, it doesn't show.
    I have to close the DB then enter it again to get the FrmEntry to show again.

    I'm sure it must be something with my code, which is in the following order :

    ------------------------------------------------
    If (Format(X, "0") < Format(TxtSysDate, "0")) And Me.NewRecord =True Then
    Msg = MsgBox("Unfortunately the Cut Off Date for this period has passed." & _
    vbNewLine & vbNewLine & "Please use the following template instead.", _
    vbCritical + vbOKOnly)
    'prevent the "Are You Sure" Msg box from popping up....
    DoCmd.SetWarnings False
    'Delete the current record...
    DoCmd.RunCommand acCmdDeleteRecord
    'set warnings to show again
    DoCmd.SetWarnings True
    'hide the entry form
    Me.Visible = False
    'open the outlook oft file
    ShellExecute 0&, "Open", "c:\Template.oft", 0, 0, SW_NORMAL
    'close entry form
    DoCmd.Close acForm, "Frm Entry"
    'open list form
    DoCmd.OpenForm "FrmList"
    'refresh date in list form
    Form_FrmList.Lstcustomers.Requery
    ------------------------------------------------

    Any help would be greatly appreciated. Thanks.

    J.

  10. #10
    Join Date
    Nov 2003
    Posts
    1,487
    Try this...

    Code:
    If (Format(X, "0") < Format(TxtSysDate, "0")) And Me.NewRecord =True Then
    Msg = MsgBox("Unfortunately the Cut Off Date for this period has passed." & _
    vbNewLine & vbNewLine & "Please use the following template instead.", _
    vbCritical + vbOKOnly)
    'prevent the "Are You Sure" Msg box from popping up....
    DoCmd.SetWarnings False
    'Delete the current record...
    DoCmd.RunCommand acCmdDeleteRecord
    'set warnings to show again
    DoCmd.SetWarnings True
    'hide the entry form
    Me.Visible = False
    'open the outlook oft file
    ShellExecute 0&, "Open", "c:\Template.oft", 0, 0, SW_NORMAL
    'close entry form
    Me.Visible = True
    DoCmd.Close acForm, "Frm Entry"
    DoEvents
    'open list form
    DoCmd.OpenForm "FrmList"
    'refresh date in list form
    Form_FrmList.Lstcustomers.Requery

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •