Retrieving data from recordsets

[JudyB]

Can anyone help with the following code:

Private Sub Form Current()
Dim s As String
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = Current;"

If Not Me.NewRecord Then
Me.Current_Job_Title_Name = CurrentDb.OpenRecordset(s).Fields(6)
Me.Current_Department_Name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me. Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

I am trying to retrieve the Job Title Name and the Department Name from the
Service Record Query into two unbound text boxes (Current Job Title Name and
Current Department Name) located on the main form titled Employees. I am
getting a Run-time error 3061: Too few parameters: Expected 1. Do not
understand that at all. Do I first need to create a recordset based on the
query? If so, how? I tried putting the following code above the Private Sub
Form Current() code:

myRecordSet.Open “SELECT [Service Record].ServiceRecordID, [Service
Record].EmployeeID, [Service Record].DepartmentName, [Service
Record].JobTitleName, [Service Record].DateStart, [Service Record].DateEnd,
CalcWeeks([DateStart],[DateEnd]) AS WeeksService, [Service Record].Reason,
IIf(IsNull([DateEnd]),’Current’,’Null’) AS Status FROM [Service Record]â€

Another brick wall. Can anyone point me in the right direction? Am I even
close? Thanks in advance.

 

[bcap]

Try this:

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

 

[JudyB]

Thanks for reply. I tried your suggestion, but had no luck. I am getting a
Run-time Error 3265 - Item Not Found in this Collection. When I select the
Debug button, it has the "Me.Current_Job_Title_Name =
CurrentDB.OpenRecordset(s).Fields(6)" line highlighted. Any other
suggestions?
--
JudyB

Try this:

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

Can anyone help with the following code:

Private Sub Form Current()
Dim s As String
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = Current;"

If Not Me.NewRecord Then
Me.Current_Job_Title_Name = CurrentDb.OpenRecordset(s).Fields(6)
Me.Current_Department_Name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me. Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

I am trying to retrieve the Job Title Name and the Department Name from
the
Service Record Query into two unbound text boxes (Current Job Title Name
and
Current Department Name) located on the main form titled Employees. I am
getting a Run-time error 3061: Too few parameters: Expected 1. Do not
understand that at all. Do I first need to create a recordset based on
the
query? If so, how? I tried putting the following code above the Private
Sub
Form Current() code:

myRecordSet.Open "SELECT [Service Record].ServiceRecordID, [Service
Record].EmployeeID, [Service Record].DepartmentName, [Service
Record].JobTitleName, [Service Record].DateStart, [Service
Record].DateEnd,
CalcWeeks([DateStart],[DateEnd]) AS WeeksService, [Service Record].Reason,
IIf(IsNull([DateEnd]),'Current','Null') AS Status FROM [Service Record]"

Another brick wall. Can anyone point me in the right direction? Am I
even
close? Thanks in advance.

 

[bcap]

Actually you have had some "luck". You are now getting a different error:
this means that my previous suggestion has corrected the original error you
posted, and we have now discovered a second error.

This second error is very simple: for some reason you are referring to the
seventh field in a recordset which only contains two fields!

You should be aware that:

1. The Fields collection of a recordset is zero-based: that is to say,
the first field has ordinal number zero, not 1.
2. It is in any case better to refer to the fields by name; you then do
not need to rely on fields having any particular ordinal number, and it
makes your code easier to read.
3. If you want to refer to more than one field in a recordset, you only
need to open the recordset once. There is no need to open the recordset for
every field, as you are doing.

Try this:

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

Thanks for reply. I tried your suggestion, but had no luck. I am getting
a
Run-time Error 3265 - Item Not Found in this Collection. When I select
the
Debug button, it has the "Me.Current_Job_Title_Name =
CurrentDB.OpenRecordset(s).Fields(6)" line highlighted. Any other
suggestions?
--
JudyB

Try this:

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

Can anyone help with the following code:

Private Sub Form Current()
Dim s As String
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = Current;"

If Not Me.NewRecord Then
Me.Current_Job_Title_Name = CurrentDb.OpenRecordset(s).Fields(6)
Me.Current_Department_Name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me. Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

I am trying to retrieve the Job Title Name and the Department Name from
the
Service Record Query into two unbound text boxes (Current Job Title
Name
and
Current Department Name) located on the main form titled Employees. I
am
getting a Run-time error 3061: Too few parameters: Expected 1. Do
not
understand that at all. Do I first need to create a recordset based on
the
query? If so, how? I tried putting the following code above the
Private
Sub
Form Current() code:

myRecordSet.Open "SELECT [Service Record].ServiceRecordID, [Service
Record].EmployeeID, [Service Record].DepartmentName, [Service
Record].JobTitleName, [Service Record].DateStart, [Service
Record].DateEnd,
CalcWeeks([DateStart],[DateEnd]) AS WeeksService, [Service
Record].Reason,
IIf(IsNull([DateEnd]),'Current','Null') AS Status FROM [Service
Record]"

Another brick wall. Can anyone point me in the right direction? Am I
even
close? Thanks in advance.

 

[JudyB]

Thank you so very much!. I have been struggling with that bit of code for
weeks. Now, one other thing. All works great on every record where the
"Status" field is current, indicating that the employee is still in that
particular job and department. Since I am getting an error message on
records where the "Status" field is not current. It looks as though I need
some additional code to indicate when an employee is no longer employed. Any
suggestions? Again, thank you so much for hanging with me and helping me
with this project. As you can probably tell...this is my first database and
it is really testing my limits.
--
JudyB

Actually you have had some "luck". You are now getting a different error:
this means that my previous suggestion has corrected the original error you
posted, and we have now discovered a second error.

This second error is very simple: for some reason you are referring to the
seventh field in a recordset which only contains two fields!

You should be aware that:

1. The Fields collection of a recordset is zero-based: that is to say,
the first field has ordinal number zero, not 1.
2. It is in any case better to refer to the fields by name; you then do
not need to rely on fields having any particular ordinal number, and it
makes your code easier to read.
3. If you want to refer to more than one field in a recordset, you only
need to open the recordset once. There is no need to open the recordset for
every field, as you are doing.

Try this:

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

Thanks for reply. I tried your suggestion, but had no luck. I am getting
a
Run-time Error 3265 - Item Not Found in this Collection. When I select
the
Debug button, it has the "Me.Current_Job_Title_Name =
CurrentDB.OpenRecordset(s).Fields(6)" line highlighted. Any other
suggestions?
--
JudyB

Try this:

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

Can anyone help with the following code:

Private Sub Form Current()
Dim s As String
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = Current;"

If Not Me.NewRecord Then
Me.Current_Job_Title_Name = CurrentDb.OpenRecordset(s).Fields(6)
Me.Current_Department_Name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me. Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

I am trying to retrieve the Job Title Name and the Department Name from
the
Service Record Query into two unbound text boxes (Current Job Title
Name
and
Current Department Name) located on the main form titled Employees. I
am
getting a Run-time error 3061: Too few parameters: Expected 1. Do
not
understand that at all. Do I first need to create a recordset based on
the
query? If so, how? I tried putting the following code above the
Private
Sub
Form Current() code:

myRecordSet.Open "SELECT [Service Record].ServiceRecordID, [Service
Record].EmployeeID, [Service Record].DepartmentName, [Service
Record].JobTitleName, [Service Record].DateStart, [Service
Record].DateEnd,
CalcWeeks([DateStart],[DateEnd]) AS WeeksService, [Service
Record].Reason,
IIf(IsNull([DateEnd]),'Current','Null') AS Status FROM [Service
Record]"

Another brick wall. Can anyone point me in the right direction? Am I
even
close? Thanks in advance.

 

[bcap]

Hi Judy,

It's not entirely clear to me what you want to happen when an employee is
not Current. However, I am assuming that Status is a field on the form you
are working in, and if so you could avoid the error by doing this:

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

End Sub

Thank you so very much!. I have been struggling with that bit of code for
weeks. Now, one other thing. All works great on every record where the
"Status" field is current, indicating that the employee is still in that
particular job and department. Since I am getting an error message on
records where the "Status" field is not current. It looks as though I
need
some additional code to indicate when an employee is no longer employed.
Any
suggestions? Again, thank you so much for hanging with me and helping me
with this project. As you can probably tell...this is my first database
and
it is really testing my limits.
--
JudyB

Actually you have had some "luck". You are now getting a different
error:
this means that my previous suggestion has corrected the original error
you
posted, and we have now discovered a second error.

This second error is very simple: for some reason you are referring to
the
seventh field in a recordset which only contains two fields!

You should be aware that:

1. The Fields collection of a recordset is zero-based: that is to say,
the first field has ordinal number zero, not 1.
2. It is in any case better to refer to the fields by name; you then
do
not need to rely on fields having any particular ordinal number, and it
makes your code easier to read.
3. If you want to refer to more than one field in a recordset, you
only
need to open the recordset once. There is no need to open the recordset
for
every field, as you are doing.

Try this:

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

Thanks for reply. I tried your suggestion, but had no luck. I am
getting
a
Run-time Error 3265 - Item Not Found in this Collection. When I select
the
Debug button, it has the "Me.Current_Job_Title_Name =
CurrentDB.OpenRecordset(s).Fields(6)" line highlighted. Any other
suggestions?
--
JudyB


:

Try this:

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

Can anyone help with the following code:

Private Sub Form Current()
Dim s As String
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = Current;"

If Not Me.NewRecord Then
Me.Current_Job_Title_Name = CurrentDb.OpenRecordset(s).Fields(6)
Me.Current_Department_Name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me. Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

I am trying to retrieve the Job Title Name and the Department Name
from
the
Service Record Query into two unbound text boxes (Current Job Title
Name
and
Current Department Name) located on the main form titled Employees.
I
am
getting a Run-time error 3061: Too few parameters: Expected 1. Do
not
understand that at all. Do I first need to create a recordset based
on
the
query? If so, how? I tried putting the following code above the
Private
Sub
Form Current() code:

myRecordSet.Open "SELECT [Service Record].ServiceRecordID, [Service
Record].EmployeeID, [Service Record].DepartmentName, [Service
Record].JobTitleName, [Service Record].DateStart, [Service
Record].DateEnd,
CalcWeeks([DateStart],[DateEnd]) AS WeeksService, [Service
Record].Reason,
IIf(IsNull([DateEnd]),'Current','Null') AS Status FROM [Service
Record]"

Another brick wall. Can anyone point me in the right direction?
Am I
even
close? Thanks in advance.

 

[JudyB]

Well...still having problems. Upon entering the new code, I get: Run-Time
Error '2465': Microsoft Office Access can't find the field "|" referred to
in your expression. Maybe if I provide a little more information about what
I am trying to do, you can tell me if I am on the right track or need to move
in a different direction.

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 and
job title. I have 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".

Using the first code you sent worked great on all employees that do not have
a date in the "DateEnd" field, but when I moved to an employee that has a
date in that field I get the 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 (date in "DateEnd" field)
employees for reporting purposes.

After I establish an employee's current department and job title, I want to
be able to sum the total service time an employee has worked in his/her
current department and job title. Example: If an employee works 52 weeks in
Sales Dept, moves to the Accounting Dept for 52 weeks, then returns to the
Sales Dept where he is currently working and has 10 weeks service, I want the
Total Dept Service field to return the results of 62 weeks.

Maybe you can offer a quicker and more logical method of getting the end
results. I am new (as you can tell) to coding and just trying to accomplish
one small step at a time. I really appreciate you time and help.
--
JudyB

Hi Judy,

It's not entirely clear to me what you want to happen when an employee is
not Current. However, I am assuming that Status is a field on the form you
are working in, and if so you could avoid the error by doing this:

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

End Sub

Thank you so very much!. I have been struggling with that bit of code for
weeks. Now, one other thing. All works great on every record where the
"Status" field is current, indicating that the employee is still in that
particular job and department. Since I am getting an error message on
records where the "Status" field is not current. It looks as though I
need
some additional code to indicate when an employee is no longer employed.
Any
suggestions? Again, thank you so much for hanging with me and helping me
with this project. As you can probably tell...this is my first database
and
it is really testing my limits.
--
JudyB

Actually you have had some "luck". You are now getting a different
error:
this means that my previous suggestion has corrected the original error
you
posted, and we have now discovered a second error.

This second error is very simple: for some reason you are referring to
the
seventh field in a recordset which only contains two fields!

You should be aware that:

1. The Fields collection of a recordset is zero-based: that is to say,
the first field has ordinal number zero, not 1.
2. It is in any case better to refer to the fields by name; you then
do
not need to rely on fields having any particular ordinal number, and it
makes your code easier to read.
3. If you want to refer to more than one field in a recordset, you
only
need to open the recordset once. There is no need to open the recordset
for
every field, as you are doing.

Try this:

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


Thanks for reply. I tried your suggestion, but had no luck. I am
getting
a
Run-time Error 3265 - Item Not Found in this Collection. When I select
the
Debug button, it has the "Me.Current_Job_Title_Name =
CurrentDB.OpenRecordset(s).Fields(6)" line highlighted. Any other
suggestions?
--
JudyB


:

Try this:

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

Can anyone help with the following code:

Private Sub Form Current()
Dim s As String
s = "SELECT JobTitleName,DepartmentName" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND Status = Current;"

If Not Me.NewRecord Then
Me.Current_Job_Title_Name = CurrentDb.OpenRecordset(s).Fields(6)
Me.Current_Department_Name = CurrentDb.OpenRecordset(s).Fields(1)
Else
Me. Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If

End Sub

I am trying to retrieve the Job Title Name and the Department Name
from
the
Service Record Query into two unbound text boxes (Current Job Title
Name
and
Current Department Name) located on the main form titled Employees.
I
am
getting a Run-time error 3061: Too few parameters: Expected 1. Do
not
understand that at all. Do I first need to create a recordset based
on
the
query? If so, how? I tried putting the following code above the
Private
Sub
Form Current() code:

myRecordSet.Open "SELECT [Service Record].ServiceRecordID, [Service
Record].EmployeeID, [Service Record].DepartmentName, [Service
Record].JobTitleName, [Service Record].DateStart, [Service
Record].DateEnd,
CalcWeeks([DateStart],[DateEnd]) AS WeeksService, [Service
Record].Reason,
IIf(IsNull([DateEnd]),'Current','Null') AS Status FROM [Service
Record]"

Another brick wall. Can anyone point me in the right direction?
Am I
even
close? Thanks in advance.