Subform button characteristics

[Guest]

I have a subform that shows data from a table in continous view. I have
created a button on the subform that goes with each record. When clicked,
the button adds data from the record to a second table. I would like to set
it up so that the button in disabled if the data from the record is already
in the second table.

I have written some VBA to do that, but the problem is that it seems to
update all of the buttons based on the first record, rather than updating
each button based on its associated record.

I have tried putting the code into several different events of the subform
(OnLoad, OnOpen, OnActivate, AfterLayout).

Here is the code:

If IsNull(DLookup("[X]", "Table2", "[X]='" & Me.[X].Value & "' AND [Y]='" & _
& Me.[Y].Value & "' AND [Z]='" & Me.[Z].Value & "'")) Then
Me.ButtonName.Enabled = False
Me.ButtonName.Caption = "On List"
Else
Me.ButtonName.Enabled = True
Me.ButtonName.Caption = "Add to List"
End If

Any ideas? Thanks.

 

[Guest]

What is not very well understood with continuous forms is that there are not
multiple buttons but multiple replicas of the *same* button (and code).

Anyway, a far more important question is why do you want to transfer
information to a second table? This isn't good database design.

For a database I designed I included a checkbox that was set to false and
when i click on the button with the associated record the box is changed to
true.

You can then base two different forms on queries using the same table.

 

[Guest]

Thanks for your repsonse. It sounds like my button idea will not work.

To answer the question as to why I am doing this: I guess I misspoke. The
subform gets its data from a query and not a table. A large amount of data
is imported to the database on a regular basis. Only a small portion of this
data is of any value. The query filters the data down to a small list, then
the user can decide which items to keep for further action (those items get
added to the table). So the point of the form is to get all of the data we
need into a single table.

I have changed my query to check if the data is already in the table, but I
need to display both cases as results of the query. It seems that the only
way to display this info is by an additional text box that gives a yes/no.
Is that correct?

Thanks.

What is not very well understood with continuous forms is that there are not
multiple buttons but multiple replicas of the *same* button (and code).

Anyway, a far more important question is why do you want to transfer
information to a second table? This isn't good database design.

For a database I designed I included a checkbox that was set to false and
when i click on the button with the associated record the box is changed to
true.

You can then base two different forms on queries using the same table.

I have a subform that shows data from a table in continous view. I have
created a button on the subform that goes with each record. When clicked,
the button adds data from the record to a second table. I would like to set
it up so that the button in disabled if the data from the record is already
in the second table.

I have written some VBA to do that, but the problem is that it seems to
update all of the buttons based on the first record, rather than updating
each button based on its associated record.

I have tried putting the code into several different events of the subform
(OnLoad, OnOpen, OnActivate, AfterLayout).

Here is the code:

If IsNull(DLookup("[X]", "Table2", "[X]='" & Me.[X].Value & "' AND [Y]='" & _
& Me.[Y].Value & "' AND [Z]='" & Me.[Z].Value & "'")) Then
Me.ButtonName.Enabled = False
Me.ButtonName.Caption = "On List"
Else
Me.ButtonName.Enabled = True
Me.ButtonName.Caption = "Add to List"
End If

Any ideas? Thanks.

 

[Guest]

What I would do is include the checkbox in the subform and set the query so
the checkbox is filtered to "false". When a user ticks the box it will then
no longer appear in the subform.

You can then put all the records with a tick in another form. As I say it
isn't necessary to transfer (or store) the same kind of information in two
different tables.

The (apparently) more obvious solution to me is to decide first what you
want to import into the database.

--
"Loose Change 2nd Edition" has been seen by almost 7 million people on
Google video

Thanks for your repsonse. It sounds like my button idea will not work.

To answer the question as to why I am doing this: I guess I misspoke. The
subform gets its data from a query and not a table. A large amount of data
is imported to the database on a regular basis. Only a small portion of this
data is of any value. The query filters the data down to a small list, then
the user can decide which items to keep for further action (those items get
added to the table). So the point of the form is to get all of the data we
need into a single table.

I have changed my query to check if the data is already in the table, but I
need to display both cases as results of the query. It seems that the only
way to display this info is by an additional text box that gives a yes/no.
Is that correct?

Thanks.

What is not very well understood with continuous forms is that there are not
multiple buttons but multiple replicas of the *same* button (and code).

Anyway, a far more important question is why do you want to transfer
information to a second table? This isn't good database design.

For a database I designed I included a checkbox that was set to false and
when i click on the button with the associated record the box is changed to
true.

You can then base two different forms on queries using the same table.

I have a subform that shows data from a table in continous view. I have
created a button on the subform that goes with each record. When clicked,
the button adds data from the record to a second table. I would like to set
it up so that the button in disabled if the data from the record is already
in the second table.

I have written some VBA to do that, but the problem is that it seems to
update all of the buttons based on the first record, rather than updating
each button based on its associated record.

I have tried putting the code into several different events of the subform
(OnLoad, OnOpen, OnActivate, AfterLayout).

Here is the code:

If IsNull(DLookup("[X]", "Table2", "[X]='" & Me.[X].Value & "' AND [Y]='" & _
& Me.[Y].Value & "' AND [Z]='" & Me.[Z].Value & "'")) Then
Me.ButtonName.Enabled = False
Me.ButtonName.Caption = "On List"
Else
Me.ButtonName.Enabled = True
Me.ButtonName.Caption = "Add to List"
End If

Any ideas? Thanks.