Database Access List

[2wistd]

So I am using the following code:

Declare Function WNetGetUser Lib "mpr.dll" _
  Alias "WNetGetUserA" (ByVal lpName As String, _
  ByVal lpUserName As String, lpnLength As Long) As Long
  
Const NoError = 0           'The Function call was successful
   
Function GetUserName() As String
   Dim LUserName As String
   Const lpnLength As Integer = 255
   Dim status As Integer
   Dim lpName
   
   ' Assign the buffer size constant to lpUserName.
   LUserName = Space$(lpnLength + 1)
   
   ' Get the log-on name of the person using product.
   status = WNetGetUser(lpName, LUserName, lpnLength)
   
   ' See whether error occurred.
   If status = NoError Then
      ' This line removes the null character. Strings in C are null-
      ' terminated. Strings in Visual Basic are not null-terminated.
      ' The null character must be removed from the C strings to be used
      ' cleanly in Visual Basic.
      LUserName = Left$(LUserName, InStr(LUserName, Chr(0)) - 1)
      
   Else
      ' An error occurred.
      MsgBox "Unable to get the name."
      End
   End If
   
   GetUserName = LUserName
   
End Function

and then call it within a like this:

Public Function autoprotectmain()
If GetUserName() <> "#########" Then
MsgBox "You are not authorized in this section"
Else
DoCmd.OpenForm "maintable", acNormal, , , acFormEdit, acWindowNormal
End If
End Function

(i replaced the number with ########). My question is how can I create a table listed with numbers as an access list? Right now only the ######## has access but I'd like to create a table for numbers who will have access.

 

[pbaldy]

One way would be a DCount():

If DCount("*", "TableName", "FieldName = '" & GetUserName() & "'") = 0 Then

 

[2wistd]

Okay, I think I asked that wrong.

I have created a table with user id numbers in it. I want those people to have access to the form. Right now in the code if I replace the ######## with a user Id, that user id would have access to the form. How can I use that table of user ids, and give them access using this code? So the ######### should be replaced with the table user ids. It isn't as simple as just replacing ##########, with the table name.
if the DCount() would work, I don't really understand that solution.

Thanks!

 

[pbaldy]

The DCount() is saying "How many records in this table have a value in the user field equal to the user from the function". The code given would equate to yours, in that if there are none (a count of 0), they get the message box. If I'm logged in and I'm in that table, the count would be 1 so it would open the form.

 

[2wistd]

ahhhh, the light bulb turned on. Thanks for the explanation. Off to see if I can implement it!

 

[pbaldy]

No problemo!

 

[Galaxiom]

BTW The GetUserName function can be replaced with a single expression.

CreateObject("wscript.network").UserName