DLookUp Active User, Only Active User Can Edit

[rawlstonb]

I've seen some threads on several forums concerning this, but they either have completely unapplicable Login Forms or just said they fixed it without defining in what way they ended up fixing it.

Seems pretty simple...

1st ISSUE:

My login form is as simple as I could find a decent example of...



Login: cboUser FROM tblUser... 5 Columns: [UserID], [Last] & [First], [UserPassword], [UserSecurity], [PWReset]
Password: txtPassword


Cancel: Exits Database
Submit: Runs the following code...

Option Compare DatabaseOption Explicit


Private Sub cboUser_AfterUpdate()


End Sub


Private Sub cmdSubmit_Click()
If IsNull(Me.cboUser) Then
    MsgBox "Please select a user", vbCritical
    Me.cboUser.SetFocus
Else
    If Me.txtPassword = Me.cboUser.Column(2) Then
        If Me.cboUser.Column(4) = True Then
            DoCmd.OpenForm "frmPWReset", , , "[UserID] = " & Me.cboUser
        End If
        DoCmd.OpenForm "frmNav"
        Me.Visible = False
    Else
        MsgBox "Password does not match", vbOKOnly
        Me.txtPassword = Null
        Me.txtPassword.SetFocus
    End If
End If
End Sub

On frmRouterEntry...



I have a non-editable [txtAuthor] that I'd like to auto-populate with the [UserLogin] FROM tblUser with criteria drawn from the theoretically still open but not visible frmLogin

So I set the Default Value of that field to:

DLookUp("[UserLogin]","tblUser","'me.[txtAuthor].value= & [Forms]![frmLogin]![cboUser]'")

...which is drawing a value, but it's doing the DLookUp-first-value thing and not giving the actual active user... which is pretty important for the concept.


2nd ISSUE:

So after I fix that, on to the frmRouterSearch (that's still under kind of under construction) due to this issue...



The search button existing will be used to make a printable report.

I'll need a different command button that will search records for editing and bring up a separate but identical form to frmRouterEntry. Since the records will all be linked in this form, they should auto-populate without any issue, but here comes the login thing again... only the author should be able to edit these records.


Any help or guidance on these issues is greatly appreciated. I'd rather you just assume I'm a total noob and provide some context around your explanations... because if not it'll just send me on another waste a bunch of hours googling spree...

If anybody needs any more information or I wasn't clear on what I needed to accomplish, let me know... I need to have this thing done before Monday or I'm gonna get yelled at

[June7]

Domain aggregate must reference a table field in the WHERE CONDITION argument. Is the user the author? Why use DLookup? You already have user info in the combobox.

Code in form Open event to set textbox DefaultValue property:

Me.txtAuthor.DefaultValue = [Forms]![frmLogin]![cboUser]

If you don't want UserID but UserLogin (this is another field in the same table?), include the UserLogin field in the combobox RowSource. Reference that column by index. If the UserLogin field is 3rd column, its index is 2.

Me.txtAuthor.DefaultValue = [Forms]![frmLogin]![cboUser].Column(2)

[rawlstonb]

Domain aggregate must reference a table field in the WHERE CONDITION argument. Is the user the author? Why use DLookup? You already have user info in the combobox.

Code in form Open event to set textbox DefaultValue property:

Me.txtAuthor.DefaultValue = [Forms]![frmLogin]![cboUser]

If you don't want UserID but UserLogin (this is another field in the same table?), include the UserLogin field in the combobox RowSource. Reference that column by index. If the UserLogin field is 3rd column, its index is 2.

Me.txtAuthor.DefaultValue = [Forms]![frmLogin]![cboUser].Column(2)

I know, it's weird sounding... I certainly CAN include [UserLogin] into the combobox... and I certainly will if that does the trick... but the [UserLogin] is the company standard that I can tie back into the company SQL tables... I just wanted to display the names soas not to confuse people that didn't know their standard company UserLogin name... trying to keep everything as plain English as I can. But yes... the [UserLogin] is on tblUser, so I was hoping I could define by criteria which user it was through frmLogin and pull a different field for that. I was mainly posting the code to make sure I wasn't misreading anything and the frmLogin was in fact still open and available to take active user data from.

I tried calling [Last] with DLookUp("[Last]","tblUser","'me.[txtAuthor].value= & [Forms]![frmLogin]![cboUser].column(2)'") and it still returned the first user... so that's what got me discouraged with the whole thing, so maybe you're right about DLookUp... I'll try that in the morning for sure.

Any input on that 2nd future soon to be current issue?

[June7]

The names will still be displayed in the combobox but the UserLogin field would be another column (hidden if you wish) in the combobox RowSource. This will make the value available without having to query the table again with a DLookup.

Don't really understand 2nd future soon-to-be-current issue. Perhaps you need to apply filter criteria so only records associated with the user are retrieved?

[rawlstonb]

Combobox thing worked great... thanks...

The filter thing might work... open to any other ideas if there are any

[davegri]

Just FYI
DLookUp("[Last]","tblUser","'me.[txtAuthor].value= & [Forms]![frmLogin]![cboUser].column(2)'") and it still returned the first user..

The third argument (in red) should be a fieldname in the domain (aqua). The me.prefix indicates it's instead a controlname on the form.

[June7]

Applying filter to form is only way I know to limit user interaction to just relevant records. However, if user has access to ribbon and shortcut menu, they can likely override and view any records they want.

[rawlstonb]

Applying filter to form is only way I know to limit user interaction to just relevant records. However, if user has access to ribbon and shortcut menu, they can likely override and view any records they want.

cboUser context:
0 = [UserID]
1 = [Last] & ", " & [First] AS Fullname (name for login display)
2 = [UserPassword] (password)
3 = [UserSecurity] (access level from tblSecurity [SecurityID],[SecurityLvl]... 1=Admin, 2=User, 3=Read-Only)
4 = [PWReset] (checkbox for if they have set a password from the standard one)
5 = [UserLogin] (name for form display)

I was imagining some kind of VBA on the form that draws from frmLogin like...

IF (the locked from edit) me.txtAuthor.value=Forms!frmLogin!cboUser.column(5 ) Allow Editing ELSE Read-Only

...instead of a filter, but I don't know VBA syntax enough to write the actual code or if that's even feasible. Seems like that would be simple but I'm sure it's not that easy haha... Also, I would prefer a summary box of the search results if >1 or something... to select the correct record for the form instead of manually scrolling through the bottom record selection for user ease... but that's low-hanging fruit for later unless somebody has some ready to roll code with an example on hand for that

If I do go with a filter, I'll need something in the OnOpen event of the form to restrict access or Read-Only for Forms!frmLogin!cboUser.column(3)=3 and allow access if it = 1 or 2.

I've got something I can probably make work from a different database I did a long time ago, but it's written for 32-bit and we use 64-bit systems now so it doesn't work anymore...

Anyways... open for any ideas if ya have em as always! Have a good Thanksgiving guys and thanks for any time you can spare to help a brother out!

[June7]

Suppose code could lock/unlock form (set the AllowEdits property) as each record gets focus, but why even present records to the user they aren't supposed to work with?

I would use WHERE CONDITION argument of OpenForm method to filter dataset on form open.

I migrated a db created on 32-bit Windows to 64-bit with little difficulty. Had to make just a couple code edits.

[rawlstonb]

It would be a specific author editing their own record for that function... there's no reason I can think of not to filter it for the logged in user... also, the author would have the BOM in hand if they were editing a record, so they should have the material# on hand, which is the most specific limiter on any of the fields... that would only conflict to multiple records down the line if the part gets reordered and the router gets updated and then needs editing after the initial submit

So really to edit should only need a prompt for material #, filter to user, some method of selection of which record if there are multiples of the same material #

just thinking out loud