Results 1 to 11 of 11
  1. #1
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22

    Applying a SetValue macro to all returned query Subform results


    Hi - I've got a Subform based off a query and only pulls in the values associated with a job # in the parent form.
    Most of the time this returns multiple line items. I have a command button that sets one value (a checkbox) when clicked, but it only does it to the selected line item. Is there any way I can get it to set all the line items in the form?

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Options:

    1. code moves to each record of the subform and sets value of the field

    2. code runs sql UPDATE on the table using job# (and any other fields) as criteria
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22
    How do I do option #1?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I have never done #1.
    Am guessing would need to use
    DoCmd.GoToRecord , , acNext

    #2 is easier.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22
    OK - I'll play around with the update query then.
    Each of the line items has a UniqueID based on a combo of Job_ID&Hours_Serial, so I think I should be able to make that work.

  6. #6
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22
    Two questions:

    1) How do I isolate the query to only update the records that are in the subforms (When I drag/drop the original query in, Access brings up the dialog wondering whether to associate the Job# from the parent to the subforms, but I'm not sure how to do that otherwise).

    2) If the original query has criteria that it's only showing unbilled items (the variable I'm changing), when I run the query to update those values, will everything from the form disappear?

    Thanks

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You drag/drop what original query in where?

    The UPDATE I suggested would not reference UniqueID, but use criteria that would capture all the records showing on the subform.
    I would use VBA to execute SQL action, something like:
    CurrentDb.Execute "UPDATE tablename Set Unbilled=True WHERE Unbilled=False AND JobID=" & Me.JobID

    Then requery the subform to immediately reflect the change:
    Me.subform.Requery

    The fact that you are attempting to maintain a billed/unbilled check field tells me your data entry is not optimized. Maintaining fields dependent on other data is very risky. The item is billed if it is associated with an invoice number.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22
    Quote Originally Posted by June7 View Post
    You drag/drop what original query in where?
    To put the query into the main form as the subform, I've just been drag/dropping the query from the sidebar. So when I do that it asks if I only want entries from the job # associated with the invoice.


    The UPDATE I suggested would not reference UniqueID, but use criteria that would capture all the records showing on the subform.
    I would use VBA to execute SQL action, something like:
    CurrentDb.Execute "UPDATE tablename Set Unbilled=True WHERE Unbilled=False AND JobID=" & Me.JobID

    Then requery the subform to immediately reflect the change:
    Me.subform.Requery

    The fact that you are attempting to maintain a billed/unbilled check field tells me your data entry is not optimized. Maintaining fields dependent on other data is very risky. The item is billed if it is associated with an invoice number.
    Thanks - I'll give it a try.

    The marking line items as billed rather than whole invoices is sort of the way we have to go, just based on the way we do our billing, so I (hopefully) have it set up right for that, although this is my first db design, so who knows, really?

  9. #9
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22
    OK - I've got the VBA for marking everything as billed...
    But now I'm trying to do more coding so that some calculated values in the form store to my parent table (Invoices form, it pulls in values from three different subforms to a total - I want to be able to store that value in the invoices table.)

    I have a command button where on click it does:

    Code:
    CurrentDb.Execute "Update Invoices Set Amount=" & Me.Amount
    CurrentDb.Execute "Update Invoices Set Province=" & Me.Province
    CurrentDb.Execute "Update Invoices Set GST=" & Me.GST
    CurrentDb.Execute "Update Invoices Set Provincial_Tax=" & Nz(Me.Provincial_Tax, 0)
    But this updates every single Invoices record, not just the Invoice_ID associated with the current form.

    Any help with the code to isolate the Update to only the current Invoice_ID would be appreciated.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    CurrentDb.Execute "Update Invoices Set Amount=" & Me.Amount & ", Province=" & Me.Province & ", GST=" & Me.GST & ", Provincial_Tax=" & Nz(Me.Provincial_Tax, 0) & " WHERE Invoice_ID=" & Me.ID

    That assumes all fields are number type. If any are text or date/time, the values will need delimiters. Text use apostrophe and date/time use # character. Like:

    & ", Province='" & Me.Province & "',

    & ", GST=#" & Me.GST & "#,
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    thedanch is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2012
    Posts
    22
    You're a lifesaver. Thanks so much.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SetValue Macro action
    By rkalapura in forum Programming
    Replies: 3
    Last Post: 12-19-2011, 11:54 AM
  2. No SetValue in Macro editor
    By asabri in forum Programming
    Replies: 2
    Last Post: 09-21-2011, 01:39 PM
  3. Problem with SetValue Macro on Form
    By Accidental DBA in forum Forms
    Replies: 3
    Last Post: 04-01-2011, 01:48 PM
  4. Returned No Results Message Box
    By Swilliams987 in forum Queries
    Replies: 2
    Last Post: 02-04-2011, 12:07 PM
  5. Access Reports - Applying a subform filter
    By AMCUser in forum Queries
    Replies: 13
    Last Post: 06-25-2010, 07:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums