Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 48
  1. #31
    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 think my db has a mind of it's own! Before, once I selected the player, I'd click on tab or return, and it would bring up the relevant player. I'll keep trying to solve it. Thanks for your help.
    Does the attached db work for you
    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

  2. #32
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In a *** COPY *** of the database (just to get familiar with the process):

    Quote Originally Posted by neilsolaris View Post
    The only thing I'm not sure how to fix is the look up fields in the T-Payments table. I think I used a wizard for this initially. Could you possibly talk me through how to fix that please?
    Open the table T_Payments in design view
    Click on the field PlayerID
    Click on the tab LOOKUP (bottom of the dialog box)The top property is Display Control - click on the down arrow and select "Text Box"
    Repeat as necessary
    Close and save table.



    Quote Originally Posted by neilsolaris View Post
    Also, why is it considered bad practice to have a look up field in the table?
    See The Evils of Lookup FIELDS
    Also, to me, it is a waste of time to create look up FIELDS, because
    1) I never let users have direct access to tables and
    2) you have to do the same set up when creating combo box controls on a form. ( Note - I also don't use datasheet views)




    -------------------------------------------------------------------------------------------
    About table Indexes:
    Click image for larger version. 

Name:	Indexes1.png 
Views:	36 
Size:	97.5 KB 
ID:	42153


    -------------------------------------------------------------------------------------------
    How I name PK/FK fields - (when looking at queries, easier to tell which field is which)
    Click image for larger version. 

Name:	PK_FK1.png 
Views:	38 
Size:	97.6 KB 
ID:	42154







    Clear as mud????

  3. #33
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Many thanks Bob and Steve.

    Bob, my db is behaving now, and the form is working as it should. I'm not sure what happened before, but touch wood it keeps working!

    Thanks for the great info Steve. I'll get on now or tomorrow morning and implement all of your suggestions. Maybe I can post it again for the once over?!

    After that, I'd be interested to know the most efficient way to upload my payment files from Excel. Maybe I'll save that one for next time though.

    Many thanks.

  4. #34
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Here's my latest version. I think I managed to incorporate all of your suggestions Steve, except for the indexing one. I went to the design view for T_InstrumentPlayers, then clicked on Indexes, to bring up the screen you showed before. I tried to copy in the two extra lines as it was appearing in your photo, but when I tried to save it, it came up with this message.

    The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

    Do you know what I was doing wrong? Does the rest of it look ok otherwise?

    Many thanks for your help.
    Attached Files Attached Files

  5. #35
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could not set RI between T_MusiciansDetails and T_InstrumentsPlayers nor the compound index in T_InstrumentsPlayers because T_MusiciansDetails only has 3 Musicians and T_InstrumentsPlayers (field PlayerID_FK) has values up to 774.
    You cannot have PlayerID_FK values that are not in T_MusiciansDetails.PlayerID_PK.


    Open your version of the dB and look at the records in T_InstrumentsPlayers.
    Then open the Mod2 version of the dB and look at the records in T_InstrumentsPlayers.
    Attached Files Attached Files

  6. #36
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Oh yes, I'm kicking myself for not realizing that! I deleted the excess records and it works perfectly now. Thanks a lot for your help.

    I'll post my player payments spreadsheet later, to ask if anyone can advise me of a quick and easy way to upload it to Access.

  7. #37
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Here's my edited player payments file, as promised. I've changed the names of any personal information about myself and the company. Basically, when I've finished entering the data for the particular job, I'd like to import it to Access.

    In the Excel file I have the unique alphanumerical player codes (which I need to submitting the BACS payment), but not the unique player ID's that Access generated. I guess if I need the player ID I could write a code to enter it.

    Thanks for any suggestions.
    Attached Files Attached Files

  8. #38
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I looked at the Excel workbook. Did you write the code? If so, you seem to have a pretty good grasp on VBA.
    You already have a sub to export data to a CSV file. Why can't you use the CSV file to import into Access?

    You have lots of formulas with links to another spreadsheet and without me knowing what the data looks like, it would help a lot if you posted a filled out spreadsheet (changing the sensitive data) instead of a blank one.
    Also post the CVS file. CSV files are easy to import. I used CSV files to import monthly benefit and employee census info - 3500 - 3800 records with 28 columns - Took about 7 minutes (if there were no errors in the data).


    About the spreadsheet:

    I added "OPTION EXPLICIT" to top of the modules. There are a couple of errors
    1) In Sub EnterPlayerCodes(), the variable "z" has not been declared.
    2) In Sub PaymentFields(), there is an error (two places) in the format expression for the format string. Missing the double quotes.



    One more thing. You have code to hide/unhide columns.
    Code:
    Sub HideColumns()
    
        If Columns("f:q").Hidden = False Then
            Columns("f:q").Hidden = True
        Else
            Columns("f:q").Hidden = False
        End If
    
    End Sub
    This could be shortened to one line:
    Code:
    Sub HideColumns()
        Columns("f:q").Hidden = Not Columns("f:q").Hidden
    End Sub

  9. #39
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi Steve,

    Many thanks for going through my spreadsheet, that's really useful. I made all those changes that you suggested. I'm assuming the Sub PaymentFields error was relating to the date (ddmmyyyy)? I've put "" around that both times now. I've haven't tested it yet, but hopefully that's what you were referring to.

    Edit...I just tested the PaymentFields now, and for some bizarre reason the dates work properly when not enclosed in inverted commas, and come up as an error when I include them (well, the date appears in the ddmmyyyy format, but without any forward slashes (dd/mm/yyyy), so it doesn't work when I submit the userform). I looked online, and all the examples have inverted commas. Could it be because I'm entering the date as a variable?

    I did write the code myself. I'm relieved it doesn't sound like I made a complete hash of it then! I used to programme games as a kid, but much of the language has changed now, so I had to relearn a few things.

    I've attached the two excel files you requested. The MusicianDetails one is just the main musician table from Access that I exported to Excel (minus the Access Autonumber which I didn't feel I needed. If you think I do I can add it, although I'll need to change the vlookup formulas). Ideally, I wanted to vlookup the Access table from Excel, but I read that's not practical. Is that right? My system would involve me having to re export the Access table any time I added or edited a musicians entry. Is there a more efficient way of achieving my goal?

    The CSV file is just the format that is required to send to the 3rd party to make the BACS payment. There's hardly any information on it though. I think the file I uploaded is not a csv file, but in all other respects it should be identical.

    Otherwise, would you recommend me writing code to convert all the relevant values to a CSV file, before importing to Access? I've seen examples on Youtube of importing Excel data to an existing table in Access, but my engagement data is dispersed over 3 or 4 table in Access. Do I need to create a query that encompasses all the fields from the relevant tables, and then import the Excel file to that query?

    Many thanks.
    Attached Files Attached Files
    Last edited by neilsolaris; 06-16-2020 at 09:40 AM.

  10. #40
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think I might have lost sight of the big picture..

    Are you trying to move away from Excel to a totally Access solution?

    Are you the only one that does the bookkeeping?


    ================================================== =======

    And thanks for the additional files. I was hoping for a few more records (there are columns with missing data). But I have a little better understanding (I think?).

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

    Sorry, I didn't do exactly what you asked me to before! I've attached the payment file again, but this time I've filled in one line. Normally, I'd enter the player codes in column B, and then the vloopup formulas would extract the personal data from the MusiciansDetails file, and enter them in columns C to N (obviously I couldn't do that with this file). Columns O to AB are the engagement details and fee info, and you can ignore any columns after that if you like. Basically, columns AD to AK is the information required for the BACS payment (it's mostly copying data from earlier columns). Columns AN onward is for me to check that the data the manager supplied me (player name & net totals) match my record, in case I made a mistake.

    Originally, I was toying with the idea of transferring this whole payment process to Access. But now I'm thinking I'd prefer to use my spreadsheet, and when I've finished with it, upload the data to Access. I feel that Excel is easier to enter the data from the manager's version, as he's using Excel too. But I like the idea of being able to use Access to search though past records more efficiently. Up until now, I have another spreadsheet with a summary of past engagements, but I think Access would be better in the long run. What do you think?

    In terms of importing to Access, here's my thinking. Column A (Job No.), column Q (description) and maybe I need another column (Venue) would go to Access Engagements table? I realize this second that in Excel I always put the date of the job in the same cell as the description, whereas in Access it has a separate column. I guess the payment details and date of payment would go to the Access Payments table?

    I'm the only bookkeeper by the way.

    Thanks again.
    Attached Files Attached Files

  12. #42
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    I've been thinking about it, and I think the Venues table, and Date of Engagement field in Access is superfluous to my needs. All I need really is the date of payment, and description of event (which would probably include venue and date/s anyway). The problem is that some engagements span multiple dates and venues, so I think a general description would be easier. What do you think?

    Is a csv file generally easier to export to Access using vba than a normal excel file?

    Thanks.

  13. #43
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry for the slow response. I had a friend that is moving out of state - back to the east coast.


    OK, my thoughts.
    From what little I know about your requirements, I would probably go 100% Access. You might be more comfortable with Excel, so then I would suggest 100% Excel. Excel with a little Access seems cumbersome.
    As far as exporting from Excel to Access, there are several ways. You could link the Excel spreadsheet to Access and use queries or code to add the data, you could import a CSV file or you could write code to insert the data into Access using ADO.


    PaymentFileTest.xlsm - One entry really doesn't help that much. There are 146 rows (row 5 - row 150) and 37 columns (A:AK) of which you actually enter something into only 6 columns (the other columns have formulas). Maybe 7 columns if you count the one entry in "A5" for the Job Number (shouldn't use No for Number - pet peeve of mine)

    So, for an engagement, is there only one musician? How many musicians could be in one engagement? You have 150 rows for musicians....???

    In PaymentFileTest.xlsm data tab, do you clear the rows 5 - 150 each time you want to pay 1 or more musicians?

    How many spreadhseets are there? I see PaymentFileTest.xlsm and T_MusiciansDetailsAccesstest.xlsx, but I don't see a spreadsheet for Engagements/Jobs/Musicians for Job...


    As you can tell, at this point, I am like a blind man in a dark room. So where do you want to go with this??

  14. #44
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks Steve, and absolutely no need to apologize for your slow reply!

    I only entered one line on the payment file, to give you an idea. I could have entered more, but it would just have been more random data that I'd made up on the spot! But you're correct. Typically there would be anything from about 5 to 80 musicians per job, so I only entered the Vlookup formulas to row 150. Also, I wouldn't manually enter the details of each musician, like I did for the one I posted. Normally, I'd type their player code into column B, and their home address, email address, bank details, VAT number etc. would automatically populate in the columns C to L (I think you gathered this). Or if I paste the manager's list of names into column AN then I can run my "Enter Player Codes" code to automatically enter the codes into column B for me. Once this is done I manually enter the data into columns O to Y, although I can largely copy and paste from the manager's spreadsheet he emailed me. This is why I envision Excel being much faster to prepare the payment file than Access, but maybe I'm wrong. With a payment file containing about 60 musicians, I can probably prepare the payment file in about 5 minutes. If I have to enter each player manually, maybe 30 minutes or more.

    The reason I manually entered the musician's details in the PaymentFileTest, is because the vloopup formulas link to the Excel file that holds the actual musicians' personal details, so I wouldn't have been able to post that on here, because it contains sensitive information. I think you realized that though, am I right?

    So, once I've completed filling in the players codes, and other details and it's all correct, I run my "generate PDF files" code to produce remittance advice or self billing invoices, and these get sent to the location on my computer with the same folder name as the job number. Then I click on "send emails" to send a personalized email with the relevant PDF attachment to Outlook.

    I should add, before I actually send the emails, I run my "create CSV file" code, and then upload that file to a third party website, who make the bank payments on my behalf.

    So I have one payment file for each job, which occupies its own folder on my computer. This might not be the most efficient way of doing things, and I can see it's different to how things are done on Access. Would it be better to have one payment file that holds all the jobs? My only worry is that it would end up being a massive file.

    Thanks for explaining about uploading to Access. Probably, in my case, from what you said, I think I would try to write VBA code to link the data directly to the Access tables from my payment files.

    I'll consider your point about either using Access 100% or not at all. I still find Access a bit confusing at the moment. I need to learn more about creating forms and reports. Maybe one day I'll completely change over!

    Thanks again for your help.
    Last edited by neilsolaris; 06-20-2020 at 10:59 AM.

  15. #45
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I understand correctly:

    You have a spreadsheet (T_MusiciansDetailsAccess.xlsx) that contains musician info:
    PlayerCode, First Name, Last Name, Business Name, email address, Sort Code, Account Number, VAT Number, Address line 1, Address line 2, Address line 3, County, Postcode

    There is a 2nd spreadsheet (PaymentFileTest.xlsm) used to calculate/report Pay info.

    There is a 3rd spreadsheet (unknown name/design) from a manager with info about 1 job/gig (at a time?) with 1 - 150 musicians.

    How many people can open the spreadsheet? just you and a manager? How many managers?



    After a job:
    1) A blank Payment spreadsheet is opened.
    2) The Payment spreadsheet has the musician's names pasted in from the manager spreadsheet.
    3) There is code (a button) to look up the musician info from the MusiciansDetails spreadsheet.
    4) Then you manually enter in data for columns O - U ( Today's date, Date of payment, Description of engagement, Fee, Doubling, Porterage, Travel)
    5) In the spreadsheet for PaymentFileTest.xlsm, the columns V-Y do not have formulas, data nor headers..?

    6)Next you do the save to CSV and save the PDF files.
    7)Then you save the Payment spreadsheet with the name as the job number.

    8) You clear the Payment spreadsheet, then close it?


    --------------------------------------------------------------------------------------------------
    Any chance you would post
    1) T_MusiciansDetailsAccess.xlsx with maybe 5 musician's info (made up of course. (Lois Lane, Mickey Mouse, Fred Flintstone, Barney Rubble)?
    2) The manager spreadsheet for 1 job (with 5 musicians) just like you receive it?

    I would like to see the design of the spreadsheets.

    And, yes , I am very aware of the privacy issues.

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

Similar Threads

  1. Replies: 3
    Last Post: 02-10-2020, 07:28 AM
  2. Replies: 21
    Last Post: 01-21-2014, 05:04 PM
  3. Replies: 1
    Last Post: 01-11-2014, 12:39 PM
  4. Replies: 1
    Last Post: 03-30-2012, 11:57 PM
  5. Active X form control
    By amitsingha4u in forum Access
    Replies: 2
    Last Post: 05-18-2010, 12:21 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