Multi-field Primary Key Question

[Guest]

I have read the posts here that seem to deal with multiple primary keys and
haven't seen one that addresses my problem. I have a data base that has a
tblUsers table and a tblFamilyMembers table. In the Users table the Primary
Key is CustomerID and the table consists of name, address, and phone number.
The CustomerId is auto-number - the Address field is text. The Family member
table has the CustomerID and Address from the Users table as the primary key
along with the name of the person and their relation to the Customer name in
the Users table. The CustomerID field in the FamilyMembers table is number
and the Address field is text. I have a form that I have command button on to
open the Family Members form to enter the name and relationship. The Customer
field is the name of the text box field that I'm using as a look-up for the
Customer. However, I keep getting a 'type mismatch' error when I execute the
following:

Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFamilyMembersSubForm"

stLinkCriteria = "[CustomerID]=" & Str(Nz(Me![Customer], 0)) And
"[CustomerAddress]=" & Me![Address]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

Any help would be greatly appreciated.

Thanks,
Randy M

 

[Rick Brandt]

I have read the posts here that seem to deal with multiple primary
keys and haven't seen one that addresses my problem. I have a data
base that has a tblUsers table and a tblFamilyMembers table. In the
Users table the Primary Key is CustomerID and the table consists of
name, address, and phone number. The CustomerId is auto-number - the
Address field is text. The Family member table has the CustomerID and
Address from the Users table as the primary key along with the name
of the person and their relation to the Customer name in the Users
table. The CustomerID field in the FamilyMembers table is number and
the Address field is text. I have a form that I have command button
on to open the Family Members form to enter the name and
relationship. The Customer field is the name of the text box field
that I'm using as a look-up for the Customer. However, I keep getting
a 'type mismatch' error when I execute the following:

Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFamilyMembersSubForm"

stLinkCriteria = "[CustomerID]=" & Str(Nz(Me![Customer], 0)) And
"[CustomerAddress]=" & Me![Address]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

If CustomerID is numeric why are you wrapping Str() around your value? That
makes it a string and thus a mismatched "type".

 

[Douglas J Steele]

While Rick's correct that the Str function seems unnecessary, another, more
urgent, error is the fact that the And is outside of the string. As well,
presumably CustomerAddress is a text field, which means that whatever is
being passed to the expression needs to be wrapped in quotes. (Chr$(34) is
")


stLinkCriteria = "[CustomerID]=" & Nz(Me![Customer], 0) & " And
[CustomerAddress]=" & Chr$(34) & Me![Address] & Chr$(34)

If CustomerID is a text field, not a numeric field, use the Chr$(34) on
either side of that value as well.

 

[Guest]

Rick,
I'm not sure to be honest. I'm new to Access and VBA and am still working
my way through. Somebody clued me in on somethings and one of the things they
did was to set-up a look-up on a field for me. This was what they used and
it worked. "[CustomerID]=" & Str(Nz(Me![Customer], 0)) So, I've used it in a
few other places as well and haven't had a problem withit. I think it's
because I'm using a multi-field primary key and am not sure how to refer to
it. I found help document on 'Multiple Fields in Criteria Expressions' and it
used the AND - I thought it might work for me, but it doesn't seem to. Not
sure what's the problem, but persistence pays off so I'll keep at it until I
find out. Thanks again for answering

I have read the posts here that seem to deal with multiple primary
keys and haven't seen one that addresses my problem. I have a data
base that has a tblUsers table and a tblFamilyMembers table. In the
Users table the Primary Key is CustomerID and the table consists of
name, address, and phone number. The CustomerId is auto-number - the
Address field is text. The Family member table has the CustomerID and
Address from the Users table as the primary key along with the name
of the person and their relation to the Customer name in the Users
table. The CustomerID field in the FamilyMembers table is number and
the Address field is text. I have a form that I have command button
on to open the Family Members form to enter the name and
relationship. The Customer field is the name of the text box field
that I'm using as a look-up for the Customer. However, I keep getting
a 'type mismatch' error when I execute the following:

Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFamilyMembersSubForm"

stLinkCriteria = "[CustomerID]=" & Str(Nz(Me![Customer], 0)) And
"[CustomerAddress]=" & Me![Address]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

If CustomerID is numeric why are you wrapping Str() around your value? That
makes it a string and thus a mismatched "type".

 

[Guest]

Douglas,
Thanks for the help, but unfortunately that didn't fix my problem. Of
course, it's possible that there's something I should know that is obvious,
except I'm new to Access and VBA and it might not be obvious to me. Anyhoo, I
tried the changes you suggested and now I'm getting a 'syntax error', instead
of the 'type mix' one. Is there a way when you get a generic error message
like this to find out what it's referring to with a little more explicitness?
Thanks again, Randy M

While Rick's correct that the Str function seems unnecessary, another, more
urgent, error is the fact that the And is outside of the string. As well,
presumably CustomerAddress is a text field, which means that whatever is
being passed to the expression needs to be wrapped in quotes. (Chr$(34) is
")


stLinkCriteria = "[CustomerID]=" & Nz(Me![Customer], 0) & " And
[CustomerAddress]=" & Chr$(34) & Me![Address] & Chr$(34)

If CustomerID is a text field, not a numeric field, use the Chr$(34) on
either side of that value as well.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have read the posts here that seem to deal with multiple primary keys and
haven't seen one that addresses my problem. I have a data base that has a
tblUsers table and a tblFamilyMembers table. In the Users table the Primary
Key is CustomerID and the table consists of name, address, and phone number.
The CustomerId is auto-number - the Address field is text. The Family member
table has the CustomerID and Address from the Users table as the primary key
along with the name of the person and their relation to the Customer name in
the Users table. The CustomerID field in the FamilyMembers table is number
and the Address field is text. I have a form that I have command button on to
open the Family Members form to enter the name and relationship. The Customer
field is the name of the text box field that I'm using as a look-up for the
Customer. However, I keep getting a 'type mismatch' error when I execute the
following:

Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFamilyMembersSubForm"

stLinkCriteria = "[CustomerID]=" & Str(Nz(Me![Customer], 0)) And
"[CustomerAddress]=" & Me![Address]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

Any help would be greatly appreciated.

Thanks,
Randy M

 

[Douglas J Steele]

Show your new code, in case you mistyped something.

Also, what values do Me![Customer and Me![Address] contain when you're
getting this error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,
Thanks for the help, but unfortunately that didn't fix my problem. Of
course, it's possible that there's something I should know that is obvious,
except I'm new to Access and VBA and it might not be obvious to me. Anyhoo, I
tried the changes you suggested and now I'm getting a 'syntax error', instead
of the 'type mix' one. Is there a way when you get a generic error message
like this to find out what it's referring to with a little more explicitness?
Thanks again, Randy M

While Rick's correct that the Str function seems unnecessary, another, more
urgent, error is the fact that the And is outside of the string. As well,
presumably CustomerAddress is a text field, which means that whatever is
being passed to the expression needs to be wrapped in quotes. (Chr$(34) is
")


stLinkCriteria = "[CustomerID]=" & Nz(Me![Customer], 0) & " And
[CustomerAddress]=" & Chr$(34) & Me![Address] & Chr$(34)

If CustomerID is a text field, not a numeric field, use the Chr$(34) on
either side of that value as well.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have read the posts here that seem to deal with multiple primary

keys
and

haven't seen one that addresses my problem. I have a data base that has a
tblUsers table and a tblFamilyMembers table. In the Users table the Primary
Key is CustomerID and the table consists of name, address, and phone number.
The CustomerId is auto-number - the Address field is text. The Family member
table has the CustomerID and Address from the Users table as the

primary
key

along with the name of the person and their relation to the Customer

name
in

the Users table. The CustomerID field in the FamilyMembers table is number
and the Address field is text. I have a form that I have command

button on
to

open the Family Members form to enter the name and relationship. The Customer
field is the name of the text box field that I'm using as a look-up

for
the

Customer. However, I keep getting a 'type mismatch' error when I

execute
the

following:

Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFamilyMembersSubForm"

stLinkCriteria = "[CustomerID]=" & Str(Nz(Me![Customer], 0)) And
"[CustomerAddress]=" & Me![Address]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

Any help would be greatly appreciated.

Thanks,
Randy M

 

[Guest]

Douglas,
I think I figured out my problem...at least, I've got it working now. I
added the table name to the field names and that seemed to fix it. Where I
was using [CustomerID], I started using [tblFamilyMember.CustomerID] and
viola it worked. I don't know if that's the best way to do it or not, but
it's working. As I said before, I'm new to Access and VBA, but I'm working my
way through it, slowly, but I'm getting there. Thanks again for your help.

Randy M

Show your new code, in case you mistyped something.

Also, what values do Me![Customer and Me![Address] contain when you're
getting this error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,
Thanks for the help, but unfortunately that didn't fix my problem. Of
course, it's possible that there's something I should know that is obvious,
except I'm new to Access and VBA and it might not be obvious to me. Anyhoo, I
tried the changes you suggested and now I'm getting a 'syntax error', instead
of the 'type mix' one. Is there a way when you get a generic error message
like this to find out what it's referring to with a little more explicitness?
Thanks again, Randy M

While Rick's correct that the Str function seems unnecessary, another, more
urgent, error is the fact that the And is outside of the string. As well,
presumably CustomerAddress is a text field, which means that whatever is
being passed to the expression needs to be wrapped in quotes. (Chr$(34) is
")


stLinkCriteria = "[CustomerID]=" & Nz(Me![Customer], 0) & " And
[CustomerAddress]=" & Chr$(34) & Me![Address] & Chr$(34)

If CustomerID is a text field, not a numeric field, use the Chr$(34) on
either side of that value as well.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have read the posts here that seem to deal with multiple primary keys
and
haven't seen one that addresses my problem. I have a data base that has a
tblUsers table and a tblFamilyMembers table. In the Users table the
Primary
Key is CustomerID and the table consists of name, address, and phone
number.
The CustomerId is auto-number - the Address field is text. The Family
member
table has the CustomerID and Address from the Users table as the primary
key
along with the name of the person and their relation to the Customer name
in
the Users table. The CustomerID field in the FamilyMembers table is number
and the Address field is text. I have a form that I have command button on
to
open the Family Members form to enter the name and relationship. The
Customer
field is the name of the text box field that I'm using as a look-up for
the
Customer. However, I keep getting a 'type mismatch' error when I execute
the
following:

Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmFamilyMembersSubForm"

stLinkCriteria = "[CustomerID]=" & Str(Nz(Me![Customer], 0)) And
"[CustomerAddress]=" & Me![Address]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

Any help would be greatly appreciated.

Thanks,
Randy M