Dlookup Data Type Mismatch

Question

I am having some issues with a simple Dlookup command.  When I open a report that contains this code VBA tell me that I can't assign a value to an object and highlights the line in yellow.  During this break in code, I can hover over the fields and get the the following information as indicated below.  In my dlookup, "Explanation is a text field", "Code is a number field" and the value for strCombo or 104783 is a valid figure in the Code column of my table.  Any idea of why I can't get this to work? 

  Dim strAccountName As String          <hover: "Clerk II">

  Dim strCombo As Double                 <hover: 104783>

  strCombo = Forms!frmAccounts.Form.cboJobCode

  strAccountName = DLookup("Explanation", "tblDepartmentAcct", "Code=" & Forms!frmAccounts.Form.cboJobCode)

  Me.AccountName = strAccountName   <error here>

Answer 1

Nice explanation of the code.  However the error indicates that AccountName is a Number data type so we need to know more about the two tables, their fields and each field's data type.

You may be misled into thinking it's a text field if you have it set in its table as a Lookup field.  If that's what's happening, change the field's Lookup from Combo Box to Text Box so you can see what's really happening.  Then post bacl with what's in tblDepartmentAcct to confirm or refute my conclusion.

If I'm right, try changing the report's record source to a query that joins that table to tblDepartmentAcct so you can use the Explanation field without using DLookup.

Answer 2

BrianJohn wrote:

Me.AccountName is an unbound text box. The code that I supplied is

from the OnOpen EP of my report. The two lines Dim strCombo... and

strCombo = are there just for me to confirm that the value is

indeed a number (i.e. 104783). Does this help?  

Yep. You can't assign a value to a text box in the Open event of a report

even if it's unbound.

Instead use the Format event of the section that contains the text box

or e.g. of the report header.

---

cu

Karl

*******

Access-FAQ (German/Italian): http://www.donkarl.com

Answer 3

John,

Me.AccountName  is an unbound text box. The code that I supplied is from the OnOpen EP of my report.  The two lines Dim strCombo... and strCombo = are there just for me to confirm that the value is indeed a number (i.e. 104783).  Does this help?

Answer 4

What is the control Me.AccountName? a textbox or something else? What field is it bound to?

If AccountName is bound to a Lookup Field in your table then the value you need to insert is a number (strCombo?) rather than the displayed text.

---

John W. Vinson/MVP