DLookUp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am wanting to "display only" the key contact for an organisation. I have
used the expression below which works well but on occassions there is more
than one contact for an org. My question is - can I get the expression to
display multiple contacts OR how do I get a DLookUp query to search for the
2nd or 3rd etc contact?

TIA

=DLookUp("OrgContact","ContactsOrg","ContactID = " & [ContactID])
=DLookUp("OrgPosition","ContactsOrg","ContactID = " & [ContactID])
 
You can't.

You'll need to open a recordset and loop through it until you reach the end
of the recordset.

Dim rsContact As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrgContact, OrgPosition " & _
"FROM ContactsOrg " & _
"WHERE ContactID = " & ContactID
Set rsContact = CurrentDb.OpenRecordset(strSQL)
Do Until rsContact.EOF
Msgbox rsContact!OrgContact & " " & _
rsContact!OrgPosition
rsContact.MoveNext
Loop
rsContact.Close
 
Hi Douglas

Thanks for this. Still a novice here, so I need a bit more guidance please.

The code you've written, what event do I assign it to in my text box?

--
Sue Compelling


Douglas J. Steele said:
You can't.

You'll need to open a recordset and loop through it until you reach the end
of the recordset.

Dim rsContact As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrgContact, OrgPosition " & _
"FROM ContactsOrg " & _
"WHERE ContactID = " & ContactID
Set rsContact = CurrentDb.OpenRecordset(strSQL)
Do Until rsContact.EOF
Msgbox rsContact!OrgContact & " " & _
rsContact!OrgPosition
rsContact.MoveNext
Loop
rsContact.Close

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sue Compelling said:
Hi

I am wanting to "display only" the key contact for an organisation. I
have
used the expression below which works well but on occassions there is more
than one contact for an org. My question is - can I get the expression to
display multiple contacts OR how do I get a DLookUp query to search for
the
2nd or 3rd etc contact?

TIA

=DLookUp("OrgContact","ContactsOrg","ContactID = " & [ContactID])
=DLookUp("OrgPosition","ContactsOrg","ContactID = " & [ContactID])
Click to expand...
Click to expand...
 
Without knowing how your form is constructed, it's a little difficult to
say. The form's Current event might be a reasonable place, though.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sue Compelling said:
Hi Douglas

Thanks for this. Still a novice here, so I need a bit more guidance
please.

The code you've written, what event do I assign it to in my text box?

--
Sue Compelling


Douglas J. Steele said:
You can't.

You'll need to open a recordset and loop through it until you reach the
end
of the recordset.

Dim rsContact As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrgContact, OrgPosition " & _
"FROM ContactsOrg " & _
"WHERE ContactID = " & ContactID
Set rsContact = CurrentDb.OpenRecordset(strSQL)
Do Until rsContact.EOF
Msgbox rsContact!OrgContact & " " & _
rsContact!OrgPosition
rsContact.MoveNext
Loop
rsContact.Close

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi

I am wanting to "display only" the key contact for an organisation. I
have
used the expression below which works well but on occassions there is
more
than one contact for an org. My question is - can I get the expression
to
display multiple contacts OR how do I get a DLookUp query to search for
the
2nd or 3rd etc contact?

TIA

=DLookUp("OrgContact","ContactsOrg","ContactID = " & [ContactID])
=DLookUp("OrgPosition","ContactsOrg","ContactID = " & [ContactID])
Click to expand...
Click to expand...
Click to expand...
 
Thanks Douglas for your advice - I chose to simply display it as a subform in
the end.
--
Sue Compelling


Douglas J. Steele said:
Without knowing how your form is constructed, it's a little difficult to
say. The form's Current event might be a reasonable place, though.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Sue Compelling said:
Hi Douglas

Thanks for this. Still a novice here, so I need a bit more guidance
please.

The code you've written, what event do I assign it to in my text box?

--
Sue Compelling


Douglas J. Steele said:
You can't.

You'll need to open a recordset and loop through it until you reach the
end
of the recordset.

Dim rsContact As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrgContact, OrgPosition " & _
"FROM ContactsOrg " & _
"WHERE ContactID = " & ContactID
Set rsContact = CurrentDb.OpenRecordset(strSQL)
Do Until rsContact.EOF
Msgbox rsContact!OrgContact & " " & _
rsContact!OrgPosition
rsContact.MoveNext
Loop
rsContact.Close

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi

I am wanting to "display only" the key contact for an organisation. I
have
used the expression below which works well but on occassions there is
more
than one contact for an org. My question is - can I get the expression
to
display multiple contacts OR how do I get a DLookUp query to search for
the
2nd or 3rd etc contact?

TIA

=DLookUp("OrgContact","ContactsOrg","ContactID = " & [ContactID])
=DLookUp("OrgPosition","ContactsOrg","ContactID = " & [ContactID])
Click to expand...
Click to expand...
Click to expand...
 
Back
Top