By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

i cant pass the value from table to textbox through dlookup

P: 9
I dont know why I cant pass the value of AccountType (from TblAccount) to Me.txtWAccounType(textbox from WithdrawView)

pls help me.. here's my code

Expand|Select|Wrap|Line Numbers
  1.  If IsNull(DLookup("[AccountId]", "TblAccount", "AccountId = '" & txtWAccountId & "'")) Then
  2.  MsgBox "Account Number Doesn't Exist"
  3.  
  4.  Else
  5.  Me.txtWAccounType.Value = DLookup("[AccountType]", "TblAccount", "AccountId = ' " & Forms![WithdrawView]![txtWAccountId] & "' ")
  6.  MsgBox "Account Number Do Exist"
  7.  
  8.  End If
  9.  
my goal is to retrieve that AfterUpdate of AccountId. messagebox"Account Number Do Exist" pop up so it means there is a value but why it doesn't appear in Me.txtWAccounType.

pls help.. thank you.
Oct 10 '12 #1

✓ answered by Seth Schrock

Lets see if I can narrow down where the problem is. Above the code that you posted, put in the following:

Expand|Select|Wrap|Line Numbers
  1. Dim strResult as String
  2. strResult = DLookup("AccountID", "TblAccount", "AccountID =" & txtWAccountID)
Then, in line 5 of what you posted, replace the DLookup... with strResult.

Now click in the gray area next to that line of code (while in the VBA editor). A red dot should appear. Now do whatever is required to make your code execute. The code will stop at the line with the red dot. Now press Ctrl+G to make the Immediate window appear. Type into the immediate window
Expand|Select|Wrap|Line Numbers
  1. ? strResult
What do you get?

Share this Question
Share on Google+
11 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
Something that I just noticed is that in line 5, it looks like you left out a T in Me.txtWAccounType. Just a guess. If that fixes it, then you can ignore the rest of this post.

Your DLookup should be like this:

Expand|Select|Wrap|Line Numbers
  1. DLookup("AccountID", "TblAccount", "AccountID =" & txtWAccountID)
The name AccountID makes me think that the value is a number, in which case you don't want the ' surrounding it. Also, in line 5, I'm assuming that the active form is WithdrawView since in line 1 you left off the Forms!WithdrawView! before the control name. If I'm wrong, then you would need to add it back on to the code that I posted. Also in line 5, you don't need the .Value. All you need is
Expand|Select|Wrap|Line Numbers
  1. Me.txtWAccounType = ...
Oct 10 '12 #2

P: 9
Thank you. I did your suggestion but the textbox value is still null. I didn't get any error message and the messagebox"Account Number Do Exist" only proves that there is a value found but why it doesn't appear on my textbox? anyway AccountID is a string
example : 20091S

Thank you so much..
Oct 10 '12 #3

Seth Schrock
Expert 2.5K+
P: 2,941
Lets see if I can narrow down where the problem is. Above the code that you posted, put in the following:

Expand|Select|Wrap|Line Numbers
  1. Dim strResult as String
  2. strResult = DLookup("AccountID", "TblAccount", "AccountID =" & txtWAccountID)
Then, in line 5 of what you posted, replace the DLookup... with strResult.

Now click in the gray area next to that line of code (while in the VBA editor). A red dot should appear. Now do whatever is required to make your code execute. The code will stop at the line with the red dot. Now press Ctrl+G to make the Immediate window appear. Type into the immediate window
Expand|Select|Wrap|Line Numbers
  1. ? strResult
What do you get?
Oct 10 '12 #4

P: 9
Sir Do you have other suggestion on how can i get table field value and put it in the textbox (as a part of searching the record) where the values to be inserted on the textbox depends upon the AccountID?
thank you so much..
Oct 10 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Rheigny,

Are txtWAccountId and Forms![WithdrawView]![txtWAccountId] supposed to be the same control on the same form? If so, then your code could look like this:

Expand|Select|Wrap|Line Numbers
  1. If IsNull(DLookup("[AccountId]", "TblAccount", "AccountId = '" & Me.txtWAccountId & "'")) Then 
  2.     MsgBox "Account Number Doesn't Exist" 
  3.  
  4. Else 
  5.     Me.txtWAccounType.Value = DLookup("[AccountType]", "TblAccount", "AccountId = ' " & Me.txtWAccountId & "'")
  6.     MsgBox "Account Number Do Exist" 
  7. End If
Syntax for references to forms must be pretty exact....
Oct 10 '12 #6

P: 9
There's an error now after i did your suggestion its
Run-time error "3075":
Syntax error (missing operator) in query expression 'AccountID = 20091S'

and highlight this

Expand|Select|Wrap|Line Numbers
  1. strResult = DLookup("AccountID", "TblAccount", "AccountID =" & txtWAccountId)
Oct 10 '12 #7

P: 9
? strResult
20091S

Here's what I get from immediate window sir.
Oct 10 '12 #8

twinnyfo
Expert Mod 2.5K+
P: 3,284
You forgot the single quote to designate a String...

Expand|Select|Wrap|Line Numbers
  1. strResult = DLookup("AccountID", "TblAccount", "AccountID ='" & txtWAccountId & "'")
Oct 10 '12 #9

P: 9
Wow sir It's working! Thank you so much!
Oct 10 '12 #10

twinnyfo
Expert Mod 2.5K+
P: 3,284
My pleasure! Glad we could assist you today!
Oct 10 '12 #11

P: 9
Thank you so much again!
Oct 10 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.