UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Unbound Textbox On Userform, Access 2016    
 
   
pgreenway
post Mar 26 2020, 09:01 AM
Post#1



Posts: 11
Joined: 6-September 19



Hello,

I am unsure of how to perform this but I'm pretty sure it would use DLookup.

I have a table 'Employees' that has a Employee_Name and a Comp_Name fields.

On my UserForm 'Home' I have two unbound textboxes. The first one gathers the computer name. The second one is supposed to use that computer name to lookup the value from table 'Employees' to return the Employee_Name field.

But I'm not quite sure how to do this DLookup or if this is even a good way to go about it.

On my 'Home' form I want a Welcome _____ with the blank filled in by the user's name.

Our computers use a Common Access Card and the username pulled off of it is a series of numbers and when I pull either the Username (10-digit code) or the MachineName I don't get what I want. Therefore I created a table and thought a DLookup would serve me best but I can't implement it correctly.

Any help would be greatly appreciated.

Thanks!
Go to the top of the page
 
Larry Larsen
post Mar 26 2020, 09:08 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,526
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
welcome2UA.gif

It would be helpful if you could either show use what you table has with regards to the records it holding.. (Image would be good)

Seeing what's available will give us a better understanding how to compile that DLookUp(...) (Field names..)
thumbup.gif


--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
pgreenway
post Mar 26 2020, 09:31 AM
Post#3



Posts: 11
Joined: 6-September 19



Larry,

Here is essentially the same table as I have (same fields). I can't show my actual table due to personal information but I created this dummy table for example.




Had to do it as an attachment. Not sure why imgur isn't allowed or how to upload pics here directly.
This post has been edited by pgreenway: Mar 26 2020, 09:33 AM
Attached File(s)
Attached File  Untitled.png ( 9.17K )Number of downloads: 7
 
Go to the top of the page
 
projecttoday
post Mar 26 2020, 10:08 AM
Post#4


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


Show the Dlookup that doesn't work.

--------------------
Robert Crouser
Go to the top of the page
 
pgreenway
post Mar 26 2020, 10:19 AM
Post#5



Posts: 11
Joined: 6-September 19



I put this in the control source of text47 textbox. I have a dummy textbox Text49 that reads the computer name (this will be hidden in the final version of the form). I am getting the computer name pulled up correctly. Trying to cross-reference it to pull the Employee_Name in Employees table

=DLookUp("Employee_Name","Employees","Comp_Name = Text49.value")

DLookup


I haven't used DLookup before so I'm trying to figure it out but am falling short.

When I use the above, Text47 textbox is blank.
Attached File(s)
Attached File  Home_Form.png ( 52.52K )Number of downloads: 4
 
Go to the top of the page
 
Larry Larsen
post Mar 26 2020, 11:12 AM
Post#6


UA Editor + Utterly Certified
Posts: 24,526
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Try:
CODE
=DLookUp("Employee_Name","Employees","Comp_Name = '" & Me. Text49 & "'")

thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
pgreenway
post Mar 26 2020, 11:25 AM
Post#7



Posts: 11
Joined: 6-September 19



Thank you Larry. I understand how that last part works now, but I'm still getting this: #Name?

I think it might be due to how Text49 is determined. Currently, it has a default value of a function that gets the computer name.

Does this mean that Text47 needs an afterUpdate or OnLoad event?
This post has been edited by pgreenway: Mar 26 2020, 11:25 AM
Go to the top of the page
 
projecttoday
post Mar 26 2020, 12:24 PM
Post#8


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


Maybe the form's recordset. Try this:

=DLookUp("Employee_Name","Employees","Comp_Name = '" & Me.Comp_Name & "'")

--------------------
Robert Crouser
Go to the top of the page
 
pgreenway
post Mar 26 2020, 01:11 PM
Post#9



Posts: 11
Joined: 6-September 19



No luck to either. My database is too large to upload here, any suggestions?
Go to the top of the page
 
Larry Larsen
post Mar 26 2020, 01:27 PM
Post#10


UA Editor + Utterly Certified
Posts: 24,526
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

Don't need the whole db, just a small amount of data from your Employees table..
So we can finalize the complete DlookUp(...)

Table with a few records..
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
projecttoday
post Mar 26 2020, 01:51 PM
Post#11


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


Try the on current event of the form.

Me.Text47 = DLookUp("Employee_Name","Employees","Comp_Name = '" & Me.Comp_Name & "'")
or
Me.Text47 = DLookUp("Employee_Name","Employees","Comp_Name = '" & Me.Text49 & "'")

If that doesn't work put a stop on the statement and confirm the contents of Me.Comp_Name.


--------------------
Robert Crouser
Go to the top of the page
 
pgreenway
post Mar 26 2020, 02:02 PM
Post#12



Posts: 11
Joined: 6-September 19



Here is just the table saved in its own access database. If that's not what you were requesting, let me know and I'll get you something else.

Regards!
Attached File(s)
Attached File  Employee_Table_Only.zip ( 16.06K )Number of downloads: 2
 
Go to the top of the page
 
pgreenway
post Mar 26 2020, 02:13 PM
Post#13



Posts: 11
Joined: 6-September 19



projecttoday,

When I do either of those it says that the database cannot find the object 'Me.'

If 'Me" is a new macro or macro group, make sure you have saved it and that you have typed its name correctly
This post has been edited by pgreenway: Mar 26 2020, 02:13 PM
Go to the top of the page
 
projecttoday
post Mar 26 2020, 02:26 PM
Post#14


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


Then try it without "Me.".

You have to post a form. The table by itself isn't good enough. We need something that replicates the error.

--------------------
Robert Crouser
Go to the top of the page
 
Larry Larsen
post Mar 26 2020, 02:31 PM
Post#15


UA Editor + Utterly Certified
Posts: 24,526
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

Yes I created a small form and placed the DLookUp(..) in the OnCurrent event..

All works fine..
CODE
Private Sub Form_Current()
Me.txtPName = DLookup("Employee_Name", "Employees", "Comp_Name = '" & Me.txtCompName & "'")
End Sub


thumbup.gif
Attached File(s)
Attached File  Employee_Table_Only.zip ( 19.37K )Number of downloads: 2
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
pgreenway
post Mar 26 2020, 02:44 PM
Post#16



Posts: 11
Joined: 6-September 19



Ok, so here it is. I got rid of a picture to reduce file size. There's one module, which gathers the computer name for Text49 and there's form 'Home' where both Text47 and Text49 are both on.

The table for Employees is there as well.

I do appreciate all the assistance. I'm trying to learn this stuff, there's just a lot to it I still don't know.

This is not just the employees table only, I just didn't change the file name.
This post has been edited by pgreenway: Mar 26 2020, 02:44 PM
Attached File(s)
Attached File  Employee_Table_Only.zip ( 25.08K )Number of downloads: 4
 
Go to the top of the page
 
projecttoday
post Mar 26 2020, 04:09 PM
Post#17


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


This is a menu. It is unbound. So the user is supposed to type Comp_Name in text49 and the employee is supposed to appear in text47? You would use the after update event of text49. It must execute for it to do anything. Did you see the last line of my post #11 ?

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Mar 26 2020, 04:10 PM
Post#18


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


text49 and text47 are not good names to use.

--------------------
Robert Crouser
Go to the top of the page
 
pgreenway
post Mar 26 2020, 04:16 PM
Post#19



Posts: 11
Joined: 6-September 19



projecttoday,

the user types nothing, text 49 has a function that finds the machine name (it will be hidden in final form); I have a table I want to relate to that textbox and text 47 textbox and find a correlating field (Employee_Name) and automatically appear next to the 'Welcome'

the first part is working great, I cannot get the second part yet.

The second part needs to be when the form loads it looks at the table for the machine name found in text 49's textbox and finds the corresponding employee name that matches their machine then outputs the employee name in text 47 textbox.

I just want to be able to recognize who is actively using the dB when my users are in it.

If I haven't been clear, I apologize; I'm new to this but I'm trying to get my knowledge base built up in Access.
Go to the top of the page
 
projecttoday
post Mar 26 2020, 06:18 PM
Post#20


UtterAccess VIP
Posts: 11,793
Joined: 10-February 04
From: South Charleston, WV


Well, you definitely have some issues. Like I couldn't even get the form to open. I had to go into design view and then switch to form view. And there is nothing in Comp_Name in the table. So I typed "def" in for John Thomas.
So this returns "John Thomas":

DLookup("Employee_Name", "Employees", "Comp_Name = 'def'")

I hope John Thomas isn't a real person.


--------------------
Robert Crouser
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    2nd April 2020 - 08:27 PM