access use FindFirst command with an array

Question

I'm trying to use the FindFirst command in vba for access with an Array, the Array contains usernames, and I want to search through a table and find the ID of each of the usernames in the array. but I keep getting "Compile Error: Type Mismatch" whenever I press the search button on the access form.

Any ideas? - Also does it look like i am passing the array correctly to the next private sub?

The bit that i'm trying to search with the array starts when I create the username() array.

Private Sub createrel_Click()
'declare variables
    Dim stDocName As String, db As Database, RS As Recordset, FindPraNumber As String
    Dim vary As Variant
    Dim Msg As String
    Dim Response As Integer
    Dim username() As String
    Dim varx() As Variant

    If IsNull(Me![userlist]) Then
      Msg = "Please choose a pra number from the list"
      MsgBox Msg, vbCritical, MsgText
      DoCmd.GoToControl "userlist"
      Exit Sub
    End If

    If IsNull(Me![folderlist]) Then
      Msg = "Please choose a folder from the list"
      MsgBox Msg, vbCritical, MsgText
      DoCmd.GoToControl "folderlist"
      Exit Sub
    End If

    username() = Split(Me.userlist, ",")
    MsgBox Join(username())
    Set db = DBEngine(0)(0)

    Set RS = db.OpenRecordset("tblPra", DB_OPEN_DYNASET)
      RS.FindFirst "[praNo] = """ & username() & """"
      varx() = DLookup("praID", "tblPra", "[praNo] = 'username()'")

    Set RS = db.OpenRecordset("tblFolder", DB_OPEN_DYNASET)
      RS.FindFirst "[folder] = """ & Me.folderlist & """"
      vary = DLookup("folderID", "tblFolder", "[folder] = " & "forms!frmrelationship!folderlist")

    Response = MsgBox("You are about to create a relationship. Continue?", vbYesNo)
    If Response = vbNo Then
      Exit Sub
    Else
      cmdAddRecord varx(), vary
    End If
End Sub

Private Sub cmdAddRecord(x(), y)

   Dim stDocName As String, db As Database, RS As Recordset, FindPraNumber As String
   Dim exists As Boolean
   Dim total As Integer

   Set db = DBEngine(0)(0)
   Set RS = db.OpenRecordset("tblRelationship", DB_OPEN_DYNASET)

   exists = False
   If Not RS.EOF Then RS.MoveLast
   total = RS.RecordCount

   'check to see if relationship exists already
   RS.FindFirst "[praID] = " & x() & ""

   If RS.NoMatch Then

     exists = False

   Else

      If RS("folderID") = y Then
        exists = True

      Else

        For i = 1 To total Or exists = True
         RS.FindNext "[praID] = " & x & ""
         If RS.NoMatch Then
         Else
         If RS("folderID") = y Then exists = True
         End If
        Next i

      End If

   End If

   If exists = False Then
     RS.addNew
     RS("praID").Value = x
     RS("folderID").Value = y
     RS.Update
     Msg = "Relationship has now been created"
     MsgBox Msg, vbInformation, MsgText
   Else
     Msg = "Relationship already exists"
     MsgBox Msg, vbCritical, MsgText
   End If


End Sub

I think you'll need to join the array as join(username()," OR ") maybe, but why not just use a SQL Recordset, with these as the criteria? — Nathan_Sav
– Nathan_Sav, Commented May 16, 2016 at 16:22

Gustav · Accepted Answer · 2016-05-16 20:02:53Z

3

You can't do this:

varx() = DLookup("praID", "tblPra", "[praNo] = 'username()'")

You can't assign an array using DLookup, DLookup can't pull an array of IDs, username() should be username(n), and the concatenating of username is wrong. In fact, the only valid parts in this sentence are "tblPra" and "[praNo] = ".

So rethink your concept. There is no reason to complicate matters when straight recordsets or a query can do the job.

answered May 16, 2016 at 20:02

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andre Over a year ago

+one for "rethink your concept". @PaulMachin: you are doing this way too complicated.

Collectives™ on Stack Overflow

access use FindFirst command with an array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related