Retrieving data from query using If, Then, Else

J

JudyB

Help needed.

I have created a master form (Employees) with a subform (Service Record).
The Service Record subform has fields ServiceRecordID, EmployeeID,
DepartmentName, JobTitleName, DateStart, DateEnd, and WeeksService. I want
to use the subform to track an employee's service time in each department
he/she works during their time of employment along with the various job
titles that he/she may hold. I have created a query (Service Record Query)
with a field titled
"Status". If the DateEnd field is left blank, "Current" will automatically
fill this field. If DateEnd has a date, the field fills with "Null".

I am currently using the following code to retrieve each employees current
department and job title:

Private Sub Form_Current()
Dim s As String
Dim rs As Recordset

s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

It works great for employees that have "Current" in the Status field of the
Service Record Query, but I am getting the following error message for
employees that have "Null" in the Status field.

Run-Time Error '-2147352567 (80020009)': The
value you entered is not valid for this field.

I assume this is because the system is lost when it can't find "Current" in
the Status field? I want to separate the current employees from the
terminated (Null in the Status field) employees for reporting purposes. I
would have thought that the Else statement at the end of the code would have
taken care of the problem, but I am still green and do not have a clear
understanding of code yet.

I was advised to use the following code to solve the problem, but I didn't
have any luck with it either:

Private Sub Form Current()
Dim s As String
Dim rs As Recordset

Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null

If Not Me.NewRecord Then
If [Status] = "Current" Then
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
End If
End If

Can anyone offer a solution? Any help is greatly appreciated. Thanks
 
T

tina

Null is a value that has a specific meaning in Access, so you might try
replacing it in your Status field with something else, like "Previous" or
"Ended", etc.

hth


JudyB said:
Help needed.

I have created a master form (Employees) with a subform (Service Record).
The Service Record subform has fields ServiceRecordID, EmployeeID,
DepartmentName, JobTitleName, DateStart, DateEnd, and WeeksService. I want
to use the subform to track an employee's service time in each department
he/she works during their time of employment along with the various job
titles that he/she may hold. I have created a query (Service Record Query)
with a field titled
"Status". If the DateEnd field is left blank, "Current" will automatically
fill this field. If DateEnd has a date, the field fills with "Null".

I am currently using the following code to retrieve each employees current
department and job title:

Private Sub Form_Current()
Dim s As String
Dim rs As Recordset

s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

It works great for employees that have "Current" in the Status field of the
Service Record Query, but I am getting the following error message for
employees that have "Null" in the Status field.

Run-Time Error '-2147352567 (80020009)': The
value you entered is not valid for this field.

I assume this is because the system is lost when it can't find "Current" in
the Status field? I want to separate the current employees from the
terminated (Null in the Status field) employees for reporting purposes. I
would have thought that the Else statement at the end of the code would have
taken care of the problem, but I am still green and do not have a clear
understanding of code yet.

I was advised to use the following code to solve the problem, but I didn't
have any luck with it either:

Private Sub Form Current()
Dim s As String
Dim rs As Recordset

Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null

If Not Me.NewRecord Then
If [Status] = "Current" Then
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
End If
End If

Can anyone offer a solution? Any help is greatly appreciated. Thanks
Click to expand...
 
J

JudyB

New code did the trick! Thank you so much. Didn't think I would ever get
past that problem.

--
JudyB


tina said:
well, no reason to thank me, i read your post in a hurry, and gave you a
poor answer. i'll see if i can improve on it now!
I
Click to expand...

no, the Else statement only runs when the current record is a new record -
the If condition doesn't consider job status at all. your code is erring
when the current record is *not* a new record, and the SQL statement doesn't
return a record, so the code is attempting to assign values that do not
exist because there is no record in the recordset. and you really don't need
to check the "Status" anyway; it's a calculated value based on the value in
a hard data field, so i'd just check that field directly. try something like

Private Sub Form_Current()

Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me!EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me!Current_Job_Title_Name = rs!JobTitleName
Me!Current_Department_Name = rs!DepartmentName
End If
rs.Close
Set rs = Nothing
End If

End Sub

the recordset only opens when the current record is not a new record, as in
your posted code. but then the code checks to see if there is a record
present, assigns the field values only if there is; and then closes and
disposes of the recordset in all cases.

notice also that i specified the recordset as a DAO recordset. it's a good
habit to get into, to avoid possible conflicts between DAO and ADO
properties and methods as you work with recordsets in code.

hth


JudyB said:
Hi Tina,
Thanks for suggestion. It didn't do the trick. Got any other
suggestions? Does the code look right to you? Thanks again!
--
JudyB


tina said:
Null is a value that has a specific meaning in Access, so you might try
replacing it in your Status field with something else, like "Previous" or
"Ended", etc.

hth


Help needed.

I have created a master form (Employees) with a subform (Service Record).
The Service Record subform has fields ServiceRecordID, EmployeeID,
DepartmentName, JobTitleName, DateStart, DateEnd, and WeeksService. I
want
to use the subform to track an employee's service time in each department
he/she works during their time of employment along with the various job
titles that he/she may hold. I have created a query (Service Record
Query)
with a field titled
"Status". If the DateEnd field is left blank, "Current" will
automatically
fill this field. If DateEnd has a date, the field fills with "Null".

I am currently using the following code to retrieve each employees current
department and job title:

Private Sub Form_Current()
Dim s As String
Dim rs As Recordset

s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

It works great for employees that have "Current" in the Status field of
the
Service Record Query, but I am getting the following error message for
employees that have "Null" in the Status field.

Run-Time Error '-2147352567 (80020009)': The
value you entered is not valid for this field.

I assume this is because the system is lost when it can't find "Current"
in
the Status field? I want to separate the current employees from the
terminated (Null in the Status field) employees for reporting purposes. I
would have thought that the Else statement at the end of the code would
have
taken care of the problem, but I am still green and do not have a clear
understanding of code yet.

I was advised to use the following code to solve the problem, but I didn't
have any luck with it either:

Private Sub Form Current()
Dim s As String
Dim rs As Recordset

Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null

If Not Me.NewRecord Then
If [Status] = "Current" Then
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
End If
End If

Can anyone offer a solution? Any help is greatly appreciated. Thanks
Click to expand...
Click to expand...
Click to expand...
 
T

tina

you're welcome :)


JudyB said:
New code did the trick! Thank you so much. Didn't think I would ever get
past that problem.

--
JudyB


tina said:
well, no reason to thank me, i read your post in a hurry, and gave you a
poor answer. i'll see if i can improve on it now!
I
would have thought that the Else statement at the end of the code would
have
taken care of the problem, but I am still green and do not have a clear
understanding of code yet.
Click to expand...

no, the Else statement only runs when the current record is a new record -
the If condition doesn't consider job status at all. your code is erring
when the current record is *not* a new record, and the SQL statement doesn't
return a record, so the code is attempting to assign values that do not
exist because there is no record in the recordset. and you really don't need
to check the "Status" anyway; it's a calculated value based on the value in
a hard data field, so i'd just check that field directly. try something like

Private Sub Form_Current()

Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me!EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me!Current_Job_Title_Name = rs!JobTitleName
Me!Current_Department_Name = rs!DepartmentName
End If
rs.Close
Set rs = Nothing
End If

End Sub

the recordset only opens when the current record is not a new record, as in
your posted code. but then the code checks to see if there is a record
present, assigns the field values only if there is; and then closes and
disposes of the recordset in all cases.

notice also that i specified the recordset as a DAO recordset. it's a good
habit to get into, to avoid possible conflicts between DAO and ADO
properties and methods as you work with recordsets in code.

hth


JudyB said:
Hi Tina,
Thanks for suggestion. It didn't do the trick. Got any other
suggestions? Does the code look right to you? Thanks again!
--
JudyB


:

Null is a value that has a specific meaning in Access, so you might try
replacing it in your Status field with something else, like
Click to expand...
"Previous"
or
"Ended", etc.

hth


Help needed.

I have created a master form (Employees) with a subform (Service Record).
The Service Record subform has fields ServiceRecordID, EmployeeID,
DepartmentName, JobTitleName, DateStart, DateEnd, and WeeksService. I
want
to use the subform to track an employee's service time in each department
he/she works during their time of employment along with the
Click to expand...
various
job
titles that he/she may hold. I have created a query (Service Record
Query)
with a field titled
"Status". If the DateEnd field is left blank, "Current" will
automatically
fill this field. If DateEnd has a date, the field fills with "Null".

I am currently using the following code to retrieve each employees current
department and job title:

Private Sub Form_Current()
Dim s As String
Dim rs As Recordset

s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

It works great for employees that have "Current" in the Status
Click to expand...
field
of
the
Service Record Query, but I am getting the following error message for
employees that have "Null" in the Status field.

Run-Time Error '-2147352567 (80020009)': The
value you entered is not valid for this field.

I assume this is because the system is lost when it can't find "Current"
in
the Status field? I want to separate the current employees from the
terminated (Null in the Status field) employees for reporting purposes. I
would have thought that the Else statement at the end of the code would
have
taken care of the problem, but I am still green and do not have a clear
understanding of code yet.

I was advised to use the following code to solve the problem, but
Click to expand...
I
didn't
have any luck with it either:

Private Sub Form Current()
Dim s As String
Dim rs As Recordset

Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null

If Not Me.NewRecord Then
If [Status] = "Current" Then
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = 'Current';"
Set rs = CurrentDb.OpenRecordset(s)
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
rs.Close
Set rs = Nothing
End If
End If

Can anyone offer a solution? Any help is greatly appreciated. Thanks
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

Retrieving data from recordsets 6
Retrieving data from query to form 3
DLookup or Code 2
Help editing record set 4
Want to avoid the possibilty to edit field in listbox 6
Adding "If Statement" with DSum 1
Modification to current code 5
query to textbox 1

Top