DLookup Multiple Records

[ddevil]

I'm trying to either enable for disable the save button on a form based on a security table.

SecCheck = DLookup("[AreaCode]", "Security_Table", "[Text57]=[AdministratorID]")
SecCheckResults = IIf([Area] = [SecCheck], True, False)
If (SecCheckResults = True) Then
SaveBtn.Enabled = True
Else
SaveBtn.Enabled = False
End If

So, the above example, I'm bringing back AreaCode based on if the Login ID(in Text57) of the user matches the AdminstratorID in the Security_Table. However, if they have multiple areas that they have security for(there ID is in the table more than once), I'm only getting the first row. Can I use DLookup then, or should I use something else?

Please help. Thanks!

 

[oscarse]

I just used DCount to do something similar ... try replacing dlookup with dcount and then if 0 no security is allowed. Set your SQL statement to return the number of allowed records that were found.

The only other way is to iterate through a recordset using DAO methods in VBA

 

[ddevil]

I guess I don't understand what you mean? I'm looking to make sure that the user can add a record for the specific area. So they pull up a record and the area is BET. If they don't have security for BET, then they can look at the record, but the save button is disables so they can't make changes. I don't get how DCount would help me with this.

Please help!

 

[ddevil]

Do you mean something like this?

Public Function SecurityCheck() As String

Dim ConSecCheckLogin As Variant
Dim ConSecCheckArea As Variant
Dim SecCount As Integer

ConSecCheckLogin = [Text57]
ConSecCheckArea = [Area]
SecCount = DCount("[Area]", "SecurityQuery", _
"[AreaCode] = '" & ConSecCheckArea & _
"' AND [AdministratorID] = '" & ConSecCheckLogin)


If (SecCount >= 1) Then
SaveBtn.Enabled = True
Else
SaveBtn.Enabled = False
End If
End Function

I get an error 3075, syntax error in string in query expression.

 

[PHV]

You may consider this:

Public Function SecurityCheck() As String
SaveBtn.Enabled = Not IsNull(DLookUp("Area", "SecurityQuery", _
  "AreaCode='" & [Area] & "' AND AdministratorID='" & [Text57] & "'"))
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ddevil]

That's the ticket. Thank you so much, I will be able to go to sleep tonight!!

 

[TheAceMan1]

How are ya ddevil . . .

If you include [blue]areacode[/blue] in the criteria you can nail it. Example:

[blue]   Dim Criteria As String
   
   Criteria = "([AdministratorID]= '" & Me!Text57 & "') AND " & _
              "([AreaCode] = '" & Me!Area & "')"
   
   If Not IsNull(DLookup("[AreaCode]", "Security_Table", Criteria)) Then
      Me!SaveBtn.Enabled = True
   Else
      Me!SaveBtn.Enabled = False
   End If[/blue]

In the code [blue]AdministratorID[/blue] and [blue]AreaCode[/blue] are assumed to be text.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884: