Using DLOOKUP in a textbox

P

pwalter83

Registered User.
Local time
Today, 12:02
Joined
Dec 19, 2011
Messages
31
Hi,

I am trying to autopopulate a textbox2 from another textbox1 and using this code for the same:

=DLookUp("DIVISION_CD","dbo_DIVISION","[Text41]")

The problem is the above code works for only the first time. When I change the value in textbox1 the next time, the value in textbox2 does not get updated. Could someone please tell what I am doing wrong and if there is another way to do this ?

Thanks,
Paul
 
Interesting that this even works at all because your criteria statement doesn't look right, it should be something like:
=DLookUp("DIVISION_CD","dbo_DIVISION","[criteria field] = ' " & Me.[Text41] & " ' ")

To make it update after you edit/change textbox1, you need to paste in this code into the AfterUpdate or OnChange event of textbox1
Me.textbox2 =DLookUp("DIVISION_CD","dbo_DIVISION","[criteria field] = ' " & Me.[Text41] & " ' ")
where [criteria field] is the name of the field that holds the value you're comparing to
and [Text41] holds the value you're looking for or should this be textbox1?

David
 
DavidAtWork said:
Interesting that this even works at all because your criteria statement doesn't look right, it should be something like:
=DLookUp("DIVISION_CD","dbo_DIVISION","[criteria field] = ' " & Me.[Text41] & " ' ")

To make it update after you edit/change textbox1, you need to paste in this code into the AfterUpdate or OnChange event of textbox1
Me.textbox2 =DLookUp("DIVISION_CD","dbo_DIVISION","[criteria field] = ' " & Me.[Text41] & " ' ")
where [criteria field] is the name of the field that holds the value you're comparing to
and [Text41] holds the value you're looking for or should this be textbox1?

David
Click to expand...


Thanks David, I tried what you mentioned but I now get an error - #Name?

I have entered this code in the textbox of the DIVISION CODE(Textbox45):
---------------------------------------
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = ' " & [Me].[Text41] & " ' ")
---------------------------------------

and entered this code in the After Update event property of LINE_CD(Textbox41):
------------------------------------------------
Private Sub Text41_AfterUpdate()
[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = ' " & [Me].[Text41] & " ' ")
End Sub
------------------------------------------------
Could you please tell what I am doing wrong here ?

Thanks again !
 
Last edited:
The first part that gives you the #Name error, if you've put that code into textbox45, it will not work because this is vba code. If you use the same code you've used in the AfterUpdate event and put in into the OnCurrent event of the form itself, it should work.
BTW what happens if you type something in text41 box that doesn't exist in the table you're looking up, if this situation can arise, then you'll need to use:
If DCount(same code as DLookup)>0 then DLookup, else display what message/value you want

David
 
DavidAtWork said:
The first part that gives you the #Name error, if you've put that code into textbox45, it will not work because this is vba code. If you use the same code you've used in the AfterUpdate event and put in into the OnCurrent event of the form itself, it should work.
BTW what happens if you type something in text41 box that doesn't exist in the table you're looking up, if this situation can arise, then you'll need to use:
If DCount(same code as DLookup)>0 then DLookup, else display what message/value you want

David
Click to expand...

Thanks, but I am not able to undertand you, do you mean this code shouldnt be there in the Control Source property of Text45:
-------------------------------------------
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = ' " & [Me].[Text41] & " ' ")
-------------------------------------------

If I remove this code then I get a blank in the Text45 textbox.

What about this code that I have mentioned in the After Update property of Text41 textbox:
------------------------------------------------
Private Sub Text41_AfterUpdate()
[Me].[Text45] = DLookup("DIVISION_CD", "dbo_DIVISION", "[LINE_CD] = ' " & [Me].[Text41] & " ' ")
End Sub
------------------------------------------------
Should it stay there ? I am very new to Access and apologize if this is geting out of hand.

-----------------------------------

An Update:

I tried to copy the After Update Event code in the On Current property of the Form itself but it returned an error:

Microsoft Office cant find the field '¦' referred to in your expression.

I am lost now and dont know what to do.

Thanks !
 
Last edited:
If the data type of Line_CD is Text, put this in the Control Source:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" & [Text41] & "'")
Note that there are no spaces between the single quotes and the double quotes.

If it's Number, use this:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = " & [Text41])
By the way, Text41 is a meaningless name. You should change it to something meaningful.
 
vbaInet said:
If the data type of Line_CD is Text, put this in the Control Source:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" & [Text41] & "'")
Note that there are no spaces between the single quotes and the double quotes.

If it's Number, use this:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = " & [Text41])
By the way, Text41 is a meaningless name. You should change it to something meaningful.
Click to expand...

Thanks, I have tried doing what you mentioned but I am getting the same error- #Name?

Also, Text41 is the name generated by access and I did not change it. Would that make a difference ? I am stuck with issue for sometime now and dont know what to do with this. Could you help me please ?
 
Alansidman said:
CrossPost at
Click to expand...

That is not a crosspost....if you look carefully this post is different in nature to what I asked yesterday.

This is just like going off-topic when the main and genuine issue still stands.
 
pwalter83 said:
Thanks, I have tried doing what you mentioned but I am getting the same error- #Name?

Also, Text41 is the name generated by access and I did not change it. Would that make a difference ? I am stuck with issue for sometime now and dont know what to do with this. Could you help me please ?
Click to expand...
No it won't make any difference if you changed the name of the textbox. Is the textbox on the same form where you're running the code?

#Name can only mean that Division_CD or Line_CD is not a field name in the table dbo_DIVISION. Recheck those. If you are adamant that it exists, show us a screenshot of the table in design view.
 
vbaInet said:
No it won't make any difference if you changed the name of the textbox. Is the textbox on the same form where you're running the code?

#Name can only mean that Division_CD or Line_CD is not a field name in the table dbo_DIVISION. Recheck those. If you are adamant that it exists, show us a screenshot of the table in design view.
Click to expand...

Please find the screenshot attached. Also, the name of the form is REGISTER_VESSEL. However, I noticed one thing that the DIVISION table is not linked to the REGISTER_VESSEL form. Could that be the problem ? Pls also find the access file attached.
 

Attachments

pwalter, the database you attached does it actually show what your expression currently looks like?

Also, if the table you're pulling from is in SQL Server, it's worth mentioning in your first post.
 
vbaInet said:
pwalter, the database you attached does it actually show what your expression currently looks like?

Also, if the table you're pulling from is in SQL Server, it's worth mentioning in your first post.
Click to expand...

I did not get you, what did you mean by 'actually show what your expression currently looks like' ?

Yes, the tables are being pulled from SQL server.
 
This is what you currently have in the textbox:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" & [COLOR=Red][Me].[COLOR=Blue][Text41][/COLOR] [/COLOR]& "'")
This was what I gave you:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" &[COLOR=Blue] [/COLOR][COLOR=Blue][Text41][/COLOR] & "'")
Not the same thing.
 
vbaInet said:
This is what you currently have in the textbox:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" & [COLOR=red][Me].[COLOR=blue][Text41][/COLOR] [/COLOR]& "'")
This was what I gave you:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" &[COLOR=blue][Text41][/COLOR] & "'")
Not the same thing.
Click to expand...

Thank you !!!! Thank you !!!! Thank you !!! verrrrrrrrry much !!!!

You're a life saver man !!!
 
DavidAtWork said:
Interesting that this even works at all because your criteria statement doesn't look right, it should be something like:
=DLookUp("DIVISION_CD","dbo_DIVISION","[criteria field] = ' " & Me.[Text41] & " ' ")

To make it update after you edit/change textbox1, you need to paste in this code into the AfterUpdate or OnChange event of textbox1
Me.textbox2 =DLookUp("DIVISION_CD","dbo_DIVISION","[criteria field] = ' " & Me.[Text41] & " ' ")
where [criteria field] is the name of the field that holds the value you're comparing to
and [Text41] holds the value you're looking for or should this be textbox1?

David
Click to expand...

Thanks a lot !!! your post was immensely helpful !
 
vbaInet said:
This is what you currently have in the textbox:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" & [COLOR=red][Me].[COLOR=blue][Text41][/COLOR] [/COLOR]& "'")
This was what I gave you:
Code: 
=DLookUp("[DIVISION_CD]","dbo_DIVISION","[LINE_CD] = '" &[COLOR=blue][Text41][/COLOR] & "'")
Not the same thing.
Click to expand...

Hi,

Thanks for your help earlier. I am faced with 2 issues now:

1. When I save the data in the table through the form, the row of data that was entered earlier gets overwritten and replaced with the new one. The requirement is to add new rows and only update the existing rows.

2. The data saved from the auto filled textboxes does not get updated and remains same from the time it was entered first.

Could you please help me on this one ?

Thanks !
 
1. You should not be saving calculated values. That's why they are calculated so you can do it on-the-fly.

2. It will only change when you type something new into Text41 AND move to another control.
 
You must log in or register to reply here.

Similar threads

J
Colour text in Text Box
Replies
8
Views
694
arnelgp
arnelgp
D
Help!
Replies
7
Views
432
drm83
D
D
Solved DLookup Not Changing Value
Replies
14
Views
635
DDJKSA
D
M
Dlookup frustration, multiple criteria
Replies
12
Views
546
Pat Hartman
P
E
DLookup Function Alternative
Replies
19
Views
644
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom