Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jun 2008
    Posts
    33

    Unanswered: Stuck, in Access 2003...

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    1 - Is this table a linked table? Do you have the rights to change the data (if you open the table, can you edit a record maually)?

    2 - Create a Group By query to Sum the three pieces of data and set the criteria for your range of time.

    3- Use a form and set the criteria to the form's control. Create an unbound form where the user can enter the criteria. Refer to the form's controls in the criteria line of your query (try right clicking in the criteria line and choosing 'Build').

  3. #3
    Join Date
    Jun 2008
    Posts
    33
    Thank you for the response.

    1. It seems like this whole table is "Read-Only", and it looks like it draws from a SharePoint site, somewhere.

    2. I created the query, and it comes out fine. That is, I run the query, and that "Enter Parameter Value" box asks "Enter Department", so I enter a department; then it asks "Enter Start Date", and I enter one; then it asks "Enter End Date", and I enter one. Then it pops out the three data in datasheet mode just fine. Now how do I get it to do the same thing, as just three vertical bars lined up next to each other on a graph, that display the field names under the three, then the numbers on a y-axis that show where the data falls? Eventually I'd like this to be where a person clicks the report, a query runs for whatever department and start/end dates the user supplies, and the bar graph with the three pieces of data pops out. But like I say, I'd like the entering in of data to be more user-friendly on those "Enter Parameter Value" boxes. But that leads to question 3..

    3. I've created a new form with a Combo-Box that goes to a Table, and then references the values in the field "Department" that is in that Table, so that when a user sees this form, he sees a Combo-Box for all of the Departments. But now, how do I get this to show up, first thing, whenever someone wants to run a query to, for example, get the hours spent for a given Department (question 2), instead of having that "Enter Parameter Value" box come up and have the user manually type the Department name?

    Thanks again for the reply.

  4. #4
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    1- Sonds like it's a linked table and you'll need to talk to the owner about permissions.

    2- Use the report wizard to create a graph based on the query.

    3- Users, in my opinion, shouldn't be running queries. Thier interaction should be limited to forms and reports. In my world the user is presented with a form upon opening the database. This form usually has buttons that will take the user to other forms and reports where data can be entered, viewed, exported, printed or whatever. In this case the user would be presented with a criteria form (unbound) containing two text boxes (txtStartDate, txtEndDate), one combobox (cboDepartment), and two buttons - Cancel and Run (to open a form or report that diplays or prints the data).

  5. #5
    Join Date
    Jun 2008
    Posts
    33
    2. I've been fooling around with the Create New Chart Wizard under the "New>" option for reports, but I cannot get the bar graph to display all of the fields/display it how I want it to. And really, the third field/bar only needs to be calculated by subtracting the first field (Hours Planned) by the second field Hours Used, so I'm not even sure I'm supposed to be selecting that field when I am supposed to be choosing which fields I want to use from which query, as I go through the Create Chart Report Wizard. For instance, I have all three of the desire fields selected, but I only see one of the fields when I finish making the bar chart:
    http://www.rjbfabrication.com/barchartsample.png

    3. Okay, I am seeing now how a form like that is supposed to work. I still have a few questions though. Like, how would you go about setting whatever someone enters into the TextBoxes and ComboBox as filters/criteria for displaying the data (Like ONLY displaying data from 01/01/2005 to 01/01/2008 , if those are respectively entered into the TxtStartDate and TxtEndDate text boxes?)
    Last edited by gsempcb; 06-05-08 at 17:39.

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    2- Create a query that outputs the three pieces of data that you need. Create a new report - on the first screen chose 'Chart Wizard' and use the combobox to chose the query with your data, click OK. Next Screen - double click each of the three fields that you want in your chart, click Next. Choose the Chart Type (upper left corner for a bar chart), click Next. This screen should have three boxes around a chart on the left and your three fields on what look like buttons on the right. The three boxes are, starting at the top, Data, Series, Axis. The Data box will most likely have one of your fields listed already. Drag the other fields from the 'buttons' on the right to the Data box. If anything is in the Series or Axis box, drag it out. You should now have all three of your fields in the data box and the Series and Axis boxes should be empty. Click Finish.

    3- In the query grid (design view), right click on the critia row in the Department column and choose 'Build'. Double click 'Forms' then 'All Forms' then your criteria form. All the controls from your form will show up in the middle pane. Double click on the one containing the Department Criteria and click OK.

  7. #7
    Join Date
    May 2008
    Posts
    3

    Dropdown boxes

    For selecting data in a form, I like to use the combo box. You can enter the itsm you want the user to select from. You can also put the items in a seperate table and have enter a combo box in your form to pick from the table.

  8. #8
    Join Date
    Jun 2008
    Posts
    33
    2. I have made some progress, so I thank you: 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.

    3. 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 just do what you're telling me--throw the two text-boxes and the combo-box 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?

    (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")

    I know I've said it a lot, but I do thank you for the help, and appreciate any more you can give.

  9. #9
    Join Date
    Jun 2008
    Posts
    33
    2. How does this look to you? This 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.

  10. #10
    Join Date
    May 2008
    Posts
    3
    Sorry so long in replying. My job requires travel

    I looked at your relationships. They look good. But, I have had this often happen when I forget that I set up one table, assign a length value of for example 5 to a field I am going to use for a relationship - forget and assign 6 for lenth to the second field. Check that first. Make sure the fields match exactly

  11. #11
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try summing the hours in qryDepartmentWRHours instead.

    Also, you may want to drop the spaces in your field names (not sure how far along you are in the developement). Having spaces can be a pain when you start using VBA.

  12. #12
    Join Date
    Jun 2008
    Posts
    33
    2. I'm "giving up" on the bar charts for now. I may come back to them, but now I am focusing on those prompts and getting my reports to show up correctly (below--and it's coming along much better)

    3. Yes, I have made some little breakthroughs. A lot of it was what rogue said:
    Try summing the hours in qryDepartmentWRHours instead.
    and what Kathysexton said:
    Make sure the fields match exactly
    So I thank you both, so far. I *finally* just looked at the design behind qryDepartmentWRHours instead of just trying to make a whole new query, and I saw the "[Enter Department Name]" and "Between [Start Date] and [End Date]" expressions in the criteria field under the "Requesting Department" and "Date" fields, respectively--so that's what was making those prompts this whole time. So therefore, any other report or query that references those fields from this query also made those stinking prompts come up. I've now created a form with two Text Boxes--"Start Date" and "End Date"--and a ComboBox with the Departments listed in it. Now, whenever I run a query a report behind the scenes, as long as these new form controls I made have values in them, everything runs and shows the data correctly. Or even if I make a button ON that form that gives the command to open a given report, everything runs and shows the data correctly, as well.

    So now, I've fixed 95% of the report/queries that I need to fix. The only thing that's left for me to do is to see how some of these old/outdated expressions work, on this one query. Then I need to figure out what fields they are calculating, and make some new more accurate expressions, and make sure and link them to the start date/end date textboxes and department combo box.

    Thank you both so much for the help--I still may be back too though, heh..

  13. #13
    Join Date
    Jun 2008
    Posts
    33
    (Stuff in red is stuff that is already able to be done with the way I have the form created now; I just want to make sure I keep those capabilities)

    Well, I am back ( 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 so that whenever they are input in the above part of the form, they are automatically put into the subform, below.
    - "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 with this form:

    - "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")
    Last edited by gsempcb; 06-16-08 at 17:23.

  14. #14
    Join Date
    Jun 2008
    Posts
    33
    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.

  15. #15
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Look up msgbox and the open event of a form in help.

    Why would you want to do this?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •