dlookup

  • Thread starter Thread starter Bill H.
  • Start date Start date
B

Bill H.

I have a field on a subform showing a prikey. I want another field on the
form that uses that prikey to lookup a name from a query.

I have this set to the name's data source:

= DLookup("[Name]", "Qry_FamilyMember_Attendance_select", "[RFS_ID_NO] = "
& Forms!Frm_FamilyMember_Attendance_subform!RFS_ID_NO_Combo)

but of course it does not work.

Where did I go wrogn?
 
so your setup is: you have a main form open. there is a subform on the main
form. there is an unbound textbox control on the main form, that you want to
display a value returned by a DLookup() function. the DLookup() function
uses a value from a control on the subform in its' criteria argument.

correct so far?

if yes, your problem is in the syntax of the criteria argument - and
probably also in the subform control name you're using. a subform sits in a
*control* on the main form (like putting an object in a box). that control
has a name, which may be different from the name of the subform object in
the database window. so let's get the correct subform control name first. to
do this, open the main form in design view. click once on the subform
(within the main form design view) to select it. in the Properties box,
click on the Other tab, and look at the Name property. that's the name of
your subform control.

to show you the correct syntax, i'll call your subform control
"ChildAttendance" and your main form "MainFormName" (you'll have to
substitute the correct names when you type the expression in your unbound
textbox control's ControlSource).

=DLookup("[Name]", "Qry_FamilyMember_Attendance_select", "[RFS_ID_NO] = " &
Forms!MainFormName!ChildAttendance.Form!RFS_ID_NO_Combo)

btw, if you have a field in your table called "Name", i recommend you call
it something else. "Name" is a Reserved word in Access, and it should not be
used as the name of anything you create in the database.

hth
 
Bill,

It is incorrect to refer to Forms!Frm_FamilyMember_Attendance_subform.
This form is not open, but simply displayed via the medium of the main
form. But in any case, you are referring to the value of a control on
the same subform, so any reference to a form is not applicable.

Try it like this...
=DLookup("[Name]","Qry_FamilyMember_Attendance_select","[RFS_ID_NO]=" &
[RFS_ID_NO_Combo])

Make sure that the name of the textbox is not 'Name', i.e. the same as
the field you are looking up and/or the same as a field in the form's
record source.

In fact, it would be a good idea to also change the name of the Name
field in the table/query, as 'name' is a Reserved Word (i.e. has a
special meaning) in Access, and as such should not be used as the name
of a field or control. But this is a side issue, and in this case is
unlikely to be related to your problem.

There may be a simpler way of achieving what you are aiming at. Have a
look at http://accesstips.datamanagementsolutions.biz/lookup.mht
 
Yeah I discovered that.

In a related question, how WOULD I refer to a control on a subform displayed
on a parent form?


Steve Schapel said:
Bill,

It is incorrect to refer to Forms!Frm_FamilyMember_Attendance_subform.
This form is not open, but simply displayed via the medium of the main
form. But in any case, you are referring to the value of a control on
the same subform, so any reference to a form is not applicable.

Try it like this...
=DLookup("[Name]","Qry_FamilyMember_Attendance_select","[RFS_ID_NO]=" &
[RFS_ID_NO_Combo])

Make sure that the name of the textbox is not 'Name', i.e. the same as
the field you are looking up and/or the same as a field in the form's
record source.

In fact, it would be a good idea to also change the name of the Name
field in the table/query, as 'name' is a Reserved Word (i.e. has a
special meaning) in Access, and as such should not be used as the name
of a field or control. But this is a side issue, and in this case is
unlikely to be related to your problem.

There may be a simpler way of achieving what you are aiming at. Have a
look at http://accesstips.datamanagementsolutions.biz/lookup.mht

--
Steve Schapel, Microsoft Access MVP

I have a field on a subform showing a prikey. I want another field on the
form that uses that prikey to lookup a name from a query.

I have this set to the name's data source:

= DLookup("[Name]", "Qry_FamilyMember_Attendance_select", "[RFS_ID_NO] = "
& Forms!Frm_FamilyMember_Attendance_subform!RFS_ID_NO_Combo)

but of course it does not work.

Where did I go wrogn?
Click to expand...
Click to expand...
 
Bill,

This works:
[Forms]![NameOfParentForm]![NameOfSubformControl]![NameOfControl]

.... but local wisdom has it that this is preferable...
[Forms]![NameOfParentForm]![NameOfSubformControl].[Form]![NameOfControl]
 
guess I'm missing the boat. :-(

I want to set the rowsource (of a combo box) on subform1 from the main
parentform equal to strSQL



Steve Schapel said:
Bill,

This works:
[Forms]![NameOfParentForm]![NameOfSubformControl]![NameOfControl]

... but local wisdom has it that this is preferable...
[Forms]![NameOfParentForm]![NameOfSubformControl].[Form]![NameOfControl]

--
Steve Schapel, Microsoft Access MVP
Yeah I discovered that.

In a related question, how WOULD I refer to a control on a subform displayed
on a parent form?
Click to expand...
Click to expand...
 
Bill,

Well, that would probably be something like this...
Me.Subform1.Form!YourCombobox.RowSource = strSQL

If this doesn't work, please post back with more complete details...
remember we can't see your computer from here!
 
I think that got it!

I get confused as to when to use the bang and when to use the dot. :-(
 
Bill,

Following is an extract from a newsgroup posting by Access MVP Dirk
Goldgar, which I hope you will find interesting:

-------------
Courtesy Dirk Goldgar...

It's not so much a question of one or the other being "proper syntax", but
that they mean different things that nevertheless almost always give the
same result. As I understand it, the bang (!) notation specifically denotes
that what follows is a member of a collection; in this case, a member of
the form object's default collection, the Controls collection. The dot (.)
notation denotes that what follows is a property or method of the preceding
object. That would logically make the bang notation "proper" and the dot
notation improper.

But wait. Wherever possible, Access makes the controls on a form and the
fields in its recordsource all available as properties of the form. It also
makes the fields of the recordsource available via the bang notation. I'm
not sure exactly how it does this; maybe if a name is not found in the
Controls collection it checks the Fields collection of the form's recordset
as a fallback position. So for most practical purposes Me!ControlName and
Me.ControlName evaluate to the same thing, and the timing tests I've seen
suggest that there is little to choose between them as far as execution
efficiency is concerned. I seem to recall that there is a very slight
difference, but I can't remember which way the advantage lies, and it's not
much. There's a coding-time advantage, however, to using the dot notation,
as it makes the "intellisense" dropdown lists available. That's a strong
argument for using the dot notation, in my book.

But wait again! I said above that Access makes the controls available as
properties "wherever possible". There are cases where it can't do that.
Specifically, it can't do it when there is already a property of the same
name as the control in question. For example, if your form "Form1" has a
control or a field foolishly named "Name", currently displaying the value
"Ken Snell", then executing this statement in the form's code module:

Debug.Print Me!Name, Me.Name

will print

Ken Snell Form1

in the Immediate Window. So you must be careful not to use any reserved
words or built-in properties as names for your controls, if you want to use
the dot notation to refer to them. But then, you should avoid doing that
anyway, as it tends in general to confuse poor Access.
 
Back
Top