Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Referencing a query (2002)

    Hi,

    Is it possible to reference an item stored in a query the same way that it is done with [forms]![frm.............]? I tried but it didn't seem to work which is why I'm asking the question before spending a ton more time with it. I have a multiselect table that uses a query to send the selected items to print as a report. This works fine. What I am trying to do is to update [reprintDate] only for the items selected in the multiselect box. The update query runs fine as long as I don't put anything in the criteria section. Unfortunately all the cells get updated which is not what I want. I tried referencing the [iD] on the form that contains the multiselect list but that resulted in nothing being updated. That's when I thought if maybe I reference the query that contains the [id] from which the report is filtered.

    Stuck again,
    Leesha

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Referencing a query (2002)

    In the Detail section of the report can you use an Update query to update the RePrintDate using the ID that is passed thru by way of the Reports recordsource?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing a query (2002)

    Hi Pat,

    OK, I've been messing around with this for the past hour. My problem is that the [reprintdate] is going to all the fields not just the ones in the report. I'm sure I haven't defined is correctly so that the "ID is passed thru by way of the reports recordsource." This is the code I put into "onprint" in the "print" even of the detail section of the report:

    DoCmd.OpenQuery "qryISFReprintUpdate", acViewNormal, acEdit

    I tried putting [reports]![rptISFReprintForm]![id] into the query but I get parameter issues, and I did define the parameter. I even tried putting in [forms]![frmISFReprint]![id] but that resulted in nothing.

    What am I missing?

    Thanks,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing a query (2002)

    Leesha,

    Without the SQL for the query, we're just guessing. Post the SQL and someone should be able to help you. As far as your code goes, all you're doing it calling an update query. When you run a query in code, you can't just refer to a control like that, you have to pass the parameters in in code.
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing a query (2002)

    Hi Charlotte,

    Here you go. To populate the list, enter the dates 10/1/04 to 10/31/04 and hit refresh. My goal is that only the records that are reprinted get updated in the reprint field.

    Thanks,
    Leesha

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing a query (2002)

    In the on print of the detail section, use following code :
    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim strSQL As String
    strSQL = "UPDATE tblISFHistory SET tblISFHistory.ReprintDate = Now() Where [ID] =" & Me.ID
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    End Sub

    This will update the records one by one, each time a record is printed. You don't need the query qryISFReprintUpdate any more.
    Francois

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Referencing a query (2002)

    Thank you! That worked just as I needed it to!

    Leesha

Posting Permissions

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