Looking up a record in a subform and matching a field in main form for verification (1 Viewer)

[jjake]

Hello,


i have 3 tables

(tblUser)
(tblDepartment)
(tblfrm)

On my main form (frm1) is a combo box (cbo1) and a text box (txt1).

the txt1 is hidden and displayers the current users computer login.

Private Sub Form_Current()
Me.txt1 = Environ("USERNAME")
End Sub

I'm trying to achieve a verification process so if a users name in txt1 is in the same department cbo1 is registered too, it will allow that user to alter a second text box (txt2)

My user table is the following

UserID | UserName | UserCode | Department |
1 | Jake F | FosJak | 1 |



The user code is manually input when a user is created to give a value to compare against for the txt1.

This is an example statement obviously not in the correct coding.

on change of cbo1 lookup txt1 (FosJak) and compare to user table, if the user that has matching UserCode (FosJak) has department value of 1 then allow txt2 to be enabled.

Hopefully this makes sense?

 

[bastanu]

Try this in the AfterUpdate event of the combo box (the OnChange fires with every key press):

If me.column(3)=dlookup("[Department]","[tblUser]","[UserCode]='" & me.txt1 & "'") then
Me.txt2.Locked=False
Else
Me.txt2.Locked=True
End if

This assumes that your cbo1 combobox is based on the full tblUser table (with the first column width set to 0 to hide it).

Cheers,
Vlad

 

[jjake]

Thanks for the reply bastanu. My cbo1 is actually a yes/no combo box.

If the user tries to change the combo box to a yes/no answer, txt2 will only be enabled if txt1 matches cbo1 code asking for department = 1.


I was thinking maybe this had to be achieved with a continuous subform displaying the user records?

 

[bastanu]

So you have code behind the combo asking for a department, can you show it?

 

[jjake]

Or maybe another way to do this if it's easier would be,

If current usercode in txt1 has department = 1 then cbo1 is enabled. if not cbo1 is disabled by default.

 

[jjake]

So you have code behind the combo asking for a department, can you show it?

There is no current code asking for the department. This what i am trying to achieve by verifying that the current user in txt1 has the correct department before they can change cbo1 and/or enable txt2

 

[bastanu]

That is very easy, you add that to the load event of the form:

If dlookup("[Department]","[tblUser]","[UserCode]='" & me.txt1 & "'") =1 then
Me.cbo1.Locked=False
Else
Me.cbo1.Locked=True
End if

 

[bastanu]

Or you could do both:
If dlookup("[Department]","[tblUser]","[UserCode]='" & me.txt1 & "'") =1 then
Me.cbo1.Locked=False
Me.txt2.Locked=False
Else
Me.cbo1.Locked=True
Me.txt2.Locked=True
End if

 

[jjake]

This didn't work bastanu. The field is still locked. I double checked the names and Id's.

 

[bastanu]

Can you show your full code? Have you tried to step though the code and see what values you get?

 

[bastanu]

Is Department a numeric or text field in tblUser?

 

[jjake]

Is Department a numeric or text field in tblUser?

It is a numeric field.

 

[jjake]

Can you show your full code? Have you tried to step though the code and see what values you get?

The only code i have on the Onload even is the code you provided me. By stepping through i assume you mean hovering my mouse over the fields in VBA? i don't get any values.

 

[bastanu]

Is the form saved with the controls "locked"? Are they both "Enabled" (two separate properties)? Can you try to unlock them, save the form and let the code lock them on demand? Are you sure your Windows login matches UserCode in tblUser? Maybe unhide txt1 for testing so you can easily see what you get in there.

Cheers,
Vlad

 

[bastanu]

Sorry, I just went back and looked at your initial post. You set the txt1 in OnCurrent which fires after OnLoad; move that to the OnOpen event of the form and it should work.

 

[jjake]

Sorry, I just went back and looked at your initial post. You set the txt1 in OnCurrent which fires after OnLoad; move that to the OnOpen event of the form and it should work.

That fixed it. Excellent, thank you!

 

[bastanu]

Glad to help! Good luck!