DLOOKUP from a subform

[Robert_L_Ross]

Ok, these DLOOKUP's kill me every time.

I'm in a subform...when I choose a value from a drop down box, I want to
populate a few fields.

The drop down box source data is a SQL string:
"SELECT SCHOOLS.SCHOOL_CODE, SCHOOLS.SCHOOL_NAME FROM SCHOOLS; "
Basically, show the School Code and School Name from the Schools table, and
bind to the School Code.

I want a macro to run on the "On Change" event that will populate the School
Name, School Rep and School E-Mail.

I got stuck on the School Name. Here's my Macro:
Action:
SetValue
Item:
[Forms]![SCHOOL_INFORMATION]![SCHOOL_NAME]
Expression:
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[SCHOOLS]![SCHOOL_CODE])

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION"

I've tried to specify the main form and subform together in the Item field,
tried just using the field name, tried all kinds of fun stuff to the
expression and I can't get it to work.

Any ideas?

THX!

 

[Me]

Robert,

Try this -

DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=Forms!SCHOOL_INFORMATION![SCHOOL_CODE])

Thank you,
-Me

 

[Robert_L_Ross]

I get the following error:
"Microsoft Access can't find the form 'SCHOOL_INFORMATION' referred to in a
amacro expression or Visual Basic code."

Then I get the Action Failed dialog:
Macro Name: test
Condition: True
Action Name: Set Value
Arguments: [SCHOOL_NAME],
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[Forms]![SCHOOL_INFORMATION]![SCHOOL_CODE])

Any idea why this is failing?

Robert,

Try this -

DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=Forms!SCHOOL_INFORMATION![SCHOOL_CODE])

Thank you,
-Me

Ok, these DLOOKUP's kill me every time.

I'm in a subform...when I choose a value from a drop down box, I want to
populate a few fields.

The drop down box source data is a SQL string:
"SELECT SCHOOLS.SCHOOL_CODE, SCHOOLS.SCHOOL_NAME FROM SCHOOLS; "
Basically, show the School Code and School Name from the Schools table, and
bind to the School Code.

I want a macro to run on the "On Change" event that will populate the School
Name, School Rep and School E-Mail.

I got stuck on the School Name. Here's my Macro:
Action:
SetValue
Item:
[Forms]![SCHOOL_INFORMATION]![SCHOOL_NAME]
Expression:
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[SCHOOLS]![SCHOOL_CODE])

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION"

I've tried to specify the main form and subform together in the Item field,
tried just using the field name, tried all kinds of fun stuff to the
expression and I can't get it to work.

Any ideas?

THX!

 

[Douglas J Steele]

Do you have a form named SCHOOL_INFORMATION in your application? Is that
form open when the macro's running?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I get the following error:
"Microsoft Access can't find the form 'SCHOOL_INFORMATION' referred to in a
amacro expression or Visual Basic code."

Then I get the Action Failed dialog:
Macro Name: test
Condition: True
Action Name: Set Value
Arguments: [SCHOOL_NAME],
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[Forms]![SCHOOL_INFORMATION]![
SCHOOL_CODE])

Any idea why this is failing?

Robert,

Try this -

DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=Forms!SCHOOL_INFORMATION![SCHO
OL_CODE])

Thank you,
-Me

Ok, these DLOOKUP's kill me every time.

I'm in a subform...when I choose a value from a drop down box, I want to
populate a few fields.

The drop down box source data is a SQL string:
"SELECT SCHOOLS.SCHOOL_CODE, SCHOOLS.SCHOOL_NAME FROM SCHOOLS; "
Basically, show the School Code and School Name from the Schools table, and
bind to the School Code.

I want a macro to run on the "On Change" event that will populate the School
Name, School Rep and School E-Mail.

I got stuck on the School Name. Here's my Macro:
Action:
SetValue
Item:
[Forms]![SCHOOL_INFORMATION]![SCHOOL_NAME]
Expression:
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[SCHOOLS]![SCHOOL_CODE])

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION"

I've tried to specify the main form and subform together in the Item field,
tried just using the field name, tried all kinds of fun stuff to the
expression and I can't get it to work.

Any ideas?

THX!

 

[Robert_L_Ross]

Doug,

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION". The macro runs when you are on the subform while in
the main form...not sure how to verbalize it, but you open ApplicationMaster,
tab to the subform SCHOOL_INFORMATION and the macro runs when you select a
record from a drop down box within SCHOOL_INFORMATION.

THX for looking at this Doug.

Rob

Do you have a form named SCHOOL_INFORMATION in your application? Is that
form open when the macro's running?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I get the following error:
"Microsoft Access can't find the form 'SCHOOL_INFORMATION' referred to in a
amacro expression or Visual Basic code."

Then I get the Action Failed dialog:
Macro Name: test
Condition: True
Action Name: Set Value
Arguments: [SCHOOL_NAME],
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[Forms]![SCHOOL_INFORMATION]![
SCHOOL_CODE])

Any idea why this is failing?

Robert,

Try this -

DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=Forms!SCHOOL_INFORMATION![SCHO
OL_CODE])

Thank you,
-Me

:

Ok, these DLOOKUP's kill me every time.

I'm in a subform...when I choose a value from a drop down box, I want to
populate a few fields.

The drop down box source data is a SQL string:
"SELECT SCHOOLS.SCHOOL_CODE, SCHOOLS.SCHOOL_NAME FROM SCHOOLS; "
Basically, show the School Code and School Name from the Schools table, and
bind to the School Code.

I want a macro to run on the "On Change" event that will populate the School
Name, School Rep and School E-Mail.

I got stuck on the School Name. Here's my Macro:
Action:
SetValue
Item:
[Forms]![SCHOOL_INFORMATION]![SCHOOL_NAME]
Expression:
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[SCHOOLS]![SCHOOL_CODE])

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION"

I've tried to specify the main form and subform together in the Item field,
tried just using the field name, tried all kinds of fun stuff to the
expression and I can't get it to work.

Any ideas?

THX!

 

[Douglas J Steele]

Ah, that's the problem. You can't refer to the name of the form that's being
used as a subform: it's actually not in the Forms collection.

To refer to a field on a subform, you need to refer to the parent form's
subform container that contains the form being used as a subform. Note that
the name of the subform container may not be the same as the name of the
form being used as a subform. If you created the subform by dragging
SCHOOL_INFORMATION onto ApplicationMaster, then the subform container should
be the same. However, if you created the form/subform relationship by
dragging a subform container from the tool box onto ApplicationMaster, then
provided the necessary information (either manually or through the wizard),
the subform container will likely be named something like Child0 (where 0
could be any number)

Once you know the name of the subform container, change your existing
DLookup to

DLookUp("[SCHOOL_NAME]","SCHOOLS","[SCHOOL_ID]=[Forms]![ApplicationMaster]![
SubformContainerName].Form![ SCHOOL_CODE]")

(Replace "SubformContainerName" with the appropriate name)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION". The macro runs when you are on the subform while in
the main form...not sure how to verbalize it, but you open ApplicationMaster,
tab to the subform SCHOOL_INFORMATION and the macro runs when you select a
record from a drop down box within SCHOOL_INFORMATION.

THX for looking at this Doug.

Rob

Do you have a form named SCHOOL_INFORMATION in your application? Is that
form open when the macro's running?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I get the following error:
"Microsoft Access can't find the form 'SCHOOL_INFORMATION' referred to

in
a

amacro expression or Visual Basic code."

Then I get the Action Failed dialog:
Macro Name: test
Condition: True
Action Name: Set Value
Arguments: [SCHOOL_NAME],

DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[Forms]![SCHOOL_INFORMATION]![
SCHOOL_CODE])

Any idea why this is failing?

:

Robert,

Try this -

DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=Forms!SCHOOL_INFORMATION![SCHO
OL_CODE])

Thank you,
-Me

:

Ok, these DLOOKUP's kill me every time.

I'm in a subform...when I choose a value from a drop down box, I

want
to

populate a few fields.

The drop down box source data is a SQL string:
"SELECT SCHOOLS.SCHOOL_CODE, SCHOOLS.SCHOOL_NAME FROM SCHOOLS; "
Basically, show the School Code and School Name from the Schools table, and
bind to the School Code.

I want a macro to run on the "On Change" event that will populate

the
School

Name, School Rep and School E-Mail.

I got stuck on the School Name. Here's my Macro:
Action:
SetValue
Item:
[Forms]![SCHOOL_INFORMATION]![SCHOOL_NAME]
Expression:
DLookUp("[SCHOOL_NAME]","SCHOOLS",[SCHOOL_ID]=[SCHOOLS]![SCHOOL_CODE])

The main form is calld "ApplicationMaster", the subform is called
"SCHOOL_INFORMATION"

I've tried to specify the main form and subform together in the

Item
field,

tried just using the field name, tried all kinds of fun stuff to the
expression and I can't get it to work.

Any ideas?

THX!