Dlookups just pulling first record

[thmsjlmnt3953]

Hi,

I have a dlookup which gets the empid on login - i know this works as i fire a msgbox with this, this is stored in a global variable.

i have the following code running on 'form_load' to set securities however its just using the first records and not the ones linked to the specified accont - i have a second messagebox fire just before these dlookups just so i know the global is correct

Private Sub form_load()
DontKick = 0
DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblKickUsers SET KickFlag = 0"
DoCmd.SetWarnings True
DoCmd.Maximize
SetFormIcon Me.hWnd, "w:\backshift database\hsicon.ico"
MsgBox EmpIDLogin
If DLookup("EnterRecords", "tblpermissions", "[empid]" = "'" & EmpIDLogin & "'") = -1 Then
cmdNavEnterRecords.Enabled = True
End If
If DLookup("EnterBackshift", "tblpermissions", "[empid]" = "'" & EmpIDLogin & "'") = -1 Then
cmdRecordsConveyor.Enabled = True
End If
If DLookup("EnterPetfood", "tblpermissions", "[empid]" = "'" & EmpIDLogin & "'") = -1 Then
cmdPetFood.Enabled = True
End If
If DLookup("EnterBCode", "tblpermissions", "[empid]" = "'" & EmpIDLogin & "'") = -1 Then
cmdRecordsBCode.Enabled = True
End If

End Sub[ATTACH]57128._xfImport[/ATTACH]

The EmpIDLogin is 1786 (my own) and i have pasted a copy of the table in question (well a portion)

its only using the first row of checks to provide permissions.

any help would be fantastic

 

[pbaldy]

I would use a recordset rather than several DLookups, but your syntax is off plus your value is numeric rather than text. Try

If DLookup("EnterRecords", "tblpermissions", "[empid] = " & EmpIDLogin) = -1 Then

 

[plog]

Debugging 101: check your values.

You know EmpIDLogin is correct because you verified it with your own eyes. Do the same for the other areas that are giving you problems. What does this code display?

msgbox(DLookup("EnterRecords", "tblpermissions", "[empid]" = "'" & EmpIDLogin & "'"))

 

[thmsjlmnt3953]

Hi,

massive thanks for the reply, i was using dlookups as the only way i know (or hoped i could) - would recordset be easier?

 

[thmsjlmnt3953]

plog - didnt realise i could do that but always a great tool to have in future, massive thanks to both of you!

 

[pbaldy]

A recordset would be 1 trip to the data rather than 4. The DLookup's will work, just less efficient. In truth, you probably wouldn't notice the difference, especially if the table is small. I just lean towards the more efficient methods.

 

[thmsjlmnt3953]

Hi

the table is quite vast - around 40 records and 14 users atm...

so 40 dlookups - i didnt think it would be very efficient.

 

[pbaldy]

Well, unless I misunderstand, it wouldn't be 40 DLookups. It is one per field value you're trying to look up.

 

[thmsjlmnt3953]

Apologies, 40 fields, each one related to a cmdbutton on my nav screen as the DB will potentially have 30/40 users holding business wide data, only some of that must be viewable by certain people

 

[pbaldy]

The I would use a recordset, if for no other reason than I'm lazy and don't want to create 40 DLookups. It would look something like:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM tblpermissions WHERE [empid] = " & EmpIDLogin
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rs.EOF Then
    Me.cmdNavEnterRecords.Enabled = rs!EnterRecords
    Me.cmdRecordsConveyor.Enabled = rs!EnterBackshift
    ...
  End If

  set rs = nothing
  set db = nothing

 

[thmsjlmnt3953]

Massive thanks for this will save me a large amount of time and as you say your only going to the data once

 

[pbaldy]

Happy to help!

 

[thmsjlmnt3953]

Hi,

I got all that filled in how it needed to be however when i log in now no-one has access to anything?

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT * FROM tblpermissions WHERE [empid] = " & EmpIDLogin
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  If Not rs.EOF Then
    Me.cmdNavEnterRecords.Enabled = rs!EnterRecords
    Me.cmdRecordsConveyor.Enabled = rs!EnterBackshift
    Me.cmdPetFood.Enabled = rs!EnterPetfood
    Me.cmdRecordsBCode.Enabled = rs!EnterBCode
    Me.cmdNavViewRecords.Enabled = rs!ViewRecords
    Me.cmdConveyorRecords.Enabled = rs!ViewBackshift
    Me.cmdPetfoodRecords.Enabled = rs!ViewPetfood
    Me.cmdbarcodingRecords.Enabled = rs!ViewBCode
    Me.cmdnavEnterErrors.Enabled = rs!EnterErrors
    Me.cmdEnterConveyor.Enabled = rs!EnterErrorsConveyor
    Me.cmdbarcodingRecords.Enabled = rs!EnterErrorsBCode
    Me.cmdEnterDespatch.Enabled = rs!EnterErrorsDespatch
    Me.cmdEnterPetfood.Enabled = rs!EnterErrorsPetfood
    Me.cmdEnterBCode.Enabled = rs!EnterErrorsBCode
    Me.cmdNavViewErrors.Enabled = rs!ViewErrors
    Me.cmdErrorsBackshift.Enabled = rs!ViewErrorsConveyor
    Me.cmdErrorsDespatch.Enabled = rs!ViewErrorsDespatch
    Me.cmdErrorsPetfood.Enabled = rs!ViewErrorsPetfood
    Me.cmdsearchBcodeError.Enabled = rs!ViewErrorsBCode
    Me.cmdNavAdmin.Enabled = rs!Admin
    Me.cmdConveyorStats.Enabled = rs!TSMStats
    Me.cmdConveyorStat.Enabled = rs!ConveyorStats
    Me.cmdBarcodingStats.Enabled = rs!BarcodingStats
    Me.cmdNavIssues.Enabled = rs!Issues
     
    
    
  End If

  Set rs = Nothing
  Set db = Nothing



End Sub

 

[pbaldy]

Add the bit in red to test if the recordset is returning the record correctly:

    ...
    Me.cmdNavIssues.Enabled = rs!Issues
     
[COLOR="Red"]  Else
    Msgbox "Invalid login"[/COLOR]
  End If

 

[thmsjlmnt3953]

That seems to be it working now, strange one but glad its working

 

[pbaldy]

That shouldn't have changed anything, but glad it's working.