DLookUp problem works in Debug but not on the form?

[SummerSolstice]

I'm stumped! I have a form that we will be using to write reports. The form is based off of a query that pulls from two other queries that go back to two different tables (one holds customer information the other will hold each report and they are linked off of the ClientID / IDNumber).

There is information stored in the customer table that I need to default value on the report which will be stored in the report table. This information changes from time to time and we need to be able to retrieve the information at the time the report is written and the customer table only holds the "current" information so yes redundant but it works for our need.

I have been trying to use a DLookUp on the default value for the field (ComboBox) but it won't give me the information on the form. If I go to Debug the code works fine, just not on the form. Even though it says IDNumber and ClientID they are both stored as text not numbers. Any ideas what I might be missing?

Here's what I have:

 DLookUp ("[Axis Ia dx]", "QryContactComplete", "[IDNumber] = Forms!FrmContact![ClientID]")

This information would then be stored in [AxisIadx] on the report table and is on the field properties as the data source.

[colintis]

A little correction to your quoting in criteria part

DLookUp ("[Axis Ia dx]", "QryContactComplete", "[IDNumber] = " & Forms!FrmContact![ClientID])

Try again and see if it works

[SummerSolstice]

That didn't work either, wouldn't there need to be the single quote after the [IDNumber]= in the criteria along with & "'" at the end due to the criteria being text?

Strange part is that I am able to get this to work in Debug by using the Table "TblClientProfi le" instead of the Query "QryContactComp lete" for the Domain, which doesn't make much sense to me. If I go and open up the Query directly the [Axis Ia dx] field (and all the other similar type fields on the form) are populated correctly there but the DLookUp isn't bringing the data into the Form as the default value for the field.

[TheSmileyCoder]

Where exactly are you using the Dlookup?, and yes if the criteria is text, you need to use a ' at the begining and end.

If your writing it directly in the defaultvalue property of a control on a form, you should be aware that the defualt value will only load once, when the form is opened. It will not "refresh/reload" so to speak for each new record.

Is the Forms!FrmContac t![ClientID]" the same form that your trying to set a default value for? In that case I don't think it will work. Instead you can use the forms Current event to set the value.