DLOOUP Error

[emina002]

I just want to retrieve a set of data on Database using the specific full name, however, all is ok except 1 textbox, and I am getting #Error, as I want to reload the data, I compare it to other DLOOKUP formula I set and all is ok and no problem at all, I mean except the dlookup function below, I am getting specific error. Kindly help.

"=DLookUp(""Date of Death"",""RiskFactor"",""FullName='"" & [Forms]![TabsControl]!Child15.[Form]![FullName] & ""'"")"


I am solving this for an hour :banghead:

 

[CJ_London]

for the future, provide the error description, do not expect responders to know what you are talking about. As it is, your code is wrong in so many places, just to make it work

1. you have spaces in your field name - so you must use square brackets.
2. You also have double double quotes, only need them once
3. you are missing a single quote and
4. you do not surround the whole lot with double quotes

=DLookUp("[Date of Death","RiskFactor","FullName='" & [Forms]![TabsControl]!Child15.[Form]![FullName] & '")

 

[emina002]

Hi @CJ_London, thanks for the response, I put double quotes because I include this inside VBA code, which requires me to put double quotes.

The scenario is, I have drop down which is connected to "FullName" field, and once the name is selected, a textbox will be automatically populated, and I used DLOOKUP function and will include it on Control Source.

Sorry if I didn't explain it well.

 

[June7]

Domain Aggregate functions querying large dataset can slow performance of query/form/report. This should be used as last resort.

[Date Of Death] and FullName are in same table? Why not include [Date Of Death] in combobox RowSource? This will then show as a column that can be referenced in textbox ControlSource. Reference combobox column by index. If data in column 2 its index is 1. Combobox and textbox are on same form? Simply: =[comboboxname].Column(1)

@CJ_London - you missed the closing ].

 

[isladogs]

VBA does not require the use of double double quotes
For examples ...and purely randomly...

Public Function GetRandomID()
    GetRandomID = Nz(DLookup("RandomID", "tblSettings", "ID=1"), 0)
End Function

Two mistakes in CJL's response. It should have a ']' bracket and another double quote

=DLookUp("[Date of Death[COLOR="Red"][B]][/B][/COLOR]","RiskFactor","FullName='" & [Forms]![TabsControl]!Child15.[Form]![FullName] &[B][COLOR="red"] "[/COLOR][/B]'")

OR...

=DLookUp("[Date of Death[COLOR="Red"][B]][/B][/COLOR]","RiskFactor","FullName='" & Forms!TabsControl.Child15.Form!FullName & [B][COLOR="red"]"[/COLOR][/B]'")

NOTE:
1. I would change Child15 and TabsControl to something meaningful
2. As presumably not everyone will be dead, you may need to allow for nulls e.g.

=Nz(DLookUp("[Date of Death[COLOR="Red"][B]][/B][/COLOR]","RiskFactor","FullName='" & Forms!TabsControl.Child15.Form.FullName & [COLOR="red"][B]"l[/B][/COLOR]'"),Null)

You may wish to substitute some valid future date in place of Null e..g. #01/01/9999#

 

[June7]

Will have to double up the quotes if you want to set the ControlSource with the DLookup expression, which is what I think OP is attempting. And since they said this is working for other textboxes, probably just need the [ ] to fix the original code.

 

[CJ_London]

Two mistakes in CJL's response

just testing

 

[emina002]

Thanks for the help, I will work on your suggestion. Just to make it more clear,

TabsControl is the main form
I used tab controls(named as TabCtl17) and one Subform which is Child15

I used another subform and embed it on tab controls (to call 3 forms) that is child23.

Dropdown of full names include in Child15
And all controls include are on Child23.

Date of Death and FullName include in one database, that is why I have to use DLookup using full name as criteria.

So as I said earlier, as I used Dlookup in another control box (eg textboxes), regardless that "double quote" is included, it is working aside from a certain textbox which I have to populate.

 

[isladogs]

Sorry but I think you've just made it LESS clear.
You really do need to rename your database objects and controls.

No idea what 'included in one dstabase' means

There are usually better methods of populating controls than using domain functions

 

[June7]

@emina002, did you read posts 4 and 6?

 

[emina002]

hello there, I was able to solve it after hours of working on it, so in my tables, I have Date of Death field, and was calling a specific data on using full name, I just removed some spaces like dateofdeath and it works, somehow, I have problem again on DLOOKUP. I used loads of dlookup value to load a specific data using fullname as criteria, I was able to lookup automatically using After Updates event on FullName field(it was combobox), but when I am trying to update a specific control, the controls are non-editable now . So is there any way to load the data without using dlookup?

here is my piece of code,

If If Forms!tabscontrol.TabCtl17.Value = 0 Then Then
Forms!tabscontrol!Child23.Form!Text52.ControlSource = "=DLookUp(""BMTDate"",""RiskFactor"",""FullName='"" & [Forms]![TabsControl]![Child15].[Form]![FullName] & ""'"")"
Forms!tabscontrol!Child23.Form!Combo58.ControlSource = "=DLookUp(""BMTPhysician"",""RiskFactor"",""FullName='"" & [Forms]![TabsControl]![Child15].[Form]![FullName] & ""'"")"
Forms!tabscontrol!Child23.Form!Combo62.ControlSource = "=DLookUp(""DRIGroup"",""RiskFactor"",""FullName='"" & [Forms]![TabsControl]![Child15].[Form]![FullName] & ""'"")"
.....'and so on, I have many fields to load up to using Dlookup
End if

Any idea would be much appreciated.

 

[MajP]

Can you explain this Rube Goldberg machine you are building? This seems crazy overly complicated to do native Access capabilities. Why can you not simply use a bound form? If you do not make it bound how do you expect to edit the values and do anything with it. If not bound then simply load them via code. I would pull them from a recordset. You really should think of a new naming convention You have a form named tabsControl, that is almost purposely confusing.

 

[emina002]

MajP I used unbound controls yes, so the user can still update data when needed to the particular chosen person/fullname. I used tab controls because I have 3 forms to show off.sorry if I confused you that much "tabscontrols" is my main form.

Thank you for suggesting recordset anyway.

 

[MajP]

MajP I used unbound controls yes, so the user can still update data when needed to the particular chosen person/fullname.

Sorry, may be lost in translation but that makes no sense. This should be a bound form. This is a waste of time IMO. If you had to do this (and not suggesting you do) this is far simpler code

dim fullName as string
fullName = me.child15.form.fullName
fullName = "'" & fullName & "'"
If Me.TabCtl17.Value = 0 Then Then
  with me.Child23.form
    .Text52 = DLookUp("BMTDate","RiskFactor","FullName = " & FullName )
    .Combo58 = DLookUp("BMTPhysician","RiskFactor","FullName= " & FullName)
    .Combo62 = DLookUp("DRIGroup","RiskFactor","FullName=" & fullName)
  end with
end if

But if I had to do it unbound then return the record from the recordset

dim rs as dao.recordset
dim fullName as string
dim strSql as string

If Me.TabCtl17.Value = 0 Then Then
  fullName = me.child15.form.fullName
  strSql = "Select * from RiskFactor where FullName = '" & fullname & "'"
  set rs = currentdb.openrecordset(strSql)
  if not (rs.eof and rs.bof) then
     with me.Child23.form
       .TxtBMT_Date = DLookUp("BMTDate","RiskFactor","FullName = " & FullName )
       .txtBMT_Physician = DLookUp("BMTPhysician","RiskFactor","FullName= " & FullName)
       .txtDRI_Group = DLookUp("DRIGroup","RiskFactor","FullName=" & fullName)
       .....
     end with
   end if
end if
etc.

 

[emina002]

Sorry, may be lost in translation but that makes no sense. This should be a bound form. This is a waste of time IMO. If you had to do this (and not suggesting you do) this is far simpler code

dim fullName as string
fullName = me.child15.form.fullName
fullName = "'" & fullName & "'"
If Me.TabCtl17.Value = 0 Then Then
  with me.Child23.form
    .Text52 = DLookUp("BMTDate","RiskFactor","FullName = " & FullName )
    .Combo58 = DLookUp("BMTPhysician","RiskFactor","FullName= " & FullName)
    .Combo62 = DLookUp("DRIGroup","RiskFactor","FullName=" & fullName)
  end with
end if

But if I had to do it unbound then return the record from the recordset

dim rs as dao.recordset
dim fullName as string
dim strSql as string

If Me.TabCtl17.Value = 0 Then Then
  fullName = me.child15.form.fullName
  strSql = "Select * from RiskFactor where FullName = '" & fullname & "'"
  set rs = currentdb.openrecordset(strSql)
  if not (rs.eof and rs.bof) then
     with me.Child23.form
       .TxtBMT_Date = DLookUp("BMTDate","RiskFactor","FullName = " & FullName )
       .txtBMT_Physician = DLookUp("BMTPhysician","RiskFactor","FullName= " & FullName)
       .txtDRI_Group = DLookUp("DRIGroup","RiskFactor","FullName=" & fullName)
       .....
     end with
   end if
end if
etc.

@Majp, I did what you suggested, I get an error, error 3075 missing operator, and it will highliht a .txtbmt_date line.
I just uploaded a screenshot of my form, and set an explanation well, so any of you could picture out my problem about dlookup.

 

[Gasman]

Whenever you use strings as criteria, you must surround with single quotes

.TxtBMT_Date = DLookUp("BMTDate","RiskFactor","FullName = '" & FullName  & "'")

 

[June7]

Why open recordset if it isn't used? Data is in recordset so no need for DLookup().

.TxtBMT_Date = rs!BMTDate
.txtBMT_Physician = rs!BMTPhysician
.txtDRI_Group = rs!DRIGroup