Yet another DLOOKUP question

[Justin]

The users of my database will be entering their inventory of cellphones. The
serial numbers on the phones are 15 digits (known as an IMEI). The first 8
digits identify the make and model (those digits are known as the TAC). This
is great for uniformity of data on the backend and it makes it easier for the
end user as they do not have to enter all of that info themselves.

I am trying to include an unbound text box that will read the first 8 digits
that are entered into the form on a data entry text cox and do a DLOOKUP so
that the users can be assured that what they are entering is valid. If they
think it should be a Samsung Super Whamadyne 3000 but the form is showing it
as a BlackBerry Xtreme 3111 then they know there may be an issue with their
IMEI.

'IMEI_Entry' - is the bound data entry text box
'tbl_TAC' - is the two column table from which this is being read. First
column is the TAC (primary key) and the second column (PHONES) is the
make/model info. The TAC data was entered into the table as text and not
numbers if that matters.

Is it possible to do something like this in an unbound text box? I know
what I have below is so incredibly wrong it's not funny. I am grasping at
straws here and trying to cobble together syntax from a number of different
sites...and it's turning into a giant wad of fail.

=DLOOKUP("[Phones]","tbl_TAC","TAC='" & LEFT(Me.IMEI_Entry,8) & "'")

 

[Jeanette Cunningham]

Hi Justin,
what you have looks OK.
It needs a bit of debugging to see where the problem is.
------------------------
Dim strTxt as String

Debug.Print Me.IMEI_Entry
Debug.Print LEFT(Me.IMEI_Entry,8)

strTxt = Nz(DLookup("[Phones]","tbl_TAC","TAC='" & LEFT(Me.IMEI_Entry,8) &
"'"), "")
Debug.Pring strTxt
------------------------

I have wrapped the dlookup in the Nz function to cover any cases where there
is no matching phone.

Check that you have correctly spelled the name of the fields and the table
in your DLookup statement.

It would be useful to first check that there is a entry in the IMEI_Entry
field before you try to get the dlookup value for the phone.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Justin]

Ha! Got it! That debug definitely helped. Thank you very much.

The syntax for the LEFT formula was wrong. Instead of doing a Me.IMEI_Entry
I had to narrow it down much further and tell it *exactly* where to look for
the IMEI.

Here is the correct formula.

=DLookUp("[Phones]","tbl_TAC","TAC='" &
Left([Forms]![frm_IMEI]![IMEI_Entry],8) & "'")

Thanks again for the help!