Skipping records in a report using code

[Guest]

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

 

[Guest]

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

 

[Guest]

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

 

[Guest]

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

 

[Guest]

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

What error are you getting?

Steve

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

:

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

No error. When you come to a record that runs the code, you can't move to
the next record. You can click next record, but you stay on the same record.
When you close the form, Access says that you cannot save this record at
this time.

What error are you getting?

Steve

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

:

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

:

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

So you can't move past the first record?

Can you post the code in your form's OnCurrent event?

Steve

No error. When you come to a record that runs the code, you can't move to
the next record. You can click next record, but you stay on the same record.
When you close the form, Access says that you cannot save this record at
this time.

What error are you getting?

Steve

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record
on close. I have stepped through the code and everything occurs exactly the
way I want. Any suggestions?

:

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

:

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

:

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

The form contains all student records so the code only runs when it
encounters a student who is attending in the fall. I thought that would
speed things up. Anyway, I have the code sitting in both the OnCurrent and
AfterUpdate events. I think that's the problem. The code runs when the
record opens and changes the record. Then the AfterUpdate event is
triggered. Access might think the record has been changed by another user.
Does that sound possible? Anyway, when I REM out the line that changes the
blnRegComplete variable that you suggested I add from one of the two events,
it works. Now I'm just trying to decide which event should have the line
deleted, or if I should write code that handles this situation.

So you can't move past the first record?

Can you post the code in your form's OnCurrent event?

Steve

No error. When you come to a record that runs the code, you can't move to
the next record. You can click next record, but you stay on the same record.
When you close the form, Access says that you cannot save this record at
this time.

What error are you getting?

Steve

:

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record
on close. I have stepped through the code and everything occurs exactly the
way I want. Any suggestions?

:

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

:

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

:

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

I'd keep the one in the form's OnCurrent event.

To make the code run when you change a value, put it in the AfterUpdate
event of any of those controls.

Steve

The form contains all student records so the code only runs when it
encounters a student who is attending in the fall. I thought that would
speed things up. Anyway, I have the code sitting in both the OnCurrent and
AfterUpdate events. I think that's the problem. The code runs when the
record opens and changes the record. Then the AfterUpdate event is
triggered. Access might think the record has been changed by another user.
Does that sound possible? Anyway, when I REM out the line that changes the
blnRegComplete variable that you suggested I add from one of the two events,
it works. Now I'm just trying to decide which event should have the line
deleted, or if I should write code that handles this situation.

So you can't move past the first record?

Can you post the code in your form's OnCurrent event?

Steve

No error. When you come to a record that runs the code, you can't move to
the next record. You can click next record, but you stay on the same record.
When you close the form, Access says that you cannot save this record at
this time.

:

What error are you getting?

Steve

:

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record
on close. I have stepped through the code and everything occurs exactly the
way I want. Any suggestions?

:

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

:

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

:

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.

 

[Guest]

I think you are absolutely right, and I truly appreciate all your help.

I'd keep the one in the form's OnCurrent event.

To make the code run when you change a value, put it in the AfterUpdate
event of any of those controls.

Steve

The form contains all student records so the code only runs when it
encounters a student who is attending in the fall. I thought that would
speed things up. Anyway, I have the code sitting in both the OnCurrent and
AfterUpdate events. I think that's the problem. The code runs when the
record opens and changes the record. Then the AfterUpdate event is
triggered. Access might think the record has been changed by another user.
Does that sound possible? Anyway, when I REM out the line that changes the
blnRegComplete variable that you suggested I add from one of the two events,
it works. Now I'm just trying to decide which event should have the line
deleted, or if I should write code that handles this situation.

So you can't move past the first record?

Can you post the code in your form's OnCurrent event?

Steve

:

No error. When you come to a record that runs the code, you can't move to
the next record. You can click next record, but you stay on the same record.
When you close the form, Access says that you cannot save this record at
this time.

:

What error are you getting?

Steve

:

I don't know if this is going to open up a can of worms or not, but I
inserted my code in both the afterupdate and oncurrent events. Whenever a
student comes up who is attending school in the fall the code runs, but
access will not permit the user to go to the next record nor save the record
on close. I have stepped through the code and everything occurs exactly the
way I want. Any suggestions?

:

Yes, you can use the AfterUpdate event of the form or each relevant control.
I'd probably go for the latter since you can work directly with the field on
the form rather than possibly having to execute some SQL to update a table.

Unless you have a save button or something...

Steve

:

I am assuming the AfterUpdate event. Thanks for your help. If I have any
further problems I will let you know.

You guys are the bomb.

:

Yes, that's what I would do...
Then the status is updated as you enter the various items of data.

Any other queries or reports will then run faster because they don't have to
evaluate the criteria, just the value of the status field. You may want to
create queries later on, for example, to show all students with complete
registrations in a particular County - you can just use the value of the
status field instead of having to evaluate all records again...

On your form(s), you may want to have this invisible to the user or at least
locked so they don't override your code. You can always have a locked textbox
on the form(s) that informs the user of current status just by looking at the
current value of the field.

Steve

:

So, my criteria code is in the wrong place. Instead of running this code
under the report, I would run it under the form where the data is entered, so
whenever the data changed their reg status would change accordingly. Am I
getting you?

:

You have a table somewhere that you store registration info?
Add a Yes/No field to that table that represents whether it is considered to
be complete or not. You can incorporate your criteria code to set the field
with each change of information. This field would need to be added to the
query your report letter is based on.

Now you can have a button that prints all incomplete and a button that
prints all complete. If you want to get even fancier, you could present the
user with the option to print one or the other or both!

You would just send criteria with your code that opens the report. Let's say
the field you added was called ysnComplete. Your code to open the report with
incomplete registrations would be:
DoCmd.OpenReport "MyReport",,,"[ysnComplete] = " & False

Make sense?

Steve

:

Thanks for the response; however, I am a little confused. The code is
running behind the letter. How do I assign a value to a query when it's
running? Won't I get an error? Or am I assigning the value to a table? If
so, wouldn't I need to run the report twice: Once to assign the value and
once to filter the query?

:

If you have all the criteria in code, why don't you add a field to each
student's registration record to represent whether it is complete or not. You
can use your code to detrmine the setting for this field. Then you can simply
use this field in your report queries...one for complete, one for incomplete.

Steve

:

I have a letter that is generated to every student attending our school in
the fall. If a student's registration is incomplete, the letter blocks out
certain key information to encourage the student to complete registration
before the start of school in order to receive this key information The
criteria for complete and incomplete is quite lengthy (e.g. whether 10 forms
have been received, whether completed correctly, tuition and fees paid,
record transfers, etc.) you get the picture. I have all the criteria in code
and everything is working fine. Now, if I only want to print the
"registration incomplete" letters, how do I get the report to skip all the
"registration complete" letters? The criteria seems too long to sort with
SQL. Is there a way in VBA?

Thanks in advance.