dlookup help

[scatterbrain]

Hi everyone

prolly a simple problem but could you help if possible:

i have a form where when i choose a video number from a combo box, the next field, video title, is automatically populated by the choice from this combo box.

This displays in the form but doesnt save to the video title field in the "copy_of_video" table where i am trying to save.

in the control source i have :

=DLookUp("[Video Title]","Video","[Video Number] =" & [Forms]![Copy_of_video]![Video Number])

what do i need to add to this to make it save in the relevant field?

i tried adding "Video Title" before the '=' but this didnt work

any help would be greatly appreciated

thanks, craig

 

[jfgambit]

Craig:
By adding the Video Name, you are duplicating data in your database. Why not add a DLookup to the On Current Event of the form, then the name of the Movie would show up when the form is opened or you move to another record.

Dim rsNm as Variant
rsNm=DLookUp("[Video Title]","Video","[Video Number] =" & [Forms]![Copy_of_video]![Video Number])

me.TitleTextbox = rsNm

This should also reside in the After Update Event of the combobox.

HTH

 

[scatterbrain]

thanks jfgambit, but i cant try it out at the moment because for some reason the builder will not open!!!!arghhhhhh

again thanks for your help

Craig

 

[scatterbrain]

jfgambit:

i tried this but when i tried to run the form it says that "i cant assign a value to this object"????

any ideas?

thanks, Craig

 

[jfgambit]

Can you post the code you added?

 

[scatterbrain]

code i added was :

Private Sub Form_Current()
Dim rsNm As Variant
rsNm = DLookup("[Video Title]", "Video", "[Video Number] =" & [Forms]![Copy_of_video]![Video Number])

Me.txtVideoTitle = rsNm


it now works, but then when i click to add a new record it says that [video number] is missing from the query expression.

i think your also right about no need for the repeating of that data in the copy of video table.

cheers for help

Craig

 

[bradcccs]

Definately no need to duplicate data.

Your initial post seems to have been what you are wanting anyway.

i have a form where when i choose a video number from a combo box, the next field, video title, is automatically populated by the choice from this combo box.

However, if you have multiple columns in your combo box for selection purposes (as most purposes do), you can reference the other columns using:

=ComboBoxName.Column(1) etc

Note: numbering of columns starts at 0.

 

[scatterbrain]

ok thanks for your help bradcccs.

its sorted now

thanks to jfgambit as well