Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Vba

  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Vba

    The below VBA are on two forms. All the lines in blue works correct. The one in red, errors. I am missing something obvious.
    __________________________________________________ _________________________________
    Private Sub T002_Click()


    If (Me.T001) = 1 Then
    Me!CmbEnt_ID02b = Me!CmbEnt_ID02c
    End If
    End Sub
    __________________________________________________ _________________________________
    Private Sub T003_Click()
    If (Me.T001) = 2 Then
    Me!CmbEnt_ID02b = Me!CmbEnt_ID02a
    Me!DocumentNumber02a = "PIN" & Me!PchInvID
    End If
    End Sub
    __________________________________________________ ____________________________
    Private Sub DiscountRate02_AfterUpdate()
    Me!Discount02 = Me!Unitprice02 * Me!Quantity02 * Me!DiscountRate02
    Me!ExclVatDr02b = (Me!Unitprice02 * Me!Quantity02) - Me!Discount02

    Me.Requery
    End Sub
    __________________________________________________ ____________________________

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    I don't see anything obvious. What is the error message?

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Goto the VBA of form f02PurchaseInvoice. No error, but it should concatenate "PIN" with field PchInvID in the query q02PurchaseInvoice.
    Attached Files Attached Files

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks. If I may be a nuisance. In the database go to q02PurchaseInvoiceSub in design. I would like to join two fields PchInv_ID01 and PchInvID. That is from query q02PurchaseInvoice. It makes the query q02PurchaseInvoiceSub not updatable??

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Umm, I asked for the error message not the database. Can you please tell me what the error is?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Why save to table? Why save a static string prefix to table? Just calculate this concatenation whenever needed on output.
    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
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    First thing you need to do is add option explicit to the top of each of your modules and clean up the errors.

  8. #8
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    No error, it left the field empty.

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi June7. My previous thread discusses delays extensive, lagging and halting. I describe how we found a solution that work well. No delays. Every month our experience improved so now we have better insight. We strongly want to keep calculations and expressions away from large tables. We decided to test using VBA to calculate documents. All documents are also combined in the Combined Transactions query. For report purposes we need this field. All the different documents have different prefixes but ends up in the same field in q02CombinedTransactions. Another experience when we use union queries the prefix falls away when formatted, but not when concatenated.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Moke123. Thanks. Our running system runs well with no errors. I copied a few objects, to make it small enough otherwise I couldn't post it. I know the modules here is messed up.

  11. #11
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Does that mean the initial issue is also resolved?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Right, the static prefix can be concatenated in expression in UNION. Why is UNION needed? But if you really want instead to use storage and increase size of db as well as tables, save static string.

    You have tables linked on PchInvID and PchInv_ID01 in Relationship builder. PchInvID is an autonumber but you have CmbTrnNumber02a defined as primary key which is linked to CbTrMinID in q02CmbTrnMain. The form's RecordSource is not updatable.

    The field DocumentNumber02a is alias for DocumentNumber01A (DocumentNumber02a: DocumentNumber01a). The field you are really trying to update is DocumentNumber01a.

    Your code has "INP" instead of "PIN" but since it won't work regardless, moot point.



    Embedding image files into table may also cause slow performance and will certainly chew up file size limit.
    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.

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    No this issue is not resolved yet. Must still have a good look at June7's latest suggestions.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Did some edits on my previous post. Sorry, no suggested fix, just identified possible cause.

    Usually a form does data entry/edit for one table. Why is this form's RecordSource so complicated?
    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.

  15. #15
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Sorry for being a bit at sleep here. Over many months we were taught to get all the various documents into one query for the purpose of ledger and financials, we must do a Union query. We do have insight in other ways and of course we stay open for the best way, but Union works well. I am following up on your other advice. Thank you.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

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