DLookUp syntax

[AccessMan]

I have a [One] table with two key fields, [x] and [y]. [One] has a
one-to-many relationship to [Many], the latter having foreign key fields [x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two foreign key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select values for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify the way that
the user creates new records. I would like them to select one record from an
unbound combo box control whose row source is a query that concatenates the
two key fields of table [One] into one field, then use VBA associated with
the BeforeUpdate event of this combo box to update the form controls for [x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" & combo box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" & combo box
contents)

I'm not sure that frmMany!x is the correct syntax, and I definitely don't
understand the syntax for the criteris field. I know this probably isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[Douglas J. Steele]

If the combo box is named x and is on form frmMany, you refer to it as
Form!frmMany!x. If you're trying to refer to it from the module associated
with form frmMany, you can shortcut that to Me!x

 

[AccessMan]

Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the DLookUp
statement.

If the combo box is named x and is on form frmMany, you refer to it as
Form!frmMany!x. If you're trying to refer to it from the module associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a [One] table with two key fields, [x] and [y]. [One] has a
one-to-many relationship to [Many], the latter having foreign key fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two foreign key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select values for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify the way
that
the user creates new records. I would like them to select one record from
an
unbound combo box control whose row source is a query that concatenates
the
two key fields of table [One] into one field, then use VBA associated with
the BeforeUpdate event of this combo box to update the form controls for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" & combo box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" & combo box
contents)

I'm not sure that frmMany!x is the correct syntax, and I definitely don't
understand the syntax for the criteris field. I know this probably isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[Douglas J. Steele]

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting the x and
y values from the combo boxes, aren't you? Why, then, do you think it's
necessary to do DLookups against the query to get values for x and y? Isn't
the value that's supposed to be in Forms!frmMany!x simply the value that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value of "1,
2". You could, of course, do a DLookup on the concatenation of the two combo
box values, but why bother? The third argument of the DLookup statement is a
valid Where clause without the word Where in it. In other words, you could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query (qry One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the DLookUp
statement.

If the combo box is named x and is on form frmMany, you refer to it as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a [One] table with two key fields, [x] and [y]. [One] has a
one-to-many relationship to [Many], the latter having foreign key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two foreign key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify the way
that
the user creates new records. I would like them to select one record
from
an
unbound combo box control whose row source is a query that concatenates
the
two key fields of table [One] into one field, then use VBA associated
with
the BeforeUpdate event of this combo box to update the form controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" & combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" & combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I definitely
don't
understand the syntax for the criteris field. I know this probably
isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[AccessMan]

No problem.

This is a simplified example. My true problem has three (or more) key
fields in the [One] table, thus it is a pain for the user to have to
individually select each of these in combo boxes when creating a record in
the [Many] table. I want them to select the concatenated string of key
fields in one combo box, then use VBA to apply the individual key values to
the foreign key fields. (I don't really need individual combo box controls
on the form for the key fields in this case.) The nature of my data also
makes it very advantageous to select the concatenation.

So, I want to set Many.x to ...

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)

and set Many.y similarly. It's the rules for the syntax of ...

"[xy] =" & combo box contents

that I need to understand. Of course, I'd be happy enough knowing how it
should read for this particulat problem!

Thanks!!!

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting the x and
y values from the combo boxes, aren't you? Why, then, do you think it's
necessary to do DLookups against the query to get values for x and y? Isn't
the value that's supposed to be in Forms!frmMany!x simply the value that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value of "1,
2". You could, of course, do a DLookup on the concatenation of the two combo
box values, but why bother? The third argument of the DLookup statement is a
valid Where clause without the word Where in it. In other words, you could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query (qry One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the DLookUp
statement.

If the combo box is named x and is on form frmMany, you refer to it as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a [One] table with two key fields, [x] and [y]. [One] has a
one-to-many relationship to [Many], the latter having foreign key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two foreign key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify the way
that
the user creates new records. I would like them to select one record
from
an
unbound combo box control whose row source is a query that concatenates
the
two key fields of table [One] into one field, then use VBA associated
with
the BeforeUpdate event of this combo box to update the form controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" & combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" & combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I definitely
don't
understand the syntax for the criteris field. I know this probably
isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[Douglas J. Steele]

I think there are several simpler options.

First would be to take advantage of the fact that a combo box can contain
multiple columns.

For example, the RowSource of the combo box could be:

SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM One;

Make sure the ColumnCount property set to 3, even if you don't want all
three columns to appear in the combo box (you can play with the ColumnWidths
property to hide the second and third columns.

Once they selected the row of interest, put code in the combo box's
AfterUpdate event:

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = Me.MyCombo.Column(1)
Forms!frmMany!y = Me.MyCombo.Column(2)

End Sub

(Note that the Column collection starts numbering at 0, so 1 is the second
column)

A second option would be to only display the concatenated fields in the
combo box, and then use the Split function to separated the concatenated
value into its component parts.

Private Sub MyCombo_AfterUpdate()

Dim varValues As Variant

varValues = Split(Me.MyCombo, ",")

Forms!frmMany!x = varValues(0)
Forms!frmMany!y = varValues(1)

End Sub


If you insist on using DLookup, remember that the concatenated field is
text, so you must have quotes around the value you're looking up

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = DLookUp ("[x]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")
Forms!frmMany!y = DLookUp ("[y]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")

End Sub

You also have the option of using a form/subform model.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

No problem.

This is a simplified example. My true problem has three (or more) key
fields in the [One] table, thus it is a pain for the user to have to
individually select each of these in combo boxes when creating a record in
the [Many] table. I want them to select the concatenated string of key
fields in one combo box, then use VBA to apply the individual key values
to
the foreign key fields. (I don't really need individual combo box
controls
on the form for the key fields in this case.) The nature of my data also
makes it very advantageous to select the concatenation.

So, I want to set Many.x to ...

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)

and set Many.y similarly. It's the rules for the syntax of ...

"[xy] =" & combo box contents

that I need to understand. Of course, I'd be happy enough knowing how it
should read for this particulat problem!

Thanks!!!

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting the x
and
y values from the combo boxes, aren't you? Why, then, do you think it's
necessary to do DLookups against the query to get values for x and y?
Isn't
the value that's supposed to be in Forms!frmMany!x simply the value
that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value of "1,
2". You could, of course, do a DLookup on the concatenation of the two
combo
box values, but why bother? The third argument of the DLookup statement
is a
valid Where clause without the word Where in it. In other words, you
could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query (qry
One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the
DLookUp
statement.


:

If the combo box is named x and is on form frmMany, you refer to it as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a [One] table with two key fields, [x] and [y]. [One] has a
one-to-many relationship to [Many], the latter having foreign key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two foreign
key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select
values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify the
way
that
the user creates new records. I would like them to select one
record
from
an
unbound combo box control whose row source is a query that
concatenates
the
two key fields of table [One] into one field, then use VBA
associated
with
the BeforeUpdate event of this combo box to update the form controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the
syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the
DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x, One.y,
FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" & combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" & combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I definitely
don't
understand the syntax for the criteris field. I know this probably
isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[AccessMan]

Thanks for the multiple alternative suggestions - all have merit and have
taught me something, but the DLookUp method is still my preference (mostly
for maintainability reasons). I have it working now as well! Part of my
problem was an extra space in the criteria clause.

One follow up question though. Instead of referring to the x control on the
form using Forms!frmMany!x, can I use something like Me!Parent!x ? I'd like
to avoid having to refer to frmMany explicitly and would rather refer to it
indireclty as the form that contains MyCombo.

Thanks!

I think there are several simpler options.

First would be to take advantage of the fact that a combo box can contain
multiple columns.

For example, the RowSource of the combo box could be:

SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM One;

Make sure the ColumnCount property set to 3, even if you don't want all
three columns to appear in the combo box (you can play with the ColumnWidths
property to hide the second and third columns.

Once they selected the row of interest, put code in the combo box's
AfterUpdate event:

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = Me.MyCombo.Column(1)
Forms!frmMany!y = Me.MyCombo.Column(2)

End Sub

(Note that the Column collection starts numbering at 0, so 1 is the second
column)

A second option would be to only display the concatenated fields in the
combo box, and then use the Split function to separated the concatenated
value into its component parts.

Private Sub MyCombo_AfterUpdate()

Dim varValues As Variant

varValues = Split(Me.MyCombo, ",")

Forms!frmMany!x = varValues(0)
Forms!frmMany!y = varValues(1)

End Sub


If you insist on using DLookup, remember that the concatenated field is
text, so you must have quotes around the value you're looking up

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = DLookUp ("[x]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")
Forms!frmMany!y = DLookUp ("[y]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")

End Sub

You also have the option of using a form/subform model.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

No problem.

This is a simplified example. My true problem has three (or more) key
fields in the [One] table, thus it is a pain for the user to have to
individually select each of these in combo boxes when creating a record in
the [Many] table. I want them to select the concatenated string of key
fields in one combo box, then use VBA to apply the individual key values
to
the foreign key fields. (I don't really need individual combo box
controls
on the form for the key fields in this case.) The nature of my data also
makes it very advantageous to select the concatenation.

So, I want to set Many.x to ...

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)

and set Many.y similarly. It's the rules for the syntax of ...

"[xy] =" & combo box contents

that I need to understand. Of course, I'd be happy enough knowing how it
should read for this particulat problem!

Thanks!!!

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting the x
and
y values from the combo boxes, aren't you? Why, then, do you think it's
necessary to do DLookups against the query to get values for x and y?
Isn't
the value that's supposed to be in Forms!frmMany!x simply the value
that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value of "1,
2". You could, of course, do a DLookup on the concatenation of the two
combo
box values, but why bother? The third argument of the DLookup statement
is a
valid Where clause without the word Where in it. In other words, you
could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query (qry
One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the
DLookUp
statement.


:

If the combo box is named x and is on form frmMany, you refer to it as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a [One] table with two key fields, [x] and [y]. [One] has a
one-to-many relationship to [Many], the latter having foreign key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two foreign
key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select
values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify the
way
that
the user creates new records. I would like them to select one
record
from
an
unbound combo box control whose row source is a query that
concatenates
the
two key fields of table [One] into one field, then use VBA
associated
with
the BeforeUpdate event of this combo box to update the form controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the
syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the
DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x, One.y,
FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" & combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" & combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I definitely
don't
understand the syntax for the criteris field. I know this probably
isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[Douglas J. Steele]

The only reason I used Forms!frmMain was because you didn't give any details
about your forms.

If you're running this code in the module associated with a subform and you
want to refer to controls on the parent form, yes, you can use
Me.Parent!NameOfControl (note it's a dot in Me.Parent: Parent is a property
of the form)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the multiple alternative suggestions - all have merit and have
taught me something, but the DLookUp method is still my preference (mostly
for maintainability reasons). I have it working now as well! Part of my
problem was an extra space in the criteria clause.

One follow up question though. Instead of referring to the x control on
the
form using Forms!frmMany!x, can I use something like Me!Parent!x ? I'd
like
to avoid having to refer to frmMany explicitly and would rather refer to
it
indireclty as the form that contains MyCombo.

Thanks!

I think there are several simpler options.

First would be to take advantage of the fact that a combo box can contain
multiple columns.

For example, the RowSource of the combo box could be:

SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM One;

Make sure the ColumnCount property set to 3, even if you don't want all
three columns to appear in the combo box (you can play with the
ColumnWidths
property to hide the second and third columns.

Once they selected the row of interest, put code in the combo box's
AfterUpdate event:

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = Me.MyCombo.Column(1)
Forms!frmMany!y = Me.MyCombo.Column(2)

End Sub

(Note that the Column collection starts numbering at 0, so 1 is the
second
column)

A second option would be to only display the concatenated fields in the
combo box, and then use the Split function to separated the concatenated
value into its component parts.

Private Sub MyCombo_AfterUpdate()

Dim varValues As Variant

varValues = Split(Me.MyCombo, ",")

Forms!frmMany!x = varValues(0)
Forms!frmMany!y = varValues(1)

End Sub


If you insist on using DLookup, remember that the concatenated field is
text, so you must have quotes around the value you're looking up

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = DLookUp ("[x]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")
Forms!frmMany!y = DLookUp ("[y]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")

End Sub

You also have the option of using a form/subform model.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

No problem.

This is a simplified example. My true problem has three (or more) key
fields in the [One] table, thus it is a pain for the user to have to
individually select each of these in combo boxes when creating a record
in
the [Many] table. I want them to select the concatenated string of key
fields in one combo box, then use VBA to apply the individual key
values
to
the foreign key fields. (I don't really need individual combo box
controls
on the form for the key fields in this case.) The nature of my data
also
makes it very advantageous to select the concatenation.

So, I want to set Many.x to ...

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box
contents)

and set Many.y similarly. It's the rules for the syntax of ...

"[xy] =" & combo box contents

that I need to understand. Of course, I'd be happy enough knowing how
it
should read for this particulat problem!

Thanks!!!

:

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting the
x
and
y values from the combo boxes, aren't you? Why, then, do you think
it's
necessary to do DLookups against the query to get values for x and y?
Isn't
the value that's supposed to be in Forms!frmMany!x simply the value
that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value of
"1,
2". You could, of course, do a DLookup on the concatenation of the two
combo
box values, but why bother? The third argument of the DLookup
statement
is a
valid Where clause without the word Where in it. In other words, you
could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query (qry
One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box
contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the
DLookUp
statement.


:

If the combo box is named x and is on form frmMany, you refer to it
as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a [One] table with two key fields, [x] and [y]. [One] has
a
one-to-many relationship to [Many], the latter having foreign key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two
foreign
key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select
values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify
the
way
that
the user creates new records. I would like them to select one
record
from
an
unbound combo box control whose row source is a query that
concatenates
the
two key fields of table [One] into one field, then use VBA
associated
with
the BeforeUpdate event of this combo box to update the form
controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the
syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the
DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x,
One.y,
FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" &
combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" &
combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I
definitely
don't
understand the syntax for the criteris field. I know this
probably
isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[AccessMan]

Doug, your advice has helped me immensely! Thanks again!

The only reason I used Forms!frmMain was because you didn't give any details
about your forms.

If you're running this code in the module associated with a subform and you
want to refer to controls on the parent form, yes, you can use
Me.Parent!NameOfControl (note it's a dot in Me.Parent: Parent is a property
of the form)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the multiple alternative suggestions - all have merit and have
taught me something, but the DLookUp method is still my preference (mostly
for maintainability reasons). I have it working now as well! Part of my
problem was an extra space in the criteria clause.

One follow up question though. Instead of referring to the x control on
the
form using Forms!frmMany!x, can I use something like Me!Parent!x ? I'd
like
to avoid having to refer to frmMany explicitly and would rather refer to
it
indireclty as the form that contains MyCombo.

Thanks!

I think there are several simpler options.

First would be to take advantage of the fact that a combo box can contain
multiple columns.

For example, the RowSource of the combo box could be:

SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM One;

Make sure the ColumnCount property set to 3, even if you don't want all
three columns to appear in the combo box (you can play with the
ColumnWidths
property to hide the second and third columns.

Once they selected the row of interest, put code in the combo box's
AfterUpdate event:

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = Me.MyCombo.Column(1)
Forms!frmMany!y = Me.MyCombo.Column(2)

End Sub

(Note that the Column collection starts numbering at 0, so 1 is the
second
column)

A second option would be to only display the concatenated fields in the
combo box, and then use the Split function to separated the concatenated
value into its component parts.

Private Sub MyCombo_AfterUpdate()

Dim varValues As Variant

varValues = Split(Me.MyCombo, ",")

Forms!frmMany!x = varValues(0)
Forms!frmMany!y = varValues(1)

End Sub


If you insist on using DLookup, remember that the concatenated field is
text, so you must have quotes around the value you're looking up

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = DLookUp ("[x]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")
Forms!frmMany!y = DLookUp ("[y]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")

End Sub

You also have the option of using a form/subform model.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


No problem.

This is a simplified example. My true problem has three (or more) key
fields in the [One] table, thus it is a pain for the user to have to
individually select each of these in combo boxes when creating a record
in
the [Many] table. I want them to select the concatenated string of key
fields in one combo box, then use VBA to apply the individual key
values
to
the foreign key fields. (I don't really need individual combo box
controls
on the form for the key fields in this case.) The nature of my data
also
makes it very advantageous to select the concatenation.

So, I want to set Many.x to ...

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box
contents)

and set Many.y similarly. It's the rules for the syntax of ...

"[xy] =" & combo box contents

that I need to understand. Of course, I'd be happy enough knowing how
it
should read for this particulat problem!

Thanks!!!

:

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting the
x
and
y values from the combo boxes, aren't you? Why, then, do you think
it's
necessary to do DLookups against the query to get values for x and y?
Isn't
the value that's supposed to be in Forms!frmMany!x simply the value
that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value of
"1,
2". You could, of course, do a DLookup on the concatenation of the two
combo
box values, but why bother? The third argument of the DLookup
statement
is a
valid Where clause without the word Where in it. In other words, you
could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query (qry
One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box
contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the
DLookUp
statement.


:

If the combo box is named x and is on form frmMany, you refer to it
as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a [One] table with two key fields, [x] and [y]. [One] has
a
one-to-many relationship to [Many], the latter having foreign key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two
foreign
key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must select
values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to modify
the
way
that
the user creates new records. I would like them to select one
record
from
an
unbound combo box control whose row source is a query that
concatenates
the
two key fields of table [One] into one field, then use VBA
associated
with
the BeforeUpdate event of this combo box to update the form
controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get the
syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the
DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x,
One.y,
FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" &
combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" &
combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I
definitely
don't
understand the syntax for the criteris field. I know this
probably
isn't
that hard, but I'm very frustrated trying to understand the HELP.

 

[Douglas J. Steele]

You're welcome. I'd just like to point out that I think you're making a
mistake using DLookup. It's completely unnecessary to requery the table when
you can easily include that data in your combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug, your advice has helped me immensely! Thanks again!

The only reason I used Forms!frmMain was because you didn't give any
details
about your forms.

If you're running this code in the module associated with a subform and
you
want to refer to controls on the parent form, yes, you can use
Me.Parent!NameOfControl (note it's a dot in Me.Parent: Parent is a
property
of the form)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the multiple alternative suggestions - all have merit and
have
taught me something, but the DLookUp method is still my preference
(mostly
for maintainability reasons). I have it working now as well! Part of
my
problem was an extra space in the criteria clause.

One follow up question though. Instead of referring to the x control
on
the
form using Forms!frmMany!x, can I use something like Me!Parent!x ? I'd
like
to avoid having to refer to frmMany explicitly and would rather refer
to
it
indireclty as the form that contains MyCombo.

Thanks!

:

I think there are several simpler options.

First would be to take advantage of the fact that a combo box can
contain
multiple columns.

For example, the RowSource of the combo box could be:

SELECT [x] & ", " & [y] AS xy, One.x, One.y, FROM One;

Make sure the ColumnCount property set to 3, even if you don't want
all
three columns to appear in the combo box (you can play with the
ColumnWidths
property to hide the second and third columns.

Once they selected the row of interest, put code in the combo box's
AfterUpdate event:

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = Me.MyCombo.Column(1)
Forms!frmMany!y = Me.MyCombo.Column(2)

End Sub

(Note that the Column collection starts numbering at 0, so 1 is the
second
column)

A second option would be to only display the concatenated fields in
the
combo box, and then use the Split function to separated the
concatenated
value into its component parts.

Private Sub MyCombo_AfterUpdate()

Dim varValues As Variant

varValues = Split(Me.MyCombo, ",")

Forms!frmMany!x = varValues(0)
Forms!frmMany!y = varValues(1)

End Sub


If you insist on using DLookup, remember that the concatenated field
is
text, so you must have quotes around the value you're looking up

Private Sub MyCombo_AfterUpdate()

Forms!frmMany!x = DLookUp ("[x]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")
Forms!frmMany!y = DLookUp ("[y]", _
"[qry One Concatenated]", _
"[xy] ='" & Me!MyCombo & "'")

End Sub

You also have the option of using a form/subform model.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


No problem.

This is a simplified example. My true problem has three (or more)
key
fields in the [One] table, thus it is a pain for the user to have to
individually select each of these in combo boxes when creating a
record
in
the [Many] table. I want them to select the concatenated string of
key
fields in one combo box, then use VBA to apply the individual key
values
to
the foreign key fields. (I don't really need individual combo box
controls
on the form for the key fields in this case.) The nature of my data
also
makes it very advantageous to select the concatenation.

So, I want to set Many.x to ...

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box
contents)

and set Many.y similarly. It's the rules for the syntax of ...

"[xy] =" & combo box contents

that I need to understand. Of course, I'd be happy enough knowing
how
it
should read for this particulat problem!

Thanks!!!

:

Sorry, guess I didn't read it all that carefully.

I don't really understand what you're trying to do. You're getting
the
x
and
y values from the combo boxes, aren't you? Why, then, do you think
it's
necessary to do DLookups against the query to get values for x and
y?
Isn't
the value that's supposed to be in Forms!frmMany!x simply the value
that's
in the first combo box (and the value that's supposed to be in
Forms!frmMany!y the value that's in the second combo box)?

If x has a value of 1 and y has a value of 2, xy will have a value
of
"1,
2". You could, of course, do a DLookup on the concatenation of the
two
combo
box values, but why bother? The third argument of the DLookup
statement
is a
valid Where clause without the word Where in it. In other words,
you
could
use

"[xy] = " & Forms![NameOfForm]![NameOfFirstCombo] & ", " &
Forms![NameOfForm]![NameOfSecondCombo]

but it makes far more sense just to use

"[x] = " & Forms![NameOfForm]![NameOfFirstCombo] & " And [y] = " &
Forms![NameOfForm]![NameOfSecondCombo]

A minor point. Since you've put spaces into the name of the query
(qry
One
Concatenated), you need square brackets in the DLookups:

DLookUp ("[x]", "[qry One Concatenated]", "[xy] =" & combo box
contents)



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks again Doug! That solves an important part of my issue.

I'm still befuddled about how to handle the criteria part of the
DLookUp
statement.


:

If the combo box is named x and is on form frmMany, you refer to
it
as
Form!frmMany!x. If you're trying to refer to it from the module
associated
with form frmMany, you can shortcut that to Me!x

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
I have a [One] table with two key fields, [x] and [y]. [One]
has
a
one-to-many relationship to [Many], the latter having foreign
key
fields
[x]
and [y] and another key field [z].

Form [frmMany] has separate combo box controls for the two
foreign
key
fields [x] and [y] and a text box for the third key field [z].

When creating a new record with [frmMany], the user must
select
values
for
[x] and [y], and then enter a value for [z].

Based on a great suggestion from this group, I'd like to
modify
the
way
that
the user creates new records. I would like them to select one
record
from
an
unbound combo box control whose row source is a query that
concatenates
the
two key fields of table [One] into one field, then use VBA
associated
with
the BeforeUpdate event of this combo box to update the form
controls
for
[x]
and [y] to the appropriate values.

My problem is with the DLookUp function in VBA. I can't get
the
syntax
right, and HELP is not helpful enough.

Here's the sql for the concatenation query and a start on the
DLookUp
statements from the combo box event VBA.

qry One Concatenated: SELECT [x] & ", " & [y] AS xy, One.x,
One.y,
FROM
One;

frmMany!x = DLookUp ("[x]", "qry One Concatenated", "[xy] =" &
combo
box
contents)

frmMany!y = DLookUp ("[y]", "qry One Concatenated", "[xy] =" &
combo
box
contents)

I'm not sure that frmMany!x is the correct syntax, and I
definitely
don't
understand the syntax for the criteris field. I know this
probably
isn't
that hard, but I'm very frustrated trying to understand the
HELP.