Dlookup - changing a value in a form (1 Viewer)

[KateM]

Hi - I wonder if anyone can help?

I'm making a form for colleagues to use which will eventually populate a table. I'm using DLookup to populate some of the fields in the form from another table. The problem is that for a few of the fields in that other table, the values are incorrect.
(Obviously the most sensible thing to do would be to amend the source data but for various reasons I cannot do that.)

For example, "date built" in the original table might be "f" but that needs to be changed to "h", so I tried the following but it doesn't work:
..............................................................................................

If DLookup("date_built", "dbo_meas_questionnaire", "[meas_id] = Forms!FrmRetestSearch.numOrigId") = "f" Then
Me.TxtDateBuilt = "h"
Else
Me.TxtDateBuilt = DLookup("date_built", "dbo_meas_questionnaire", "[meas_id] = Forms!FrmRetestSearch.numOrigId")
End If
....................................................................................................


Do you have any suggestions as to how to amend my coding, please?

Thanks !

KateM

 

[pr2-eugin]

What is the relationship between the two tables? Why are the controls not bound to the table?

 

[KateM]

Hi Paul

The DLookup table contains the details we have about a property which has already been tested.

The new form is to generate a new test at the same property, hence needs to have a few of the fields populated by known details about that property.

(The new form also has text boxes and combo boxes to input new details about the new test - e.g. issue dates, number of detectors, etc., which need to be input manually or selected from a drop down box.)

I don't quite understand what you mean by " why are the controls not bound to the table?" Would you please explain?

 

[pr2-eugin]

If you have information about something stored in another table. Let them be there, you only need the ID of the table so you can link them up using JOINS, for form design you go for Main Form SubForm set up. This is what makes a Relational Database tick. You have to work on that. I think you might need to work on relationships, and normalization.

Regarding Bound & Unbound controls : http://www.baldyweb.com/BoundUnbound.htm

 

[KateM]

Thanks for your reply.

We decided to go for an unbound form so that if the user made a mistake, it wouldn't corrupt the underlying database, just the end table used to issue new tests.

I'm already using a subform so the user can input a postcode on the main form and then select the correct address from the subform.

The DLookup is to another table entirely.....