textbox controlsource property

[wraithzshadow]

hello, so i have a form that has as many as 200 textboxes... when the
form loads it runs through a function that positions each textbox (only
the textboxes its going to use... it wont use all of them, those that
dont get used are not visible)... it then sets the controlsource to an
expression with dlookup that looks something like this...
"=DLookup(" & Chr$(34) & "[Control Measure Description]" & Chr$(34) &
"," & Chr$(34) & "[Control Measure table]" & Chr$(34) & "," & Chr$(34)
& "[Control Measures Number]=" & FrcrdSet![Control Measures Number] &
Chr$(34) & ")"

this works for one part of the form which is supposed to display the
information without allowing editing, but another part of the form is
supposed to show the same info in the same textboxes, but in such a way
as to allow editing. is there a way to set the textbox controlsource
to look at a specific row in a specific table, display the value, and
allow me to edit the value?

thanks for any advice

 

[Guest]

If you want the value in the text box to update the recordset, the field in
recordset must be the control source. This makes it a bound control.
You can move what you are using a a control source to the default value.

 

[wraithzshadow]

Klatuu, i didnt understand what you meant by

You can move what you are using a a control source to the default value.

the problem with using the field as a control source is that the
textbox needs to be specific entry from the table, always the same
field, but a specific entry for each textbox... i thought a query would
be able to do it so that i could edit the values... but then i would
have to create as many as two hundred queries on the fly....

 

[Guest]

You said this is the control source for the text box:

"=DLookup(" & Chr$(34) & "[Control Measure Description]" & Chr$(34) &
"," & Chr$(34) & "[Control Measure table]" & Chr$(34) & "," & Chr$(34)
& "[Control Measures Number]=" & FrcrdSet![Control Measures Number] &
Chr$(34) & ")"

I don't know why you have all those extra " Chr$(34) in the DLookup, they
are not necessary.
=DLookup("[Control Measure Description]","[Control Measure table]","[Control
Measures Number]=" & FrcrdSet![Control Measures Number])

All you need to do is make the control source the field you want to update
for the control. Put the DLookup code in the Default Value property rather
than the Control Source property.

 

[wraithzshadow]

i put the Chr$(34) there because the value is being set in a vba
function... the whole sentence looks something like this...

frm.[JSA Number].DefaultValue = "=DLookup(" & Chr$(34) & "[JSA Number]"
& Chr$(34) & "," & Chr$(34) & "[JSA table]" & Chr$(34) & "," & Chr$(34)
& "[JSA Number]=" & JsaNum & Chr$(34) & ")"

the problem with putting the DLookup in the DefaultValue and putting
the field in the ControlSource is that the value JsaNum is a number
that changes whenever i push a button... basically the program scrolls
through entries without using the "Record Selectors"... i did it this
way because i couldnt customize the forms built-in record selectors to
the extent that would work in an agreeable way
when the form loads it will load the information the the specification
to the first entry in a table, then there are buttons, the use of which
causes the form to be reloaded to the specification of that specific
entry... I did this because the form varies in how much info is
displayed in each section and each row... I used the DLookup for the
controlsource becuase because then everytime i load the frm, the
function positions the textbox and sets its control source to look for
the record with the value specified by the "[JSA Number] = JsaNum"...
the variable JsaNum changes as the form loops through the controls, so
each control ends up pointing at different records

 

[Guest]

I don't see any reference to a VBA function in your code. Even so, I don't
think it is going to work with the syntax you are using.
In any case, you still have to have the control source set to the recordset
field for it to be updatable.
If you want to load a specific value in a control and the Default Value
property will not work for you, then you will need to assign the value in the
Form Load event.