Lookup record in a different databse then current

[Rob Hamlin]

I have a database with projects in it. I have another database with contacts
in it.

The project database contains a field [PM] with a name for a value

The same name is in the contacts database with phone, email and other
associtaed information.

In Access 2007 on my form I have a bound field to [PM]. I want to fill in
the values of other unbound fileds with phone numbers and email address from
the Contacts database.

Can this be done?

 

[Jeff Boyce]

Rob

I'm not clear whether you are talking about separate databases (files) or
separate tables within a single database file.

If you are saying that you are trying to connect data from two separate
tables (one or two dbs) by the name of the Project Manager, won't this be
problematic as soon as you have two John Smiths as PMs?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Daniel Pineault]

Why not link the contact table from your other db into your current db and
then you could display the relevant information as required.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Rob Hamlin]

Thanks Daniel,

I had the other table from the contacts DB linked into my current DB.

I used this code to find what I was loooing for.

Dim cont As String
cont = DPM.Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Contacts Extended")
rs.MoveFirst
Do While Not rs.EOF
If rs.Fields("Contact Name") = cont Then
DPM_Phone.Caption = rs.Fields("Business Phone")
GoTo Done
End If
rs.MoveNext
Loop

Done:
rs.Close
Set rs = Nothing
Set db = Nothing


Is there a more efficient way of doing this besides looping through every
record?

Why not link the contact table from your other db into your current db and
then you could display the relevant information as required.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

I have a database with projects in it. I have another database with contacts
in it.

The project database contains a field [PM] with a name for a value

The same name is in the contacts database with phone, email and other
associtaed information.

In Access 2007 on my form I have a bound field to [PM]. I want to fill in
the values of other unbound fileds with phone numbers and email address from
the Contacts database.

Can this be done?

 

[Daniel Pineault]

I'm not sure that I entirely follow your scenario, but as far as the vba code
is concerned you could refine it a bit by doing something more along the
lines of

Dim cont As String
cont = DPM.Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Business Phone] FROM Contacts
Extended WHERE [Contact Name] ='" & cont & "'")
if rs.RecordCount<>0 Then
rs.MoveFirst
DPM_Phone.Caption = rs.Fields("Business Phone")
Else
'There are no matching records, now what?!
End If

Done:
rs.Close
Set rs = Nothing
Set db = Nothing



What happens in the event that more than 1 record fit the bill?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

Thanks Daniel,

I had the other table from the contacts DB linked into my current DB.

I used this code to find what I was loooing for.

Dim cont As String
cont = DPM.Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Contacts Extended")
rs.MoveFirst
Do While Not rs.EOF
If rs.Fields("Contact Name") = cont Then
DPM_Phone.Caption = rs.Fields("Business Phone")
GoTo Done
End If
rs.MoveNext
Loop

Done:
rs.Close
Set rs = Nothing
Set db = Nothing


Is there a more efficient way of doing this besides looping through every
record?

Why not link the contact table from your other db into your current db and
then you could display the relevant information as required.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

I have a database with projects in it. I have another database with contacts
in it.

The project database contains a field [PM] with a name for a value

The same name is in the contacts database with phone, email and other
associtaed information.

In Access 2007 on my form I have a bound field to [PM]. I want to fill in
the values of other unbound fileds with phone numbers and email address from
the Contacts database.

Can this be done?

 

[Rob Hamlin]

I tired you revised code and reveived an error on the following line.


Set rs = db.OpenRecordset("SELECT [Business Phone] FROM Contacts
Extended WHERE [Contact Name] ='" & cont & "'")

Run-Time error '3601': Too Few Parameters. Expected 1.

Any ideas?

I have no duplicates in that field.

I'm not sure that I entirely follow your scenario, but as far as the vba code
is concerned you could refine it a bit by doing something more along the
lines of

Dim cont As String
cont = DPM.Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [Business Phone] FROM Contacts
Extended WHERE [Contact Name] ='" & cont & "'")
if rs.RecordCount<>0 Then
rs.MoveFirst
DPM_Phone.Caption = rs.Fields("Business Phone")
Else
'There are no matching records, now what?!
End If

Done:
rs.Close
Set rs = Nothing
Set db = Nothing



What happens in the event that more than 1 record fit the bill?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

Thanks Daniel,

I had the other table from the contacts DB linked into my current DB.

I used this code to find what I was loooing for.

Dim cont As String
cont = DPM.Value
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Contacts Extended")
rs.MoveFirst
Do While Not rs.EOF
If rs.Fields("Contact Name") = cont Then
DPM_Phone.Caption = rs.Fields("Business Phone")
GoTo Done
End If
rs.MoveNext
Loop

Done:
rs.Close
Set rs = Nothing
Set db = Nothing


Is there a more efficient way of doing this besides looping through every
record?

Why not link the contact table from your other db into your current db and
then you could display the relevant information as required.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



:

I have a database with projects in it. I have another database with contacts
in it.

The project database contains a field [PM] with a name for a value

The same name is in the contacts database with phone, email and other
associtaed information.

In Access 2007 on my form I have a bound field to [PM]. I want to fill in
the values of other unbound fileds with phone numbers and email address from
the Contacts database.

Can this be done?