cycling though records

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

Hello All

I have a form [staffs subform new] that is normally in single form view, and
I need to cycle through a subset of the records and perform a particular
action on them (this action takes a number of parameters from the current
record on [staffs subform new]). Normally this is done by manually selecting
each record in turn, and then initiating the action, but I have refined the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just there
to help me get this working. The problem I'm having is that the filter seems
only to work once (the form moves to the first expected record), then I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct value of
!name. It's just that the form does not move on to the next record.

I think this is probably something simple - but I'm blowed if I can find it!

Hope someone can help.
Many thanks
Leslie Isaacs


My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT " &
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

'Me.FilterOn = False

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544] =
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
 
First, it appears you have a field named "name" -- Access treats this word
as a reserved word, and may not "mean" the same thing that you do.

Next, you appear to be iterating/looping through a recordset. Is there a
chance you could use a query instead to perform set-level operations instead
of one-by-one?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Hello Jeff

Thanks for your reply.

As I mentioned in the initial post, I am very aware that the field 'name'
is normally a no-no, but I inherited this application, it's complex ... and
it does work!! Also, I am sure that this field is not the problem here
because it is recognised and handled correctly by the MsgBox command within
the loop.
Regarding the possibility of using a query instead of looping, this really
wouldn't be feasible in this case - at least not without a great deal of
recoding in the main process. I had felt pretty sure that it would be
simpler to loop through the records: I realise that this will be slower, but
in fact the process once initiated could be left unattended for however long
it takes.

Hope that explains things better!

Thanks for any further advice.
Les



Jeff Boyce said:
First, it appears you have a field named "name" -- Access treats this word
as a reserved word, and may not "mean" the same thing that you do.

Next, you appear to be iterating/looping through a recordset. Is there a
chance you could use a query instead to perform set-level operations instead
of one-by-one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

PayeDoc said:
Hello All

I have a form [staffs subform new] that is normally in single form view,
and
I need to cycle through a subset of the records and perform a particular
action on them (this action takes a number of parameters from the current
record on [staffs subform new]). Normally this is done by manually
selecting
each record in turn, and then initiating the action, but I have refined
the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just
there
to help me get this working. The problem I'm having is that the filter
seems
only to work once (the form moves to the first expected record), then I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct value
of
!name. It's just that the form does not move on to the next record.

I think this is probably something simple - but I'm blowed if I can find
it!

Hope someone can help.
Many thanks
Leslie Isaacs


My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT "
&
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

'Me.FilterOn = False

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544] =
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...
Click to expand...
 
Leslie

I guess I'm not real clear on what you want to have happen.

Repeated setting (then re-setting, then re-setting, ...) a filter will allow
you to do ... ?

If you step back from the "how" and consider the "what", what business need
will you be able to satisfy by "performing an action on a subset of the
records". For instance, I can imagine that someone with a list of folks and
their email addresses might want to update the list to show those to whom an
email was sent.

Depending on what you need to accomplish, it may still be less work to use a
query. For instance, if you had a sublist of folks in my example who were
to be sent an email, you could use that sublist, joined to the table in a
query, to do an update on the table.

?More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Leslie Isaacs said:
Hello Jeff

Thanks for your reply.

As I mentioned in the initial post, I am very aware that the field 'name'
is normally a no-no, but I inherited this application, it's complex ...
and it does work!! Also, I am sure that this field is not the problem here
because it is recognised and handled correctly by the MsgBox command
within the loop.
Regarding the possibility of using a query instead of looping, this
really wouldn't be feasible in this case - at least not without a great
deal of recoding in the main process. I had felt pretty sure that it would
be simpler to loop through the records: I realise that this will be
slower, but in fact the process once initiated could be left unattended
for however long it takes.

Hope that explains things better!

Thanks for any further advice.
Les



Jeff Boyce said:
First, it appears you have a field named "name" -- Access treats this word
as a reserved word, and may not "mean" the same thing that you do.

Next, you appear to be iterating/looping through a recordset. Is there a
chance you could use a query instead to perform set-level operations instead
of one-by-one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

PayeDoc said:
Hello All

I have a form [staffs subform new] that is normally in single form view,
and
I need to cycle through a subset of the records and perform a particular
action on them (this action takes a number of parameters from the current
record on [staffs subform new]). Normally this is done by manually
selecting
each record in turn, and then initiating the action, but I have refined
the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just
there
to help me get this working. The problem I'm having is that the filter
seems
only to work once (the form moves to the first expected record), then I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct value
of
!name. It's just that the form does not move on to the next record.

I think this is probably something simple - but I'm blowed if I can find
it!

Hope someone can help.
Many thanks
Leslie Isaacs


My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT "
&
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

'Me.FilterOn = False

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544] =
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
PayeDoc said:
I have a form [staffs subform new] that is normally in single form view, and
I need to cycle through a subset of the records and perform a particular
action on them (this action takes a number of parameters from the current
record on [staffs subform new]). Normally this is done by manually selecting
each record in turn, and then initiating the action, but I have refined the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just there
to help me get this working. The problem I'm having is that the filter seems
only to work once (the form moves to the first expected record), then I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct value of
!name. It's just that the form does not move on to the next record.

My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT " &
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544] =
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...

Assuming the name field is type Text:

Me.Filter = "[forms]![staffs subform new]!Text544=""" &
!name & """"
 
Hello Marshall

Many thanks for your reply. Your amended syntax succeeded in stopping the
message about being asked for the parameter value !name, so now I get the
expected succession of MsgBox actions ("Found John Smith", then "Found Fred
Brown", etc.), but in fact the form seems only to find and then retain the
record of the first person found - it does not find any of the subsequent
records - i.e. even though I get the MsgBox "Found Fred Brown" the form does
not display the record for Fred Brown. I know that Fred Brown is in the
form's underlying dataset, because I can find that record manually (using a
combobox). It may be relevant that the form is normally filtered by default
to show a particular subset of the form's main record source, and that in
order to find an I first have to select the appropriate subset (using
another combobox). But if I do right-click>remove filter, I can find any of
the main dataset records. I have tried adding Me.FilterOn = False to the
loop after the MsgBox action, to remove the previous filter, but this hasn't
helped.

So I hope you can!

Thanks again
Les



Marshall Barton said:
PayeDoc said:
I have a form [staffs subform new] that is normally in single form view, and
I need to cycle through a subset of the records and perform a particular
action on them (this action takes a number of parameters from the current
record on [staffs subform new]). Normally this is done by manually selecting
each record in turn, and then initiating the action, but I have refined the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just there
to help me get this working. The problem I'm having is that the filter seems
only to work once (the form moves to the first expected record), then I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct value of
!name. It's just that the form does not move on to the next record.

My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT " &
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544] =
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...

Assuming the name field is type Text:

Me.Filter = "[forms]![staffs subform new]!Text544=""" &
!name & """"
Click to expand...
 
Hello Jeff

Thanks for your further reply.

OK: you asked for it!

The 'particular action' that needs to happen for each of the records that I
was hoping to loop through is in fact two procedures, the 2nd of which
contains 3 sub-procedures. The first main procedure is to build an XML file,
and the 2nd main procedure is to send the XML file to a government server.
The sub-procedures involve polling the government server, checking for
response, and recording the 'submission complete' response. All this works
fine, one at a time, but now I'm just trying to allow the process to run as
a batched job. Typically it will need to be done for 50-100 individual
records, once/month, with each record representing an employee - for whom
there is data in 4 tables that needs to be included in the XML file.

It occurs to me that it is only the first procedure (the creation of the XML
file) that takes data from the current form ([staffs subform new]), where I
was intending to loop through the records. This first procedure uses the
recordset below, and - if I understand your suggestion correctly - perhaps
it would be better to put this strSQL expression into a loop rather that
looping the form: is that what you had in mind? If so ... I would need help!

Hope you don't give up on me.

Thanks again
Les

The recordset that is used to create the XML file is:

strSQL = "SELECT [x confirmed].[name], practices.[prac name], Max([x
confirmed].period) AS MaxOfperiod, Sum([x confirmed].[pay liable for tax 5])
AS [SumOfpay liable for tax 5], Last([x confirmed].[ytd tax due 6]) AS
[LastOfytd tax due 6], Sum([x confirmed].[ytd tax due 6]) AS [SumOfytd tax
due 6], Sum([x confirmed].[tax pd in mth 7]) AS [SumOftax pd in mth 7],
Sum([x confirmed].[oride tax ytd 6]) AS [SumOforide tax ytd 6], Sum([x
confirmed].[pay liable tax mth]) AS [SumOfpay liable tax mth], Sum([x
confirmed].[overide pay]) AS [SumOfoveride pay], Sum([SumOftaxable pay
2]+[overide pay]) AS [p11 pay ytd], practices.add1, practices.add2,
practices.add3, practices.postcode, practices.[paye ref], practices.[tax
district no], staffs.[mth 1 basis], [staffs]![Tax code no] & [staffs]![Tax
code ltr] AS [c tax cod], staffs.[first name], staffs.surname, staffs.DOB,
staffs.[leaving date], staffs.[staff add1], staffs.[staff add2],
staffs.[staff add3], staffs.[staff postcode], staffs.[type name],
staffs.title, "
strSQL = strSQL & "Last([x confirmed].[SumOftaxable pay 2]) AS
[LastOfSumOftaxable pay 2], staffs.[NI number], staffs.[date left prev emp],
staffs.[student loan], Sum(IIf([months]![month name] Not Like 'prev' &
'*',[x confirmed]![pay liable tax mth],0)) AS [this emp taxable pay],
Sum(IIf([months]![month name] Not Like 'prev' & '*',[x confirmed]![tax pd
in mth 7],0)) AS [this emp tax], staffs.sex, staffs.[tax code ltr],
staffs.[tax code no], staffs.[employment start], staffs.[prev paye office],
staffs.[prev monthorweek], staffs.[prev paye ref], Sum(IIf([months]![month
name] Like 'prev' & '*',[x confirmed]![pay liable tax mth],0)) AS [pre emp
taxable pay], Sum(IIf([months]![month name] Like 'prev' & '*',[x
confirmed]![tax pd in mth 7],0)) AS [pre emp tax], IIf([months]![month name]
Like 'prev' & '*',IIf([month number] Mod 12=0,12,[month number] Mod 12),0)
AS [pre emp period] "
strSQL = strSQL & "FROM (practices INNER JOIN staffs ON
practices.[prac name] = staffs.practice) INNER JOIN (months INNER JOIN [x
confirmed] ON months.[month name] = [x confirmed].[month name]) ON
staffs.name = [x confirmed].name "
strSQL = strSQL & "WHERE ((([x confirmed].[month number]) > " &
[Forms]![frm x main]![number] - [Forms]![frm x main]![period] & ")) "
'strSQL = strSQL & "WHERE [x confirmed].[month number] > " &
[Forms]![frm x main]![number]
strSQL = strSQL & "GROUP BY [x confirmed].name, practices.[prac
name], practices.add1, practices.add2, practices.add3, practices.postcode,
practices.[paye ref], practices.[tax district no], staffs.[date left prev
emp], staffs.[mth 1 basis], [staffs]![Tax code no] & [staffs]![Tax code
ltr], staffs.[first name], staffs.surname, staffs.DOB, staffs.[leaving
date], staffs.[staff add1], staffs.[staff add2], staffs.[staff add3],
staffs.[staff postcode], staffs.title, staffs.[NI number], staffs.[student
loan], staffs.sex, staffs.[tax code ltr], staffs.[type name], staffs.[tax
code no], staffs.[employment start], staffs.[prev paye office], staffs.[prev
monthorweek], staffs.[prev paye ref], IIf([months]![month name] Like 'prev'
& '*',IIf([month number] Mod 12=0,12,[month number] Mod 12),0) "
strSQL = strSQL & "HAVING ((([x confirmed].name)=" & Chr(34) &
[Forms]![staffs subform new]![name] & Chr(34) & "))"
strSQL = strSQL & " ORDER BY [x confirmed].Name" 'is this needed to
avoid "too few parameters - expected 3" error?



Jeff Boyce said:
Leslie

I guess I'm not real clear on what you want to have happen.

Repeated setting (then re-setting, then re-setting, ...) a filter will allow
you to do ... ?

If you step back from the "how" and consider the "what", what business need
will you be able to satisfy by "performing an action on a subset of the
records". For instance, I can imagine that someone with a list of folks and
their email addresses might want to update the list to show those to whom an
email was sent.

Depending on what you need to accomplish, it may still be less work to use a
query. For instance, if you had a sublist of folks in my example who were
to be sent an email, you could use that sublist, joined to the table in a
query, to do an update on the table.

?More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Leslie Isaacs said:
Hello Jeff

Thanks for your reply.

As I mentioned in the initial post, I am very aware that the field 'name'
is normally a no-no, but I inherited this application, it's complex ...
and it does work!! Also, I am sure that this field is not the problem here
because it is recognised and handled correctly by the MsgBox command
within the loop.
Regarding the possibility of using a query instead of looping, this
really wouldn't be feasible in this case - at least not without a great
deal of recoding in the main process. I had felt pretty sure that it would
be simpler to loop through the records: I realise that this will be
slower, but in fact the process once initiated could be left unattended
for however long it takes.

Hope that explains things better!

Thanks for any further advice.
Les



Jeff Boyce said:
First, it appears you have a field named "name" -- Access treats this word
as a reserved word, and may not "mean" the same thing that you do.

Next, you appear to be iterating/looping through a recordset. Is
Click to expand...
there
a
chance you could use a query instead to perform set-level operations instead
of one-by-one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All

I have a form [staffs subform new] that is normally in single form view,
and
I need to cycle through a subset of the records and perform a particular
action on them (this action takes a number of parameters from the current
record on [staffs subform new]). Normally this is done by manually
selecting
each record in turn, and then initiating the action, but I have refined
the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records)
Click to expand...
could
be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just
there
to help me get this working. The problem I'm having is that the filter
seems
only to work once (the form moves to the first expected record),
Click to expand...
then
I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct value
of
!name. It's just that the form does not move on to the next record.

I think this is probably something simple - but I'm blowed if I can find
it!

Hope someone can help.
Many thanks
Leslie Isaacs


My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT "
&
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

'Me.FilterOn = False

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform
Click to expand...
new]![Text544]
=
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Leslie

I'm not clear yet on why the FORM needs to be cycled through. Since you're
using a recordset and doing something with each record in that recordset,
why use a form and a filter?

I'd probably approach this from the standpoint of first identifying all the
records I need to work with (ahem! this might be a query <g>). Then, given
the multiple processes you're describing, I'd loop through the records in
that recordset, performing the functions/procedures for each item.

What will working via the form help you/your users do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

PayeDoc said:
Hello Jeff

Thanks for your further reply.

OK: you asked for it!

The 'particular action' that needs to happen for each of the records that
I
was hoping to loop through is in fact two procedures, the 2nd of which
contains 3 sub-procedures. The first main procedure is to build an XML
file,
and the 2nd main procedure is to send the XML file to a government server.
The sub-procedures involve polling the government server, checking for
response, and recording the 'submission complete' response. All this works
fine, one at a time, but now I'm just trying to allow the process to run
as
a batched job. Typically it will need to be done for 50-100 individual
records, once/month, with each record representing an employee - for whom
there is data in 4 tables that needs to be included in the XML file.

It occurs to me that it is only the first procedure (the creation of the
XML
file) that takes data from the current form ([staffs subform new]), where
I
was intending to loop through the records. This first procedure uses the
recordset below, and - if I understand your suggestion correctly - perhaps
it would be better to put this strSQL expression into a loop rather that
looping the form: is that what you had in mind? If so ... I would need
help!

Hope you don't give up on me.

Thanks again
Les

The recordset that is used to create the XML file is:

strSQL = "SELECT [x confirmed].[name], practices.[prac name],
Max([x
confirmed].period) AS MaxOfperiod, Sum([x confirmed].[pay liable for tax
5])
AS [SumOfpay liable for tax 5], Last([x confirmed].[ytd tax due 6]) AS
[LastOfytd tax due 6], Sum([x confirmed].[ytd tax due 6]) AS [SumOfytd tax
due 6], Sum([x confirmed].[tax pd in mth 7]) AS [SumOftax pd in mth 7],
Sum([x confirmed].[oride tax ytd 6]) AS [SumOforide tax ytd 6], Sum([x
confirmed].[pay liable tax mth]) AS [SumOfpay liable tax mth], Sum([x
confirmed].[overide pay]) AS [SumOfoveride pay], Sum([SumOftaxable pay
2]+[overide pay]) AS [p11 pay ytd], practices.add1, practices.add2,
practices.add3, practices.postcode, practices.[paye ref], practices.[tax
district no], staffs.[mth 1 basis], [staffs]![Tax code no] & [staffs]![Tax
code ltr] AS [c tax cod], staffs.[first name], staffs.surname, staffs.DOB,
staffs.[leaving date], staffs.[staff add1], staffs.[staff add2],
staffs.[staff add3], staffs.[staff postcode], staffs.[type name],
staffs.title, "
strSQL = strSQL & "Last([x confirmed].[SumOftaxable pay 2]) AS
[LastOfSumOftaxable pay 2], staffs.[NI number], staffs.[date left prev
emp],
staffs.[student loan], Sum(IIf([months]![month name] Not Like 'prev' &
'*',[x confirmed]![pay liable tax mth],0)) AS [this emp taxable pay],
Sum(IIf([months]![month name] Not Like 'prev' & '*',[x confirmed]![tax pd
in mth 7],0)) AS [this emp tax], staffs.sex, staffs.[tax code ltr],
staffs.[tax code no], staffs.[employment start], staffs.[prev paye
office],
staffs.[prev monthorweek], staffs.[prev paye ref], Sum(IIf([months]![month
name] Like 'prev' & '*',[x confirmed]![pay liable tax mth],0)) AS [pre emp
taxable pay], Sum(IIf([months]![month name] Like 'prev' & '*',[x
confirmed]![tax pd in mth 7],0)) AS [pre emp tax], IIf([months]![month
name]
Like 'prev' & '*',IIf([month number] Mod 12=0,12,[month number] Mod 12),0)
AS [pre emp period] "
strSQL = strSQL & "FROM (practices INNER JOIN staffs ON
practices.[prac name] = staffs.practice) INNER JOIN (months INNER JOIN [x
confirmed] ON months.[month name] = [x confirmed].[month name]) ON
staffs.name = [x confirmed].name "
strSQL = strSQL & "WHERE ((([x confirmed].[month number]) > " &
[Forms]![frm x main]![number] - [Forms]![frm x main]![period] & ")) "
'strSQL = strSQL & "WHERE [x confirmed].[month number] > " &
[Forms]![frm x main]![number]
strSQL = strSQL & "GROUP BY [x confirmed].name, practices.[prac
name], practices.add1, practices.add2, practices.add3, practices.postcode,
practices.[paye ref], practices.[tax district no], staffs.[date left prev
emp], staffs.[mth 1 basis], [staffs]![Tax code no] & [staffs]![Tax code
ltr], staffs.[first name], staffs.surname, staffs.DOB, staffs.[leaving
date], staffs.[staff add1], staffs.[staff add2], staffs.[staff add3],
staffs.[staff postcode], staffs.title, staffs.[NI number], staffs.[student
loan], staffs.sex, staffs.[tax code ltr], staffs.[type name], staffs.[tax
code no], staffs.[employment start], staffs.[prev paye office],
staffs.[prev
monthorweek], staffs.[prev paye ref], IIf([months]![month name] Like
'prev'
& '*',IIf([month number] Mod 12=0,12,[month number] Mod 12),0) "
strSQL = strSQL & "HAVING ((([x confirmed].name)=" & Chr(34) &
[Forms]![staffs subform new]![name] & Chr(34) & "))"
strSQL = strSQL & " ORDER BY [x confirmed].Name" 'is this needed to
avoid "too few parameters - expected 3" error?



Jeff Boyce said:
Leslie

I guess I'm not real clear on what you want to have happen.

Repeated setting (then re-setting, then re-setting, ...) a filter will allow
you to do ... ?

If you step back from the "how" and consider the "what", what business need
will you be able to satisfy by "performing an action on a subset of the
records". For instance, I can imagine that someone with a list of folks and
their email addresses might want to update the list to show those to whom an
email was sent.

Depending on what you need to accomplish, it may still be less work to
use a
query. For instance, if you had a sublist of folks in my example who
were
to be sent an email, you could use that sublist, joined to the table in a
query, to do an update on the table.

?More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Leslie Isaacs said:
Hello Jeff

Thanks for your reply.

As I mentioned in the initial post, I am very aware that the field 'name'
is normally a no-no, but I inherited this application, it's complex ...
and it does work!! Also, I am sure that this field is not the problem here
because it is recognised and handled correctly by the MsgBox command
within the loop.
Regarding the possibility of using a query instead of looping, this
really wouldn't be feasible in this case - at least not without a great
deal of recoding in the main process. I had felt pretty sure that it would
be simpler to loop through the records: I realise that this will be
slower, but in fact the process once initiated could be left unattended
for however long it takes.

Hope that explains things better!

Thanks for any further advice.
Les



First, it appears you have a field named "name" -- Access treats
this
word
as a reserved word, and may not "mean" the same thing that you do.

Next, you appear to be iterating/looping through a recordset. Is there
a
chance you could use a query instead to perform set-level operations
instead
of one-by-one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All

I have a form [staffs subform new] that is normally in single form
view,
and
I need to cycle through a subset of the records and perform a
particular
action on them (this action takes a number of parameters from the
current
record on [staffs subform new]). Normally this is done by manually
selecting
each record in turn, and then initiating the action, but I have
refined
the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could
be
initiated from single mouse-click. The procedure I have is below. The
required action would go in place of the MsgBox command, which is just
there
to help me get this working. The problem I'm having is that the filter
seems
only to work once (the form moves to the first expected record), then
I'm
asked for a parameter value !name. Yes, I know 'name' is probably the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct
value
of
!name. It's just that the form does not move on to the next record.

I think this is probably something simple - but I'm blowed if I can
find
it!

Hope someone can help.
Many thanks
Leslie Isaacs


My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code],
staffs.hasLEFT "
&
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

'Me.FilterOn = False

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544]
=
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
PayeDoc said:
Many thanks for your reply. Your amended syntax succeeded in stopping the
message about being asked for the parameter value !name, so now I get the
expected succession of MsgBox actions ("Found John Smith", then "Found Fred
Brown", etc.), but in fact the form seems only to find and then retain the
record of the first person found - it does not find any of the subsequent
records - i.e. even though I get the MsgBox "Found Fred Brown" the form does
not display the record for Fred Brown. I know that Fred Brown is in the
form's underlying dataset, because I can find that record manually (using a
combobox). It may be relevant that the form is normally filtered by default
to show a particular subset of the form's main record source, and that in
order to find an I first have to select the appropriate subset (using
another combobox). But if I do right-click>remove filter, I can find any of
the main dataset records. I have tried adding Me.FilterOn = False to the
loop after the MsgBox action, to remove the previous filter, but this hasn't
helped.
Click to expand...


That's what happens when you ask for a fix to some code
instead of briefly describing WHAT you are trying to
accomplish with the code. I guess my take on your problem
just takes you back to the point that Jeff is struggling
with.

If you want to use the form to **navigate** to a record
instead of filtering the form's recordset to a subset of its
records. then you should use the FindFirst method and forget
about repeatedly filtering the form. OTOH, you say you
wanted to perform some operation on the filtered records so
I don't see where navigation to and displaying other records
comes into it.
 
Hello Jeff and Marshall

I really appreciate your help with this.

My reason for cycling through the form was simply that I knew that the
processes currently all work by referring to the form: the user just has to
select each record in turn, using the combobox on the form, and then
initiate the processes. It seemed to me (before starting this thread!) that
the simplest way of getting the processes to run 'automatically' for each
required record was to replace the user's manual selection of successive
records with a mechanism that would loop through them. In light of your
comments, however, I'm now coming to realise that it may be better to put
the process itself in the loop. As you say, the required recordset is easily
defined by a query - in fact this is the query that I gave in the initial
post, which I was trying to use to apply successive filters to the form!

So, the recordset from which the XML file is created is given by the long
strSQL expression in my last post in this thread. This expression contains
the parameter:

"HAVING ((([x confirmed].name)=" & Chr(34) & [Forms]![staffs subform
new]![name] & Chr(34) & "))"

I'm at (beyond!) the limits of my VBA knowledge now, but would I replace
this with

"HAVING ((([x confirmed].name)=" & Chr(34) & !name & Chr(34) & "))"

and then put the whole of the long strSQL expression inside the loop:

Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date], staffs.[P45
pt1 to IR], staffs.payroll, staffs.[NI code], staffs.hasLEFT "& _
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND((staffs.hasLEFT)=True));")
With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF

'put long strSQL expression here

.MoveNext
Loop
End If
End With

Am I close?

Many thanks for for continued help.
Les



Jeff Boyce said:
Leslie

I'm not clear yet on why the FORM needs to be cycled through. Since you're
using a recordset and doing something with each record in that recordset,
why use a form and a filter?

I'd probably approach this from the standpoint of first identifying all the
records I need to work with (ahem! this might be a query <g>). Then, given
the multiple processes you're describing, I'd loop through the records in
that recordset, performing the functions/procedures for each item.

What will working via the form help you/your users do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

PayeDoc said:
Hello Jeff

Thanks for your further reply.

OK: you asked for it!

The 'particular action' that needs to happen for each of the records that
I
was hoping to loop through is in fact two procedures, the 2nd of which
contains 3 sub-procedures. The first main procedure is to build an XML
file,
and the 2nd main procedure is to send the XML file to a government server.
The sub-procedures involve polling the government server, checking for
response, and recording the 'submission complete' response. All this works
fine, one at a time, but now I'm just trying to allow the process to run
as
a batched job. Typically it will need to be done for 50-100 individual
records, once/month, with each record representing an employee - for whom
there is data in 4 tables that needs to be included in the XML file.

It occurs to me that it is only the first procedure (the creation of the
XML
file) that takes data from the current form ([staffs subform new]), where
I
was intending to loop through the records. This first procedure uses the
recordset below, and - if I understand your suggestion correctly - perhaps
it would be better to put this strSQL expression into a loop rather that
looping the form: is that what you had in mind? If so ... I would need
help!

Hope you don't give up on me.

Thanks again
Les

The recordset that is used to create the XML file is:

strSQL = "SELECT [x confirmed].[name], practices.[prac name],
Max([x
confirmed].period) AS MaxOfperiod, Sum([x confirmed].[pay liable for tax
5])
AS [SumOfpay liable for tax 5], Last([x confirmed].[ytd tax due 6]) AS
[LastOfytd tax due 6], Sum([x confirmed].[ytd tax due 6]) AS [SumOfytd tax
due 6], Sum([x confirmed].[tax pd in mth 7]) AS [SumOftax pd in mth 7],
Sum([x confirmed].[oride tax ytd 6]) AS [SumOforide tax ytd 6], Sum([x
confirmed].[pay liable tax mth]) AS [SumOfpay liable tax mth], Sum([x
confirmed].[overide pay]) AS [SumOfoveride pay], Sum([SumOftaxable pay
2]+[overide pay]) AS [p11 pay ytd], practices.add1, practices.add2,
practices.add3, practices.postcode, practices.[paye ref], practices.[tax
district no], staffs.[mth 1 basis], [staffs]![Tax code no] & [staffs]![Tax
code ltr] AS [c tax cod], staffs.[first name], staffs.surname, staffs.DOB,
staffs.[leaving date], staffs.[staff add1], staffs.[staff add2],
staffs.[staff add3], staffs.[staff postcode], staffs.[type name],
staffs.title, "
strSQL = strSQL & "Last([x confirmed].[SumOftaxable pay 2]) AS
[LastOfSumOftaxable pay 2], staffs.[NI number], staffs.[date left prev
emp],
staffs.[student loan], Sum(IIf([months]![month name] Not Like 'prev' &
'*',[x confirmed]![pay liable tax mth],0)) AS [this emp taxable pay],
Sum(IIf([months]![month name] Not Like 'prev' & '*',[x confirmed]![tax pd
in mth 7],0)) AS [this emp tax], staffs.sex, staffs.[tax code ltr],
staffs.[tax code no], staffs.[employment start], staffs.[prev paye
office],
staffs.[prev monthorweek], staffs.[prev paye ref], Sum(IIf([months]![month
name] Like 'prev' & '*',[x confirmed]![pay liable tax mth],0)) AS [pre emp
taxable pay], Sum(IIf([months]![month name] Like 'prev' & '*',[x
confirmed]![tax pd in mth 7],0)) AS [pre emp tax], IIf([months]![month
name]
Like 'prev' & '*',IIf([month number] Mod 12=0,12,[month number] Mod 12),0)
AS [pre emp period] "
strSQL = strSQL & "FROM (practices INNER JOIN staffs ON
practices.[prac name] = staffs.practice) INNER JOIN (months INNER JOIN [x
confirmed] ON months.[month name] = [x confirmed].[month name]) ON
staffs.name = [x confirmed].name "
strSQL = strSQL & "WHERE ((([x confirmed].[month number]) > " &
[Forms]![frm x main]![number] - [Forms]![frm x main]![period] & ")) "
'strSQL = strSQL & "WHERE [x confirmed].[month number] > " &
[Forms]![frm x main]![number]
strSQL = strSQL & "GROUP BY [x confirmed].name, practices.[prac
name], practices.add1, practices.add2, practices.add3, practices.postcode,
practices.[paye ref], practices.[tax district no], staffs.[date left prev
emp], staffs.[mth 1 basis], [staffs]![Tax code no] & [staffs]![Tax code
ltr], staffs.[first name], staffs.surname, staffs.DOB, staffs.[leaving
date], staffs.[staff add1], staffs.[staff add2], staffs.[staff add3],
staffs.[staff postcode], staffs.title, staffs.[NI number], staffs.[student
loan], staffs.sex, staffs.[tax code ltr], staffs.[type name], staffs.[tax
code no], staffs.[employment start], staffs.[prev paye office],
staffs.[prev
monthorweek], staffs.[prev paye ref], IIf([months]![month name] Like
'prev'
& '*',IIf([month number] Mod 12=0,12,[month number] Mod 12),0) "
strSQL = strSQL & "HAVING ((([x confirmed].name)=" & Chr(34) &
[Forms]![staffs subform new]![name] & Chr(34) & "))"
strSQL = strSQL & " ORDER BY [x confirmed].Name"



Jeff Boyce said:
Leslie

I guess I'm not real clear on what you want to have happen.

Repeated setting (then re-setting, then re-setting, ...) a filter will allow
you to do ... ?

If you step back from the "how" and consider the "what", what business need
will you be able to satisfy by "performing an action on a subset of the
records". For instance, I can imagine that someone with a list of
Click to expand...
folks
and
their email addresses might want to update the list to show those to
Click to expand...
whom
an
email was sent.

Depending on what you need to accomplish, it may still be less work to
use a
query. For instance, if you had a sublist of folks in my example who
were
to be sent an email, you could use that sublist, joined to the table in a
query, to do an update on the table.

?More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff

Thanks for your reply.

As I mentioned in the initial post, I am very aware that the field 'name'
is normally a no-no, but I inherited this application, it's complex ....
and it does work!! Also, I am sure that this field is not the
Click to expand...
problem
here
because it is recognised and handled correctly by the MsgBox command
within the loop.
Regarding the possibility of using a query instead of looping, this
really wouldn't be feasible in this case - at least not without a great
deal of recoding in the main process. I had felt pretty sure that it would
be simpler to loop through the records: I realise that this will be
slower, but in fact the process once initiated could be left unattended
for however long it takes.

Hope that explains things better!

Thanks for any further advice.
Les



First, it appears you have a field named "name" -- Access treats
this
word
as a reserved word, and may not "mean" the same thing that you do.

Next, you appear to be iterating/looping through a recordset. Is there
a
chance you could use a query instead to perform set-level operations
instead
of one-by-one?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello All

I have a form [staffs subform new] that is normally in single form
view,
and
I need to cycle through a subset of the records and perform a
particular
action on them (this action takes a number of parameters from the
current
record on [staffs subform new]). Normally this is done by manually
selecting
each record in turn, and then initiating the action, but I have
refined
the
particular action such that it would now be feasible - and far more
efficient - if the whole process (for all the required records) could
be
initiated from single mouse-click. The procedure I have is
Click to expand...
below.
The
required action would go in place of the MsgBox command, which
Click to expand...
is
just
there
to help me get this working. The problem I'm having is that the filter
seems
only to work once (the form moves to the first expected record), then
I'm
asked for a parameter value !name. Yes, I know 'name' is
Click to expand...
probably
the
ultimate in bad naming protocols - but I inherited this and in fact
everything generally works! In any case, the line

MsgBox ("Found " & !name)

is working every time, so in the loop access is getting the correct
value
of
!name. It's just that the form does not move on to the next record.

I think this is probably something simple - but I'm blowed if I can
find
it!

Hope someone can help.
Many thanks
Leslie Isaacs


My code:

Private Sub Command919_Click()
Dim rstS As Recordset
Set rstS = CurrentDb.OpenRecordset( _
"SELECT staffs.practice, staffs.name, staffs.[leaving date],
staffs.[P45 pt1 to IR], staffs.payroll, staffs.[NI code],
staffs.hasLEFT "
&
_
"from staffs " & _
"WHERE (((staffs.[P45 pt1 to IR]) Is Null) AND
((staffs.hasLEFT)=True));")

'Me.FilterOn = False

With rstS
If .RecordCount Then
.MoveLast
.MoveFirst
Do Until .EOF
Me.Filter = "[forms]![staffs subform new]![Text544]
=
!name"
Me.FilterOn = True
MsgBox ("Found " & !name)
.MoveNext
Loop
End If
End With
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top