Results 1 to 14 of 14
  1. #1
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149

    Why does code work on ertain subs but not on other?

    Hi Guys



    I've noticed that In some situations code work and then in other the same doesnt work. For example:

    In my PaymntsF form I have this:
    Private Sub InvoiceID_AfterUpdate()
    Dim TmpCustID As Integer

    TmpCustID = Me!InvoiceID.Column(1)
    Me!txtCustomerID = TmpCustID
    End Sub

    Here it works fine. I am using the InvoiceID from the previous form to give a textbox on the current form a value. Then this value is saved to the table. The textbox txtCustomerID is bound to CustomerID in the table PaymentsT.

    Then If I do the same thing in a different form I get an error that I cannot assign a value to this object.

    Private Sub Form_Open(Cancel As Integer)
    Dim TmpInv As Integer
    Dim TmpCust As Integer
    TmpInv = Forms!InvoiceDataEntryF!InvoiceID
    TmpCust = Forms!InvoiceDataEntryF!CustomerID
    Me.txtInvoiceID = TmpInv
    Me.txtCustomerID = TmpCust
    End Sub

    On second code the textboxes both populate with the values but it wont save it to the source fields on the table

    Does anyone now why Access does this?

    I want to save the two text boxes value to the 2 fields on the table.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Try it on form load, not form open, then add

    Code:
    If Me.Dirty then Me.Dirty = False
    to save the record.
    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 ↓↓

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Some things cannot be done in some events. Likely the controls are not yet available when the code runs in Open event.

    Use Load event.
    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.

  4. #4
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Hi Minty

    Could you explain what does the code mean pls? What is Dirty r rather what does it mean on a form?

  5. #5
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Hi June7... that actualy makes sense even to me lol. I'll try that

  6. #6
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    When any data changes on a form the is a flag to indicate the data is changed - this is the Dirty property.
    Me.Dirty will be True if any data has changed, you can force that data to be saved by Setting the Dirty property to False.

    And as both June and I said in the earlier post - move the event to the Load event of the form.
    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 ↓↓

  7. #7
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Ooooh okay..thank you now I understand the Dirty concept

    I have changed the code from open to load event and no error anymore. However, the textboxes are not being populated. I tested the TmpInv and TmpCust variables with message boxes and they do get the correct values...just the textboxes don't show it so the table is not saving it (yet)

  8. #8
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    I removed the source from both text boxes to make them unbound and now they show the values, so obviously they don't update the values in table

  9. #9
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Is this form opening on a new record by any chance ?
    What is the forms record source ?

    Can you post up a stripped down version of your database?
    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 ↓↓

  10. #10
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Yes, the form is opening to a new record. The form source is a table called PaymentsT.

    Every payment a client makes is captured to this table. It has the invoiceID, CustomerID, Date, payment method and amount as fields. All the fields work except the invoiceId and the customerid which both are supposed to be acquired from the form and thn saved. Ive got a Form for the invoice (InvoiceDataEntryF) that has a button for payment. when clicked it opens a Form for the payment so that the payment is for this specific invoice only.

  11. #11
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Quote Originally Posted by Freddie81 View Post

    ...I removed the source from both text boxes to make them unbound and now they show the values...(
    That sounds like you had the values assigned by having an expression in the Control's Control Source Property...is that correct? That would be responsible for not allowing it to be set using code in the Form_Load event.

    Also,

    Quote Originally Posted by Freddie81 View Post

    ...I've noticed that In some situations code work and then in other the same doesnt work...I get an error that I cannot assign a value to this object...
    Once again, do you (or have you, in the past) had the values assigned by having an expression in the Control's Control Source Property?

    Another possibility involves the Datatype of the Field that the Control is Bound to. Many times things like ID numbers are defined as Autotype Fields, and thus cannot be set by VBA code or thru their Control Sources.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    No there was no expression in the data source field, it was directly linked to InvoiceID and CustomerID. The fields on the table are both number and not autonumber.Click image for larger version. 

Name:	001.JPG 
Views:	8 
Size:	82.4 KB 
ID:	34184

  13. #13
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If the form is only ever used for new records then I would set the forms default values to the calling form underlying fields

    Me.txtInvoiceID.DefaultValue = Forms!InvoiceDataEntryF!InvoiceID

    Or better still use
    the open args property in the form load event, to pass the default values so that it's not tied to one calling form.
    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 ↓↓

  14. #14
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    That default value code works like a charm. Many Thanks

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

Similar Threads

  1. Replies: 2
    Last Post: 07-16-2017, 05:48 PM
  2. Replies: 5
    Last Post: 01-08-2015, 12:40 PM
  3. Deleting unused subs
    By snipe in forum Programming
    Replies: 2
    Last Post: 12-20-2013, 01:57 PM
  4. Code Does not work
    By larabeelw in forum Access
    Replies: 11
    Last Post: 11-27-2013, 03:18 PM
  5. Creating/Using Public Subs
    By sparlaman in forum Programming
    Replies: 3
    Last Post: 05-19-2011, 03:29 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