I.T. Discussion Community!
-Collapse +Expand
Access
Search Access Group:

Advanced
-Collapse +Expand Access To/From
To/FromCODEGuides
-Collapse +Expand Access Store
PRESTWOODSTORE

Prestwood eMagazine

April Edition
Subscribe now! It's Free!
Enter your email:

   ► MB LobbyMicrosoft Access BoardMS Access Interactive Topic   Print This     

Stuck, in Access 2003...

Stuck, in Access 2003... in MS Access Interactive topic (part of our Microsoft Access group).

Quick Search: Stuck Access   Stuck Access 2003  
gsempcb
Tyler, TX USA

Hi, just want to start by briefly giving you an idea of what I am working with, overall: I am using Access 2003. The overal Access file is made up of various Tables, Forms, Queries, and Reports (like normal), a few relationships among them--just standard stuff. Also, one of the tables imports data from SharePoint.

I'm stuck in a few places, though--I have three different issues, currently:

1. I'm trying to do a mass changes query, and change all of the values in a certain field (Text data type) from an old name to a new name--one value to another value. I thought I knew correct procedure on how to do this: ("New Query", go to Design view, Choose desired "Field", Choose desired "Table", and either fill in "Update To" or go to the Property Sheet and fill in "Caption" with the new name I want the given piece of data to have, and fill in "Criteria" with the old value that I want changed). But, there's one problem. After I set this how I want and attempt to Run the query, I get an error stating "Once data is changed, it can't be undone", so I click "Yes"--that's not so bad. The problem comes in on the second error box. It tells me the data is read-only, and can't be changed. Now the field I am trying to query to change is part of the table that is imported from SharePoint. Does this matter? What can I do about this?

2. I am trying to create a bar chart to include on a report. I just want three bars: One for a piece of data on "Budgeted Time", one for a piece of data on "Time ended up being Used", and then a piece of data that is a variance/difference of the previous two. I'd just like there to be a bar for each of these pieces of data, standing side by side whenever a user specifies a certain range of time--like what's happened with the hours, up to now (see problem 3 for more on that, as well). For the life of me, I cannot figure out how to pull the data off in the best way and show it like this. Any ideas?

3. Various of my reports/queries run, based on my supplying two criteria for it to display data. That is, when double-clicking to view some reports, it needs me to provide a Certain Department, and a certain start and end date. I provide these by typing them in to three seperate text prompts. What is a more efficient way to be able to supply the department and time range without having to type them by hand? Is there any sort of way to have a drop-down box or some way of choosing them?

Thanks in advance for any help anyone can offer.

God bless!
Click here to AIM gsempcb

 Posted 12 years ago (Thread Starter)
Comment Quote
About gsempcb -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12823, 3 replies
Thread Started 6/5/2008 10:41:37 AM
View Counter=3921
Last Reply Posted 6/23/2008 3:17:53 PM)
Location=Tyler, TX USA 
Joined=12 years ago   MB Posts=24  
gsempcb
Tyler, TX USA

(I've been doing some work, and have made some progress, so I want to update my questions and see if anyone can help, now. I am fairly certain that I cannot do any more with question number 1 regarding the SharePoint table, so I'm just kind of mentioning it as a side-note, now.)

(1. Update Query
This table pulls some data from SharePoint and makes it into a table. I can't update it because I don't have permission, But I still want to ask, this would be correct procedure to change all of the "Phase Status" fields that say "Identification" to say "New", instead, right?
Identification to New
Yes, there is also a field called "Identification", but disregard that--that's not what I'm referring to. There is a field called "Phase Status" in that table, and some of the data are "Identification" for that field, and
that's what I'm trying to change to say "New")

2. Creating Bar Chart
I have made some progress in making this bar chart. I got the bar graph to spit out two of the pieces of data that I need--still don't know what's going on with the third one. But anyway, I decided to redo the query I'm working with, in order to get the pieces of data. This whole time, I am working on a database that someone else put together, and that someone else linked all the data together and all that, so it takes me awhile to see where such-and-such data comes, and is calculated with such-and-such other data. So I am working on re-creating this query that will output the three pieces of data that I need, but I need some assistance in typing those expressions to calculate fields. Like, I need the query to make the "Hours Spent" field sum up into one number (based on that department and date range, that I keep mentioning); and also I need to make the query calculate a "Remaining Hours" field by subtracting "Hours" minus the sum-up of "Hours Spent" (what I just said, above). So rather than ask/get someone to write these expressions for me, does anyone know of a good resource page that shows some of this basic expression writing for Access queries? I have done these expressions before (these are what I'm talking about, and these are what's in this database right now), but it's been a while, and I forget the syntax. I did create a new query and write some new expressions, and the following image gets so close, and brings such close results to what I'd like to do:

Query Design for Bar Chart

When I run that query, I get another "Enter Parameter Value" prompt, now, asking you to manually type in "Sum of Hours Spent", in order to calculate the last field "Remaining Hours". If I put in the right value for the Sum of Hours, it spits the data out *perfectly* But why does it ask for Sum of Hours to be typed in manually, in the first place? That's why I put in the whole "Hours Spent", "Sum" thing.

3. Scrapping "Enter Parameter Value" method, to create more user-friendly method of filtering data
Regarding the report I am making, with the two text boxes for start and end date, and the combo-box for department, I am trying to figure out how to link these buttons to the sources of data. Like I said above, someone else had built this database before and I'm just now working on it. Part of what they built in is to make the "Enter Parameter Value" text box show up for the enter start/end date and department details whenever you try to view the reports or queries. And I am now trying to figure out how to get rid of those prompts, and throw the two text-boxes (that would allow the user to enter a Start Date and and End Date, for desired data) and the combo-box (that would allow the user to choose a Department, for desired data) on a form, let users enter the department and range they want, in order to filter out and get the data in whatever reports. Do you know any sources, like a webpage/tutorial webpage, or something that could show me a bit more on how that works?

Thanks for any help anyone can offer. I am also up for talking in AIM about this, if anyone prefers. My handle is gsempcb .

 Posted 12 years ago (Thread Starter)
Comment Quote
About gsempcb -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12830 (Level 1.1)  Reply to 12823
Thread Started 6/6/2008 2:06:17 PM
Location=Tyler, TX USA 
Joined=12 years ago   MB Posts=24  
gsempcb
Tyler, TX USA

Well, I am back ({#rolleyes} wonderful, huh?), and I am working on something new within the same database--working with forms and subforms.

4. I would like to have a subform in one of my forms that reflects the changes that I am making in the main part of the form. So far, I already have it working *somewhat* how I would like it to, but there are a couple of more intricacies I would like to add, but I do not know how to incorporate these. They may require additional macro(s), module(s), query/queries--I don't know. Here is a snapshot of what I've got now:

Hours Form

The form needs to be set up such that after the users presses "Enter" on entering "Hours Spent", that the changes are reflected in the subform right under it like this:
- "Week Ending"/"Date Worked" are linked; "Project ID"/"Project" are linked; "Hours Spent"/"Hours" are linked.
- "Project ID", "Discipline", and "Hours Spent" at the top then blank out
- "Week Ending" and "Developer" stay the same
So that multiple projects can be added for the same employee and same date. But then, whenever user clicks "Next record" there at the VERY bottom, then everything blanks out, and you start over with a new employee and date. (User can then manually go in there to "Work Description" and add that, if desired, just whenever)

Other, not-as-important (for right now) problems:

- "Sum of Hours" there at the bottom brings error. I just set it to sum the "Hours" field in the subform, but it doesn't do it.

- "Discipline"/"Discipline Code" are not linked. That is, when it's entered/chosen at the top, it doesn't throw it down there into the subform. It makes sense because in the wizard, I only set three fields to link, but is it possible to have four links in a form/subform?

(1. This goes back to that permissions situation, in which this pulls some data from SharePoint and makes it into a table. I can't update it because I don't have permission, But I still want to ask, this would be correct procedure to change all of the "Phase Status" fields that say "Identification" to say "New", instead, right?
Identification to New
Yes, there is also a field called "Identification", but disregard that--that's not what I'm referring to. There is a field called "Phase Status" in that table, and some of the data are "Identification" for that field, and that's what I'm trying to change to say "New")

 Posted 12 years ago (Thread Starter)
Comment Quote
About gsempcb -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12872 (Level 1.2)  Reply to 12823
Reply Posted 6/16/2008 12:03:44 PM
Location=Tyler, TX USA 
Joined=12 years ago   MB Posts=24  
Most Recent Post
gsempcb
Tyler, TX USA

Ok, just brand new question:

How do you go about having a text field on a form set up so that right when user gets to the form, a Message Box (with accompanying space for user to type) appears saying "Enter Date", then whatever user enters into that message box is entered into the text field?

Basically I'm trying to do the exact opposite for this form I am creating, from the "Enter Parameter Value" occurence that kept happening in one of my queries, a few weeks ago. That is, I would like something like this "Enter Paramter Value" for this form.

 Posted 12 years ago (Thread Starter)
Comment Quote
About gsempcb -Collapse +Expand
Visit Profile
Approved member.
Member subscribes to this thread with a verified email.
Old Account!
If this is your account, sign in to activate web presence data (sign in quarterly to keep active). Alternatively, you can subscribe to our monthly eMag with a valid email address.
Web Presence Hidden.
Once above is taken care of, full Profile content will display including back links, about me, my message, custom Profile html, social networking links, message board signature, company profile, etc.

Post ID #12888 (Level 1.3)  Reply to 12823
Reply Posted 6/23/2008 3:17:53 PM
Location=Tyler, TX USA 
Joined=12 years ago   MB Posts=24  

Revive Thread!

Add a comment to revive this old thread and make this archived thread more useful.

Write a Comment...
Full Editor
...
Sign in...

If you are a member, Sign In. Or, you can Create a Free account now.


Anonymous Post (text-only, no HTML):

Enter your name and security key.

Your Name:
Security key = P184A1
Enter key:
Icon: A Post    Thread    Idea    Important!    Cool    Sad    No    Yes    Includes a Link...   
Thread #12823 Counter
3921
Since 6/5/2008

Follow PrestwoodBoards on: 


©1995-2020 PrestwoodBoards  [Security & Privacy]
Professional IT Services: Coding | Websites | Computer Tech