Incorrect Data from an old query is getting populated in my search form

[clukem]

Hello

I'm very new to access. I have an issue, which is getting very irritating for me.

I have a search form, which provides basic details of a SIM card upon entering the SIM serial number (primary key). When the serial number is entered and search button pressed, it should provide the remaining details of the SIM card (pre_search_button). However, when this search button is pressed, it swaps the serial number for a different serial number and provides the data for that (after_search_button). I can't see why this is happening. This rogue serial number isn't stored anywhere other than the database along with the rest. The code for the search button is as follows

Public Sub Search_Click()
Dim rs As New ADODB.Recordset
Dim conn As New ADODB.Connection
Dim strsql As String
strsql = "Select * From SIMCards Where SerialNumber = """ & SerialNumber.Value & """ OR IMSI = """ & IMSI.Value & """ OR MSISDN = """ & MSISDN.Value & """"
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\chris\Desktop\SIMDB_2024-09-05.accdb;persist security info = false"
conn.Open
rs.Open strsql, conn
If rs.EOF Then
MsgBox "Record Not Found or SIM S/N is Empty"
SerialNumber.Value = Nothing
IMSI.Value = Nothing
MSISDN.Value = Nothing
CurrentLocation.Value = Nothing
StorageLocation.Value = Nothing
Else
SerialNumber.Value = rs.Fields("SerialNumber")
IMSI.Value = rs.Fields("IMSI")
MSISDN.Value = rs.Fields("MSISDN")
CurrentLocation.Value = rs.Fields("CurrentLocation")
StorageLocation.Value = rs.Fields("StorageLocation")
End If
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing
SerialNumber.Enabled = True
MSISDN.Enabled = True
IMSI.Enabled = True
End Sub

Has anyone any ideas why this is happening?


Regards


Chris

[Welshgasman]

Walk your code with F8 and breakpoints.
I would always use Me.whatever to ensure I am using the control values.

Regardless, inspect what you have as you go through each line of the code, and see what you actually have, as opposed to what you *think* you have.
If all those criteria are numeric, you do not need all those quotes.

[Bulzie]

Not much to add other then agreeing with Welsh, set a breakpoint on the strsql select and check the value of serialnumber there. For testing you can add a line of x = me.serialnumber just below the Dim line and put breakpoint on that to check that value. Step thru and see where that value changes or if it starts with the wrong value. Do you have code in the events of that SIM SN field like maybe a requery or something in the AfterUpdate or LostFocus?

[Welshgasman]

Also please post the code indented, which will help us and you a lot more.
You posted withing code tags, and that was good, but unless it is indented, which the code tags will keep indentation, then there is no point.

Public Sub Search_Click()
    Dim rs As New ADODB.Recordset
    Dim conn As New ADODB.Connection
    Dim strsql As String
    strsql = "Select * From SIMCards Where SerialNumber = """ & SerialNumber.Value & """ OR IMSI = """ & IMSI.Value & """ OR MSISDN = """ & MSISDN.Value & """"
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\chris\Desktop\SIMDB_2024-09-05.accdb;persist security info = false"
    conn.Open
    rs.Open strsql, conn
    If rs.EOF Then
        MsgBox "Record Not Found or SIM S/N is Empty"
        SerialNumber.Value = Nothing
        IMSI.Value = Nothing
        MSISDN.Value = Nothing
        CurrentLocation.Value = Nothing
        StorageLocation.Value = Nothing
    Else
        SerialNumber.Value = rs.Fields("SerialNumber")
        IMSI.Value = rs.Fields("IMSI")
        MSISDN.Value = rs.Fields("MSISDN")
        CurrentLocation.Value = rs.Fields("CurrentLocation")
        StorageLocation.Value = rs.Fields("StorageLocation")
    End If
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
    SerialNumber.Enabled = True
    MSISDN.Enabled = True
    IMSI.Enabled = True
End Sub