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

[sara]

Thanks. I totally understand and will work on this (along with other
coding hints) right now to "upgrade" my style.

I am so very happy to receive tips and help as there is no one I can
ask at work (I am the only "coder" in the company) and my user group
meets only once per month!

Sara

[Bob Quintal]

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

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

The continuation flag _ must come at every line in a statement
except the last one. It must be outside a string.

The concatenation operator & must come between any two strings.

So if you have to break a string into two parts to use the
continuation flag, you need an & character.and this must be
followed by a quotation mark ", or by a string variable.

You don't use the concatenation operator if it precedes an
Access keyword.

Any SQL statement is always a string, and nothing but a string,
there you always need the concatenation operator.

You may find it a lot easier if you were to Dim and build a
string variable, and put that in the messagebox statement.
Dim stMsg as string

stMsg = "Customer " & Me.txtFirstName & " " & Me.txtLastName"
stMsg = stMsg & " " & Me.txtAddress1 & " " & Me.txtCity
stmsg = stmsg & " exists. "
stmsg = stMsg & " Are you sure you want to add this one too?"

If MsgBox( stMsg, _
vbYesNoCancel + vbExclamation, _
"Duplicate Customer") _
<> vbYes Then

You dont need a & before "Duplicate customer", because you
aren't breaking a string into parts.

Once you have the stMsg working as multiple statements you can
simply replace the stMsg = stMsg with a _ ot the end of the line
above.

HTH..

--
Bob Quintal

PA is y I've altered my email address.

[Bri]

lylefair wrote:

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?

If you are debugging this code, then the active window is the code
module not the form so you get the "acCmdSaveRecor d is not available
now" error. Of course, at runtime this isn't a problem.

--
Bri

[sara]

OK. I'm getting this and maybe even going to get good at it. I have
tried to build a SQL string and run it in my code, to change the
customer status to "I" (Inactive) along with all the items that belong
to that customer, if my user says she wants to Delete a customer.
(She's never used a computer before, is nervous, and may delete in
error).

My query runs, so I tried to convert the query SQL into a string and
run it.

Query Code:
UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON tblCustomers.Cu stID =
tblCustomerItem s.CustID SET tblCustomers.Cu stStatus = "I",
tblCustomerItem s.ItemStatus = "I"
WHERE (((tblCustomers .CustID)=[Forms]![frmCust]![txtCustID]));

MyCode:
strWhere = " WHERE tblCustomers.Cu stID = " &
Forms!frmCust!t xtCustID
strInactive = "I"
strSQL = "UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON " _
& " tblCustomers.Cu stID = tblCustomerItem s.CustID " _
& "SET tblCustomers.Cu stStatus = " & strInactive & "," _
& " tblCustomerItem s.ItemStatus = " & strInactive & strWhere
' Run the Update Query
db.Execute strSQL

I put in a breakpoint and stepped through the code (I'm learning that,
too) and ?strSQL to see the SQL:

UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON tblCustomers.Cu stID
= tblCustomerItem s.CustID SET tblCustomers.Cu stStatus = I,
tblCustomerItem s.ItemStatus = I WHERE tblCustomers.Cu stID = 15

This looks correct to me, as I compare it to the SQL view of the query.
I did see extra spaces here and there, but even removing them gives me
the error:

91 Object Variable or With Block Variable not set

I'm cluless, as I don't see any variables I've left Unset. I tried
making the strInactive = " 'I' " and that didn't work.
I tried saying strSQL = and put in the resolved statement, even though
my resolution seemed ok.
I tried adding a comma before "WHERE"

All give me the same error. Don't know what else to try.
thanks

[David W. Fenton]

"lylefair" <ly***********@ aim.com> wrote in
news:11******** **************@ f14g2000cwb.goo glegroups.com:

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?

If, say, there is a time running in a background form and it brings
the form to the front sometime after the subroutine with
DoCmd.RunComman d acCmdSaveRecord was initiated that could easily
mean that the first two lines of the code snippet execute after the
other form has the focus, and the third line would then save the
record in the form that was brought to the foreground by your timer
event.

Of course, I avoid timers wherever possible, but some features
require them.

I don't consider the focus issue to be a strong reason to use
Me.Dirty = False to save a record -- it's just a side benefit, in my
opinion. I prefer it for logical reasons, as it is more clear what
it is doing, because the command shows that it's acting on a
particular object and changing a property of that object. That seems
to me to be a better way of coding than just running a menu command.

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

[David W. Fenton]

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

I think my confustion is around when to use the & on a
continuation line and when not to.

Write the code first on a single line. Then break it with line
continuation characters. If you're breaking in the middle of a
string value, you have to surround the line continuation character
with quotes and ampersand.

So, where you insert a line break in the code, you have to add
(using braekets to serve as quote markes) [" _] at the end, and then
add [& "] at the beginning of the next line.

Consider, if you have this string:

"This is a string that I want to break into two lines."

You could change that to:

"This is a string that I" & " want to break into two lines."

That's pretty clearly equivalent. Then all you have to do is add the
line continuation character:

"This is a string that I" _ & " want to break into two lines."

then the line break:

"This is a string that I" _
& " want to break into two lines."

Does that make more sense?

However, in your original problem, the real issue was that your
string concatenation was all happening as an argument that was a
parameter of an Access command. In that kind of case, you end up
with more readable and more maintainable code if you break it all
down into multiple stagee, as someone suggested:

1. create a variable to store your string value.

2. assign the string to that variable

3. use that variable in the line executing the command.

In terms of the MsgBox function, that makes it much more readable.

I'd also suggest another little hint that I got from others here in
CDMA. Instead of

If MsgBox(...) = vbYes Then

change it to

If vbYes - MsgBox(...) Then

THis makes it much easier to see what you're testing for in your
If/Then statement.

Last of all, let me say that I generally don't use line continuation
characters in any of my code -- I just scroll over to the right. I
know that's something that drives many people absolutely nuts, but
it's exactly the kind of problems you're having that has led me to
avoid using them. I don't find the broken up lines any more readable
than the long ones that I have to scroll through.

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

[Bob Quintal]

Comments in-line.
"sara" <sa*******@yaho o.com> wrote in
news:11******** ************@g4 4g2000cwa.googl egroups.com:

OK. I'm getting this and maybe even going to get good at it.
I have tried to build a SQL string and run it in my code, to
change the customer status to "I" (Inactive) along with all
the items that belong to that customer, if my user says she
wants to Delete a customer. (She's never used a computer
before, is nervous, and may delete in error).

My query runs, so I tried to convert the query SQL into a
string and run it.

Query Code:
UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON
tblCustomers.Cu stID = tblCustomerItem s.CustID SET
tblCustomers.Cu stStatus = "I", tblCustomerItem s.ItemStatus =
"I" WHERE
(((tblCustomers .CustID)=[Forms]![frmCust]![txtCustID]));

MyCode:
strWhere = " WHERE tblCustomers.Cu stID = " &
Forms!frmCust!t xtCustID
strInactive = "I"
strSQL = "UPDATE tblCustomers LEFT JOIN tblCustomerItem s
ON " _
& " tblCustomers.Cu stID = tblCustomerItem s.CustID " _
& "SET tblCustomers.Cu stStatus = " & strInactive &
"," _ & " tblCustomerItem s.ItemStatus = " &
strInactive & strWhere
' Run the Update Query
db.Execute strSQL

I put in a breakpoint and stepped through the code (I'm
learning that, too) and ?strSQL to see the SQL:

UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON
tblCustomers.Cu stID = tblCustomerItem s.CustID SET
tblCustomers.Cu stStatus = I, tblCustomerItem s.ItemStatus = I
WHERE tblCustomers.Cu stID = 15

This looks correct to me, as I compare it to the SQL view of
the query.
I did see extra spaces here and there, but even removing them
gives me
the error:

91 Object Variable or With Block Variable not set
If you compare the two sql statements, you' will see a slight
difference.
tblCustomers.Cu stStatus = "I", vs
tblCustomers.Cu stStatus = I,

Notice that you need to put quotes around the value.

& "SET tblCustomers.Cu stStatus = " & strInactive & "," _
& " tblCustomerItem s.ItemStatus = " & strInactive & strWhere

needs to be
& "SET tblCustomers.Cu stStatus = """ & strInactive & """," _
& " tblCustomerItem s.ItemStatus = """ & strInactive & """" _
& strWhere

To embed a quote in the string you have to double it up. You
might also need quotes in hte whereclause, if custID is defrined
as text in the table.
I'm cluless, as I don't see any variables I've left Unset. I
tried making the strInactive = " 'I' " and that didn't work.
I tried saying strSQL = and put in the resolved statement,
even though my resolution seemed ok.
I tried adding a comma before "WHERE"
Finally, Access likes to see a semicolon at the end of a sql
statement. You need to add that too. The last line would become
& strWhere & ";"

All give me the same error. Don't know what else to try.
thanks

HTH
--
Bob Quintal

PA is y I've altered my email address.

[sara]

I'm now getting "91: Object variable or With Block Variable not set".
The code goes to my error routine right after the
"db.execute strSQL" statement.

I've been working on this since last night and most of today. My query
statement (resolved - strSQL) is identical to the query SQL (that works
when I call the query from the code), thanks to all the help on coding
such strings.

UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON tblCustomers.Cu stID
= tblCustomerItem s.CustID SET tblCustomers.Cu stStatus = "I",
tblCustomerItem s.ItemStatus = "I" WHERE tblCustomers.Cu stID = 15;

I tried to get quotes around the "15" as it is txtCustID, but I failed
on that. If that is the problem, it's back to coding strings!

Code:

Private Sub cmdDeleteCust_C lick()
On Error GoTo Err_cmdDeleteCu st_Click

Dim strSQL As String ' for Update Query
Dim strWhere As String ' for Update Query
Dim txtCustID As String
' to hold I for query - avoids need for quotes in string build
Dim strInactive As String
Dim db As DAO.Database ' Current database

If MsgBox _
("Are you sure? Pressing Yes will remove the customer" & _
" and all customer items", vbYesNoCancel + vbExclamation,
"Delete Customer") _
<> vbYes Then
Exit Sub
End If

' If the user wants to "delete", change the customer status to
Inactive,
' updqChangeItemS tatus - changes all item statuses for this customer
to I

' Turn off warnings (and then back on) so user doesn't get message
about updates
DoCmd.SetWarnin gs False

' DoCmd.OpenQuery ("updqChangeCus tAndItemStatus" )

txtCustID = Forms!frmCust!t xtCustID

strWhere = " WHERE tblCustomers.Cu stID = " & txtCustID
strInactive = "I"

strSQL = "UPDATE tblCustomers LEFT JOIN tblCustomerItem s ON " _
& " tblCustomers.Cu stID = tblCustomerItem s.CustID " _
& "SET tblCustomers.Cu stStatus = """ & strInactive & """," _
& " tblCustomerItem s.ItemStatus = """ & strInactive & """" &
strWhere & ";"

' Run the Update Query

db.Execute strSQL ' goes from here to error when stepping thru
code

DoCmd.SetWarnin gs True

' Set the criteria back to what the user entered and requery the form
' Deleted customer should be gone

DoCmd.Close acForm, Me.Name

Forms!frmFindCu stomer.lstCusto mers = Me.txtCustID
Forms!frmFindCu stomer.lstCusto mers.Requery

Exit_cmdDeleteC ust_Click:
Exit Sub

Err_cmdDeleteCu st_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdDeleteC ust_Click

End Sub

Hopefully you can help with this? I want to execute the query in the
code, both to learn how, and to hope it will trigger the before_update
event so I can capture the changes in an audit table.

Thank you very much.
sara

[Smartin]

sara wrote:

I'm now getting "91: Object variable or With Block Variable not set".
The code goes to my error routine right after the
"db.execute strSQL" statement.

Guessing?

You dim'd the db object but you never instantiated it. Put something
like this in there before you try to use the db object:

Set db = Currentdb()

HTH
--
Smartin

[sara]

Oh my! It worked! I am learning and so very appreciative of all the
help.

Until next problem...

Sara