Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query in Read Only (2007)

    Can someone please tell me why this query opens in read only mode?

    SELECT tblentry.[Emp#], tblentry.[Job Number], tblentry.[Activity Code], tblentry.[Week#], tblentry.MR, tblentry.TUR, tblentry.WR, tblentry.THR, tblentry.FR, tblentry.SAR, tblentry.SUR, tblentry.MO, tblentry.TUO, tblentry.WO, tblentry.THO, tblentry.FO, tblentry.SAO, tblentry.SUO, tblentry.Year, tblJobNumbers.[Billing Code], tblJobNumbers.Description, [Billing Code] & [Description] AS combine, Left([Billing Code],7) AS [Part of Billing Code], tblJobNumbers.[Customer Name]
    FROM (tblJobNumbers INNER JOIN tblentry ON tblJobNumbers.[Job Number] = tblentry.[Job Number]) INNER JOIN [Total Time per Job Query 2 for local] ON tblentry.[Job Number] = [Total Time per Job Query 2 for local].[Job Number]
    WHERE (((tblentry.[Emp#])=[Forms]![frmCalendar]![txtEmpNo]) AND ((tblentry.[Week#])=[Forms]![frmCalendar]![txtCurWkSt]) AND ((tblentry.Year)=[forms]![frmcalendar]![txtyear]))
    ORDER BY tblentry.[Job Number];

    It has something to do with the query "Total Time per Job Query 2 for local", I could add data until I add this query to the new query. Job number is indexed with no dublicates.

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    Is "Total Time per Job Query 2 for local" a totals query? Totals queries and queries based on totals queries are not updatable, because they aggregate records together.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Read Only (2007)

    I will try to explain what I want to do. I have a timesheet program. Users enter their time in every week. On each week, I would like to display the total time they have for the year on that current job number. Sum of Expr1 returns that total, but it also brings the timesheet up in read only mode. Is there a way to accomplish this where it won't be in read only mode?
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    Leave "Total Time per Job Query 2 for local" out of the query, so that the query and the form based on it will not be read-only.
    Create a small form based on "Total Time per Job Query 2 for local", and place this as a subform on the "main" form. You will probably join the subform to the "main" form on Job Number.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Read Only (2007)

    I tried what you suggested, however I hit a problem. In this case the main form, frmEntrySub, goes into another form as a subform and is set to continuous form. When I try to add the new form based off of "TotalTime per Job Query 2 for local", I get an error saying "A form with a subform object can't have its DefaultView property set to Continuous Forms". When I set the frmEntrySub to single form, the total does what I want it to do, but it only shows one record. I need frmEntrySub to be continuous that way it will show all records.
    Thanks,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    What information from "TotalTime per Job Query 2 for local" do you want to display on frmEntrySub?

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Read Only (2007)

    The query has three fields in it: Emp #, Job Number, and Sum Of Expr1. Sum of Expr1 is the total time an employee has on a job number for the year. When I view this query in datasheet, it works correctly. I want to display Sum Of Expr1 of frmEntrySub.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    You could try to use a text box instead of the subform. Set the Control Source property of the text box to
    <code>
    =DLookup("[Sum of Expr1]", "[TotalTime per Job Query 2 for local]", "[Emp#]=" & [Emp#] & " AND [Job Number]=" & [Job Number])
    </code>
    This expression assumes that Emp# and Job Number are numeric. If one or both of them are text fields, you need to change the expression, for example if Job Number is text:
    <code>
    =DLookup("[Sum of Expr1]", "[TotalTime per Job Query 2 for local]", "[Emp#]=" & [Emp#] & " AND [Job Number]=" & Chr(34) & [Job Number] & Chr(34))
    </code>
    Chr(34) is the double quote character ", needed to enclose text values.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Read Only (2007)

    The job number it needs to match is a job number found in a combo box on frmEntrySub. It is called cmbJob. Here is what I tried:

    =DLookUp("[Sum of Expr1]","[TotalTime per Job Query 2 for local]","[cmbjob]=" & Chr(34) & [Job Number] & Chr(34))

    I get #Error in the text box. I think it might be a syntax error. I left Emp# off because the query only produces totals for the active employee number.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    You should probably switch the field and combo box:

    =DLookUp("[Sum of Expr1]","[TotalTime per Job Query 2 for local]","[Job Number]=" & Chr(34) & [cmbJob] & Chr(34))

    The name within the quotes is the field name, and the one between the Chr(34)'s is the control on the form.

  11. #11
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Read Only (2007)

    I had to change in to read the correct column number of the combo box, but that works fine. But now when the subform opens, it does not allow me to add a new record. The frmWeek holds frmEntrySub. frmWeek is based off of quTime. When I open quTime, it allows me to add records. When I go back and open frmWeek, it shows the new added records, but it won't allow me to add a new record. Where do I set this? frmEntrySub is set to continuous form.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    I don't think I can assist further without seeing the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  13. #13
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query in Read Only (2007)

    I figured it out. I guess when I added the new subform to the existing subform, it changed the data entry settings. I had to changed Allow Additions, Allow Deletions, and Allow Edits from No to Yes.
    Thanks for your help.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query in Read Only (2007)

    Glad you were able to solve it! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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