Results 1 to 3 of 3
  1. #1
    Wingnut74 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    10

    Saving invoices with services filtered by date for future reference

    Hi Everyone.


    I am currently trying to solve a problem with invoicing. The idea is to create an invoice that looks up all the work completed for one customer in the previous month and then create the invoice. I have no issues creating the invoice based on the work records, I can even search for invoices by Owner which will give me all services but only by adding the InvoiceID to the services table manually. how can I save the services details with the invoice ID/Number to be able to to review the invoice at a later date. I have a feeling this is an easy fix but I just can't see it.

    Tables:
    Customer
    Invoice
    InvoiceDetails
    Services
    All linked with their respective ID's

    I have looked at various examples on the internet but they all seem to concentrate on sales invoices created by using a drop down box to select items but I don't want to have to select 30+ services each time I create an invoice. So my question is: How do I get the invoice number or ID to register on each individual service OR am I just going about it the wrong way.

    I would be grateful for any advice.

    Many thanks in advance.

    Wingnut74

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    781
    Can you post a screenshot of your relationships?

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,682
    I'm guessing your services records have an empty field for InvoiceRef?

    In which case

    Code:
    SELECT * FROM Services 
    WHERE InvoiceRef Is Null And CustomerID = EnterYourCustRef
    Will select the records you want. Then

    Code:
    UPDATE Services SET InvoiceRef = [Enter the invoice ref here] 
    WHERE InvoiceRef Is Null And CustomerID = EnterYourCustRef
    Would update the same records with the InvoiceRef

    Obviously, you could use a form to get the criteria and automate the invoice number value from your invoices table.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 3
    Last Post: 02-24-2019, 02:35 PM
  2. Replies: 1
    Last Post: 01-09-2017, 10:50 AM
  3. Replies: 1
    Last Post: 04-14-2014, 07:12 PM
  4. Replies: 3
    Last Post: 11-20-2012, 01:40 PM
  5. Future date parameter
    By normie in forum Queries
    Replies: 10
    Last Post: 08-12-2010, 09:38 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 - Senior Forums