Dlookup() and Forms 2

[rexrivas]

Can i use the dlookup() feature on a form?

I would base the dlookup on the result of field on the form. This form will of course feed a table.

The table "Vendor List" includes both fields "Vendor Name" and "Vendor Nbr".

THe form name is "Vendor-REp Info". The form has the following as Row Source SELECT [vendor list].[Vendor Name], [vendor list].[Vendor Nbr] FROM [vendor list] ORDER BY [vendor list].[Vendor Name];

The dlookup i created is the following, it does not feed the "vendor name" int table. Waht am i doing wrong

=DLookUp("[Vendor Name]","Vendor list","[Vendor list]![Vendor Nbr]=&[Vendor Info]&")

Thanks in Advance

 

[kjv1611]

Try putting brackets on "Vendor Lists", like this:

=DLookUp("[Vendor Name]","[B][[/B]Vendor list[B]][/B]","[Vendor list]![Vendor Nbr]=&[Vendor Info]&")

Also, you need to have a variable which you set the Dlookup to, or put it in a conditional statement.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

The reason there being that Access will not recognize the full name if it includes spaces, unless you include brackets around the name - works with fields and tables.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PHV]

Provided the form has a [Vendor Info] control which value is a valid [Vendor Nbr], you may try this:
=DLookUp("[Vendor Name]", "[Vendor list]", "[Vendor Nbr]='" & [Vendor Info] & "'")
If [Vendor Nbr] is defined as numeric in [Vendor list], get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[rexrivas]

PHV, I've used your code. it appeaers on the form, but when saved the Vednor Name field on the table is blank. i would like for the Vendor Name to appear on the table once the record has been saved. Any thoughts that can be helpful are greatly appreciated

 

[kjv1611]

How are you saving?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[rexrivas]

using the record selector at the bottom of the form. By clicking on next record button

 

[kjv1611]

Do you have the control source of your text box which is updated set to the correct field in the correct table?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[rexrivas]

No, under control source i have : =DLookUp("[Vendor Name]","[Vendor list]","[Vendor Nbr]='" & [Vendor Info] & "'")

i guesss i shoiuld have this somewhere else

 

[kjv1611]

Hmm, I think that is your problem. You see, the control source is where you want to send the data to from your text box. I would think it'd be best to put the Dlookup function in an event on your form, and put the field you want to update as the controls source.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[rexrivas]

made the recommended changes, table is not updated

 

[kjv1611]

Hmm, what are you setting your Dlookup equal to in VBA. I would think you would need to set it to a variable, which you would then set to your control, or perhaps directly to your control. Something like this:
[/code]
Me.txtMyTextBox = Dlookup(...)
[/code]


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

oops, typo - fixed here:

Hmm, what are you setting your Dlookup equal to in VBA. I would think you would need to set it to a variable, which you would then set to your control, or perhaps directly to your control. Something like this:

Me.txtMyTextBox = Dlookup(...)

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[rexrivas]

sorry kjv, i am drawing a blank. i have a bad case of the mondays.

How do i set up a variable?

 

[kjv1611]

No problem (I've had much of that myself [WINK])...

Private Sub Me.txtMyTextBox_AfterUpdate()
  Dim strStringVariable as String
  Dim intIntegerVariable as Integer
  Dim bolBooleanVariable as Boolean

Also, do a google search for the [blue]Reddick naming convention[/blue] - it is not necessarily a must, but highly suggestable. It makes your code easier for you and anybody else to read. Basically, you abbreviate each variable with the first part signifying what type of variable it is. For example, if you named a variable strName as String, the str part will signify that the variable is string, and the Name part gives detail as to what value the variable is used to store.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV