Type Mismatch

H

hughess7

Hi all

I am trying to open a popup form based on the 'key' of the main form by
putting these variables into a where condition...

stLinkCriteria = ([Review Date] = Format(dtReview, "\#mm\/dd\/yyyy\#")) And
"([Country Code] = '" & txtCountry & "') AND ([Dealer_Code] = '" & txtDealer
& "') AND ([NE Claim No] = '" & txtClaim & "')"

I get type mismatch error? All are text data types apart from Review Date
which is a date variable.

P.S. For ref I don't want a subform as I want it to appear as a popup to the
user.

Thanks in advance for any help.
Sue
 
J

John Spencer MVP

Perhaps what you want it the following

stLinkCriteria = "[Review Date] = " & Format(dtReview,"\#mm\/dd\/yyyy\#") & _
" And [Country Code] = '" & txtCountry & "'" & _
" AND [Dealer_Code] = '" & txtDealer & "'" & _
" AND [NE Claim No] = '" & txtClaim & "'"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
J

Jeff Boyce

Sue

Are all of those fields "text" type data, or is there a chance that [NE
Claim No] field is numeric? If it is numeric, you would need to compare it
to a numeric value, not a string value.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
H

hughess7

Thanks John, that did it :)

One other thing I am struggling with, if a record does not exist I need to
pass the variables from the master to this popup form as they are the key. If
I use Me!fieldname it doesn't work, but if I put forms!nameofform!fieldname
it does work. I can't use this though as the form name changes each time a
different issue is used. Is there a way round this, I am not sure why 'Me'
doesn't work as the form should have the focus...

This is the code I have at the moment

For x = 0 To mydb.Containers("forms").Documents.Count - 1
If FormToOpen = mydb.Containers("forms").Documents(x).Name Then
If DCount("*", "[Dealer Claim Check Table Data]", stLinkCriteria) =
0 Then
DoCmd.OpenForm FormToOpen
Me![Country Code] = txtCountry
Me![DEALER_CODE] = txtDealer
Me![Review Date] = dtReview
Me![NE Claim No] = txtClaim
Else
DoCmd.OpenForm FormToOpen, , , stLinkCriteria
End If
End If
Next x

Thanks in advance for any help.
Sue


John Spencer MVP said:
Perhaps what you want it the following

stLinkCriteria = "[Review Date] = " & Format(dtReview,"\#mm\/dd\/yyyy\#") & _
" And [Country Code] = '" & txtCountry & "'" & _
" AND [Dealer_Code] = '" & txtDealer & "'" & _
" AND [NE Claim No] = '" & txtClaim & "'"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi all

I am trying to open a popup form based on the 'key' of the main form by
putting these variables into a where condition...

stLinkCriteria = ([Review Date] = Format(dtReview, "\#mm\/dd\/yyyy\#")) And
"([Country Code] = '" & txtCountry & "') AND ([Dealer_Code] = '" & txtDealer
& "') AND ([NE Claim No] = '" & txtClaim & "')"

I get type mismatch error? All are text data types apart from Review Date
which is a date variable.

P.S. For ref I don't want a subform as I want it to appear as a popup to the
user.

Thanks in advance for any help.
Sue
Click to expand...
Click to expand...
 
J

John Spencer MVP

Me refers to the current object (form or report normally). The current object
is whatever is running your code.

I think (I don't completely understand what you are doing) that what you want
is to reference controls on the form you have just opened. You can reference
an open form with
Forms("NameOfForm") so your code might look like:

For x = 0 To mydb.Containers("forms").Documents.Count - 1
If FormToOpen = mydb.Containers("forms").Documents(x).Name Then
If DCount("*","[Dealer Claim Check Table Data]",stLinkCriteria)= 0 Then
DoCmd.OpenForm FormToOpen
Forms(FormToOpen).[Country Code] = txtCountry
Forms(FormToOpen).[DEALER_CODE] = txtDealer
Forms(FormToOpen).[Review Date] = dtReview
Forms(FormToOpen).[NE Claim No] = txtClaim
Else
DoCmd.OpenForm FormToOpen, , , stLinkCriteria
End If
End If
Next x

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, that did it :)

One other thing I am struggling with, if a record does not exist I need to
pass the variables from the master to this popup form as they are the key. If
I use Me!fieldname it doesn't work, but if I put forms!nameofform!fieldname
it does work. I can't use this though as the form name changes each time a
different issue is used. Is there a way round this, I am not sure why 'Me'
doesn't work as the form should have the focus...

This is the code I have at the moment

For x = 0 To mydb.Containers("forms").Documents.Count - 1
If FormToOpen = mydb.Containers("forms").Documents(x).Name Then
If DCount("*", "[Dealer Claim Check Table Data]", stLinkCriteria) =
0 Then
DoCmd.OpenForm FormToOpen
Me![Country Code] = txtCountry
Me![DEALER_CODE] = txtDealer
Me![Review Date] = dtReview
Me![NE Claim No] = txtClaim
Else
DoCmd.OpenForm FormToOpen, , , stLinkCriteria
End If
End If
Next x

Thanks in advance for any help.
Sue


John Spencer MVP said:
Perhaps what you want it the following

stLinkCriteria = "[Review Date] = " & Format(dtReview,"\#mm\/dd\/yyyy\#") & _
" And [Country Code] = '" & txtCountry & "'" & _
" AND [Dealer_Code] = '" & txtDealer & "'" & _
" AND [NE Claim No] = '" & txtClaim & "'"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi all

I am trying to open a popup form based on the 'key' of the main form by
putting these variables into a where condition...

stLinkCriteria = ([Review Date] = Format(dtReview, "\#mm\/dd\/yyyy\#")) And
"([Country Code] = '" & txtCountry & "') AND ([Dealer_Code] = '" & txtDealer
& "') AND ([NE Claim No] = '" & txtClaim & "')"

I get type mismatch error? All are text data types apart from Review Date
which is a date variable.

P.S. For ref I don't want a subform as I want it to appear as a popup to the
user.

Thanks in advance for any help.
Sue
Click to expand...
Click to expand...
Click to expand...
 
H

hughess7

Thanks John, I had tried forms!formtoopen!fieldname but it didn't like that.
Now using this code i get a different error: 2456 the number you used to
refer to the form is invalid.

I am trying to get the form to act as a subform to the main form, but I want
it in a popup format to retain focus until the user has enter all the
required data and then it closes and updates data from the main form.


John Spencer MVP said:
Me refers to the current object (form or report normally). The current object
is whatever is running your code.

I think (I don't completely understand what you are doing) that what you want
is to reference controls on the form you have just opened. You can reference
an open form with
Forms("NameOfForm") so your code might look like:

For x = 0 To mydb.Containers("forms").Documents.Count - 1
If FormToOpen = mydb.Containers("forms").Documents(x).Name Then
If DCount("*","[Dealer Claim Check Table Data]",stLinkCriteria)= 0 Then
DoCmd.OpenForm FormToOpen
Forms(FormToOpen).[Country Code] = txtCountry
Forms(FormToOpen).[DEALER_CODE] = txtDealer
Forms(FormToOpen).[Review Date] = dtReview
Forms(FormToOpen).[NE Claim No] = txtClaim
Else
DoCmd.OpenForm FormToOpen, , , stLinkCriteria
End If
End If
Next x

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Thanks John, that did it :)

One other thing I am struggling with, if a record does not exist I need to
pass the variables from the master to this popup form as they are the key. If
I use Me!fieldname it doesn't work, but if I put forms!nameofform!fieldname
it does work. I can't use this though as the form name changes each time a
different issue is used. Is there a way round this, I am not sure why 'Me'
doesn't work as the form should have the focus...

This is the code I have at the moment

For x = 0 To mydb.Containers("forms").Documents.Count - 1
If FormToOpen = mydb.Containers("forms").Documents(x).Name Then
If DCount("*", "[Dealer Claim Check Table Data]", stLinkCriteria) =
0 Then
DoCmd.OpenForm FormToOpen
Me![Country Code] = txtCountry
Me![DEALER_CODE] = txtDealer
Me![Review Date] = dtReview
Me![NE Claim No] = txtClaim
Else
DoCmd.OpenForm FormToOpen, , , stLinkCriteria
End If
End If
Next x

Thanks in advance for any help.
Sue


John Spencer MVP said:
Perhaps what you want it the following

stLinkCriteria = "[Review Date] = " & Format(dtReview,"\#mm\/dd\/yyyy\#") & _
" And [Country Code] = '" & txtCountry & "'" & _
" AND [Dealer_Code] = '" & txtDealer & "'" & _
" AND [NE Claim No] = '" & txtClaim & "'"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

hughess7 wrote:
Hi all

I am trying to open a popup form based on the 'key' of the main form by
putting these variables into a where condition...

stLinkCriteria = ([Review Date] = Format(dtReview, "\#mm\/dd\/yyyy\#")) And
"([Country Code] = '" & txtCountry & "') AND ([Dealer_Code] = '" & txtDealer
& "') AND ([NE Claim No] = '" & txtClaim & "')"

I get type mismatch error? All are text data types apart from Review Date
which is a date variable.

P.S. For ref I don't want a subform as I want it to appear as a popup to the
user.

Thanks in advance for any help.
Sue
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DCount criteria multiple variables 2
Selecting data 2
2nd highest 4
Creating data via code 12
data type mismatch when using dates 6
type mismatch error 13 1
Problems displaying dates 5
Dlookup error 13 7

Top