none
Search form not displaying correct company RRS feed

  • Question

  • I have a search form that automatically populates a combo box with file numbers. In the past there could only be one file number in the list and the following code works perfectly under those conditions. User selects the correct file number and the company name and demographics prepopulate form. However, due to a reg change a file number can be associated with different companies. The multiple file numbers are displayed correctly in the combo box but when I select such a file number (associated with multiple companies) only the data of the initial company (displayed first in the list) displays and not the company I selected.

    Function SearchOK_Click()
      Dim rsttemp As Recordset
      Dim db As Database
      Dim RecNo As Integer
      Dim RcdFnd As Boolean
      Dim SQLstr As String
      Dim PForm As String
      
      PForm = GetParentForm()
      Forms(PForm).MineName.Enabled = True
      DoCmd.GoToControl "MineName"
      Forms(PForm)!SearchForm.Visible = False
      Set db = CurrentDb
      Select Case PForm
        Case "MIR"
          SQLstr = "SELECT StatEfilerNumber FROM [Mine Insp Data]"
        Case "Exploration"
          SQLstr = "SELECT StatEfilerNumber FROM [Exploration Data]"
      End Select
      Set rsttemp = db.OpenRecordset(SQLstr, dbOpenDynaset, dbReadOnly)
      RecNo = 1
      rsttemp.MoveFirst
      Do While Not rsttemp.EOF
      If rsttemp.Fields("StatEfilerNumber").Value = Forms(PForm)!SearchForm!SearchPermitNumber.Value Then
          Exit Do
        Else
          RecNo = RecNo + 1
          rsttemp.MoveNext
        End If
      Loop
      rsttemp.Close
    '  Forms(PForm).Requery
      DoCmd.GoToRecord acDataForm, PForm, acGoTo, RecNo
    End Function


    jim neal

    Tuesday, September 26, 2017 10:28 AM

Answers

  • The solution was right in front of me..... Instead of pulling the File Number I changed it to a unique ID for each record and it pulled fine.


    jim neal

    • Marked as answer by JamesLNeal Tuesday, September 26, 2017 1:07 PM
    Tuesday, September 26, 2017 1:07 PM