Results 1 to 11 of 11
  1. #1
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62

    How to use 'IF' clause in Macro to extract and compare two values in two separate tables?

    Hi,



    I have a simple invoice system in my database. Here are the main data sources:

    tblSTOCK: StockID, Stock_Description, QTY_in_Stock

    tblINVOICE: InvoiceID, CustomerID, Date

    tblINVOICE_DETAILS: InvoiceID, StockID, QTY_Ordered

    Simply put, when the user selects a QTY_Ordered amount (which is in a subform) and initiates an event to process the invoice (e.g. click of a command button or close form) a macro is invoked which runs an update query to update the new level in stock.

    I've tried to use an if statement to avoid entering in more stock than available, here is what i tried:
    Code:
    [Forms]![fsubInvoice_Details]![QTY_Ordered]< [tblStock]![QTY_in_Stock]
    This doesn't work however. Can the DB not access the QTY_in_Stock field in tblStock?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Cannot pull data from tables by direct reference like that. Use domain aggregate function (DLookup, DCount, DSum, etc.).
    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
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    Quote Originally Posted by June7 View Post
    Cannot pull data from tables by direct reference like that. Use domain aggregate function (DLookup, DCount, DSum, etc.).
    Hi, I took your advice and used a DLookup("[QTY_In_Stock]",tblStock) although still encountering errors.

    See attachment... look at frmInvoice and click on Process button.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review: http://rogersaccessblog.blogspot.com...mystified.html

    "Each argument must be a string expression and must, therefore, be surrounded with quotes."

    Saving calculated data is usually a bad idea, especially aggregate data. This should be calculated when needed.

    Ideally, there are transaction records for product in/out. Do queries that sum product received and sum product used. The difference is inventory on hand.

    http://allenbrowne.com/AppInventory.html
    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
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    Hi, I'm can't seem to get my DLookup working...

    Code:
    =DLookUp("QuantityInStock","tblStock","[StockID]=[forms]![fsubInvoiceDetails]![StockID]")
    This is my attempt to, at least, pull data from the Stock table...looking up the current quantity in stock. Point to note, this is in a subform. It works ok if it's a single form...why not a subform?

    From here, if i get this working, i'd like to use the return value from the DLookup to decide whether or not the entered 'Order Quantity' is valid.

    thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That same article discusses how to use variables in domain aggregate expression. Look at the last example.

    Variables are never placed within quote marks. Reference to form control as filter parameter is a variable. Concatenate variables.
    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.

  7. #7
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    Thanks for your help June... but still not getting the data to come up. I read that article and tried this:
    Code:
    Me.Text12.ControlSource = DLookup("QuantityInStock", "tblStock", "[StockID] = " & [Forms]![fsubInvoiceDetails]![StockID])
    Also tried:
    Code:
    Me.Text12.ControlSource = DLookup("QuantityInStock", "tblStock", "[StockID] = " & me.[stockID])
    I was actually hoping that it could work through Expression Builder?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I virtually never use expression builder or any of the wizard tools.

    The syntax is correct so the issue may be with form design.

    What form?
    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.

  9. #9
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    The form is a sub form fsubInvoiceDetails which is in frmInvoice. The subform allows user to select StockID and a Quantity. I just want to ensure that they can't enter a quantity greater than that stored in tblStock.[QuantityInStock]

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Your field names in table have spaces in them but there are no spaces in them as used in the DLookup.

    Also, the Stock ID combobox name has a space because it was replicated from the field name. I always rename controls that I want to use in code or expressions, like: tbxStockID.

    This works in textbox ControlSource: =DLookUp("[Quantity In Stock]","tblStock","[Stock ID] = " & [stock ID])

    Also, do not want to set ControlSource property with VBA, set Value and since it is the default, don't need to specify.

    Me.Text12 = DLookUp("[Quantity In Stock]","tblStock","[Stock ID] = " & Me.[stock ID])

    DLookup is actually my last choice for this - preferred alternatives:

    1. StockId combobox RowSource includes [Quantity In Stock] field then textbox can reference columns of the combobox for the quantity

    2. include tblStock in subform RecordSource, join type "Include all records from tblInvoiceDetails and only those from tblStock that match.", bind textboxes to tblStock fields and set them Locked Yes, TabStop No.



    Advise not to use spaces nor special characters/punctuation (underscore is exception) in naming convention. If used, must enclose names in [].
    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
    tim_tims33 is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Dec 2011
    Posts
    62
    Thanks June , will try this out to see if it works. The ultimate goal is not for use in this form. Rather, I Wud like to use this as a criteria for a macro... If statement, to determine if macro shud run update stock query or not. I will test thiS.

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

Similar Threads

  1. Macro to parse data and compare tables
    By twckfa16 in forum Programming
    Replies: 3
    Last Post: 01-05-2015, 03:24 PM
  2. Replies: 1
    Last Post: 06-08-2012, 02:45 PM
  3. Extract String From Between 2 Values
    By kathleencampbell in forum Queries
    Replies: 5
    Last Post: 03-23-2012, 10:52 AM
  4. Replies: 3
    Last Post: 02-23-2012, 06:29 PM
  5. Extract certain values from field
    By tylerg11 in forum Programming
    Replies: 19
    Last Post: 09-23-2011, 03:27 PM

Tags for this Thread

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