if elseif help

T

tracktraining

Hi ALL,

There are three messages appearing at different condition that i want to
have in my save button. I have the following code and I am having problems
with the if, elseif, else statement (or maybe i am not supposed to use the
if, elseif statement?)


Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

strSQLD = "SELECT * FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND EmpDocStatus.Revision =
Me.Revision"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub


Please let me know if there is a better way of writing this.

Thanks,
Tracktraining
 
M

Marshall Barton

tracktraining said:
There are three messages appearing at different condition that i want to
have in my save button. I have the following code and I am having problems
with the if, elseif, else statement (or maybe i am not supposed to use the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]
Click to expand...


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
 
T

tracktraining

Where would i put the other end if? I fixed the elseif (i.e. making it one
word) I tried putting the end if in different location and i need getting an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
--
Learning


Marshall Barton said:
tracktraining said:
There are three messages appearing at different condition that i want to
have in my save button. I have the following code and I am having problems
with the if, elseif, else statement (or maybe i am not supposed to use the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]
Click to expand...


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
Click to expand...
 
T

tracktraining

so this is my code as far:

Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newemail = Me.EmpEmail
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

strSQLD = "SELECT * FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND EmpDocStatus.Revision =
Me.Revision"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Me.DocID.SetFocus
Else

MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub

--
Learning


tracktraining said:
Where would i put the other end if? I fixed the elseif (i.e. making it one
word) I tried putting the end if in different location and i need getting an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
--
Learning


Marshall Barton said:
tracktraining said:
There are three messages appearing at different condition that i want to
have in my save button. I have the following code and I am having problems
with the if, elseif, else statement (or maybe i am not supposed to use the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]
Click to expand...


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

You seem to be saving the record regardless of whether it passes your edits.

And

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

really isn't the best approach to doing the save: the DoMenuItem method
really is only there for backwards compatibility.

Try putting

Me.Dirty = False

before the End If.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tracktraining said:
so this is my code as far:

Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newemail = Me.EmpEmail
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

strSQLD = "SELECT * FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND EmpDocStatus.Revision =
Me.Revision"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Me.DocID.SetFocus
Else

MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub

--
Learning


tracktraining said:
Where would i put the other end if? I fixed the elseif (i.e. making it
one
word) I tried putting the end if in different location and i need getting
an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
--
Learning


Marshall Barton said:
tracktraining wrote:
There are three messages appearing at different condition that i want
to
have in my save button. I have the following code and I am having
problems
with the if, elseif, else statement (or maybe i am not supposed to use
the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned
To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
Click to expand...
Click to expand...
Click to expand...
 
T

tracktraining

what will that do?


--
Learning


Douglas J. Steele said:
You seem to be saving the record regardless of whether it passes your edits.

And

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

really isn't the best approach to doing the save: the DoMenuItem method
really is only there for backwards compatibility.

Try putting

Me.Dirty = False

before the End If.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tracktraining said:
so this is my code as far:

Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newemail = Me.EmpEmail
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

strSQLD = "SELECT * FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND EmpDocStatus.Revision =
Me.Revision"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Me.DocID.SetFocus
Else

MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub

--
Learning


tracktraining said:
Where would i put the other end if? I fixed the elseif (i.e. making it
one
word) I tried putting the end if in different location and i need getting
an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
--
Learning


:

tracktraining wrote:
There are three messages appearing at different condition that i want
to
have in my save button. I have the following code and I am having
problems
with the if, elseif, else statement (or maybe i am not supposed to use
the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned
To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
Click to expand...
Click to expand...
Click to expand...
 
T

tracktraining

I don't want to force the data to save. I want to save the data only if the
doc+rev is there and that the doc+rev has not been assigned to the employee.

--
Learning


Douglas J. Steele said:
You seem to be saving the record regardless of whether it passes your edits.

And

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

really isn't the best approach to doing the save: the DoMenuItem method
really is only there for backwards compatibility.

Try putting

Me.Dirty = False

before the End If.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tracktraining said:
so this is my code as far:

Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newemail = Me.EmpEmail
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

strSQLD = "SELECT * FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND EmpDocStatus.Revision =
Me.Revision"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Me.DocID.SetFocus
Else

MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub

--
Learning


tracktraining said:
Where would i put the other end if? I fixed the elseif (i.e. making it
one
word) I tried putting the end if in different location and i need getting
an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
--
Learning


:

tracktraining wrote:
There are three messages appearing at different condition that i want
to
have in my save button. I have the following code and I am having
problems
with the if, elseif, else statement (or maybe i am not supposed to use
the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been Assigned
To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

tracktraining said:
Where would i put the other end if? I fixed the elseif (i.e. making it one
word) I tried putting the end if in different location and i need getting an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
Click to expand...


I must have phrased that in a confusing way. What you
posted above is the correct way to do it.

If you had a reason to nest If statements, it would look
like:

If rst.EOF Then
. . .
Else
If rs.EOF Then
. . .
Else
. . .
End If
End If
 
R

rquintal

Hi ALL,
Click to expand...
See my comments in your code.

=?Utf-8?B?dHJhY2t0cmFpbmluZw==?=
Hi ALL,

There are three messages appearing at different condition that i
want to have in my save button. I have the following code and I am
having problems with the if, elseif, else statement (or maybe i am
not supposed to use the if, elseif statement?)


Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" +
newDocID +
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"
Click to expand...

'you mix the & and + signs. It looks like you are trying to create a
select statement that handles blank newDocId and/or newRevision. It
won't work. Besides, you do not need to store to variables, you can
refer directly to the conols.

Try
If len(me.DocId & "") >0 then
strsql = DocInfo.DocID = '" & Me.DocId &"' "
end if
If len(me.Revision & "") >0 then
If len(strSQL) >0 then
strSQL = strSQL & " AND " _
"DocInfo.Revision = '" & me.Revision & "'"
Else
strSQL = "DocInfo.Revision = '" & me.Revision & "'"
end if
end if
If len(strSQL) >0 then
strSQL = " SELECT * FROM DocInfo WHERE " & strSQL & ";"
Else
strSQL = " SELECT * FROM DocInfo;"

If you don't need to handle blanks,
strSQL = " SELECT * FROM DocInfo WHERE " _
& "DocInfo.DocID = '" & Me.DocId &"' AND" _
& "DocInfo.Revision = '" & me.Revision & "'"
strSQLD = "SELECT * FROM EmpDocStatus WHERE
EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND
EmpDocStatus.Revision =
Me.Revision"
Click to expand...
This statement has several problems. You now treat DocId and
Revision as numbers not strings. You also treat EmpEmail as a
number.
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
Click to expand...
the line above has a typo, ElseIf is one word. I also believe that
you have a logic error, in that if the document numbe4r and revision
has already been assigned you will have a record in the set, and
your test should be
ElseIf NOT rs.EOF
MsgBox "Document Number + Revision Has Already Been
Assigned To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
Click to expand...
The line below is deprecated old stuff kept for backwards
compatibility. me.Dirty = false is better.
Also the statement needs to be above the End If line, otherwise,
even if the record is wrong or the record exists, you'll still save
the record.
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub


Please let me know if there is a better way of writing this.

Thanks,
Tracktraining
Click to expand...
Hope this helps.
 
D

Douglas J. Steele

Me.Dirty = False

will save the current record.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tracktraining said:
what will that do?


--
Learning


Douglas J. Steele said:
You seem to be saving the record regardless of whether it passes your
edits.

And

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

really isn't the best approach to doing the save: the DoMenuItem method
really is only there for backwards compatibility.

Try putting

Me.Dirty = False

before the End If.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
so this is my code as far:

Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newemail = Me.EmpEmail
newDocID = Me.DocID
newRevision = Me.Revision


strSQL = " SELECT * FROM DocInfo WHERE DocInfo.DocID = '" + newDocID
+
"' AND " & _
" DocInfo.Revision = '" + newRevision + "'"

strSQLD = "SELECT * FROM EmpDocStatus WHERE EmpDocStatus.EmpEmail =
Me.EmpEmail AND " & _
" EmpDocStatus.DocID = Me.DocID AND EmpDocStatus.Revision =
Me.Revision"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
Set rs = db.OpenRecordset(strSQLD)


If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Me.DocID.SetFocus
Else

MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Exit_btnsave_Click:
Exit Sub
Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub

--
Learning


:

Where would i put the other end if? I fixed the elseif (i.e. making it
one
word) I tried putting the end if in different location and i need
getting
an
error.

If rst.EOF Then
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
ElseIf rs.EOF Then
MsgBox "Document Number + Revision Has Already Been Assigned
To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated. (save click)"
Me.FilterOn = False
'DoCmd.Close
End If
--
Learning


:

tracktraining wrote:
There are three messages appearing at different condition that i
want
to
have in my save button. I have the following code and I am having
problems
with the if, elseif, else statement (or maybe i am not supposed to
use
the
if, elseif statement?)
[snip]
If rst.EOF Then
MsgBox "Document Number/Revision Wrong button save click."
Me.DocID.SetFocus
Else If rs.EOF
MsgBox "Document Number + Revision Has Already Been
Assigned
To
Employee."
Me.DocID.SetFocus
Else
MsgBox "Training Record Is Updated."
Me.FilterOn = False
'DoCmd.Close
End If
[snip]


ElseIf is one word.

The way you wrote it, you have a nested If inside an Else
part of the outer If, which would require its own End If.
Click to expand...
Click to expand...
Click to expand...
 
T

tracktraining

Hi All,

I got your advices and revised my code to be the following:

'Check for conditions and record if no error found
Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String
Dim Response As String
newemail = Me.EmpEmail
newDocID = Me.DocID
newRevision = Me.Revision

'search for empemail+doc+rev to check for assigning duplicate
strSQLD = "SELECT * FROM EmpDocStatus WHERE " _
& "EmpDocStatus.EmpEmail = '" & Me.EmpEmail & "' AND" _
& "EmpDocStatus.DocID = '" & Me.DocID & "' AND" _
& "EmpDocStatus.Revision = '" & Me.Revision & "'"

'search for doc+rev to check for doc+rev existence
strSQL = "SELECT * FROM DocInfo WHERE " _
& "DocInfo.DocID = '" & Me.DocID & "' AND" _
& "DocInfo.Revision = '" & Me.Revision & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQLD)
Set rst = db.OpenRecordset(strSQL)

If rs.EOF Then
'Error message if empemail has doc+rev already
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Me.DocID.SetFocus
ElseIf Not rst.EOF Then
'Error message if doc+rev doesn't exist
MsgBox "Document Number/Revision Wrong. (save loop)."
Me.DocID.SetFocus
Else
'No error message thus allow for data to be recorded
MsgBox "Training Record Is Updated. (save click)"
Me.Dirty = False
End If

End Sub

I am still getting errors. When I pick empemail from the combobox on the
form, I get "Invaild use of Null" error message. Click debug will bring me to
the newemail=Me.EmpEmail line in the code.

Please help.

Thanks.
 
S

Steve Sanford

Change you code to look like this:

'search for empemail+doc+rev to check for assigning duplicate
strSQLD = "SELECT * FROM EmpDocStatus WHERE " _
& "EmpDocStatus.EmpEmail = '" & Me.EmpEmail & "' AND" _
& "EmpDocStatus.DocID = '" & Me.DocID & "' AND" _
& "EmpDocStatus.Revision = '" & Me.Revision & "'"

Debug.Print strSQLD ' ****** ADDED


'search for doc+rev to check for doc+rev existence
strSQL = "SELECT * FROM DocInfo WHERE " _
& "DocInfo.DocID = '" & Me.DocID & "' AND" _
& "DocInfo.Revision = '" & Me.Revision & "'"

Debug.Print strSQL ' ****** ADDED



Set a breakpoint on the line

Set db = CurrentDb


Run the code and look at the immediate window. You are missing spaces in
the strings... do you see them??


I modified your code. See if it helps..


Every code page should begin with these two lines:

Option Compare Database
Option Explicit


OK, here is the code:

'************************
Option Compare Database
Option Explicit

'Check for conditions and record if no error found
Private Sub Form_BeforeInsert(Cancel As Integer)

Dim db As Database
Dim rst As DAO.Recordset
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSQLD As String

Dim newemail As String
Dim newDocID As String
Dim newRevision As String


' convert NULLS to empty strings
newemail = Nz(Me.EmpEmail, "")
newDocID = Nz(Me.DocID, "")
newRevision = Nz(Me.Revision, "")

' test for NULL strings. If an empty string is found, Exit sub
If newemail = "" Or newDocID = "" Or newRevision = "" Then
MsgBox "Invalid Email or Doc ID or Revision." & vbLf & vbLf & "Please
correct and try again"
Cancel = True
Exit Sub
End If


'search for empemail+doc+rev to check for assigning duplicate
strSQLD = "SELECT * FROM EmpDocStatus WHERE" _
& " EmpDocStatus.EmpEmail = '" & newemail & "' AND" _
& " EmpDocStatus.DocID = '" & newDocID & "' AND" _
& " EmpDocStatus.Revision = '" & newRevision & "'"

'Debug.Print strSQLD

'search for doc+rev to check for doc+rev existence
strSQL = "SELECT * FROM DocInfo WHERE" _
& " DocInfo.DocID = '" & newDocID & "' AND" _
& " DocInfo.Revision = '" & newRevision & "'"

'Debug.Print strSQL

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQLD)
Set rst = db.OpenRecordset(strSQL)

If rs.EOF Then
'Error message if empemail has doc+rev already
MsgBox "Document Number + Revision Has Already Been Assigned To
Employee. (save loop)"
Cancel = True
Me.DocID.SetFocus
ElseIf Not rst.EOF Then
'Error message if doc+rev doesn't exist
MsgBox "Document Number/Revision Wrong. (save loop)."
Cancel = True
Me.DocID.SetFocus
Else
'No error message thus allow for data to be recorded
MsgBox "Training Record Is Updated. (save click)"
Me.Dirty = False
End If

End Sub
'*************************

HTH
 
T

tracktraining

Hi Steve,

I tried your code and I keep getting "Invalid Email or Doc ID or Revision.
Please correct and try again" when i try to type something in the field. It
seems like it is not allowing me to type in the field.
 
T

tracktraining

I figured out my problem. the event should be Form_beforeupdate NOT
Form_beforeinsert.

but i am having trouble with the Me.Dirty = False. When the condition not
met thus allowing the user to get "Training record is updated", the record
should be save to the table. In this case, I am unable to save the record. I
get run-time error of 2115 and clicking on the debug, it brings me to
Me.Dirty = false line.

If Not rs.EOF Then
'Error message if empemail has doc+rev already
MsgBox "Part number and Revision has already" & vbLf & _
"been assigned to employee. (save loop)"
Cancel = True
Me.DocID.SetFocus
ElseIf rst.EOF Then
'Error message if doc+rev doesn't exist
MsgBox "Invaid part number and/or revision. (save loop)."
Cancel = True
Me.DocID.SetFocus
Else
'No error message thus allow for data to be recorded
MsgBox "Training record is updated. (save click)"
Me.Dirty = False
End If

By the way, what is the meaning of Me.Dirty = False?

thanks for all your help so far.
 
S

Steve Sanford

Me.Dirty = false line.

If the form (or record) has been changed, it saves the change.

Your code is running in the Form_beforeupdate event; try commenting out the
Me.Dirty = false line and see in you get the error.

I'm thinking your are already trying to update the record, and the Me.Dirty
line tries to start the save process again.

HTH
 
T

tracktraining

Yes, if i comment out the Me.Dirty =false, then i don't get the error message.

Thanks for all the help.
 

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

setfocus error 3
VBA works in orignal but fails in copy of database 1
populate listbox form database table 0
Recordset within a recordset? 5
Custom ID Field 4
Want to avoid the possibilty to edit field in listbox 6
Pls help, my VBA does not work 3
Select record in a listbox 8

Top