CODEWITHSUNDEEP
vbams-access
expenguin
expenguin

Reputation: 1124

Access - Find string within a record and then goto that record

I've got an Access application that uses a UID for each record, however it does not match up to the record order in SQL. (i.e. my UID of 12845 corresponds to record number 12834 in Access)

I have a search box that I've created that is supposed to search the Access DB and pull up the record that it finds the matching UID, however, the way I've written the code is that it's going to the Record number that matches the UID (so it will goto record number 12845 instead of record 12834 using UID 12845).

I've been sitting on this for a few days and I can't find a way around it. Searching the internet has not proved helpful. IF anyone has an idea for how one can match a string and goto THAT record vs trying to parse the record info myself, then that would be great.

The following is an example of the code I am using. It takes a date string and looks for the string in the records, gets the UID, and then tries to goto the corresponding record:

Private Sub FindBarCodeDate_Click()
Dim Barcode As String
Dim EndDate As String

If IsNull(BarcodeSearch.Value) Then
    If IsNull(DateSearch.Value) Then
        GoTo Done
    Else
        EndDate = DateSearch.Value
    End If
Else
    If IsNull(DateSearch.Value) Then
        Barcode = BarcodeSearch.Value
    Else
        Barcode = BarcodeSearch.Value
        EndDate = DateSearch.Value
    End If
End If

Dim rs As New ADODB.Recordset
Dim strSql As String
Dim TSD As String

If Barcode <> "" Then
    If EndDate <> "" Then
        strSql = "SELECT [TSD ID] FROM dbo_barAdultCollectionData WHERE Barcode = '" & Barcode & "' AND [End Date] = '" & EndDate & "'"
        On Error GoTo Done
        rs.Open strSql, CurrentProject.Connection
        TSD = rs.Fields.Item(0)
        rs.Close
        DoCmd.FindRecord TSD, acEntire, False, acSearchAll, False, acAll, True
        Set rs = Nothing
    Else
        strSql = "SELECT [TSD ID] FROM dbo_barAdultCollectionData WHERE Barcode = '" & Barcode & "'"
        On Error GoTo Done
        rs.Open strSql, CurrentProject.Connection
        TSD = rs.Fields.Item(0)
        rs.Close
        DoCmd.FindRecord FindWhat:=TSD, Match:=acEntire, MatchCase:=False, Search:=acSearchAll, SearchAsFormatted:=False, OnlyCurrentField:=acAll, FindFirst:=True
        Set rs = Nothing
    End If
ElseIf Barcode = "" Then
    If EndDate <> "" Then
        strSql = "SELECT [TSD ID] FROM dbo_barAdultCollectionData WHERE [End Date] = '" & EndDate & "'"
        On Error GoTo Done
        rs.Open strSql, CurrentProject.Connection
        TSD = rs.Fields.Item(0)
        rs.Close
        DoCmd.FindRecord FindWhat:=TSD, Match:=acEntire, MatchCase:=False, Search:=acSearchAll, SearchAsFormatted:=False, OnlyCurrentField:=acAll, FindFirst:=True
        Set rs = Nothing
    End If
Else
Done:
    SearchError.Caption = "Invalid Search Term!"
End If
End Sub

Thanks!

Upvotes: 0

Views: 3088

Answers (2)

Gustav
Gustav

Reputation: 56016

First, try adding -11 to the UID:

TSD = CStr(Val(rs.Fields.Item(0).Value) - 11)

Also, you need to format your date values as string expressions:

EndDate = Format(DateSearch.Value, "yyyy\/mm\/dd")

and then:

strSql = "SELECT [TSD ID] FROM dbo_barAdultCollectionData WHERE [End Date] = #" & EndDate & "#"

Upvotes: 1

Andre
Andre

Reputation: 27644

Don't use DoCmd.FindRecord. Use the technique shown in the second example of Form.RecordsetClone, e.g.

Dim rst As DAO.Recordset 

Set rst = Me.RecordsetClone
rst.FindFirst "yourUIDcolumn = '" & TSD & "'"
If rst.NoMatch Then 
    ' This should not happen
    MsgBox "Record not found", vbCritical, "Panic!"
Else 
    ' Set current record in form to found record
    Me.Bookmark = rst.Bookmark 
End If 
rst.Close

Upvotes: 1

Related Questions