how to change property in macros...please help

[ScribblingJan]

All I want to do is have a macro set the property of a button to visible BUT
I CANT FIGURE IT... it wants the control name, but I'm darned if I can figure
it out. It's a command button called 'Add 200' on a subform.

Please help... I'm going mad!

 

[KARL DEWEY]

Open your form in design view, double click, scroll down, and select
Properties.
Click on the button.
There is a property titled Name.

You most likely will need to use the full name like this --
[Forms]![MyPrettyForm]![Command24]

 

[S.Clark]

Forms![YourFormName]![YourSubformName].Form![Add 200]

 

[ScribblingJan]

Thanks for replying but it still doesn't work. I till get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names).

Any other ideas?

Jan

Open your form in design view, double click, scroll down, and select
Properties.
Click on the button.
There is a property titled Name.

You most likely will need to use the full name like this --
[Forms]![MyPrettyForm]![Command24]

--
Build a little, test a little.

All I want to do is have a macro set the property of a button to visible BUT
I CANT FIGURE IT... it wants the control name, but I'm darned if I can figure
it out. It's a command button called 'Add 200' on a subform.

Please help... I'm going mad!

 

[ScribblingJan]

Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?

Jan

Forms![YourFormName]![YourSubformName].Form![Add 200]

All I want to do is have a macro set the property of a button to visible BUT
I CANT FIGURE IT... it wants the control name, but I'm darned if I can figure
it out. It's a command button called 'Add 200' on a subform.

Please help... I'm going mad!

 

[John Spencer]

Are you using the name of the subform CONTROL? The name of the subform
control is not necessarily the same as the name of the form being used by the
subform control

Forms![NameOfForm]![NameOfSubFormControl].Form.[Add 200]

If possible, try changing the name of the Add 200 control to remove the space.
Spaces in names of objects is a bad idea. Also, if you can't change the
control name, try using Add_200 and see if that works.

And if you are using a macro, it may be that it can't "see" a control on a
subform.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?

Jan

Forms![YourFormName]![YourSubformName].Form![Add 200]

All I want to do is have a macro set the property of a button to visible BUT
I CANT FIGURE IT... it wants the control name, but I'm darned if I can figure
it out. It's a command button called 'Add 200' on a subform.

Please help... I'm going mad!

 

[Marco Pagliero]

Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?

It sounds like a spelling problem. Are you sure for example that it
says "mysspelt" and not "misspelled"?

Would you please copy and paste the line
Forms![YourFormName]![YourSubformName].Form![Add 200]
with your own names in it, and also the message "control xxx doesn't
exist or is mysspelt" with the real name of the control instead of
"xxx"?
Thanks

Marco P

 

[ScribblingJan]

Hi John and thanks for trying to help. Unfortunately it still isn't working...

The names of the form and the subform control are all correct, and I have
tried various names for the aberrant control including [Add200] alas to no
avail.

You are quite right that the macro "can't see" the control... but I don't
know how to make it see it. It seems to see other controls on the subform
(some labels and texts etc.) but not the 2 buttons I'm trying to use (Add200
and Remove200).

What's even more bizarre is that if I use the expression builder... it can
see everything including the controls, but if I cut and paste what the
builder sees... the macro still can't see it with the same error: "The
control name 'Forms![Contact Details]![200 Club subform].Form.[Add200]' is
misspelled or refers to a control that doesn't exist"

I've also noticed another weird irregularity: I have 2 fields in a table
[Known as] and [Forename] that I can add to a form that display correctly,
but if I have 2 more fields on the form with the syntax:
iif(isnull([testfield]),"Dear "&[Known as], "Dear Sir") and
iif(isnull([testfield]),"Dear "&[Forename], "Dear Sir")... one displays the
value ("Dear Fred") but the other does not ("Dear ").

There seems to be no reason why these things don't work... either that or
I'm missing something.

Can you think of anything else?

Jan

Are you using the name of the subform CONTROL? The name of the subform
control is not necessarily the same as the name of the form being used by the
subform control

Forms![NameOfForm]![NameOfSubFormControl].Form.[Add 200]

If possible, try changing the name of the Add 200 control to remove the space.
Spaces in names of objects is a bad idea. Also, if you can't change the
control name, try using Add_200 and see if that works.

And if you are using a macro, it may be that it can't "see" a control on a
subform.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?

Jan

Forms![YourFormName]![YourSubformName].Form![Add 200]

:

All I want to do is have a macro set the property of a button to visible BUT
I CANT FIGURE IT... it wants the control name, but I'm darned if I can figure
it out. It's a command button called 'Add 200' on a subform.

Please help... I'm going mad!

 

[ScribblingJan]

Hi Marco

Thanks for trying to help. Unfortunately it still isn't working...

The line I enter is:

Forms![Contact Details]![200 Club subform].Form![Add 200]

The error I get says:

The control name 'Forms![Contact Details]![200 Club subform].Form![Add 200]'
is misspelled or refers to a control that doesn't exist

I've also noticed another weird irregularity: I have 2 fields in a table
[Known as] and [Forename] that I can add to a form that display correctly,
but if I have 2 more fields on the form with the syntax:
iif(isnull([testfield]),"Dear "&[Known as], "Dear Sir") and
iif(isnull([testfield]),"Dear "&[Forename], "Dear Sir")... one displays the
value ("Dear Fred") but the other does not ("Dear ").

There seems to be no reason why these things don't work... either that or
I'm missing something.

Can you think of anything else?

Jan

Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?

It sounds like a spelling problem. Are you sure for example that it
says "mysspelt" and not "misspelled"?

Would you please copy and paste the line
Forms![YourFormName]![YourSubformName].Form![Add 200]
with your own names in it, and also the message "control xxx doesn't
exist or is mysspelt" with the real name of the control instead of
"xxx"?
Thanks

Marco P

 

[John Spencer]

If testfield is a text field it is possible that it is NOT NULL but contains a
zero-length string. You can change the IIF to test for both conditions

IIF(IsNull(TestField) or TestField = "", "Dear " & [Known As],"Dear Sir")

The only thing I can think of is that you have some form of corruption on the
form/subform that is causing the problem. Do you by any chance have the
"dreaded" Name Autocorrect option enabled? If so, this can cause this kind of
problem.

Tools: Options: General tab.

I would try Unchecking that and compacting your database. If that doesn't fix
the problem, then check out the following from Allen Browne.

From: Allen Browne
Okay, so something has gone haywire with this database. Suggestions to recover it:

1. Make a backup copy of the mdb file, without overwriting any existing
backups, in case something goes wrong.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. If the tables are attached, open the data file, and repeat steps 1 - 3 for
that file as well.

5. Close Access. Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

6. Open Access, and compact again.

7. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

8. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

9. Compact again.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the
database for you. Follow the steps for the first symptom in this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John and thanks for trying to help. Unfortunately it still isn't working...

The names of the form and the subform control are all correct, and I have
tried various names for the aberrant control including [Add200] alas to no
avail.

You are quite right that the macro "can't see" the control... but I don't
know how to make it see it. It seems to see other controls on the subform
(some labels and texts etc.) but not the 2 buttons I'm trying to use (Add200
and Remove200).

What's even more bizarre is that if I use the expression builder... it can
see everything including the controls, but if I cut and paste what the
builder sees... the macro still can't see it with the same error: "The
control name 'Forms![Contact Details]![200 Club subform].Form.[Add200]' is
misspelled or refers to a control that doesn't exist"

I've also noticed another weird irregularity: I have 2 fields in a table
[Known as] and [Forename] that I can add to a form that display correctly,
but if I have 2 more fields on the form with the syntax:
iif(isnull([testfield]),"Dear "&[Known as], "Dear Sir") and
iif(isnull([testfield]),"Dear "&[Forename], "Dear Sir")... one displays the
value ("Dear Fred") but the other does not ("Dear ").

There seems to be no reason why these things don't work... either that or
I'm missing something.

Can you think of anything else?

Jan

 

[ScribblingJan]

Thanks John... all very useful info that I was unaware of. Unfortunately, it
still didn't help and I'm all out of ideas.

The other problem (the one with the missing value) is not the iif/isnull
part... the condition works, only the value is missing. So if it is meant to
display Hi [Value] on a null and Bye [value] on a non-null, it displays Hi
<blank> or Bye <blank> even when there's a value there. If I use a different
field from the table... it works, even if both fields and their contents are
identical.

It's driving me nuts!

Thanks for all your really useful links and help. If anything else occurs to
you... please let me know.

Jan

If testfield is a text field it is possible that it is NOT NULL but contains a
zero-length string. You can change the IIF to test for both conditions

IIF(IsNull(TestField) or TestField = "", "Dear " & [Known As],"Dear Sir")

The only thing I can think of is that you have some form of corruption on the
form/subform that is causing the problem. Do you by any chance have the
"dreaded" Name Autocorrect option enabled? If so, this can cause this kind of
problem.

Tools: Options: General tab.

I would try Unchecking that and compacting your database. If that doesn't fix
the problem, then check out the following from Allen Browne.

From: Allen Browne
Okay, so something has gone haywire with this database. Suggestions to recover it:

1. Make a backup copy of the mdb file, without overwriting any existing
backups, in case something goes wrong.

2. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect Explanation of why:
http://allenbrowne.com/bug-03.html

3. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

4. If the tables are attached, open the data file, and repeat steps 1 - 3 for
that file as well.

5. Close Access. Decompile the database by entering something like this at the
command prompt while Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

6. Open Access, and compact again.

7. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

8. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

9. Compact again.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to rebuild the
database for you. Follow the steps for the first symptom in this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi John and thanks for trying to help. Unfortunately it still isn't working...

The names of the form and the subform control are all correct, and I have
tried various names for the aberrant control including [Add200] alas to no
avail.

You are quite right that the macro "can't see" the control... but I don't
know how to make it see it. It seems to see other controls on the subform
(some labels and texts etc.) but not the 2 buttons I'm trying to use (Add200
and Remove200).

What's even more bizarre is that if I use the expression builder... it can
see everything including the controls, but if I cut and paste what the
builder sees... the macro still can't see it with the same error: "The
control name 'Forms![Contact Details]![200 Club subform].Form.[Add200]' is
misspelled or refers to a control that doesn't exist"

I've also noticed another weird irregularity: I have 2 fields in a table
[Known as] and [Forename] that I can add to a form that display correctly,
but if I have 2 more fields on the form with the syntax:
iif(isnull([testfield]),"Dear "&[Known as], "Dear Sir") and
iif(isnull([testfield]),"Dear "&[Forename], "Dear Sir")... one displays the
value ("Dear Fred") but the other does not ("Dear ").

There seems to be no reason why these things don't work... either that or
I'm missing something.

Can you think of anything else?

Jan

 

[KARL DEWEY]

Here is a wild one -- try it this way --
[Forms]![Contact Details]![200 Club subform]![Add200]

--
Build a little, test a little.

Hi Marco

Thanks for trying to help. Unfortunately it still isn't working...

The line I enter is:

Forms![Contact Details]![200 Club subform].Form![Add 200]

The error I get says:

The control name 'Forms![Contact Details]![200 Club subform].Form![Add 200]'
is misspelled or refers to a control that doesn't exist

I've also noticed another weird irregularity: I have 2 fields in a table
[Known as] and [Forename] that I can add to a form that display correctly,
but if I have 2 more fields on the form with the syntax:
iif(isnull([testfield]),"Dear "&[Known as], "Dear Sir") and
iif(isnull([testfield]),"Dear "&[Forename], "Dear Sir")... one displays the
value ("Dear Fred") but the other does not ("Dear ").

There seems to be no reason why these things don't work... either that or
I'm missing something.

Can you think of anything else?

Jan

Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?

It sounds like a spelling problem. Are you sure for example that it
says "mysspelt" and not "misspelled"?

Would you please copy and paste the line
Forms![YourFormName]![YourSubformName].Form![Add 200]
with your own names in it, and also the message "control xxx doesn't
exist or is mysspelt" with the real name of the control instead of
"xxx"?
Thanks

Marco P

 

[ScribblingJan]

Thanks Karl... tried and failed :-( ... I really miss the old days with an
Access I had proper control over.

If I explain what I'm trying to do...

I have a form on which is a subform displaying one new record in a table
with one field and 2 buttons: Add and Delete.

The user enters a 3 digit number in the field on the subform ([Draw Number])
and then presses Add to add this as a new record. If the record already
exists (ie there is already a number in the field) then the user can press
Delete to remove the record.

What I want to do is show the form with just a Delete button if the record
exists, or just an Add button if there is no record. If Add is pressed... it
saves the new record then hides the Add button and un-hides the Remove
button. And vice-versa if the Remove button is visible.

I'm trying to use a 3 step macro launched 'on click' which basically says:
save record... setproperty 'Add button' to invisible... set property 'Delete
button' to visible.

If you can give me an alternative to the macro option that does this then I
can forget my problem as a bad dream.

Thanks again

Jan

Here is a wild one -- try it this way --
[Forms]![Contact Details]![200 Club subform]![Add200]

--
Build a little, test a little.

Hi Marco

Thanks for trying to help. Unfortunately it still isn't working...

The line I enter is:

Forms![Contact Details]![200 Club subform].Form![Add 200]

The error I get says:

The control name 'Forms![Contact Details]![200 Club subform].Form![Add 200]'
is misspelled or refers to a control that doesn't exist

I've also noticed another weird irregularity: I have 2 fields in a table
[Known as] and [Forename] that I can add to a form that display correctly,
but if I have 2 more fields on the form with the syntax:
iif(isnull([testfield]),"Dear "&[Known as], "Dear Sir") and
iif(isnull([testfield]),"Dear "&[Forename], "Dear Sir")... one displays the
value ("Dear Fred") but the other does not ("Dear ").

There seems to be no reason why these things don't work... either that or
I'm missing something.

Can you think of anything else?

Jan

On 7 Okt., 11:30, ScribblingJan
Thanks for replying but it still doesn't work. I still get the error saying
"control xxx doesn't exist or is mysspelt" where xxx is the phrase you
suggest (obviously with my own names). I've tried different variations of
form/forms/Form/Forms/[Form]/etc. All to no avail. I can't think what I'm
doing wrongly.

Any other ideas?
It sounds like a spelling problem. Are you sure for example that it
says "mysspelt" and not "misspelled"?

Would you please copy and paste the line
Forms![YourFormName]![YourSubformName].Form![Add 200]
with your own names in it, and also the message "control xxx doesn't
exist or is mysspelt" with the real name of the control instead of
"xxx"?
Thanks

Marco P

 

[Marco Pagliero]

What I want to do is show the form with just a Delete button if the record
exists, or just an Add button if there is no record. If Add is pressed... it
saves the new record then hides the Add button and un-hides the Remove
button. And vice-versa if the Remove button is visible.

You could put that in the change event of [Draw Number], and beside
that it seems to me that only the names of the buttons, without
[Forms]![Contact Details]![200etc, should be necessary, as they are
all on the same subform.

I find the problem interesting and I have nothing to do in this moment
(Main customer just got out of business) so if you like you can zip
your mdb and mail it to me. I got really courious in the meantime.

Greetings