Confused about not being able to add data to field with DLookup calculation

[RSW]

I inherited a database; its form has a Materials field with the expression

=IIf(IsNull([ID]),"",DLookUp("[Material]","Materials","[ID] = " & [ID]))

A user told me that she could no longer input data into the field. I looked at the query, and the Materials table was no longer listed; it's very possible that someone accidentally deleted the table from the server. So, I added the table to the query, joined by ID, and...the form doesn't work. "Control can't be edited, it's bound to the expression" error. I tried each kind of join; none work.

I know this worked in the past. And there's a bit of information about this error online, that suggests the field should be updateable if the table (in this case Materials) is joined in the query.

Does anyone know what else may have gone wrong?

Thanks in advance!

 

[pbaldy]

If you're saying that's the control source of a textbox, you would never be able to manually change the contents of that textbox.

 

[Galaxiom]

As Paul said. An expression in the Control Source of a text box makes the value unchangable except through changes to the Materials table.

It is possible that this expression is used to display an initial value and during the operation of the database, VBA is used to change the ControlSource to something that allows updates. Maybe something in the code has broken.

Another possibility is that the form was loaded with a different ControlSource that was then changed using VBA. If the form is then put directly into edit mode and saved, the change will overwrite the originally intended ControlSource.

A lot of databases are badly designed and lack any documentation. It can be hard enough for an experienced developer to decipher the logic. Once something screws up it can be next to impossible to get back there.

It would be best to restore the front end from a backup.

 

[DCrake]

I think I would be more worried about the Materials table being deleted. Scary Front ends you can modify etc. When a table is gone its gone Data and all.

 

[RSW]

Thanks for the replies. I've gotten a bit more information...apparently double-clicking on the field in question brings up a Materials continuous subform (this still works), and then once one or more materials are added and the subform is closed, the top one appears in the main form's field (this no longer works, and led to confusion).

 

[pbaldy]

Okay, first thing to check is that the recreated table is the same name, and the field names and data types are the same. If it was imported from a backup, then this likely isn't the problem. You may need to requery the textbox containing that formula after changing the related data.

 

[RSW]

Thanks Paul,

All the names and data types look to be the same.

Can you explain a bit more what you mean by this?

You may need to requery the textbox containing that formula after changing the related data.

I specifically don't understand what you mean by "requery the textbox".

 

[pbaldy]

Since you're adding to the data that formula looks at on another form, I'm not sure it will update when you come back to this form. In the code that closes the other form, add this:

Forms!FormName.TextboxName.Requery

Changing the form and textbox names. It's a shot in the dark, but sometimes those hit the mark. If that doesn't work, what exactly is going wrong? Does the textbox display error, an incorrect result, or ?

 

[SOS]

Another way would be to just use

Me.Recalc

to recalculate the form

(or if the code is on another form):

Forms!YourFormName.Recalc

and that will take care of any and all expressions.