# Need Help with Access Database/MACRO



## Gemuine (Aug 16, 2007)

Hello:
I have this database that I need to complete. I have completed the basics but can not seem to figure ot how to do the rest of it. Can someone help me.

Here is what I am trying to do:
1. Final section greyed out when no is selected for response
2. Change current stage field when key fields are populated 
For example: 
- When exit meeting is entered, the current stage should automatically change to tracking.
- When response needed is not checked, current stage should be completed.
- When response needed is checked, current stage should be in acknowledgement.
3. Have the BOG and Internal Due Dates Met fields automatically calculated in the background

In addition: I also need a switchboard created but I think I can figure it out. I do have reports I need though.
1. Open Report
2. Tracking Report
3. Completed Report
4. Acknowledgement Report

I have attached the file. Thank so much


----------



## slurpee55 (Oct 20, 2004)

I take it you are writing with regard to the form for data entry primarily?


----------



## Gemuine (Aug 16, 2007)

yes I am . Thanks


----------



## jimr381 (Jul 20, 2007)

Welcome to the forum.

If you email me the file I will take a look at it. Please email me the file at duluvian at verizon dot net. We can whip up a few macros that will do a setvalue to have it automatically set the value of those fields when you leave the fields that have a condition based upon it. Please reply to this message afterwards so I can go check that e-mail as well.


----------



## slurpee55 (Oct 20, 2004)

Jim, I sent you the file.


----------



## Gemuine (Aug 16, 2007)

Thanks so much Jim and slurpee55!!! I really appreciate it. I've also sent thhe file via email to Jim.


----------



## jimr381 (Jul 20, 2007)

OK so you want everything from Final on down not available unless the "Check for Yes" checkbox in the Final section is checked off?


----------



## Gemuine (Aug 16, 2007)

Jim:
I goy a notification that your posted something but I dont see it. I want to make sure that I answer you.
Your question was:

OK so you want everything from Final on down not available unless the "Check for Yes" checkbox in the Final section is checked off?

Answer:
Yes, please and thank you


----------



## jimr381 (Jul 20, 2007)

OK I fixed your Internal Date setvalue. The field name was off.  Do you want me to do a switchboard as well? Also when naming the fields it is good form to use no space in the name of the field and then set the caption to resemble the field having spaces.


----------



## jimr381 (Jul 20, 2007)

Also as an addendum Will mail date be filled in when it is marked as completed or acknowledgement?


----------



## Gemuine (Aug 16, 2007)

The mail date is manual entered. So no need to do anything with that. If you could, can you do the switchboard. Of course I still have to add the reports and add more items to the switchboard but I can take care of that( I think). Can I see the file?


----------



## jimr381 (Jul 20, 2007)

But is there something in the Mail Date field when the current stage should be set to completed or acknowledgement?


----------



## Gemuine (Aug 16, 2007)

The mail date should not need any coding. That is manually entered. This is how this wroks, when the user types in a date in the Mail date field, the next thing they need to do is select "completed" or "acknowledgement" on the that field. So if they select completed then the other fields should be grayed out meaning that is it. No additional information is needed. Record closed


----------



## jimr381 (Jul 20, 2007)

I was not trying to add coding to the mail date field.  I was looking for another variable so I can tell the system to set the current stage up.


----------



## Gemuine (Aug 16, 2007)

BTW, I sent you an email because I think I gave you the wrong answer. But as far as the mail date, nothing the only thing that's there is a date that the users place. Would it be better if I just call u so that I can explain further?


----------



## jimr381 (Jul 20, 2007)

I am working the help desk today. I can get in contact with you on Tuesday if you like though.


----------



## Gemuine (Aug 16, 2007)

Would you be able to send me the database today because I kind of need it today. I know I am asking too much but maybe I can play with what you already have.


----------



## jimr381 (Jul 20, 2007)

Sure. Now that I have that information I have it automatically updating the fields for you using some macros. I will whip up some queries and reports really quick for you. Do you want a summary report that will show you the number of items in each classification and also individual reports as well?


----------



## jimr381 (Jul 20, 2007)

Actually, I will just e-mail you the file, because I do not know which fields you want in it.


----------



## Gemuine (Aug 16, 2007)

I just got the database back and it is in read only status. I could not make any uodates. Also, can you please let me know what you did? as far as coding?


----------



## jimr381 (Jul 20, 2007)

It should not be in a read only state. Do you already have the file open and are trying to open it again? Try closing all the way out of access and opening it again. I did no code I just threw in a few macros that have it automatically update that current status field and tweaked yours which was wrong.


----------



## Gemuine (Aug 16, 2007)

Yeah. Thanks I just got it to open. One last question pleae, the internal due date is automaticaly populated, how do make it to add 15 days from thr internal due date to have tp BOD due date?

Thank u so much


----------



## jimr381 (Jul 20, 2007)

I did not understand the previous question. Can you please restate it? If you want that BOD due date to be 15 days after the internal, then you can setup another macro with a setvalue action happening.


----------



## Gemuine (Aug 16, 2007)

Hi:
Not sure of my reply earlier posted si here it is again. How do I add a MACRO to add 15 days to the internal due date to get the BOG date populate?


----------



## Gemuine (Aug 16, 2007)

Oh, how do I do that?


----------



## slurpee55 (Oct 20, 2004)

make a macro called whatever. in the command line. set it to setvalue. In Item, enter:[BOGdate] (or select it from the options). In the Expression enter: [internal due date]+15.
After you enter the date in the internal due date and hit enter, to go to the BOG date, it will automatically add 15 days. Note that it will only do this via the form - if you enter a date into the table, nothing will happen.
Attached is a small file that has just that - a table, a form and a macro.


----------



## OBP (Mar 8, 2005)

Visual Basic is better than Macros for this kind of work as the VBA stays with the Form if Copied or Imported whereas the Macro is a separate entity.


----------



## slurpee55 (Oct 20, 2004)

well, true....
Oh well!


----------



## OBP (Mar 8, 2005)

Sorry slurpee, I wasn't knocking what you had created, :up: just pointing out that VBA is easy to do that kind of thing.


----------



## slurpee55 (Oct 20, 2004)

No offense taken! I am still just working on learning VBA (actually, it makes a lot of sense to read how to do it, but the actual use is, let's say, a bit trickier!)


----------



## OBP (Mar 8, 2005)

slurpee, as you seem quite comfortable with Macros, why not learn from them.
You can Save As a Macro as a Module and Access will convert it in to VBA for you.
You do end up with a few bits that aren't always necessary, but it shows you the VBA equivalent to Macros.


----------



## slurpee55 (Oct 20, 2004)

Thanks - rather slick. Now, it is obvious what it is doing (I did that for the macro I uploaded) but using the With command...hmmm


----------



## Gemuine (Aug 16, 2007)

Thanks for the advice. However, as you probably can tell, I am not good on MACROS specially VBA.


----------



## Gemuine (Aug 16, 2007)

I still need help with my database. Can Jim, Slurpee or OBP help?


----------



## jimr381 (Jul 20, 2007)

I sent an e-mail to your work e-mail early this morning. Are you at home today?


----------



## slurpee55 (Oct 20, 2004)

And did you get my email also?


----------



## Gemuine (Aug 16, 2007)

Hello:
No I am at work. Yes I received Jim and Slurpee's email. Jim, please disregard the file I sent you. I will post the database. I had to make a quick change.


----------



## jimr381 (Jul 20, 2007)

OK. Can you also e-mail the new file to me as well? My work does not allow me to download zipped files. **hrmpfs**


----------



## slurpee55 (Oct 20, 2004)

Jim, you are just going to have to fix that!!!


----------



## Gemuine (Aug 16, 2007)

Thank you both!!!


----------



## Gemuine (Aug 16, 2007)

Here is the access database again!!! If anyone can assist me with some changes (listed below), i'd really appreciate it.
1. When the mail date is entered (which is manual), the current stage should go to 'Tracking".
2. For the CA/CRA form, when the consumer exam type is selected to:
- Consumer and CRA Tier 1 (Internal due date should be 45 days from the exit meeting)
- Consumer and CRA Tier 2 (Internal due date should be 50 days from exit meeting)
- Consumer and CRA Tier 3 and FBO(Internal due date should be 55 days from exit 

So, The internal due date should be automatic when the consumer exm type is selected.

Also, I need a MACRO that would calculate # of days that would be incorporated in the report.

"Completed" is Mail date - Exit Meeting Date
"Completed ACknowledgement" is Date acknowledgement mailed - Exit meeting date

Thanks


----------



## jimr381 (Jul 20, 2007)

You previously stated that when exit meeting is entered then it needs to be set to tracking which it does and that "When response needed is not checked, current stage should be completed." Which I set it up for when you exit the checkbox and finally "When response needed is checked, current stage should be in acknowledgement." which again is setup when you exit the checkbox.  I will look into the other items for you though.

Also I am not finding CRA Tier 1, 2 or 3. All I find in that list is consumer and CRA. 

I am also not quite sure what you are wanting with this "Also, I need a MACRO that would calculate # of days that would be incorporated in the report."

What is the "Refresh Data" button for?


----------



## Gemuine (Aug 16, 2007)

Hello:

Okay if you open the form called "f_CA&CRA Input", it is one of the fields under "Consumer Exam Type". When you go to that "Consumer Exam Type" Field, you'll see it's all there, it is a drop down.

As far as the MACRO, Basically, what I need is 2 fields that would add the following:

Field # 1 Completed (calculation should be Mail date - Exit Meeting Date)

Field #2 Completed Acknowledgement 
(Calculation should be Date acknowledgement mailed - Exit meeting date).

This 2 fields should be added in the two forms( RTS data Input) and CA&CRA Data Input).

Thanks again. Please let me know if you have any questions.


----------



## jimr381 (Jul 20, 2007)

What should these two fields be named and are you going to be using that data in reports or queries later on? Also Consumer and CRA Tier 1 will not be placed in that field at the same time. Should consumer and all the CRA information be in separate fields?


----------



## Gemuine (Aug 16, 2007)

Well, you can them:
Field #1 # of days, completed
Field #2 # of days, completed acknowledgement.

And yes, these two fields will be incorporated in the reports later. ARe thos ethings hard to do? Or are they simple?


----------



## jimr381 (Jul 20, 2007)

I appended my previous post as well. Can you address that question as well please? OK I see where you were trying to show me the names of the previous fields as well.


----------



## slurpee55 (Oct 20, 2004)

Jim, look in the table f_CA&CRA Input at the field next to Consumer Exam Type; there you will find the Consumer - Tier 1, Cra - Tier 1, and so on. 
Gem, the two forms are very similar and some seem to track the same information - is that needed? If more than one person is entering data at once you could run into problems if they both are trying to enter into the same new field at the same time.


----------



## jimr381 (Jul 20, 2007)

Actually she should be ok with multiple users in the DB at the same time. Access imposes record locking so when one person creates or is in their editing a record then another user will not be be able to edit or enter info in that same record. What threw me off was there were 2 forms in the new database she sent me and 1 in the original. I was just not looking in the correct form.


----------



## Gemuine (Aug 16, 2007)

Here is the answer:

Okay if you open the form called "f_CA&CRA Input", it is one of the fields under "Consumer Exam Type". When you go to that "Consumer Exam Type" Field, you'll see it's all there, it is a drop down.

As far as the MACRO, Basically, what I need is 2 fields that would add the following:

Field # 1 Completed (calculation should be Mail date - Exit Meeting Date)

Field #2 Completed Acknowledgement 
(Calculation should be Date acknowledgement mailed - Exit meeting date).

This 2 fields should be added in the two forms( RTS data Input) and CA&CRA Data Input).

Thanks again. Please let me know if you have any questions.


----------



## Gemuine (Aug 16, 2007)

Unfortunately, I need those fields, maybe if I change the name of the field? WOuld that help.


----------



## jimr381 (Jul 20, 2007)

You are looking for when it is Consumer *OR* CRA tier 1 in the field. OK this is an easy macro to setup. I will be working on it. When you said *AND *I thought you wanted both of those values in that field. AND in access means together, OR means this or that.


----------



## jimr381 (Jul 20, 2007)

OK I am done is there anything else you need me to work on?


----------



## slurpee55 (Oct 20, 2004)

Grief...I hadn't even opened it yet!  Good job....


----------



## Gemuine (Aug 16, 2007)

No, that is it. Can you send it back to me. You are awesome. Thanks so much.


----------



## jimr381 (Jul 20, 2007)

Please make sure I did not miss anything since there has been a lot of back and forth.


----------



## Gemuine (Aug 16, 2007)

Thank you


----------



## Gemuine (Aug 16, 2007)

Hello:
So I have decided to just use one form for the data entry. I would like to use the CA/CRA form. One thing that I want is that I want to be able to do is just the Internal due date criteria.

I want to add the criteria that is already in the RTS Input form and add to the CA/CRA form. 

So if the user selects the any other exam type other than the CA/CRA, then the internal due date should be EXit meeting + 45 days.


----------



## jimr381 (Jul 20, 2007)

OK I will take a look at it tomorrow. I am about to head home.


----------



## Gemuine (Aug 16, 2007)

Hello:
Okay now I have evry thing set-up icluding some date parameters but for some reason, the report are not coming out when I do the date parameters


----------



## slurpee55 (Oct 20, 2004)

uh, in the query qryCompletedAcknowledgements, you have "([tblRTSDataInput].[Date Acknowledgement Mailed])=[Enter "From" Date]" which shows data only when ">[Enter "To" Date]".
But where is "To" Date ever defined?


----------



## jimr381 (Jul 20, 2007)

Oh whoops I was messing with that and did not delete that.  Actually I do not recall making a query within this DB. I will check and see what has been done.


----------



## Gemuine (Aug 16, 2007)

I created the queries. The queries are okay


----------



## Gemuine (Aug 16, 2007)

I created the queries, and I got the date parameters to work. I just now have to worry about the MACROS.


----------



## slurpee55 (Oct 20, 2004)

Gemuine, can you upload your latest version then (and send an unzipped copy to Jim)?


----------

