Will not read If then statement on vbyes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following:
Dim response As String
Dim findrecord As Integer
Dim strsaw As String
Dim strsql As Variant

strsaw = Saw_ID
response = MsgBox("If you continue closing this form, the saw
performance" & vbCrLf & _
"you were working on will be deleted from the database." &
vbCrLf & _
"Are you sure you want to continue?", vbYesNoCancel)
If response = vbYes Then
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID = " &
strsaw)
strsql = "DELETE FROM tblsawperformance WHERE [Run_ID] = " &
findrecord
CurrentDb.Execute strsql
DoCmd.Close
DoCmd.SelectObject acForm, "switchboard", no
End If
If response = vbNo Then
Exit Sub
End If
If response = vbCancel Then
Exit Sub
End If

the vbno and vb cancel run perfectly but for some reason the vbyes will not
execute the findrecord and strsql code but if I move the docmd.close in front
of the findrecord it will run. Does anyone have any idea why my code will
not run properly

Any help would be much appreciated.
Thanks
Steve
 
What's the DoCmd.Close supposed to be doing?

FWIW, MsgBox returns a Long Integer, not a String. Change the first line of
your code to

Dim response As Long
 
Steve said:
I have the following:
Dim response As String
Dim findrecord As Integer
Dim strsaw As String
Dim strsql As Variant

strsaw = Saw_ID
response = MsgBox("If you continue closing this form, the saw
performance" & vbCrLf & _
"you were working on will be deleted from the database." &
vbCrLf & _
"Are you sure you want to continue?", vbYesNoCancel)
If response = vbYes Then
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID =
" & strsaw)
strsql = "DELETE FROM tblsawperformance WHERE [Run_ID] = " &
findrecord
CurrentDb.Execute strsql
DoCmd.Close
DoCmd.SelectObject acForm, "switchboard", no
End If
If response = vbNo Then
Exit Sub
End If
If response = vbCancel Then
Exit Sub
End If

the vbno and vb cancel run perfectly but for some reason the vbyes
will not execute the findrecord and strsql code but if I move the
docmd.close in front of the findrecord it will run. Does anyone have
any idea why my code will not run properly

Any help would be much appreciated.
Thanks
Steve
Click to expand...

In addition to Doug Steele's comments and question, the variable
"findrecord" should probably also be declared as Long, not as Integer.
If Run_ID is an autonumber, or a Long Integer foreign key related to an
autonumber primary key, this is definitely the case.

Also, this line:
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID =
" & strsaw)
Click to expand...

is missing the closing square bracket on Saw_ID. It should be:

findrecord = _
DMax("[Run_ID]", "tblsawperformance", "[Saw_ID] = " & strsaw)

(although I don't think the square brackets are actually necessary in
this case).
 
Doug,
Thanks for your response. The Docmd.close is there to close the form or
at least that is how I worte it. Do you have any suggestions on the proper
way of doing it? I am pretty new to writing code so any help would be
appreciated.

Thanks
Steve


Douglas J. Steele said:
What's the DoCmd.Close supposed to be doing?

FWIW, MsgBox returns a Long Integer, not a String. Change the first line of
your code to

Dim response As Long

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve said:
I have the following:
Dim response As String
Dim findrecord As Integer
Dim strsaw As String
Dim strsql As Variant

strsaw = Saw_ID
response = MsgBox("If you continue closing this form, the saw
performance" & vbCrLf & _
"you were working on will be deleted from the database." &
vbCrLf & _
"Are you sure you want to continue?", vbYesNoCancel)
If response = vbYes Then
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID = " &
strsaw)
strsql = "DELETE FROM tblsawperformance WHERE [Run_ID] = " &
findrecord
CurrentDb.Execute strsql
DoCmd.Close
DoCmd.SelectObject acForm, "switchboard", no
End If
If response = vbNo Then
Exit Sub
End If
If response = vbCancel Then
Exit Sub
End If

the vbno and vb cancel run perfectly but for some reason the vbyes will not
execute the findrecord and strsql code but if I move the docmd.close in front
of the findrecord it will run. Does anyone have any idea why my code will
not run properly

Any help would be much appreciated.
Thanks
Steve
Click to expand...
Click to expand...
 
I have changed everything that both of you have suggest but for some reason
it is still not running the code. Any more suggestions?

Thanks for your help.
Steve

Dirk Goldgar said:
Steve said:
I have the following:
Dim response As String
Dim findrecord As Integer
Dim strsaw As String
Dim strsql As Variant

strsaw = Saw_ID
response = MsgBox("If you continue closing this form, the saw
performance" & vbCrLf & _
"you were working on will be deleted from the database." &
vbCrLf & _
"Are you sure you want to continue?", vbYesNoCancel)
If response = vbYes Then
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID =
" & strsaw)
strsql = "DELETE FROM tblsawperformance WHERE [Run_ID] = " &
findrecord
CurrentDb.Execute strsql
DoCmd.Close
DoCmd.SelectObject acForm, "switchboard", no
End If
If response = vbNo Then
Exit Sub
End If
If response = vbCancel Then
Exit Sub
End If

the vbno and vb cancel run perfectly but for some reason the vbyes
will not execute the findrecord and strsql code but if I move the
docmd.close in front of the findrecord it will run. Does anyone have
any idea why my code will not run properly

Any help would be much appreciated.
Thanks
Steve
Click to expand...

In addition to Doug Steele's comments and question, the variable
"findrecord" should probably also be declared as Long, not as Integer.
If Run_ID is an autonumber, or a Long Integer foreign key related to an
autonumber primary key, this is definitely the case.

Also, this line:
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID =
" & strsaw)
Click to expand...

is missing the closing square bracket on Saw_ID. It should be:

findrecord = _
DMax("[Run_ID]", "tblsawperformance", "[Saw_ID] = " & strsaw)

(although I don't think the square brackets are actually necessary in
this case).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I don't understand how closing the form before all of the code has completed
can work.

What happens when you single-step through your code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve said:
Doug,
Thanks for your response. The Docmd.close is there to close the form or
at least that is how I worte it. Do you have any suggestions on the proper
way of doing it? I am pretty new to writing code so any help would be
appreciated.

Thanks
Steve


Douglas J. Steele said:
What's the DoCmd.Close supposed to be doing?

FWIW, MsgBox returns a Long Integer, not a String. Change the first line of
your code to

Dim response As Long

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve said:
I have the following:
Dim response As String
Dim findrecord As Integer
Dim strsaw As String
Dim strsql As Variant

strsaw = Saw_ID
response = MsgBox("If you continue closing this form, the saw
performance" & vbCrLf & _
"you were working on will be deleted from the database." &
vbCrLf & _
"Are you sure you want to continue?", vbYesNoCancel)
If response = vbYes Then
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID = " &
strsaw)
strsql = "DELETE FROM tblsawperformance WHERE [Run_ID] = " &
findrecord
CurrentDb.Execute strsql
DoCmd.Close
DoCmd.SelectObject acForm, "switchboard", no
End If
If response = vbNo Then
Exit Sub
End If
If response = vbCancel Then
Exit Sub
End If

the vbno and vb cancel run perfectly but for some reason the vbyes
Click to expand...
will
not
execute the findrecord and strsql code but if I move the docmd.close
Click to expand...
in
front
of the findrecord it will run. Does anyone have any idea why my code will
not run properly

Any help would be much appreciated.
Thanks
Steve
Click to expand...
Click to expand...
Click to expand...
 
Doug,
I agree with you there but I have used this before as far as having the
close statement in there but as long as I have it after my code then all the
code finishes then it shut the form down. As far as single stepping, I
honestly don't think I know how to do that but I have placed breakpoints in
all the lines after the If response = vbyes and the only line that works is
if I put the breakpoint on the findrecord line. Otherwise if I place it on
any line below that it seems as though it can't read the findrecord line so
it gives up. I have also deleted all the lines in the if then except for the
close command and then it works fine.

Again, any help is much appreciated.

Steve

Douglas J. Steele said:
I don't understand how closing the form before all of the code has completed
can work.

What happens when you single-step through your code?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Steve said:
Doug,
Thanks for your response. The Docmd.close is there to close the form or
at least that is how I worte it. Do you have any suggestions on the proper
way of doing it? I am pretty new to writing code so any help would be
appreciated.

Thanks
Steve


Douglas J. Steele said:
What's the DoCmd.Close supposed to be doing?

FWIW, MsgBox returns a Long Integer, not a String. Change the first line of
your code to

Dim response As Long

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have the following:
Dim response As String
Dim findrecord As Integer
Dim strsaw As String
Dim strsql As Variant

strsaw = Saw_ID
response = MsgBox("If you continue closing this form, the saw
performance" & vbCrLf & _
"you were working on will be deleted from the database." &
vbCrLf & _
"Are you sure you want to continue?", vbYesNoCancel)
If response = vbYes Then
findrecord = DMax("[Run_ID]", "tblsawperformance", "[Saw_ID = " &
strsaw)
strsql = "DELETE FROM tblsawperformance WHERE [Run_ID] = " &
findrecord
CurrentDb.Execute strsql
DoCmd.Close
DoCmd.SelectObject acForm, "switchboard", no
End If
If response = vbNo Then
Exit Sub
End If
If response = vbCancel Then
Exit Sub
End If

the vbno and vb cancel run perfectly but for some reason the vbyes will
not
execute the findrecord and strsql code but if I move the docmd.close in
front
of the findrecord it will run. Does anyone have any idea why my code will
not run properly

Any help would be much appreciated.
Thanks
Steve
Click to expand...
Click to expand...
Click to expand...
 
Steve said:
Doug,
I agree with you there but I have used this before as far as having
the close statement in there but as long as I have it after my code
then all the code finishes then it shut the form down. As far as
single stepping, I honestly don't think I know how to do that but I
have placed breakpoints in all the lines after the If response =
vbyes and the only line that works is if I put the breakpoint on the
findrecord line. Otherwise if I place it on any line below that it
seems as though it can't read the findrecord line so it gives up. I
have also deleted all the lines in the if then except for the close
command and then it works fine.
Click to expand...

It sounds like you may be getting an error on the that line. But I'd
expect some sort of error message to appear, and you haven't mentioned
one. You don't have Error Trapping turned off in the VB Editor options,
do you?
 
Good Call Dirk,
I had the error trapping only to break on unhandled errors. Now it says
Run Time error '2001', I have cancelled the previous operation and it shows
the highlighted line on the findrecord line of code. Any thoughts? Thanks
again

Steve
 
Dirk and Doug,
I found the problem, I didn't have single quotes around the strsaw
variable in the Dmax function.
Thank you for all your help

Steve
 
Back
Top