Dlookup problem have been at it for weeks need urgent help

[forestpest]

Hi,

I have been trying to use dlookup for weeks but keep getting the #Name? error. I am attaching the database so you guys can help me fix this it's really urgent.

Go to the form PatientCodedDiagnosis see the edit button press that and use the combobox to select something I call a qualifier select one. The effect should be a code (e.g. XX.XX.XX) appearing in the textbox next to the combobox the code is basically the OtherCode field from the DiagnositicMasterList table where the DiagnosisID = value from combobox which is a id. If we have multiple records on the from the code should differ for each record depending on the selected qualifier. The value of the combobox is stored in the PatientCodedDiagnosis table (SelectedQualifier field). Please look at my AfterUpdate event for the combbox that's where I use DLookup

Note: Sometime a qualifier doesn't apply to a diagnosis in which case the combobox is empty and also no SelectedQualifier is stored.

 

[Remou]

Please post your code, I cannot use your attached db.

As an aside, it is never a good idea to use words like 'urgent' in Tek-Tips. I advise you to read the FAQ, particularly faq181-2886.

 

[forestpest]

code is

Private Sub Combo22_AfterUpdate()
Dim SelectedID As String
Dim Charcteristic As String
Dim TableName As String
Dim WhereClause As String
Dim finalstr As String

'Charcteristic = "DiagnosisName"
'TableName = "DiagnosticMasterList"
SelectedID = Me.Combo22.Value
'WhereClause = "DiagnosisID = " + SelectedID

finalstr = "=" + "DLookup" + "(" + "[OtherCode]" + "," + "DiagnosticMasterList" + "," + "[DiagnosisID]=" + "'" + SelectedID + "'" + ")"
MsgBox (finalstr)
Me.Text53.ControlSource = finalstr
End Sub


So about the word "urgent" thank for the advice my idiotic mistake I am just frustrated over this.

 

[Remou]

Try:

finalstr = "=" + "DLookup" + "(""" + "[OtherCode]" & """,""" & "DiagnosticMasterList" & """,""" & "[DiagnosisID]=" & "'" & SelectedID & "'" & """)"

The above implies that DiagnosisID is alpha, drop the single quotes if it is numeric.

It seems a long way round.

Welcome to Tek-Tips.

 

[forestpest]

I made the change but now I get #Error I am sure that the ID selected is ok I mean when I select a value from the combo i know it's the correct one. also I am sure the othercode field has the value I am looking for meaning the criteria is ok. my msgbox shows the DiagnosisID=1585 which exists in the table
I expect to see Q1.45.85 in the textbox but get #Error

 

[Remou]

That looks numeric to me, so drop the single quotes:

finalstr = "=" + "DLookup" + "(""" + "[OtherCode]" & """,""" & "DiagnosticMasterList" & """,""" & "[DiagnosisID]=" & SelectedID & """)"

You could also try a check:

MsgBox DlookUp("[OtherCode]","DiagnosticMasterList","[DiagnosisID]=" & SelectedID) & ""

Or subsistute an actual number for SelectedID.

 

[MajP]

I took a look at the database you have lots of problems.

1) First of all DiagnosisID is an autonumber therefore it should not be surrounded by single quotes.

Now the big issue. What you are trying to do will not work
2) You have a continuous form if you change the recordsource of one unbound control you will change the recordsource of all unbound controls.
3) Even if you could the final dlookup would need to look something like when resolved (as Remou is showing):

=dlookup("[OtherCode]","DiangosisMasterList","DiagnosisID = 1234")

You do not need to do this in code. You need to do this on the form in the control source of txt53. Something like

=dlookup("[OtherCode]","DiangosisMasterList","DiagnosisID = " & [combo22])

you may need to put a recalc or refresh statement in the combos afterupdate.

 

[forestpest]

MajP you're right by the way sorry for the state of the database my predecessor was not a programmer that's why I have so many problems now. I get your point and although removing the quotes worked it did what you said it would. I added the line of code you provided to the Control Source of the textbox but it's giving me syntax error did u try it on my database?

 

[MajP]

No I freehanded this, but it will work if you spell everything correctly. I mispelled the table name for one thing. Should be DiagnosticMasterList. I think it may be Combo22 not combo22.

When working with dlookups test them first in the debug window because they are very sensitive to syntax and do not give good error messages.

 

[MajP]

correct syntax
=DLookUp("[OtherCode]","DiagnosticMasterList","DiagnosisID = " & [Combo22])

 

[forestpest]

That's what I had after fixing the provious mistakes

I even paste yours

my controlsource look like this:

=DLookUp("[OtherCode]","DiagnosticMasterList","DiagnosisID = " & [Combo22]) still getting syntax errors

I must be missing something

 

[Remou]

Eliminate possibilities one by one, for example:

=DLookUp("[OtherCode]","DiagnosticMasterList","DiagnosisID = 1585")

Or
=DLookUp("[OtherCode]","DifferentTable")

Or

=DLookUp("DifferentField","DiagnosticMasterList","DiagnosisID = 1585")

Another useful tool is the Expression Builder.

 

[MajP]

It works fine for me but I added a null to zero check for when there is a null value in the field

=DLookUp("[OtherCode]","DiagnosticMasterList","DiagnosisID = " & NZ([Combo22],0))

 

[MajP]

Also you need to get rid of you old code.