Solved VOID BUTTON

[mloucel]

Hello All,

I am trying to learn as much as I can as almost novice in access to keep my job.

I have advanced pretty well in all my forms, queries and tables, etc. but I got an addition I don't really know how to solve:
my boss wants to add a VOID button..
I added a Yes/No field to the data base.
so now I need to:
- once the form is open
-- check
-- if the record field = VOID then skip that record else
-- change the form header color back to (#77C0D4)
-- endif

With the form OPEN and while the user DOES NOT SAVE the record:
If the EU press the VOID button then
field VOID should change to YES
Form Header should change the color to RED (#ED1C24)
a label with the caption "VOIDED" should be displayed
else
[in case the user changes his/her mind and the record is not saved]
field VOID should change to NO
Form Header should change the color to (#77C0D4)
the label with caption "VOIDED" should be hidden
endif
EU clicks the SAVE button or moves to a different record and access saves the changes..
And of course once the record is saved all records with VOID should never show up, unless I specifically open the database and uncheck the button VOID, so that employees cannot do it themselves.

Now that challenge is WAY beyond my knowledge, and I would much appreciate if any of you gurus can point me to the right code.

Thanks

Maurice.

 

[pbaldy]

You can base your form on a query that includes a criteria filtering out the voided records. You can use Conditional Formatting and/or this to get the looks you want:

Conditional Visibility

 

[mloucel]

You can base your form on a query that includes a criteria filtering out the voided records. You can use Conditional Formatting and/or this to get the looks you want:

TOO LATE for the query, I thought about that but there are too many changes to do to change the form from the database to the query and there are 3 tables involved, I may make a big mess, hence the reason I would like to simplify doing it with code, I already solve one of the problems, changing the color of the header when the form is opened.
Private Sub Form_Current()
On Error Resume Next
' IF there is a VOID record, change the color of the header and SKIP that record
If [void] Then
Me.FormHeader.BackColor = vbRed
' what code do I use to skip this record ?????? and not display it...
Else
Me.FormHeader.BackColor = 13487481
End If
End Sub

 

[pbaldy]

If there are 3 tables involved the form may already be based on a query, you'd just have to add the criteria. Or if you want to leave it alone you could use this:

Open a second form to the record

But instead of referring to a form just hard code the value

DoCmd.OpenForm "SecondFormName", , , "FieldName = 0"

Using code as you show is really not practical. To "not display it" I suppose you could use GoToRecord to jump to the next record, but it makes more sense to exclude them from the start rather than try to handle them.

 

[mloucel]

Hello Thanks but no the form is not in a query..
I am getting close to do it...
I was able to display a label not on the header but on the detail, pretty big that displays a not saying
this certificate is void, that was easy, I added a label on TOP of the form and hide the label on current() so that when it opens the form hides the label
then I added a toggle button to VOID and when I hit the button code on click () display the LABEL..
Now I need to figure out using code
how do I change the yes/no field to YES before displaying the label, and then of course reverse it on the ELSE so it will hide the label and reverse back the void FIELD to NO.

 

[moke123]

Thanks but no the form is not in a query..

If the form is based on 3 tables then it certainly is a query. Dont confuse it with a saved query. Check the recordsource of the form.

 

[mloucel]

If the form is based on 3 tables then it certainly is a query. Dont confuse it with a saved query. Check the recordsource of the form.

My apologies but NO it is NOT a query, and I accept I made a mistake...
I AM LEARNING..
The form is based on 1 table yes... I was confused since based on the relationships it takes data from 2 other data bases, so MY BAD..

I Do not have any queries whatsoever already created, I will do to make reports, but for this particular form is just based on 1 table.

I am still trying to figure out how to do the routine on

Private Sub tglVOID_Click()

over her I need to change the value of VOID to true
display my label (I think I figured out how to do it)
then if the button is pressed again set VOID to false and hide again the label

I'll keep on trying but if you or anyone else knows how to do that, it will save me some youtube videos and a few hundred pages on google.

Thanks for any help.

 

[mloucel]

Even tough the end result is not what I wanted I was able to solve my issue PARTIALLY.
instead of creating a button
I added the yes/no field and crated a sub routine based on whether the field value is yes or no, it works just that now I have to figure out the skip part when the form is reopened or saved, with pbaldy's suggestion of using gotorecord.

Thanks for trying to help.
Maurice.

 

[pbaldy]

Did you try the suggestion in post 4 to open the form excluding the voided items? What you're doing is pretty unusual, and will be inefficient if you start to have a lot of records. Best to exclude the records you don't want rather than handle them as you are.

 

[arnelgp]

try this demo.

 

[mloucel]

try this demo.

YES!!!!!!
a thousand times yes, yes, yes.. That is exactly what I need to accomplish, I have changed my code to reflect the changes, your help is very appreciated, once I have the whole project finished, I would appreciate if I can submit that to you, so you can give me your opinion, and criticize my work based on the fact that I am learning best I can, without loosing my new job.
My boss is a very nice person and has given me plenty of time to learn and do the project, this will impress her, I truly and fully understand that the way I did my job was probably the hard way, but eventually, I will be learning new commands and tricks and will not master it but become a good programmer, hey who knows maybe I will master it some day.

Thank you so much again for your help, next beer on me..

 

[arnelgp]

you can give it to me?
its a labour of love so i have to appreciate it
rather than critisize.
remember, there is no such thing as bad db.
people see it from the Outside not the internal
working and the code.

windows os was not made one time.
it undergoes many Updates and patches
and still a work in progress.

 

[moke123]

once I have the whole project finished, I would appreciate if I can submit that to you,

You may not want to wait until its finished to submit it for comment. Its very important to start with a good foundation before putting a lot of time and effort into a project, especially as a new developer. If, for instance, you have a poor table structure or bad naming convention its better to find that out up front before you drive yourself crazy with a minor problem. We have all been there at some point.

Private Sub Form_Current()
On Error Resume Next
' IF there is a VOID record, change the color of the header and SKIP that record
If [void] Then
Me.FormHeader.BackColor = vbRed
' what code do I use to skip this record ?????? and not display it...
Else
Me.FormHeader.BackColor = 13487481
End If
End Sub

One thing I noted in your code above is the use of On Error Resume Next. While it may not make a difference in that small snippet of code, be very careful using that as you may have an error that is ignored leading to inconsistant results.

 

[mloucel]

If there are 3 tables involved the form may already be based on a query, you'd just have to add the criteria. Or if you want to leave it alone you could use this:

Open a second form to the record

But instead of referring to a form just hard code the value

DoCmd.OpenForm "SecondFormName", , , "FieldName = 0"

Using code as you show is really not practical. To "not display it" I suppose you could use GoToRecord to jump to the next record, but it makes more sense to exclude them from the start rather than try to handle them.

Your knowledge is very appreciated, thou I found the answer to my issue, I would be very honored to submit my final version of the database for your consideration, I know everyone codes differently and I come from dBase and Foxbase so not exactly new coding my way of seeing things and doing things are very different and I am adapting to the new structures, commands and way of doing code.
Your expertise and criticism will be appreciated, the more the better, I really need to learn fast to keep my job, for me is a do or die type of thing.

Truly yours

Maurice.

 

[mloucel]

You may not want to wait until its finished to submit it for comment. Its very important to start with a good foundation before putting a lot of time and effort into a project, especially as a new developer. If, for instance, you have a poor table structure or bad naming convention its better to find that out up front before you drive yourself crazy with a minor problem. We have all been there at some point.


One thing I noted in your code above is the use of On Error Resume Next. While it may not make a difference in that small snippet of code, be very careful using that as you may have an error that is ignored leading to inconsistant results.

Thank you for your suggestion, and I will take your word for consideration, once I have all the parts I will submit it to everyone's critique, please apologize my coding before hand, I come from the land of "DOESN"T EXIST ANYMORE" dBase and Foxbase, so as you imagine I am a mess.
I did note some errors in naming convention specially in the databases, but I am so afraid to make a change and make everything messed, thank God i made a copy and while the changes created a nightmare, the backup copy saved my rear...
I appreciate your concern and willing to help.
Much sincerely..

Maurice.