Need to Improve Form Functionality

[Peter]

Dear Master Designers,

1. I want to create a control that will not allow data input unless a Yes/No
field has been set to yes. Similar to checking "I agree" prior to
proceeding.

2. I want to send a report automatically every week to a list of
recipients, via e-mail. I know how to use the sendObject command, but I
don't know how to get it to generate it every week.

3. Is there a way to create multiple drop down boxes, that based on the
selections I will get a report based on the variables I selected?

4. Sometimes, as I am filling out an entry form, using drop down boxes,
linked to the One side, there is a value that I want but not included. I
have created a macro to bring up the proper form so I can add the value that
I want. However, when I go back to my entry form the newly added value is
not in the list. In order to refresh the data I have to save my record, but
in order to complete the record I need the newly created value. Is there a
way around it?

I thank you in advance,
Peter

 

[Lynn Trapp]

1. I want to create a control that will not allow data input unless a

Yes/No field has been set to yes. Similar to checking "I agree" prior to
proceeding.

1. Set the enabled property of your control to False in design view.
2. In the Current Event of the form put the following code:
Me.YourControl.Enabled = Me.YourYesNoField
3. Place the same code the change event of your Yes/No field.

2. I want to send a report automatically every week to a list of
recipients, via e-mail. I know how to use the sendObject command, but I
don't know how to get it to generate it every week.

You'll probably need to create a macro that does the sending of the report.
Then use some scheduling software to call Access and run the macro. The
syntax for calling a macro from a command line is:

"FullPathToMSAccess.exe" "FullPathToDatabase.mdb" /x "MacroName"

3. Is there a way to create multiple drop down boxes, that based on the
selections I will get a report based on the variables I selected?

Yes. Have the query that the report is based on reference your dropdown
boxes in the criteria section

4. Sometimes, as I am filling out an entry form, using drop down boxes,
linked to the One side, there is a value that I want but not included. I
have created a macro to bring up the proper form so I can add the value
that I want. However, when I go back to my entry form the newly added
value is not in the list. In order to refresh the data I have to save my
record, but in order to complete the record I need the newly created
value. Is there a way around it?

You need to requery your combo box in the close event of the form where the
new value is added.

Forms!YourFormName!YourComboBox.Requery

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Peter]

Lynn,
Thank you so much for your help. Unfortunatelly, your instrucitons are
beyond my level. I only got the 4th item to work. If I describe step by
step what I am doing could you please tell me where I am going wrong?

Background Theme:
Three fields:
Field name: Yes (Yes/No)
Field name: Date (Date)
Filed name: Money (Currency)

I want not to be able to enter the date or the amount, unless the Yes field
has been set to yes.

My Steps:
1. Enter the properties of of the Date field in design view and change the
Enable property to NO.
2. Enter the Properies of the entire form and type under the Event - On
Current: Me.Date.Enabled=Me.Yes
3. Enter the Yes field properties and type under After Update:
Me.Date.Enabled=Me.yes (I could not find Change Event as indicated in your
instructions)

Result- Error: Microsoft Access can't find the Macro 'Me."

What am I doing wrong?

I have not resolved the the rest of the problems, other than the 4th item,
but I can work on that another time.

Thanks in Advance
Peter

 

[Lynn Trapp]

Peter,
Let me start by suggesting that you change the name of your date field to
something other than "Date." Date is a reserved word in Access and can cause
you no end of trouble if you try to name a field with that word.

2. Enter the Properies of the entire form and type under the Event - On
Current: Me.Date.Enabled=Me.Yes
3. Enter the Yes field properties and type under After Update:
Me.Date.Enabled=Me.yes (I could not find Change Event as indicated in
your instructions)

It sounds like you are typing that code directly in the property sheet. That
will not work. Rather you need to click the button to the right end of the
OnCurrent field in the property sheet. That will open a dialog box that has
3 choices. You want to select the Code Builder Choice. That will take you to
the Visual Basic Editor and your cursor should be placed inside the Current
event handler in the Editor. Type the code I gave you there. Do the same in
the Click event of the Yes/No field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html