I have a form that is attached to a linked table, After the user finishes filling in this form. I would like to copy information from 2 fields into a local table.
Linked table name: 9-StationIDT the 2 fields attached to this for copy are Label Name: PStationName and Table fieldname: StationName and Label name: StationID and Table field name: SID  The local table name is: StationID and the corresponding field names are: LSID and LStationName  The code i wrote seems to have some issues that i can not quite sort out and hoping someone could assist me?

Details

Public Function fnCopyData()
Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strLinkedValue As String
    
    ' Get the value from the linked table field
    strLinkedValue = Me.PStationName.Value

    ' Open a recordset for the local table
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * FROM LStationID WHERE [SCode] = " & Me.PStationName.Value)

    ' If the record exists, update the local table field
    If Not rs.EOF Then
        rs.Edit
        rs!LSID = strLinkedValue ' Copy data from linked field to local field
        rs.Update
    End If

    ' Clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    MsgBox "Data copied successfully!"
End Sub
End Function
Copy