using an on enter event to display the results of dlookup statement into another text box

Clete

Board Regular
Joined
Sep 5, 2014
Messages
62
im wondering if I can do something like this...... I have a text box called FileNo. Im wondering if I can use an on enter event so when the user enters a file number in the FileNo textbox and clicks enter it will display the result of the dlookup statement below in the txtForwarder text box. I set up this code which isn't displaying the result..... so not sure where to go from here. My program is working from beginning to end but if I could remove some of the manual work for the user using this method or something similar it would save them a lot of time. The dlookup statement itself does give me the desired result just not from the fileNo textbox so the dlookup statement itself is not why this code isn't working.

Private Sub FileNo_Enter()
Me.txtForwarder = DLookup("[FORWRD_NAME]", "File_No", "[FILE_NO] =
Form![FileNo]")
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Not the best approach. When you enter FileNo the first time, it has no value, so don't expect the lookup to work. Your best choice would be a button to run the lookup and you can test if it has something in it and deal with it if not. You could also use FileNo events related to leaving the control (On Lost Focus, On Exit, AfterUpdate, etc.) but again, if the control loses focus and there is nothing in it, same problem. If you are going to do this many times on the form in a session, something other than dlookup might be better. Even on a subsequent attempt, it appears FileNo is text and your lookup is not constructed properly for text as well as the " is in the wrong place. This should be close:

sub cmdFileNo_click
if is null (Me.FileNo) or Me.FileNo = "" then
msgbox "Please supply a fileno value."​
exit sub

Me.txtForwarder = DLookup("[FORWRD_NAME]", "File_No", "[FILE_NO] = '" & Me.FileNo & "')"
Me.txtForwader.requery


Exit Sub
cmdFileNo is the name of the command button.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top