Stuck, in Access 2003...

[gsempcb]

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

 

[gsempcb]

(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 .

 

[SydneyGeek]

I don't use the chart features in Access much -- always preferred Excel's charting model -- but here's some help on question 3.

Let's say the form is frmSearch, with controls txtStart, txtEnd, cboDept.

Ajust the criteria for the query so that the date field has this:
Between [Forms]![frmSearch]![txtStart] And [Forms]![frmSearch]![txtEnd]

On the same criteria row, the Dept field has this:
[Forms]![frmSearch]![cboDept]

Save the query. Now to create a button to launch the query:
1. Back to the form, in Design view
2. With wizards ON, drag a Command Button onto the form. Check out the possible commands -- the one to open a query is in the Miscellaneous section. Choose the query from the list, adjust the caption and name when prompted, and you should be ready to roll.

Denis

 

[gsempcb]

Thanks for the help--I've made a bit of progress with some of these things I was working on. I'm 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?

 

[gsempcb]

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.

 

[rconverse]

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.

How are you doing this? I want to link to a Sharepoint table and can't figure this out.

Sorry to intrude on your thread.

Thanks,
Roger

 

[rconverse]

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.

When your form opens, have an input box display for the used to enter the date and then populate that date into your form controls.

 

[gsempcb]

How are you doing this? I want to link to a Sharepoint table and can't figure this out.

Sorry to intrude on your thread.

Thanks,
Roger

No worries--I've been asking for help so much lately, the least I can do is help out some, where I can:

1. In Microsoft Access 2003, click "File"
2. Navigate to "Get External Data"
3. Click "Import"
4. Under Files of type", click the listbox, navigate down to "Windws SharePoint Services ()", and click it

Now you just put the URL of the SharePoint List/Table or whatever in there.

NOTE: On step 3 above, if you click "Link Tables" instead, and then do the rest of the process the same way, it does things a tad differently. You might wanna check me out on this via Google or MSDN or some other resource, but I believe if you use this method, the data is handled such that whatever changes you make to the linked entity in Access are made to the entity in SharePoint; and likewise, whatever changes you make to the linked entity in SharePoint are reflected in the shared entiity in Access (if I didn't word that well, let me know and I'll explain better).

God bless.