Form ControlSource ?

[RedHeadedMonster via AccessMonster.com]

I have a form, based on a query. However, there are a couple of fields on
the form, not in the query, that I have set the ControlSource = to a field in
a table. I want the user to be able to change the value in the field,
therefore updating the data in the table. Is this doable? If so, how?

Thanx!
RHM

 

[Douglas J. Steele]

The only way to allow fields to be changed and have the changes reflect in
the table is to bind the controls to the form's RecordSource. If you can't
add the fields to the existing query, you might have to use a bound subform.

 

[Ron2006]

There is one other way but it is obviously not the way you have it set
up.

1) field on form that is NOT part of query should have nothing in the
control source and be unbound.
2) In the oncurrent and/or on open of the form depending on things you
should load that field with the value that is appropriate.
3) In the Afterupdate event of that field you will need to either
execute sql/query to update that field in the original source or
address and update that field with some sort of code.

That is the only other way that I can think of right now to do what I
think you want to do.

Ron

 

[RedHeadedMonster via AccessMonster.com]

Ron,

If my unbound field (fldA) should display the data in tblCONFIG fldConfigA

How would i "load that field with the value that is appropriate"?

RHM

 

[Ron2006]

In the Oncurrent event of the main form issue a dlookup of that field
from the table (use this instead of the =in the control source.) The
reason for this is if it is the = type you cannot change it.

Ron

 

[RedHeadedMonster via AccessMonster.com]

Ok i put in the following in the OnCurrent event of the main form :

Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI]" = Me.MEI)

Also tried.
dim varA as variant
varA = DLookup("[fldConfigA]", "tblConfig", "[fldMEI]" = Me.MEI)
set fldA = varA

No errors either time, but no data either.

RHM

 

[Ron2006]

I think your condition is wrong.....

Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI]" = Me.MEI)


should be

Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI] = " & Me.MEI)


Ron

 

[Ron2006]

I think your condition is wrong.....

Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI]" = Me.MEI)

 should be

Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI] = " & Me.MEI)

Ron

The above assumes that [MEI] is numeric.

If alphabetic then
Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI] = '" & Me.MEI &
"'")

if a date then I believe it should be

Me.fldA = DLookup("[fldConfigA]", "tblConfig", "[MEI] = #" & Me.MEI &
"#")



Ron