Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Closing Quick Entry Form (Access 2000)

    I'm trying to follow Helen Feddema's suggestions on closing a quick entry form, but am very frustrated. I'm missing something.
    The code first saves the current record (if form is dirty), The "find first" code successfully finds the record ID number, but the subsequent test returns a "No Match", so my main form opens but not at the bookmarked record. When I look at the table the record that was saved is there with the proper ID number, so I wonder why it wasn't found? Any suggestions?


    Here's the code:

    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    Dim strSearch As String
    Dim frm As Access.Form
    Dim prj As Object
    Set prj = Application.CurrentProject

    If prj.AllForms("frmMissionary").IsLoaded Then
    Forms![frmMissionary].Visible = True
    Else
    DoCmd.OpenForm "frmMissionary"
    End If

    If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
    End If

    'Find the Missionary record that matches
    strSearch = "[ID] = " & Me.Id
    Debug.Print "Search String: " & strSearch

    Set frm = Forms![frmMissionary]
    frm.RecordsetClone.FindFirst strSearch
    If frm.RecordsetClone.NoMatch = True Then
    Debug.Print "No match found"
    End If

    frm.Bookmark = frmRecordSetClone.Bookmark
    frm.Requery 'what here?

    Exit_cmdClose_Click:
    DoCmd.Close acForm, Me.Name
    Exit Sub

    Err_cmdClose_Click:
    If Err.Number = 2467 Then
    Resume Exit_cmdClose_Click:
    Else
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
    End If
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    Which form is this code running in? Is frmMissionary the quick entry form or is it your main form? In any case, multiple references to frm.Recordsetclone do not apply to the same recordset. Try this instead:

    Dim rst As Recordset

    Set rst = frm.RecordsetClone

    rst.FindFirst strSearch
    If rst.NoMatch = True then
    Debug.Print "No match found"
    Else
    frm.Bookmark = rst.Bookmark
    End If
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    Hi Charlotte,

    Thanks for the quick reply, and I apologize for not supplying complete information. I have a table (tblMissionary) and two forms: frmMissionary and frmMissionaryQuickentry. The code is in the close event of the quick entry form. I entered your code, but then an entirely new problem popped up. The following is the beginning segment of the code, and now I get an error message that the data base cannot find the form frm.Missionary when executing the Set frm command!

    I've carefully checked the spelling of the form, etc., but the code cannot find the form!!! It certainly finds it when I go to the Main Menu and directly open the Missionary form. So I remain very confused at this point.


    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    Dim strSearch As String
    Dim frm As Access.Form
    Dim prj As Object
    Dim rst As Recordset

    Set prj = Application.CurrentProject
    Set frm = Forms![frmMissionary]

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    If frmMissionary is not loaded, you won't find it, since only loaded forms are in the Forms collection of the database. Are both these forms open at once, or only one at a time? I'm not familiar with Helen's example, so I'm not sure exactly what you're trying to do. There are a variety of methods for passing information between forms, so without further details, I don't know what to suggest.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    I recreated the quick entry form, added the following code and that got rid of the "can't find form" message (I think it was some kind of fluke or undetected typo).

    I have a main menu with controls that open frmMissionary and frmMissionaryQuickEntry. The first form is used to view/edit/enter records in tblMissionary. Quick Entry is used only for entering new records (Data Entry property = yes). Both forms are normally not open at the same time except in the quick entry close routine where I open frmMissionary just before closing quick entry.

    I've tried to insert your code as you can see below. However, I still get the "No match found" condition. But the record does get written to the table with the proper ID value, the form does close, and the main form opens on the first record -- not at the record that was just entered due to the aforementioned "No match found" problem.

    I can't understand why it doesn't find the record. It has been written to the table, the ID is correct. So it should find it.

    Also, assuming I can get it to match, is there some additional code needed to move to the bookmarked record? I'm rather ignorant regarding bookmarks in this context.

    Thanks again for your patience, as I'm still in the learning phase with Access.

    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    Dim frm As Access.Form
    Dim strSearch As String
    Dim rst As Recordset

    DoCmd.OpenForm "frmMissionary"

    Set frm = Forms![frmMissionary]

    If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
    End If

    strSearch = "[Id] = " & Me![Id]
    Debug.Print strSearch

    frm.Requery 'IS THIS NECESSARY?
    Set rst = frm.RecordsetClone
    rst.FindFirst strSearch
    If rst.NoMatch = True Then
    Debug.Print "No match found"
    Else
    frm.Bookmark = rst.Bookmark
    'NOW HOW TO MOVE TO BOOKMARKED RECORD?
    End If

    Exit_cmdClose_Click:
    DoCmd.Close acForm, Me.Name
    Exit Sub

    Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    Why are you trying to move frmMissionary to the record just created? Is that where the focus is going rather than to your "main" form? You will have to requery frmMissionary or your main form after adding a new record in the quick entry form because otherwise the new record won't be in the form's recordset yet. You move to a bookmark by setting it. If the record is not found, the pointer doesn't move. After you create the recordsetclone, check for BOF and if false, do a MoveFirst. That will take you to the beginning of the recordset and may cure your issues with NoMatch.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    The quick entry form facilitates entry of the bulk of the data in the record -- but not all. Thus, upon finishing with quick entry I would like to open the main form (frmMissionary) at the newly entered record permitting the user to review/edit and add the remaining data (add a picture and fill in a few subforms). The main form has several pages (tabs) and I find it much easier to initially enter information serially on the quick entry form.

    Anyway, in the code I sent previously I attempted to do this:

    1. Open frmMissionary
    2. Save the new record
    3. Set up the search string
    4. Requery frm.Missionary
    5. Find the matching record and set the bookmark in frmMissionary
    6. Close the quick entry form leaving frmMissionary open at the new record.

    I added the BOF text and MoveFirst, but that didn't change anything. Debug.Print reported the following:

    [ID] = 70
    No match found

    There is a record in the table with ID = 70, but the code obviously doesn't find it.

    frmMissionary opens OK, but it always opens at the first record, not the new one.

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

    Re: Closing Quick Entry Form (Access 2000)

    If the user must always complete data entry in frmMissionary, why not do the "quick entry" in a first tab on that form, with an easy-to-fill-in layout? It would avoid the hassle of having to juggle two forms displaying the same record.

  9. #9
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Closing Quick Entry Form (Access 2000)

    Hi Hans,

    I provided the quick entry for the convenience of the secretary. The boss uses the same main form to view the information and he doesn't need the quick entry. I suppose I could create a special version for him without the quick entry and another for the secretary. Ah well, life is just not simple! Since I'm having so much trouble I think I'll simply avoid the problem and let the secretary go find the new record when the main form comes up. She's used to doing that with the present version.

Posting Permissions

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