Page 3 of 3 FirstFirst 123
Results 31 to 42 of 42
  1. #31
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I deleted my dodgy VBA code now, and it works really well again. Thanks for that.



    So, I think I'll reintroduce your original system regarding the VAT payments.

    Would the form still work ok if I moved the Engagements/description and date of payment up to the main form, so I only need to enter it once per musician?

  2. #32
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Neil,
    You can't move there as the main part of the form is bound to the T_MusicianDetails table that doesn't have those fields (and shouldn't as they belong with the payments). What you can do is two unbound controls, a combo for the Engagements (just copy and paste the one from the subform) and a text box for date (make its default value = Date() or =Now()). Now in the subform edit the engagement and payment date controls to take the defaults from the new ones above.
    See this updated file for details.
    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #33
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi Vlad,

    Thanks for explaining that for me. I'm not at my computer right now, but I'll check out the file a bit later.

  4. #34
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I just tried it now, it works really well, thanks a lot for that.

    So the final bit is reverting it back to your original setup with the VAT, or getting mine to work. That can be tomorrow's project!

  5. #35
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Good morning Vlad (and everyone else),

    I just played around with your new form this morning, and I'm really pleased with it, so thanks a lot.

    As I mentioned, the final thing is the VAT. I've had a think, and I think I'll stick with my idea for now. If you or anyone could help me with with this final bit I'd be really grateful (I'm happy to do it myself, I just need a bit of help along the way).

    I've attached the database. The only tiny changes I've made are changing the data type in the VATReg field of T_Payments table from boolean to text. So "yes" indicates that the musician was VAT registered at the time of payment, and blank indicates that they weren't. I think it looks a lot cleaner than the checkboxes. And on the subform, I have added another column to display the VATReg from T_Payments.

    So my plan is this. When I enter a new line, if the musician is VAT registered (i.e. if they have a VAT number), then "yes" is automatically entered, otherwise no value is entered. If, say, the musician became VAT registered only after the engagement (and before me adding the data), then I can manually delete the "yes" after each payment line. Did I explain this ok, and is this something that could work in practice? If so, how would I go about this stage?

    Many thanks.
    Attached Files Attached Files
    Last edited by neilsolaris; 02-25-2021 at 08:30 AM.

  6. #36
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Your interpretation does not seem correct to me as it would not allow us to know if at the time of purchase it was actually in possession of the VAT number once it was subsequently modified. Also why is the billing not related to the buyers?

  7. #37
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by CarlettoFed View Post
    Your interpretation does not seem correct to me as it would not allow us to know if at the time of purchase it was actually in possession of the VAT number once it was subsequently modified. Also why is the billing not related to the buyers?
    Thanks Carletto for your help. I might be misunderstanding your point. But the 1 after the payment indicates that the musician was VAT registered at the time of payment (possibly I might change the 1's to yes to make if more meaningful). If they subsequently deregister, it won't effect past payments. The 1 indicates that they were in possession of a VAT number. Did I understand you correctly?

    Also, I didn't link the customers (sales invoices) and suppliers (musician payments). But any musician payments relating to the customer invoice I give the same reference number. It just makes end of year accruals easier. But no need to link them otherwise. It's all recorded in aggregate in QuickBooks anyway.

    Edit: I've reattached the latest version, and just changed the 1's in the T_Payments table to "yes"'s.

  8. #38
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Neil,
    Here it is, please let us know if it works.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #39
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by Gicu View Post
    Hi Neil,
    Here it is, please let us know if it works.
    Cheers,
    Hi Vlad,

    Thanks so much. It almost works perfectly. So, I entered some transactions for the VAT registered musician, and that worked. Then when I entered a transaction for the non-VAT musician straight after it automatically entered VAT for him. Does it do the same thing for you?

  10. #40
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Neil,
    Please replace the code in the subform with this one (seems the VAT Numbers are set to "" instead of Null):
    Code:
    
    Private Sub txtAmount_AfterUpdate()
    'VATReg =Yes , Null if not
    If Not IsNull(Me.txtAmount) Then
        If Nz(Me.Parent.Controls("txtVATNumber"), "") <> "" Then
            Me.VATReg = "Yes"
        Else
            Me.VATReg = Null
        End If
    End If
    Me.Refresh
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #41
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by Gicu View Post
    Hi Neil,
    Please replace the code in the subform with this one (seems the VAT Numbers are set to "" instead of Null):
    Code:
    
    Private Sub txtAmount_AfterUpdate()
    'VATReg =Yes , Null if not
    If Not IsNull(Me.txtAmount) Then
        If Nz(Me.Parent.Controls("txtVATNumber"), "") <> "" Then
            Me.VATReg = "Yes"
        Else
            Me.VATReg = Null
        End If
    End If
    Me.Refresh
    End Sub
    Cheers,
    It all works perfectly now! Many thanks for your help. You're a godsend!

  12. #42
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome Neil!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. DB Design Critique
    By Sanguineus in forum Access
    Replies: 8
    Last Post: 12-14-2018, 10:28 AM
  2. Replies: 2
    Last Post: 03-16-2018, 10:19 AM
  3. Replies: 1
    Last Post: 03-21-2015, 11:55 AM
  4. Please critique table design
    By Jennifer Murphy in forum Access
    Replies: 2
    Last Post: 02-01-2014, 11:45 PM
  5. Request for design review / critique
    By Charles7565 in forum Database Design
    Replies: 3
    Last Post: 10-26-2011, 02:04 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