Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Records not showing up in search (XPPro/SP2)

    I have a data entry form that will not find new records until I close and reopen the form. The record is in the table, if I try to add the record it says I'm creating duplicate values but still doesn't show the record when I search. The only thing I've changed is to add a unbound combo box in the header for selection of the records...but this problem didn't start right away either. The form is also extremely slow to load if I go into design view to change something. Can you give me some idea what to look for?

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

    Re: Records not showing up in search (XPPro/SP2)

    1. Not being able to find a record.

    Do you have a filter on the form or its record source? If so, the record causing the warning about duplicate records may not be displayed in the form.

    2. Slow opening of form in design view.

    Is the record source of the form a linked table, or a query based on at least one linked table? If so, Access may have to create a connection to the database containing the source each time you open the form. It might help to open a connection when the database is opened, for instance by always having a form open that is bound to a table in the other database, or by opening a recordset based on a table in the other database.

    It might also help to turn off automatic name correction in Tools | Options..., General tab.

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    1. No filters that I can see..the filter property is blank. I did check with the person entering the records and they can do a search and the record shows up for them, although not in the combo in the header.

    2. The form is in a secured database with a front/back end. It is the form that displays when the database is opened. I have turned off the auto correct. It is a pretty complex tabbed form with many subforms and a picture for nearly every record (not stored in database). It seems to open another occurence of access while it's loading that disapears when it's finished???

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    If the data is being added from a different copy of the DB you will need to requery your forms data source before it will show up for you.
    HTH

    Peter

  5. #5
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    I don't think that's the case, the front ends are all linked to the back end. Still searching...

  6. #6
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    From Access Help
    <hr>The Requery method updates the data underlying a form or control to reflect records that are new to or have been deleted from the record source since it was last requeried. The Refresh method shows only changes that have been made to the current set of records; it doesn't reflect new records or deleted records in the record source<hr>
    HTH

    Peter

  7. #7
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    I don't think I'm understanding you (ok I really don't <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> ) This has never been a problem before, we could see new records in the form immediately. I can see the entry right away in the table now. There is no requery or refresh method in the form properties. Am I missing something??

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

    Re: Records not showing up in search (XPPro/SP2)

    Requery and Refresh are not properties, so you can't see them in the Properties window. They are methods (procedures) that can be executed in VBA code when the form is open. You need Refresh when you suspect that another user has updated records since you opened the form. Requery completely reloads the record source of the form. I don't see whey they should be necessary here, but then, I don't understand what is causing the problem either...

    I was struck by your remark in an earlier reply that a second instance of Access opens when the form is opened. That is unusual. What code (if any) do you have in the Form_Open and/or Form_Load procedures of the form?

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    Hans

    It was the parts
    <hr>I have a data entry form that will not find new records until I close and reopen the form<hr>
    and
    <hr>I did check with the person entering the records and they can do a search and the record shows up for them, although not in the combo in the header<hr>
    that led me to think that this is a case of someone trying to see records entered in another form without requerying the recordsource first. Same problem with it not showing up in the combo of the person doing the data entry.

    Peter

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

    Re: Records not showing up in search (XPPro/SP2)

    Is the combobox being requeried after the new data is entered? You're going to have to get more specific about what is going on in the form. If the record has not yet been saved when you try to search, then it won't show up. If the record was added to a recordset using DAO and a recordset opened using the DBEngine rather than CurrentDb, the record won't show up until the cache is refreshed. Are you talking about it showing up in the form's recordset, the table, or the combobox? If you're talking about the form, then the reasons may be quite different from the reasons it wouldn't show up in the combobox.
    Charlotte

  11. #11
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Records not showing up in search (XPPro/SP2)

    Whew...I'll try to answer everyones questions. The extra instance of access only occurs when I go into design view and then back to form view. This is some of the code behind the form. Any other code is for combo boxes (not in list).

    Code for Combo box in header:
    Private Sub cboSelect_AfterUpdate()
    'Combo box to find record on main form
    'Written by Helen Feddema 2/17/2002
    'Last modified 2/17/2002

    On Error GoTo ErrorHandler

    Dim strSearch As String

    strSearch = "[StudentId] = " & Me![cboSelect]

    'Find the record that matches the control
    Me.RecordsetClone.FindFirst strSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark

    ErrorHandlerExit:
    Exit Sub

    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub


    Code to view pictures:
    Private Sub Form_AfterUpdate()
    CallDisplayImage
    End Sub

    Private Sub Form_Current()
    CallDisplayImage
    End Sub

    Private Sub txtImageName_AfterUpdate()
    CallDisplayImage
    End Sub

    Private Sub CallDisplayImage()
    Me!txtimagenote = DisplayImage(Me!ImageFrame, Me!txtImageName)
    End Sub


    Code for updating form:
    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg As String
    strMsg = "Data has changed."
    strMsg = strMsg & "Do you wish to save the changes?"
    strMsg = strMsg & "Click Yes to Save or No to Discard changes."
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?") = vbYes Then
    'do nothing
    Else
    DoCmd.RunCommand acCmdUndo

    'For Access 95, use DoMenuItem instead
    'DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    End If


    End Sub

    For Charlottes question:
    It doesn't show up in either. I guess one problem is that the combo box in the header needs to requery for the new records to show up?
    The form does ask you to save changes when you move from the main form or close the database though so I think the new records should be available??
    I hope this explains better. Thanks for your help

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

    Re: Records not showing up in search (XPPro/SP2)

    Well, your code doesn't reveal anything about the combobox except that it's being used to search the form's recordset. If the combobox is based on a table or query, then you would need to save a record and requery the combobox before the new record would show up in it. You could requery the combobox in the AfterUpdate event of the form, but that event won't happen unless you move off the record, close the form or otherwise trigger a save.

    As for the "extra instance of Access", I suspect that you are seeing the Visual Basic editor window as your "second instance". It isn't a second instance of Access, even if you see a (2) beside your Access button on the task bar. It is merely a second Access *window*, which happens to be the VB editor, not the interface window. They're both part of the same session of Access.
    Charlotte

Posting Permissions

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