RecordLocks In Access - Stuck here.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • amanjsingh
    New Member
    • Mar 2007
    • 48

    #1

    RecordLocks In Access - Stuck here.

    Hi,

    I am completely new to Access. Here is something that I am experiencing...

    I have this form and I search for candidate. Whatever results I get, I try to navigate from one to another and it gives me this error -

    Access can't apply the filter because all records are locked.

    I have set the Advanced Property (in Tools Menu --> Options) of Locking as No Lock but it is still the same. Please help.

    Thanks.
  • MMcCarthy
    Recognized Expert MVP
    • Aug 2006
    • 14387

    #2
    Originally posted by amanjsingh
    Hi,

    I am completely new to Access. Here is something that I am experiencing...

    I have this form and I search for candidate. Whatever results I get, I try to navigate from one to another and it gives me this error -

    Access can't apply the filter because all records are locked.

    I have set the Advanced Property (in Tools Menu --> Options) of Locking as No Lock but it is still the same. Please help.

    Thanks.
    What is the record source of the form you are using?

    Comment

    • amanjsingh
      New Member
      • Mar 2007
      • 48

      #3
      Originally posted by mmccarthy
      What is the record source of the form you are using?
      I know I am dumb to ask this but how do you find that? I am really new.

      Comment

      • MMcCarthy
        Recognized Expert MVP
        • Aug 2006
        • 14387

        #4
        Originally posted by amanjsingh
        I know I am dumb to ask this but how do you find that? I am really new.
        Open the form in Design View. Open the properties of the form. Under the Data tab there is a property called Record Source. While you're there check the property of the filter also.

        Comment

        • amanjsingh
          New Member
          • Mar 2007
          • 48

          #5
          Originally posted by mmccarthy
          Open the form in Design View. Open the properties of the form. Under the Data tab there is a property called Record Source. While you're there check the property of the filter also.
          Record Source is Candidate
          Filter field is empty.

          Is Candidate table locked? That is what you mean?

          Comment

          • MMcCarthy
            Recognized Expert MVP
            • Aug 2006
            • 14387

            #6
            Originally posted by amanjsingh
            Record Source is Candidate
            Filter field is empty.

            Is Candidate table locked? That is what you mean?
            No I think your only problem is your searching. When you search you apply a filter to show only the record(s) you are searching for. You will have to remove the filter to continue to navigate through the records. How are you seaching the form?

            Comment

            • amanjsingh
              New Member
              • Mar 2007
              • 48

              #7
              Originally posted by mmccarthy
              No I think your only problem is your searching. When you search you apply a filter to show only the record(s) you are searching for. You will have to remove the filter to continue to navigate through the records. How are you seaching the form?
              I got this -

              Hope this helps. There is an [Event Procedure] in After Update properties.

              Code:
              Private Sub SelectIDNo_AfterUpdate()
                  Me.Filter = ""
                  
                  If Not IsNull(Me.SelectIDNo) Then
                      Me.FilterOn = True
                      Me.Filter = "[SSNo] LIKE '" + Forms![AO_ViewEditCandidateData]![SelectIDNo] + "*'"
                  Else
                      Me.FilterOn = False
                  End If
                  
                  Me.SelectIDNo = ""
              End Sub
              
              Private Sub SelectLastName_AfterUpdate()
              
                  Me.Filter = ""
                  
                  If Not IsNull(Me.SelectLastName) Then
                      Me.Filter = "[LastName] LIKE '" + Forms![AO_ViewEditCandidateData]![SelectLastName] + "*'"
                      Me.FilterOn = True
                  Else
                      Me.FilterOn = False
                  End If
                      
                  Me.SelectLastName = ""
              End Sub
              Thanks- I do need to filter these results say based on SSN.

              Comment

              • MMcCarthy
                Recognized Expert MVP
                • Aug 2006
                • 14387

                #8
                Originally posted by amanjsingh
                I got this -

                Hope this helps. There is an [Event Procedure] in After Update properties.

                Code:
                Private Sub SelectIDNo_AfterUpdate()
                    Me.Filter = ""
                    
                    If Not IsNull(Me.SelectIDNo) Then
                        Me.FilterOn = True
                        Me.Filter = "[SSNo] LIKE '" + Forms![AO_ViewEditCandidateData]![SelectIDNo] + "*'"
                    Else
                        Me.FilterOn = False
                    End If
                    
                    Me.SelectIDNo = ""
                End Sub
                
                Private Sub SelectLastName_AfterUpdate()
                
                    Me.Filter = ""
                    
                    If Not IsNull(Me.SelectLastName) Then
                        Me.Filter = "[LastName] LIKE '" + Forms![AO_ViewEditCandidateData]![SelectLastName] + "*'"
                        Me.FilterOn = True
                    Else
                        Me.FilterOn = False
                    End If
                        
                    Me.SelectLastName = ""
                End Sub
                Thanks- I do need to filter these results say based on SSN.
                These filters control the records available to the form. Why do you need to navigate from the searched record. A button to return all records and remove all searches could be implemented.

                Comment

                • amanjsingh
                  New Member
                  • Mar 2007
                  • 48

                  #9
                  Originally posted by mmccarthy
                  These filters control the records available to the form. Why do you need to navigate from the searched record. A button to return all records and remove all searches could be implemented.
                  I am just search records from a given table, and getting some records. I wnt to navigate those returned records from the original recordset. But when I navigate I get this error. That is when I jump frm one record to another (in the returned recordset) I get this error. Other wise, in the original set, it navigates fine.

                  Comment

                  • MMcCarthy
                    Recognized Expert MVP
                    • Aug 2006
                    • 14387

                    #10
                    Originally posted by amanjsingh
                    I am just search records from a given table, and getting some records. I wnt to navigate those returned records from the original recordset. But when I navigate I get this error. That is when I jump frm one record to another (in the returned recordset) I get this error. Other wise, in the original set, it navigates fine.
                    You must not be returning the number of records you think you are. In the navigation buttons on the bottom of the form it should say 1 of X (Filtered). The X represents the number of records being returned by the filter.

                    Comment

                    • amanjsingh
                      New Member
                      • Mar 2007
                      • 48

                      #11
                      Originally posted by mmccarthy
                      You must not be returning the number of records you think you are. In the navigation buttons on the bottom of the form it should say 1 of X (Filtered). The X represents the number of records being returned by the filter.
                      yes it says, 1 of 3 filtered. Also, if I click OK in the error popup the record jumps to next one but the error always pops up.

                      Thanks for your help. I really appreciate your efforts.

                      Comment

                      • MMcCarthy
                        Recognized Expert MVP
                        • Aug 2006
                        • 14387

                        #12
                        Originally posted by amanjsingh
                        yes it says, 1 of 3 filtered. Also, if I click OK in the error popup the record jumps to next one but the error always pops up.

                        Thanks for your help. I really appreciate your efforts.
                        Comment out the lines reseting the textboxes.
                        Code:
                        'Me.SelectIDNo = ""
                        and see if that makes any difference.

                        Mary

                        Comment

                        • amanjsingh
                          New Member
                          • Mar 2007
                          • 48

                          #13
                          Originally posted by mmccarthy
                          Comment out the lines reseting the textboxes.
                          Code:
                          'Me.SelectIDNo = ""
                          and see if that makes any difference.

                          Mary

                          Nothing. Still same error and behaviour. :(

                          Comment

                          • MMcCarthy
                            Recognized Expert MVP
                            • Aug 2006
                            • 14387

                            #14
                            Originally posted by amanjsingh
                            Nothing. Still same error and behaviour. :(
                            Is there any code in the forms events like the on current or on change event?

                            Comment

                            • amanjsingh
                              New Member
                              • Mar 2007
                              • 48

                              #15
                              Originally posted by mmccarthy
                              Is there any code in the forms events like the on current or on change event?
                              No. All I could see is BeforeUpdate and AfterUpdate events. And some print events for printing?

                              Code:
                              Private Sub SelectIDNo_AfterUpdate()
                                  Me.Filter = ""
                                  
                                  If Not IsNull(Me.SelectIDNo) Then
                                      Me.Filter = "[SSNo] LIKE '" + Forms![AO_ViewEditCandidateData]![SelectIDNo] + "*'"
                                      Me.FilterOn = True
                                  Else
                                      Me.FilterOn = False
                                  End If
                                  
                                  Me.SelectIDNo = ""
                              End Sub
                              
                              Private Sub SelectIDNo_BeforeUpdate(Cancel As Integer)
                              
                              End Sub
                              
                              Private Sub SelectLastName_AfterUpdate()
                              
                                  Me.Filter = ""
                                  
                                  If Not IsNull(Me.SelectLastName) Then
                                      Me.Filter = "[LastName] LIKE '" + Forms![AO_ViewEditCandidateData]![SelectLastName] + "*'"
                                      Me.FilterOn = True
                                  Else
                                      Me.FilterOn = False
                                  End If
                                      
                                  Me.SelectLastName = ""
                              End Sub

                              Comment

                              Working...