problem with Dlookup (1 Viewer)

Locopete99

Registered User.
Local time
Today, 08:25
Joined
Jul 11, 2016
Messages
163
Hi Guys,

I have a database with multiple main menus.

this is across 3 different departments,and depending on your job depends on the main menu you can access.

I'm working on a log in screen that will read your username and will open the main menu for your access level.

I have a table called "users" which has 3 fields, ID, Username and Department.


The department holds the ID for the department the user is assigned to.

My Dlookup to find this departmet ID is giving me run time error 2471.

It says:


"The expression you entered as a query parameter produced this error:

and then gives me the username from fOSUserName.



Code:
Private Sub Label0_Click()
Dim User As String
Dim department1 As String


User = fOSUserName

department1 = DLookup("[Department]", "Tbl_Users", "[username] = " & User)


End Sub
 

sneuberg

AWF VIP
Local time
Today, 08:25
Joined
Oct 17, 2014
Messages
3,506
If username is a text field then the variable User needs to be enclosed in single quotes like
Code:
department1 = DLookup("[Department]", "Tbl_Users", "[username] = [COLOR="Red"]"[/COLOR]" & User [COLOR="red"]& "'"[/COLOR])
 

Locopete99

Registered User.
Local time
Today, 08:25
Joined
Jul 11, 2016
Messages
163
Thanks Steve,

moving on I have now added a second part to this.

I've changed the data type of department1 to Long as it is returning a number and I want to match this in the second dlookup to return the form name.

I did some reading and saw that if you want to match two numbers, you just need to enclose the whole thing within two "

This is not working again.

The new dlookup is giving me the same error and this time is returning "department1" in the error box.

I dont seem to be understanding the syntax for this no matter what i research. Could you point out where I've gone wrong and if you know where I can find the hard and fast rules on the syntax it'd be great.

I've never taken any course to do this, It's only what i've taught myself.

Code:
Private Sub Label0_Click()
Dim User As String
Dim department1 As Long

Dim form1 As String
Dim stdocname As String



User = fOSUserName

department1 = Val(DLookup("[Department]", "Tbl_Users", "[username] = '" & User & "'"))



form1 = DLookup("[Department form]", "tbl_deptform", "[ID] =  department1")

stdocname = form1

DoCmd.OpenForm stdocname, acNormal



End Sub
 

Locopete99

Registered User.
Local time
Today, 08:25
Joined
Jul 11, 2016
Messages
163
sorry forgot to mention tbl_deptform has two fields. the first is the ID which is a number and the second is "department form" which is a txt field that houses the form name.

my end goal is to find the ID from the user and then from the ID find the form name to open for that department.
 

sneuberg

AWF VIP
Local time
Today, 08:25
Joined
Oct 17, 2014
Messages
3,506
Getting the criteria right on these can be tricky. I suggest reading this sometime.

You might be able to fix your problem by changing this


Code:
form1 = DLookup("[Department form]", "tbl_deptform", "[ID] =  department1")

to

Code:
form1 = DLookup("[Department form]", "tbl_deptform", "[ID] = " &  department1)

but I hope you consider do this more elegantly by first create a query that joins the two table which seemed to be related by the department. Just create a select query using the Query Designer, join the tables on deparment1 and ID and include the fields [Department form] and [username] in the result. Let's say you name this query "qryUserForm". With that query you can get the form name with one DLookup something like.


Code:
form1 = DLookup("[Department form]", "qryUserForm", "[username] = '" & fOSUserName & "'")

Note that I put fOSUserName directly in here rather the User. Why have this extra string variable?
 

sneuberg

AWF VIP
Local time
Today, 08:25
Joined
Oct 17, 2014
Messages
3,506
I think you should consider some error checking in this. If you change form1 to a Variant so that it can be null then you could you something like:

Code:
Dim form1 as Variant
form1 = DLookup("[Department form]", "qryUserForm", "[username] = '" & fOSUserName & "'")
if IsNull(form1) then
   Msgbox "Error:  User not found"
   exit Sub
End If

'  open the form
 

Locopete99

Registered User.
Local time
Today, 08:25
Joined
Jul 11, 2016
Messages
163
Thanks Steve,

Thats helped and I've made a note of that address to read.

You've helped me out and pointed out error checking that I hadn't even thought about.

Many Thanks
 

Users who are viewing this thread

Top Bottom