appear or disappear a button based on data in a table

[aas1611]

Hi,

I have a form that lists data from a table. This form has a button in each row, and this button's visibility depends on a column value in that table.

The table: Rechnungsjournal
The column that decides: Mahnung1 -> rst!Mahnung1
the button affected: Mahnung1 -> Me!Mahnung1

I have written the following code:

qryUpload = "SELECT * FROM Rechnungsjournal"
Set db = CurrentDb()
Set rst = db.OpenRecordset(qryUpload, dbOpenSnapshot)

rst.MoveFirst
Do Until rst.EOF
    If rst![Mahnung1] = "" Or IsNull(rst![Mahnung1]) Then
        Me!Mahnung1.Visible = True
    Else
        Me!Mahnung1.Visible = False
    End If
    rst.MoveNext
Loop

It's meant to be, when a row has an empty data (or null) under Mahnung1 column, then the button appears. And vice versa.

From the code I've written, all rows have that button, even though some have data under their Mahnung1 column.

I hope you can understand what I'm trying to do and can help me.

Thanks!

Andre

 

[belovedcej]

It sounds like you are working with a continuous form. You won't be able to pull this off with a "real" button because if even one row fulfills the requirements for the button to appear, they will ALL appear.

Try changing your button to a label instead and change the formatting to it look like a button and then using your same code.

 

[aas1611]

First of all, I don't know exactly what continuous form is...

Well, I MUST USE that button to work on each row. I can't change it to a label, I think.

This button sends an email based on informations contained in each row (email address, name, etc). After this email has been sent, the button located at the corresponding row disappears. This way, nobody can accidently send the email to the same person twice.

In other words, I want to disable this button after using it. But other rows, that have not been worked on, still have this button.

Maybe is there a different approach or a better solution for this purpose?

Andre

 

[Iashia06]

Hi,

Why don't you try using the disabled command instead of the visible command. That way the button is only enabled when you need it to be.

Iashia

 

[adalger]

I'm thinking a better solution to your problem might be to change your recordset:

qryUpload = "SELECT * FROM Rechnungsjournal WHERE Mahnung1 IS NOT NULL"

In your button code, after you do the work, add a line:

Me.Requery

Make this a different form, and in your main form include a simple indicator of whether or not any of the records indicate that emails need to be sent -- possibly a single button in the header or footer which is only visible if the recordcount of the above query is not 0, which opens the form to view those records. Or alternately, simply have the button send the required emails; then you don't even need that form.

 

[SuryaF]

Here is an idea. It works beautifully and it does what you want. It's not a button though, it's a hyperlink.

There are basically two ways, using a textbox or a label .

You use a texbox especially if you want your form to work in Datasheet view as well.
The label version is for continous forms only but it's a bit better looking.

The textbox version:
1. In your form's record source add a constant field [Btn]:"Send email"
2. In your form add a text box for this field. Make the textbox's Hyperlink property true.
3. You can make the textbox's background and border transparent.
4. right-click the textbox and set Conditional Formatting on it. Make it disabled according to the field that holds the sent/not sent information
5. create the onclick event for the Btn textbox and put your code in it just like you would for the button:

Private Sub Btn_Click()

'-------
'your code here
'--------

Screen.PreviousControl.SetFocus 'optional

End Sub

Of course, the name of the field/textbox doesn't have to be "Btn", use whatever you feel like.

The label version (for continous form only):
1. add a label to your form. Let's call it Link_Label.
2. set the Hyperlink Address to "Send email" (whatever you put here will be displayed as TipText)
3. add a Text Box to your form. Leave it unbound, visible and enabled. Make its background and border transparent. Make it as narrow as possible, make its width 0.
4. right-click the textbox and set Conditional Formatting on it. Make it disabled according to the field that holds the sent/not sent information
5. attach the Link_Label to the new text box. This way the conditional formatting affects the label.
6. create the onclick event for the text box and put your code in just like you would for a button. Don't put the code on the label, it won't run if the label is attached to the textbox.

I hope this helps, give it a try.