DLookup - help with understanding

[TR21Mark]

First, I have read all 53 topics on this matter and I am still having problems with such an easy thing.

I have a form ( frmFaxCover ) which has a combobox showing a list of my Contacts names. What I need to happen is when I select the name from the combobox, it populates the next field with thier fax number.

Contacts - name of table where both ContactName and FaxNumber field exist
ContactName - text
FaxNumber - text

frmFaxCover:
combobox item selected is stored in FaxCover Table under ContactName when selected

current syntax:
=DLookup("[FaxNumber} "," Contacts ","[ContactName] = '" & Me!FaxNumber!Contacts & "'" )

Note- I put brackets aroun FaxNumber after Me! but when I close they are removed.

Please help me if you can, and appreciate all the help I can so much.

Mark

 

[Tanis]

I take it that the curly bracket is a typing error! Try this syntax.

=DLookUp("[Expression]", "Table/QueryName", "[PrimaryKeyField] = Forms![CurrentForm]![Criteria]")

 

[TR21Mark]

Has not been able to work yet. I have even built a new DB with 2 tables and tried on a new form and does not work.

I have lost so much sleep over something so simple.

Is there VB code that I could use to trigger the same thing as DLookup. I have lloked and searched this board and found some I think would work, but here is my problem - I am not sure what to change the terms to fit my db tables and names.

I am new to VB so please be gentle with me.

Thanks to all.

 

[Norie]

Should it not just be:

=DLookup("[FaxNumber] ","Contacts","[ContactName] = '" & Me!Contacts & "'" )

 

[TR21Mark]

I keep getting the following errors displayed in the text box:

#error

#Name


Do I need to separate the information into 2 separate tables? I will try a couple more DLookup in another form and check to see if my table could be bad or something.

Thanks to all

 

[TR21Mark]

OK, I have not been able to get anything to work I have researched hundreds of posts here, msdn library, internet, and Access Help for the past 3 full days to no avail.

I have however got it work by creating a combo box with both the Contact name and fax number with the fax number hiddin in the combo box dropdown. I then went to the textbox and typed =Combo39.column(2) and it works exactly how I want.

Here is the new problem. I need to store the selected record into a table, but since by control source is the combo box it is not stored.

Question - Can I enter something on the after_change event for the textbox to send to the proper field/table?

Thanks for all the help

 

[TR21Mark]

Curious to my statement above