Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 42
  1. #16
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

    Many thanks Vlad, I'll check that later. Maybe thanks for your help.

  2. #17
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I just tried your amendments to my database, and it works perfectly, thanks so much.

    Also thanks to Dave too. You've both been so much more helpful than I could possibly have imagined when I posted on this forum. I'll try to load my data, and I'll get back to you if I get stuck.

    Thanks a lot for your help.

  3. #18
    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, good luck with your project and post back if you get stuck!
    Stay safe!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #19
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02.....
    Click image for larger version. 

Name:	Relationships.png 
Views:	26 
Size:	48.1 KB 
ID:	44316

  5. #20
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks ssanfu, I'll have a look into that.

  6. #21
    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 is the latest file with the payment form.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #22
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Morning Vlad,

    That's really great, thanks a lot. It works perfectly.

    I forgot to mention the exact changes I made to the T_Payments table since I last posted here. So I just need to make a couple of tiny tweaks, then it'll fit my new layout. I attached my latest database so you can see what I mean.

    On the T_Payments table, the VATReg field is boolean, so ticked equals VAT registered, and not ticked equals non-VAT registered. I don't need to calculate the VAT for each payment type, just the total (and this is done in Q_Totals). On your new form I also need to enter the payment type, and tick if the musician is VAT registered at the time the job took place.

    So, on your new form, I will try to replace the VAT column with a payment type column, and add a tick box to indicate if they are VAT registered at the time of the job.

    I'll have a go today, and if I get stuck I'll come here.

    Many thanks for your help.

    Edit...for the benefit of anyone reading this, Vlad has added a new form and subform to my database.
    Attached Files Attached Files
    Last edited by neilsolaris; 02-24-2021 at 08:05 AM.

  8. #23
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Edit...this is not relevant anymore.
    Last edited by neilsolaris; 02-24-2021 at 08:45 AM.

  9. #24
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Here is my latest attempt. I'll keep working on it today. There are just two things I'm stuck on now.

    I've managed to insert a checkbox to the main form, to indicate if the musician is VAT registered or not. I'm just not sure how to reference this checkbox (named TickBox) in the event procedure. This event procedure is an 'after update' event procedure, activated from the 'amount' cell. I'm thinking, when a musicians is selected, this checkbox could be on or off depending on the VAT status of selected musician. Then I can override it if necessary. I tried entering =IIf([T_MusiciansDetails]![VAT_Number]<>"",[Me].[TickBox].[Value]=True,[Me].[TickBox].[Value]=False) in the default value property, but whilst it's not causing an error, it's not having any noticeable effect.

    I've managed to create a drop down payment type list now. When I select it though, it's displaying the ID, not the payment type text. Also I think it's posting the results to thePaymentType table instead of the Payments table. I'll see if I can work out why.

    Probably I can move the Date of Payment and Engagement/Description from the subform to the main form, because the date and job would remain constant per entry.

    I notice your advice Vlad about creating another table to show the rate of VAT, in case it changes. That's a good idea. I'll save that one for later though.

    Many thanks for any help and suggestions.
    Attached Files Attached Files
    Last edited by neilsolaris; 02-24-2021 at 09:34 AM.

  10. #25
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Neil,

    Is this the same database/project that you have marked solved? I recall responding to one of your posts re VAT.
    Suggest you deal with "solving" the whole project rather than individual glitches.
    It makes things easier to follow if you keep your thread focused on the project.

  11. #26
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by orange View Post
    Neil,

    Is this the same database/project that you have marked solved? I recall responding to one of your posts re VAT.
    Suggest you deal with "solving" the whole project rather than individual glitches.
    It makes things easier to follow if you keep your thread focused on the project.
    Hi Orange,

    I had marked both this one and the VAT one as solved. However, Vlad posted a form for me on here (about 4 posts ago) in response to a private message which, as you can see, I'm seeking help with, hence why I changed this back to unsolved. Is that ok? Or should I start a new thread? It's the same database, but the form is new.

  12. #27
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    No it's fine to stay with this. You have now given some info that tells us why this is no longer solved. Many readers/responders follow up on threads/issues/links etc. So having multiple threads on the same topic for the same or similar database/issue can be quite confusing, even erratic when trying to find the "solution" or logic to a given thread. All I'm suggesting is keep the dialog/facts in aa thread or at least provide status and a link to a new thread if appropriate.
    Good luck with your project.

  13. #28
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks for the info, I'll bear that in mind for future.

  14. #29
    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 is an updated version with the payment type issue fixed. Have a look at how to use a combo box to diaplay and choose related info (payment type) from a different table without the need to add that to the form's record source.

    On the issue of VAT, having the VatRegistered at the T_Payment table level is wrong in my opinion. It should be in the main T_MusicianDetails table as it is a property of the Musician entity, not the Payment. Also, the existing VAT_Number can be a good replacement for that as an empty VAT_Number field can be interpreted as Not VAT Registered and a filled one as VAT Registered.

    At the same time having the VAT amount field in each payment record as I originally designed is important for historical reasons as you can associate each VAT amount with the payment date. Having the check box and calculating the total VAT in a query (Q_Totals) does not give you that unless you create a new table (T_VAT_Registration) to keep track at when somebody registers, cancels, registers again, etc.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  15. #30
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Many thanks Vlad, that's great.

    I haven't managed to properly test it yet, because it's still got my dodgy VBA code on the Net payment Event Procedure. I'll see if I can rectify that, then I'll report back here!

    I understand what you're saying regarding the VAT. With your idea, I would still calculate VAT totals on the Q-Totals query (if the VAT payable on the T_Payments table is not null I calculate VAT on the net total), I wouldn't be adding up the individual VAT amounts on the T_Payments table, as that could result in rounding discrepancies. So I was thinking the individual VAT amounts for each payment type would be unnecessary, but on the other hand, it's not doing any harm. Both your and my idea would keep track if someone registers, deregisters etc. so I don't have any strong preference, as long as it works.

    I'll be back once I've managed to fix my VBA code. Thanks again.

Page 2 of 3 FirstFirst 123 LastLast
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