findfirst not working (1 Viewer)

[fearmichaele]

Hello fellow VBA users. i have a dilemma. I have a relational database that is SQL tables on the back end and Access 2016 on the front end. This is an equipment database that I have been tasked with updating functionality. This problem was not a problem until yesterday. i have a form that allows me to look at a location in my facility. i am using a drop down box to choose the location i want to see. the actual numeric primary key is brought down behind the description the user sees. after a new location is chosen an Afterupdate VBA is used to look up the new location and display it on the screen. This code has been working for several weeks now until yesterday. Now when i change the desired location in the drop down (search) box, the code keeps firing on location not found routine. I have not changed this code with the exception of adding error handling routine and it is not throwing an error. Is there something i am not seeing. my code is below. any input is greatly appreciated.

Private Sub cboSearchByOffice_AfterUpdate()
 On Error GoTo Errhand1
         
    Dim msg, title, response
    Dim criteria As String
    criteria = "[LocationPK] =" & Me.cboSearchByOffice.Value
    MsgBox (criteria)
msg = "Location not found"
title = "ATTENTION!!!!!"
With Me.RecordsetClone
    .MoveFirst
    .FindFirst criteria
       
   If .NoMatch Then
      Beep
      response = MsgBox(msg, vbOKOnly, title)
   Else
      Me.Bookmark = .Bookmark
   End If
End With
GoTo Finish

Errhand1:
MsgBox Err.Number & " " & Err.Description
Resume Next

Finish:
End Sub

I have used this in other databases without any issues. what am i missing.

1 the user opens the form.
2 the user chooses the location from a dropdown box (the drop down data is pulled from a query built in the form with LocationPK as the primary key)

cboSearchByOffice is the unbound text box the information is displayed.
3 the chosen location is supposed to display information about that location on the form.

 

[theDBguy]

Hi. The only way to find out what the problem is, is to step through the code during execution. Did you change the table structure? You might want to double check the data in the table.

 

[fearmichaele]

thanks theDBguy. I have not changed any structure of the table. I have stepped through the process. I even added a msgbox telling me what the LocationPK is and after .findfirst locationPK was not changed to the cboSearchByOffice value. LocationPK remains at the last location looked at. so the location not found always fires.

 

[fearmichaele]

Also, this form is accessable via multiple venues.

1. I have an employee form that I can search up employees and then open the location form at the location where the employee is assigned. this works good.
2. I also have another form where the user can look up an item tag number and then open the location form to see the location of the tagged item.
3. And, then where the problem lies is the Location form itself which can be accessed from the main menu.

 

[theDBguy]

thanks theDBguy. I have not changed any structure of the table. I have stepped through the process. I even added a msgbox telling me what the LocationPK is and after .findfirst locationPK was not changed to the cboSearchByOffice value. LocationPK remains at the last location looked at. so the location not found always fires.

So, when you stepped through the code, did you verify the correct data was being used in the code?

 

[fearmichaele]

during the stepthrough the criteria = "[LocationPK] =" & Me.cboSearchByOffice.Value showed the correct information to search for.

when it got to the .findfirst criteria line it automatically went to the
.nomatch line.

i placed a msgbox ([locationpk] & " " & cboSearchByOffice) in the .nomatch routine and the locationPK and cbosearchbyoffice did not match.

 

[theDBguy]

during the stepthrough the criteria = "[LocationPK] =" & Me.cboSearchByOffice.Value showed the correct information to search for.

when it got to the .findfirst criteria line it automatically went to the
.nomatch line.

i placed a msgbox ([locationpk] & " " & cboSearchByOffice) in the .nomatch routine and the locationPK and cbosearchbyoffice did not match.

Okay, just as a quick test, try creating a new blank form, bind it to the same table as the original form, and then add a new combobox using the Wizard but select the third option. See if this new form works or not.

 

[MajP]

99.9% of the time when people say the code stops working without any changes, they are normally wrong. Either they did change the code or the table data is presenting new conditions.

However this is the case where I have actually seen code stop working. Normally a compact and repair will fix it. If not you may need to do what DBguy said and rebuild.

 

[Micron]

this is not how you declare multiple variables on one line
Dim msg, title, response
unless you want all variable not explicitly typed to be variants. If not, then
Dim msg As String, title As String, response As integer

Should there not be a defined recordset object, set to Me.Recordset clone rather than just trying to findFirst on RecordsetClone? I would expect you cannot do it directly.

 

[fearmichaele]

i figured out the issue. so the query builder used for this form was borrowed from the Employee form. thanks to MajP and theDBguy. I looked at the data in the query and it was sorted ascending by employee name. i change it to sort ascending by locationPK. now the form is working again. i have tested it via all three venues of access to it and it is working. Thank for me the ideal to look at the data structure again.

 

[theDBguy]

i figured out the issue. so the query builder used for this form was borrowed from the Employee form. thanks to MajP and theDBguy. I looked at the data in the query and it was sorted ascending by employee name. i change it to sort ascending by locationPK. now the form is working again. i have tested it via all three venues of access to it and it is working. Thank for me the ideal to look at the data structure again.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.