CODEWITHSUNDEEP
ms-accessvba
user969113
user969113

Reputation: 2429

Access - VBA - Get row number by criteria

Looking at the following code, how can I receive the row number for a user using the variable usern? I can check whether a user exists using the DCount function as can be seen. Once received the row number, I would like to use it to navigate to that entry using DoCmd.GoToRecord. GoToRecord itself works already. I just cannot find a way to receive the row number...

Private Sub Form_Current()

Dim usern As String
Dim count As Integer

usern = Environ("Username")

count = DCount("name_", "Fragebogen", "name_='" & usern & "'")

DoCmd.GoToRecord acDataForm, "Fragebogen", acGoTo, 3

End Sub

Upvotes: 0

Views: 1280

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

Have you tried the FindFirst method?

Locates the first record in a dynaset - or - snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record.

Dim rs As Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "name_ = '" & Environ("Username") & "'"

If Not rs.NoMatch Then
    Me.Bookmark = rs.Bookmark
Else
    MsgBox "No match was found.", vbExclamation
Emd If

Upvotes: 1

Related Questions