Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

    Problem saving a new entry with multiple tabs


    Hi,

    I've created a form with three tabs, relating to orchestral players. The first tab is the player contact details, the second tab is their bank account details, and the third tab the instrument(s) they play. The first two tabs relate to the same table (T_MusiciansDetails), whereas the third tab is linked to the instruments table. The instruments table is related through the auto generated unique player number.

    The problem is, when I create a new entry, and click on save, I get this error message "You cannot add or change a record because a related record is required in table 'T_MusiciansDetails'".

    I'm guessing that the problem is, until the new record is created, there won't be a related entry in the instruments table. Is that right? How do I get around this?

    Many thanks for your help.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    The attached db shows how I might handle this scenario.

    Post back if you need any clarification.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    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,
    I suggest you show us your table designs and your relationship window. It would also be helpful if you could describe the intended processing by means of an example. Something about Bob who banks at BankX and plays the trombone for example. What is it that brings these things together? And what do you need from this set up? That is, what exactly is the requirement for this database in plain English?

  4. #4
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks Bob and and Orange.

    I'll look at that link in a second Bob. I'll also, once I've worked out how to do so, post my relationships window.

    In the short-term, I just want to record data relating to new players, using a form. I also have tables relating to player engagements and engagement venues. I do the bookkeeping and player payments for a freelance orchestra. Ultimately, once I have the database set up, I'm planning to use VBA to automate the following process. Create individual remittance advice or self billing VAT invoice PDF files (depending whether they have a VAT number or not), automatically give each file a unique name based on the player code and job number, then store it in the relevant folder. The other process is to automatically post a personalized email message plus the relevant attached PDF file to Outlook. I achieved this with Excel, now I'm using my free lockdown time to get Access to do the same job!
    Last edited by neilsolaris; 05-27-2020 at 05:35 AM. Reason: Typo

  5. #5
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Click image for larger version. 

Name:	Relationships.jpg 
Views:	20 
Size:	173.1 KB 
ID:	42019

    Here is my relationships window. Do I need to do a bit more explaining, or does it make sense already? Feel free to let me know if you notice any errors. Many thanks.

    I could add, payment type in this context is just a breakdown of the overall fee, e.g. fee, travel, porterage, doubling etc.

    Also, the Player_Code in the T-MusiciansDetails is a unique alphanumerical code, that is required for the BACS payments. I was originally thinking that I could use this instead of the auto generated unique number, but on another forum I was advised to use the latter, I'm not sure why though.

  6. #6
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I just managed to check your database example now Bob. Thanks very much for doing that. I notice that you set up the instruments table in a more efficient way than I did. I can look into changing mine to how you did it maybe, if it easy to do. There are about 750 musicians on my database though, so it might take a while!

    I'll have a play around with the one you created, to see if I can do the things I wasn't able to do on mine.

    Thanks again.

  7. #7
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi again Bob. Your worked just how I wanted mine to! I'm not sure how to solve what I'm doing wrong yet.

    I notice that there weren't any relationships created on yours. Is that right? It still seemed to work fine though.

  8. #8
    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,

    See this tutorial from RogersAccessLibrary. Review the Hernandez process. Work through the example and experience the design process. Then, make sure you have a clear description of your "business", work through the process with your data.
    There are several articles on Database Planning and Design etc at the link in my signature.
    Good luck.

    Quick review suggests you need to identify each of these(and maybe more) and how they relate to one another:
    players
    player engagements
    engagement
    engagement venues
    bookkeeping
    player payments
    remittance advice
    self billing VAT invoice
    player code
    job number
    personalized email message
    instrument
    Last edited by orange; 05-27-2020 at 07:02 AM. Reason: additional info

  9. #9
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks Orange.

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by neilsolaris View Post
    I just managed to check your database example now Bob. Thanks very much for doing that. I notice that you set up the instruments table in a more efficient way than I did. I can look into changing mine to how you did it maybe, if it easy to do. There are about 750 musicians on my database though, so it might take a while!

    I'll have a play around with the one you created, to see if I can do the things I wasn't able to do on mine.

    Thanks again.
    Hi Neil
    First things that I noticed in your relationship diagram are:

    I can't see a table for Instruments (I have tblInst)
    I can't see a table that lists which players use which instruments (I have tblMusInst)

    I only use one table for the main form which is bound to tblMusicians. I use fields from that on the main form and the first two tabs. The third tab has a subform bound to tblMusInst and linked to the main form by the MusicianID field (hidden) on the main form and MusicianID on the subform.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi Bob,

    You're right, I didn't design the database properly when it came to instruments. What I did, was create a table (T_Instruments) with 3 columns. Columns 1 was the unique player number, then I entered the their instrument in column 2, and if they played another instrument I entered it in column 3. Now I've seen yours I know how I should have done it! As I've got 750 players (the vast majority of which only play one instrument), I wonder how the quickest way would be to reorganize the data.

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by neilsolaris View Post
    Hi Bob,

    You're right, I didn't design the database properly when it came to instruments. What I did, was create a table (T_Instruments) with 3 columns. Columns 1 was the unique player number, then I entered the their instrument in column 2, and if they played another instrument I entered it in column 3. Now I've seen yours I know how I should have done it! As I've got 750 players (the vast majority of which only play one instrument), I wonder how the quickest way would be to reorganize the data.
    With your original design, when you get a musician that can play more than two instruments you have a problem. Also, I presume that Intrument_1 and Intrument_2 are text fields, so you would need to search both fields to find a "Trombone" player and you would probably never find a "Trombrone" player .

    If you post a copy of your db I would be happy to have a look at it and try to reorganise the tables.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks for the offer Bob. I'd love to send you a copy of my database, but I've already entered the personal details of the musicians, including bank details etc. Obviously I trust you implicitly! I wonder if there is a way around this. Maybe I could saveas with a different name, then delete all the personal data, before sending to you? Would that work?

    I managed to change the instruments tables to match yours. I exported the table to excel, then wrote VBA code to change the instruments to their relevant values, then imported back to access. I'll let you know how I get on with that when I'm back home again.
    Last edited by neilsolaris; 05-27-2020 at 02:22 PM.

  14. #14
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi Bob and Orange (and everyone else),

    I've attached a copy of my database, minus all the personal information. I'd be very grateful for any suggestions.

    I think I've successfully incorporated Bob's advice regarding the instruments tables.

    I should add, I don't really need the instrument information to do my job, but I think I'll keep it, just in case it would be useful for the director/fixer one day. Likewise, I've kept the Geographical Area column, just in case it's useful, even though I don't need to know it to do my job. Regarding the Geographical Area, would I be correct in saying that it would be better to set up out like the instruments tables?

    Ideally, I'd like two forms. One for entering and editing players' contact, financial and instrument details. Then another form for inputting details relating to new engagements (i.e. venue, engagement date, players, fee, travel etc.). Once this information is entered to the relevant tables, I can use it to create the remittance advice and self billing invoice files.

    I have some VBA code written to Excel, which I'd like to transfer. Two of which would relate to the new player form. When a new player form is submitted, I need VBA to create a unique alphernumerical code for the new entry. Also, if no business name was entered, I need VBA to enter the first and last name as the business name.

    Thanks again for your help.

    Edit: I forgot to attach the file. I can't work out how to do it though! I think my file is 1.4MB, which is too big to upload. Is that right?

  15. #15
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 22
    Last Post: 12-29-2015, 10:41 PM
  2. Replies: 3
    Last Post: 02-11-2014, 03:26 PM
  3. Replies: 12
    Last Post: 12-17-2012, 12:47 PM
  4. 1 Form used to open Multiple Tabs
    By joefonseca79 in forum Forms
    Replies: 3
    Last Post: 03-08-2011, 09:42 PM
  5. Replies: 23
    Last Post: 02-09-2011, 10:56 AM

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