Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    May 2008
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filling dialog boxes (Access 2002)

    Hello,
    I am trying to move from one form to another shutting the first form behind me and opening the new form at the record I have just left on the first form. I could do this using a stLinkCriteria, and this works perfectly, the problem is that rather than find the record it filters the data set and anyone wanting to find another record afterwards, is stuck. If I put a remove filter on the same button, I lose the link. When opening the second form the Find dialog box pops up automatically anyway so I want to use data from a field in the first form to populate the find dialog box in the second form but I can't work out how to do this and would greatly appreciate any suggestion.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling dialog boxes (Access 2002)

    Why does the Find dialog pop up automatically when the second form is opened? Do you have code in the On Load or On Open event of that form? If so, could you post that code?

  3. #3
    Lounger
    Join Date
    May 2008
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling dialog boxes (Access 2002)

    It's on the On open event (deliberately!) and reads as follows:
    Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToControl "Policy"
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling dialog boxes (Access 2002)

    OK, thanks. What kind of control is Policy? A text box or a combo box? And what kind of field is it bound to? A text field or a number field or a date/time field?

  5. #5
    Lounger
    Join Date
    May 2008
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling dialog boxes (Access 2002)

    text boxes on both forms, they have different sources but are both data type text.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling dialog boxes (Access 2002)

    Try this:

    1) In the code behind the first form that opens the second one, specify an OpenArgs argument:
    <code>
    DoCmd.OpenForm FormName:="SecondForm", OpenArgs:=Me.Policy
    </code>
    2) I'd use the On Load event of the second form instead of the On Open event:
    <code>
    Private Sub Form_Load()
    Me.Policy.SetFocus
    If Not Nz(Me.OpenArgs) = "" Then
    SendKeys Me.OpenArgs
    End If
    RunCommand acCmdFind
    End Sub
    </code>
    I replaced GoToControl and DoMenuItem with more modern equivalents.

  7. #7
    Lounger
    Join Date
    May 2008
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling dialog boxes (Access 2002)

    That has all worked brilliantly but it leaves me with an open dialog box. I tried to tidy it up by adding some send keys "%F" at the end to execute the find, but this then interferes with/overrides my On Open event which is there for more general users of the form who are not using the second form and simply brings up the unpopulated dialog box. Also, I could not then find out what the code was for the escape key to then get rid of dialog box. Is it possible to execute the find and escape the dialog box on the On Load event without interfering with the the On Open event, and if so how do I do it?
    Many thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling dialog boxes (Access 2002)

    Would it be OK *not* to display the Find dialog if the second form is opened from the first one, and simply jump to the record with the specified instead? If the form is opened by itself, the Find dialog would still be displayed.

    If so, you could use code like this:
    <code>
    Private Sub Form_Load()
    Dim rst As DAO.Recordset
    If Not Nz(Me.OpenArgs) = "" Then
    Set rst = Me.RecordsetClone
    rst.FindFirst "Policy = " & Chr(34) & Me.OpenArgs & Chr(34)
    If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing
    Else
    Me.Policy.SetFocus
    RunCommand acCmdFind
    End If
    End Sub
    </code>
    The action is different depending on whether the OpenArgs argument is supplied or not. The code requires that you have a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor.

  9. #9
    Lounger
    Join Date
    May 2008
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling dialog boxes (Access 2002)

    That would be a perfect solution but I cannot get the code to work. I have added the DAO 3.6 as a library to start with and have now got the following codes on the forms:

    frmContCert, Event code On Load only now as follows:
    Private Sub Form_Load()
    Dim rst As DAO.Recordset
    If Not Nz(Me.OpenArgs) = "" Then
    Set rst = Me.RecordsetClone
    rst.FindFirst "PolicyNumber = " & Chr(34) & Me.OpenArgs & Chr(34)
    If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing
    Else
    Me.Policy.SetFocus
    RunCommand acCmdFind
    End If
    End Sub

    This works to produce the dialog box if you are opening it for the first time, perfect.
    I then open the frmOMC, using a strLink, to the policy I was working on in frmContCert, closing frmContCert behind me so that the updates can occur. When I finish working on that form, I need to reopen frmContCert, at the same policy, but not filtered to that policy only, and close the frmOMC behind me. To that end I have the following code on the close button OnClick event:
    Private Sub btnClose_Click()
    On Error GoTo Err_btnClose_Click
    DoCmd.OpenForm "frmContCert", , , , , , Me.PolicyNumber = Policy
    DoCmd.Close acForm, "frmOMC"
    Exit_btnClose_Click:
    Exit Sub
    Err_btnClose_Click:
    MsgBox Err.Description
    Resume Exit_btnClose_Click
    End Sub
    I put the OpenArg in as I could not see how else the OnLoad in frmContCert was going to work out what to do. The OnLoad code works to the extent that it surpresses the dialog box but it does not find the right policy, just goes to the first records.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling dialog boxes (Access 2002)

    Try changing the line

    DoCmd.OpenForm "frmContCert", , , , , , Me.PolicyNumber = Policy

    to

    DoCmd.OpenForm "frmContCert", , , , , , Me.Policy

    where Policy should be the name of the relevant field/control on frmOMC (the form that runs this code).

  11. #11
    Lounger
    Join Date
    May 2008
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filling dialog boxes (Access 2002)

    works like a dream, thank you. Not sure what the OpenArg Me.Policy is doing though, is it saying that this is the field that should be the source of the data in the On Load event of the second form?
    Many thanks anyway, as the forms are now user proof!

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filling dialog boxes (Access 2002)

    The OpenArgs argument of DoCmd.OpenForm and DoCmd.OpenReport is a way of passing information from the form that runs the code to the form or report being opened. The information can be the value of a field, or a text string to be used as a title, or a Yes/No value - whatever is useful to the programmer. The code behind the form or report being opened can inspect the value of OpenArgs, usually in the On Open or (for a form) On Load event, and use any way the programmer wants.

    In this example, the value of the Policy field is passed from the first form to the second form, and the second form uses it to jump to the record with that value in the PolicyName field.

Posting Permissions

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