Type Mismatch Error

[sara]

I am getting "Type Mismatch Error" when the following code executes. I
am trying to notify the user if she attempts to add a customer with the
same FirstName, LastName, Address(line1) and City as one that already
exists on the file.

If there is a match, ask the user and cancel the record add if she sees
that it's a dup customer.

I had trouble with the line continuations, quotes and continuation
chracters, and noticed that Access added a " on the top line for me.

There are no dimensions for any of these fields; this is the only place
they are used.
' Makes sure the user entered a new customer before saving

Private Sub cmdClose_Click( )
On Error GoTo Err_cmdClose_Cl ick

' to save new cust so they can add garments

Dim lngCustID As Long

' saves the new customer record in tblCustomers

' Makes sure the user entered a new customer before saving

If Me.Dirty = True Then
lngCustID = Me.txtCustID
DoCmd.RunComman d acCmdSaveRecord
' Requery the customer list to see the new customer as well
Forms!frmFindCu stomer.lstCusto mers.Requery
Forms!frmFindCu stomer.lstCusto mers = lngCustID
End If

DoCmd.Close

Exit_cmdClose_C lick:
Exit Sub

Err_cmdClose_Cl ick:

' If the user enters the same first name, last name, street1 and
city as one on file,
' check to see if this is truly a different customer (dup index)
' of if this was an error. Allow save if different; cancel if error

' If response is NOT Yes, Me.undo and get out (recognize it's a
dup-do NOT add)
' If response is YES, add record

If Err.Number = 3022 Then
If MsgBox("Custome r " & Me.txtFirstName & " " &
Me.txtLastName, "" _
& " " & Me.txtAddress1 & " " & Me.txtCity & " exists. "
_
& " Are you sure you want to add this one too?" _
& vbYesNoCancel + vbExclamation, "Duplicate Customer") <>
vbYes Then
Me.Undo
GoTo Exit_cmdClose_C lick
End If
End If
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdClose_C lick

End Sub

Many thanks.
Sara

[Smartin]

sara wrote:

I am getting "Type Mismatch Error" when the following code executes. I
am trying to notify the user if she attempts to add a customer with the
same FirstName, LastName, Address(line1) and City as one that already
exists on the file.

If there is a match, ask the user and cancel the record add if she sees
that it's a dup customer.

I had trouble with the line continuations, quotes and continuation
chracters, and noticed that Access added a " on the top line for me.

There are no dimensions for any of these fields; this is the only place
they are used.
' Makes sure the user entered a new customer before saving

Private Sub cmdClose_Click( )
On Error GoTo Err_cmdClose_Cl ick

' to save new cust so they can add garments

Dim lngCustID As Long

' saves the new customer record in tblCustomers

' Makes sure the user entered a new customer before saving

If Me.Dirty = True Then
lngCustID = Me.txtCustID
DoCmd.RunComman d acCmdSaveRecord
' Requery the customer list to see the new customer as well
Forms!frmFindCu stomer.lstCusto mers.Requery
Forms!frmFindCu stomer.lstCusto mers = lngCustID
End If

DoCmd.Close

Exit_cmdClose_C lick:
Exit Sub

Err_cmdClose_Cl ick:

' If the user enters the same first name, last name, street1 and
city as one on file,
' check to see if this is truly a different customer (dup index)
' of if this was an error. Allow save if different; cancel if error

' If response is NOT Yes, Me.undo and get out (recognize it's a
dup-do NOT add)
' If response is YES, add record

If Err.Number = 3022 Then
If MsgBox("Custome r " & Me.txtFirstName & " " &
Me.txtLastName, "" _
& " " & Me.txtAddress1 & " " & Me.txtCity & " exists. "
_
& " Are you sure you want to add this one too?" _
& vbYesNoCancel + vbExclamation, "Duplicate Customer") <>
vbYes Then
Me.Undo
GoTo Exit_cmdClose_C lick
End If
End If
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdClose_C lick

End Sub

Many thanks.
Sara

Which line is throwing the error?

--
Smartin

[sara]

Sorry - the If MsgBox statement is highlighted in the debugger.

Sara

[Wayne Morgan]

If MsgBox("Custome r " & Me.txtFirstName & " " &
Me.txtLastName, "" _
& " " & Me.txtAddress1 & " " & Me.txtCity & " exists. "
_
& " Are you sure you want to add this one too?" _
& vbYesNoCancel + vbExclamation, "Duplicate Customer") <>
vbYes Then

Is this the If MsgBox you are referring to? If so, there is a comma after
Me.txtLastName and no & before the quotes. I don't see where the quotes
there help much, I removed them and moved the comma inside the first set of
quotes on the next line. I moved everything to the left margin so hopefully
the newsreader won't wrap the lines.

Also, there is an & instead of a comma before the vbYesNoCancel to indicate
that this is the next argument.

If MsgBox("Custome r " & Me.txtFirstName & " " & Me.txtLastName _
& ", " & Me.txtAddress1 & " " & Me.txtCity & " exists. " _
& " Are you sure you want to add this one too?", _
vbYesNoCancel + vbExclamation, "Duplicate Customer") <> vbYes Then
--
Wayne Morgan
MS Access MVP
"sara" <sa*******@yaho o.com> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.com.. .

Sorry - the If MsgBox statement is highlighted in the debugger.

Sara

[Smartin]

sara wrote:

Sorry - the If MsgBox statement is highlighted in the debugger.

Sara

If MsgBox("Custome r " & Me.txtFirstName & " " &
Me.txtLastName, "" _
& " " & Me.txtAddress1 & " " & Me.txtCity & " exists. "
_
& " Are you sure you want to add this one too?" _
& vbYesNoCancel + vbExclamation, "Duplicate Customer") <>
vbYes Then
Me.Undo
GoTo Exit_cmdClose_C lick
End If

OK here's something to look at: you have a comma on the second line.
This separates the MsgBox prompt from the buttons. I don't think you
meant to do that there. Undoubtedly this is where the Then you are
lacking a comma to separate what looks like the prompt from the buttons.

To keep things straight I would suggest building up a few string
arguments to pass to the MsgBox function, ala

Dim sPrompt as String
Dim iButtons as Integer
Dim sTitle as String

sPrompt = "Customer " & Me.foo & " bar"
iButtons = vbYesNoCancel Or vbExclamation
sTitle = "Duplicate"

If MsgBox(sPrompt, iButtons, sTitle) <> vbYes Then

etc.

HTH
--
Smartin

[Smartin]

Smartin wrote:

sara wrote:

Sorry - the If MsgBox statement is highlighted in the debugger.

Sara

If MsgBox("Custome r " & Me.txtFirstName & " " &
Me.txtLastName, "" _
& " " & Me.txtAddress1 & " " & Me.txtCity & " exists. "
_
& " Are you sure you want to add this one too?" _
& vbYesNoCancel + vbExclamation, "Duplicate Customer") <>
vbYes Then
Me.Undo
GoTo Exit_cmdClose_C lick
End If

OK here's something to look at: you have a comma on the second line.
This separates the MsgBox prompt from the buttons. I don't think you
meant to do that there. Undoubtedly this is where the

oops... meant to say "Undoubtedl y this is where the error is cropping up".
Then you are
lacking a comma to separate what looks like the prompt from the buttons.

To keep things straight I would suggest building up a few string
arguments to pass to the MsgBox function, ala

Dim sPrompt as String
Dim iButtons as Integer
Dim sTitle as String

sPrompt = "Customer " & Me.foo & " bar"
iButtons = vbYesNoCancel Or vbExclamation
sTitle = "Duplicate"

If MsgBox(sPrompt, iButtons, sTitle) <> vbYes Then

etc.

HTH

--
Smartin

[David W. Fenton]

"sara" <sa*******@yaho o.com> wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.com:

If Me.Dirty = True Then
lngCustID = Me.txtCustID
DoCmd.RunComman d acCmdSaveRecord

Your question has actually been answered later, but I'd thought I'd
make a ocmment on codeing style.

First, since Me.Dirty is a property of the form and returns a
Boolean value, you don't need to compare it to True:

If Me.Dirty Then

Last of all, rather counterintuitiv ely (or so it has always seemed
to me), the .Dirty property of a form is actually not read only. If
you set it to False, it saves the form. So, the way I'd code the
snippet above is:

If Me.Dirty Then
lngCustID = Me!txtCustID
Me.Dirty = False

I won't discuss here the fact that I use ! instaed of . for
references to controls -- that's a long thread that we just had a
couple of weeks ago!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[John Mishefske]

David W. Fenton wrote:

"sara" <sa*******@yaho o.com> wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.com:

If Me.Dirty = True Then
lngCustID = Me.txtCustID
DoCmd.RunComman d acCmdSaveRecord

Your question has actually been answered later, but I'd thought I'd
make a ocmment on codeing style.

First, since Me.Dirty is a property of the form and returns a
Boolean value, you don't need to compare it to True:

If Me.Dirty Then

Last of all, rather counterintuitiv ely (or so it has always seemed
to me), the .Dirty property of a form is actually not read only. If
you set it to False, it saves the form. So, the way I'd code the
snippet above is:

If Me.Dirty Then
lngCustID = Me!txtCustID
Me.Dirty = False

I won't discuss here the fact that I use ! instaed of . for
references to controls -- that's a long thread that we just had a
couple of weeks ago!

And to expand on David's response - you want to use Me.Dirty = False because it is very
explicit about what it is going to do. It will save a record on the current form (Me).

Using DoCmd.RunComman d acCmdSaveRecord will save a record on whatever form has the current
focus. That might not be the form you expect it to be. There is no ambiguity if you use
"Me" so using Me.Dirty = False will prevent the rare occasion where another object gets
the focus and you save a record on a form you didn't expect.

The DoCmd.Close is a similar potential landmine. Without any parameters it closes whatever
has the current focus. Its much better to be very explicit that you want to close the form
that the code is running in by using:

DoCmd.Close acForm, Me.Name

Good luck on your project.

--
'---------------
'John Mishefske
'---------------

[lylefair]

John Mishefske wrote:

And to expand on David's response - you want to use Me.Dirty = False because it is very
explicit about what it is going to do. It will save a record on the current form (Me).

Using DoCmd.RunComman d acCmdSaveRecord will save a record on whatever form has the current
focus. That might not be the form you expect it to be.
Good luck on your project.

Could you explain this further, please? Sara's "DoCmd.RunComma nd
acCmdSaveRecord " appears in a Private Sub (Private Sub
cmdClose_Click( )) seeiminglt in a Form Module in this context:

If Me.Dirty = True Then
lngCustID = Me.txtCustID
DoCmd.RunComman d acCmdSaveRecord

In what situations will this command run when the form in which it
resides (the Me form) does not have the focus?

What is the code in an Object other than the Form1 module to call
Form1's cmdClose_Click( )?
What is similar code to do so while Form2 or other form has the focus?

[sara]

Thank you - it works!

I think my confustion is around when to use the & on a continuation
line and when not to. Is there anything on the web on this? I have
looked around in Help and the web and the 3 books I have and found
nothing.

I have had to use the continuation character AND the & AND quotes at
the end of each line and the beginning of the next when building a SQL
string (like "Where" clause) in code. It appears, however that the
extra ampersand and quotes are only needed when doing continuations of
text that should be in quotes - or something like that?

The comma was my error.
Thanks
Sara