Error using DLookup to populate a field

[t2134mf]

I have a form bound to record source "qryVehicleInventory"

The form header has a listbox called "lstVehCfg". That listbox is bound to field "VehConfigID".

When you select an item in the list, I use the AfterUpdate event to filter the main form records in "qryVehicleInventory" by "VehConfigID"

Now, the listbox has a field called "Notes". The notes can be long (don't fit in list box), so I wanted an unbound text box that could show the full notes for the selected item form the listbox. So I added the following to the AfterUpdate event of the listbox.

Me.txtConfigNotes = DLookup("[Notes]", "tblVehConfig", "[VehConfigID] = Forms!frmVehicleSelection.lstVehCfg")

Here's the problem. If the selected item from the listbox has matching items records to show after the filter has been applied, the Notes field displays correctly. However, if the selected listbox item has zero matching records to display, the "Notes" textbox does not update and instead still shows the last selected items notes.

Help...

 

[lameid]

Rather than using code for this one...

Make a column in the listbox for notes in its row source.
Make the column width for that column 0" so it is not visible.

Next make the control source for txtConfigNotes the following...

=lstVehCfg.column(2)

I assumed your notes column would be your third column. Adjust the number appropriately.

This should be faster than using the Dlookup anyways.

If you wan to fix your method try wrapping Dlookup in NZ...

Me.txtConfigNotes = NZ(DLookup("[Notes]", "tblVehConfig", _
     "[VehConfigID] = Forms!frmVehicleSelection.lstVehCfg"),"")

 

[TheAceMan1]

How are ya t2134mf . . .

Let the [blue]last command[/blue] in the [blue]AfterUpdate[/blue] event of the Listbox be:

[blue]   Me.ReCalc[/blue]

Give it a spin and let us know.

BTW: Although you've been a member since Nov 14, 2002 ... Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[t2134mf]

Thanks. I love the 'column' function and will definately implement that. Tried the ReCalc as well, but I still have the same problem.

I did some more digging...the form has the 'AllowAdditions' property set to False by design. If I set it to 'True', then everything works as it should. For some reason, if you select an item in the listbox that results in a filter with no records returned and no ability to add a record, you cannot grab the 'ConfigID' form the listbox anymore. This seems liek a bug to me. Any more thoughts?

 

[lameid]

To me it sounds like a bug as you are not trying to add data since the control is unbound. But it is easy to see how they got there. It is easier to not allow changing the values of the controls at all rather than checking to see if it is unbound first.

At least the column collection works around the problem.

 

[t2134mf]

I'm sorry to say that the 'column' collection doesn't work. Same problem even with =lstVehCfg.column(2)

 

[lameid]

When you say that the column collection does not work, does it not work because AllowAdditions is set or does it just not work?

If it does not work at all, double check you are getting the column you want (and that it works by specifying column 0).

Otherwise, you might try making the data form a sub form
and put the listbox on the main form and don't set the allowadditions property on it. Try binding the listbox if you are still having problems.

 

[TheAceMan1]

t2134mf . . .

Am I right in assuming [blue]Notes[/blue] occurs in a different table than that in [blue]qryVehicleInventory[/blue] (the reasom your using [blue]DLookUp[/blue])?

If notes does occur in [blue]qryVehicleInventory[/blue], then why not add it to the form and remove it from the Listbox?

[blue]Your Thoughts? . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[t2134mf]

I reproduced the problem using two very simple tables and 1 form. Can I attach this .mdb or send it to you?