Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53

    how to set VBA to automatically active when saving a form

    Hi,



    I do the bookkeeping for a freelance orchestra, and I have been using Excel until now to store the player details, and manage payments. I've recently transferred the data to Access, with the help of some people on these forums.

    Next, I'd like to transfer my VBA code to automate some processes. To begin with, I'd like two things to happen when I save a player's details form. I'd like VBA to check whether a business name has been entered. If it hasn't, I want it to enter the first and last name of the musician.

    Secondly, if the Player_Code box is empty (i.e. it's a new entry rather than saving a current entry), I'd like to assign a unique alphanumerical code, consisting of the first 3 letters of the player's surname, followed by 3 numbers, starting from 001 (incrementing until it's a unique entry).

    I've written a VBA code already for Excel, and it works perfectly (although I'd sure there's a more efficient way of achieving it, I'm not advanced in VBA), and I've pasted it below.

    So my question is, is someone here able to advise me how I can make my code work in Access? I'm happy to do the research if someone can point me in the right direction. I'm just having a little trouble getting going!

    Many thanks.

    By the way, I realize that much of the code below is superfluous when applying to Access.



    Private Sub Submit_Click()


    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual


    Dim x As Integer
    Dim v As Integer
    Dim counter As Integer
    Dim y As String
    Dim a As Integer
    Dim match As Integer
    Dim z As String
    Dim test As String




    If Worksheets("Lists").Range("A10") = "Edit" Then
    test = Business_Name
    Else
    test = ""
    End If


    x = 6
    Do Until Worksheets("Musicians' Details").Cells(x, 4) = test
    x = x + 1
    Loop
    x = x - 1


    With Worksheets("Musicians' Details")
    .Range("A1").Offset(x, 1) = First_Name
    .Range("A1").Offset(x, 2) = Surname
    .Range("A1").Offset(x, 3) = Business_Name
    .Range("A1").Offset(x, 4) = Instrument
    .Range("A1").Offset(x, 5) = Email
    .Range("A1").Offset(x, 6) = Mobile_Number
    .Range("A1").Offset(x, 7) = Sort_Code
    .Range("A1").Offset(x, 8) = Account_Number
    .Range("A1").Offset(x, 9) = VAT_Number
    .Range("A1").Offset(x, 10) = Address1
    .Range("A1").Offset(x, 11) = Address2
    .Range("A1").Offset(x, 12) = Address3
    .Range("A1").Offset(x, 13) = County
    .Range("A1").Offset(x, 14) = Postcode
    .Range("A1").Offset(x, 15) = Geographic_Area
    End With


    'If Business_Name field is left blank, then insert first and last name
    If Business_Name = "" Then
    Worksheets("Musicians' Details").Range("A1").Offset(x, 3) = Worksheets("Musicians' Details").Range("A1").Offset(x, 1) & _
    " " & Worksheets("Musicians' Details").Range("A1").Offset(x, 2)
    End If




    If Worksheets("Lists").Range("A10") = "Add" Then
    x = x + 1
    v = 6


    'assign unique alphanumerical code to record (consisting of first three letters of surname followed by number, starting from 001)
    y = UCase(Left(Surname, 3))


    Worksheets("Musicians' Details").Activate


    Do Until Left(Cells(v, 1), 3) = y Or Cells(v, 1) = ""
    v = v + 1
    Loop


    For a = 1 To 50
    If a < 10 Then
    z = y & "00" & a
    Else
    z = y & "0" & a
    End If
    match = 0


    For counter = 0 To 49
    If Cells(v + counter, 1) = z Then
    match = 1
    End If
    Next counter
    If match = 0 Then
    Exit For
    End If


    Next a


    Worksheets("Musicians' Details").Cells(x, 1) = z



    With Worksheets("Musicians' Details").ListObjects("Musicians_Details").Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("Musicians_Details[Surname]"), Order:=xlAscending
    .Header = xlYes
    .Apply
    End With
    End If


    Unload Add


    Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic


    End Sub


    Private Sub UserForm_Click()


    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,284
    Access has an auto number field. You need do nothing to it.
    in a query use that and the left(Lastname,3) to get your Player code.

  3. #3
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Thanks ranman256, that sounds like a great and easy solution, which I hadn't thought of. At the moment there are fewer than 1000 players. If it's gets to over 1000, what would you suggest? I could subtract 900 from the autonumber to get a three digit number. But I think that would run the risk of generating a non-unique code. It needs to be exactly three digits, to comply with the BACS requirement. Is there an obvious solution to that?

    Many thanks.

  4. #4
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    In terms of my first task (entering the player's first name and surname if Business_Name is blank) I've almost worked it out. In design mode, I clicked on "save", then in Properties clicked on Event Procedure, which brought me to the relevant section in the VBA module.

    I'm able to insert the first and last name, which is of course very straightforward. But I'm struggling with this bit. I want to create an If statement, to test if the Business_Name is blank or not. I want to put "IF Business_Name = "" Then" but it this doesn't work (it doesn't seem to recognize this field as being blank). If I enter "IF Business_Name<> "" Then" it does recognize the field as containing data. What am I doing wrong? Should I be using the word null instead of ""?

    Many thanks.

  5. #5
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Having searched online, this seems to have done the trick.

    If Len([Business_Name] & "") = 0 Then

    I need to analyse it a bit, but presumably Access thought the field was zero, not null before, which is why it didn't work?

  6. #6
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,378
    The < > expression means not equal to, but Null wont evaluate as false, it evaluates as null (nulls are great )

    A Null value is not the same a zero length string (ZLS) "" as you discovered. You can test this in the immediate window;

    ? Null <> ""
    Null
    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
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Thanks Minty! What is the intermediate window?

    Edit: I found it now!

  8. #8
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,808
    Quote Originally Posted by neilsolaris View Post
    So my question is, is someone here able to advise me how I can make my code work in Access? I'm happy to do the research if someone can point me in the right direction. I'm just having a little trouble getting going!
    I've looked through the code for the Excel Submit_Click() and it seems pretty straight forward.
    And it looks like you have at least 2 worksheets. But to give a more focused answer/suggestion, it would be helpful to see 1) your dB and 2) an Excel workbook. Only needs to be a few records in Access (with matching data in Excel) - change any sensitive data. (I change names to cartoon characters.)

    Any chance you would be willing to post these??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  9. #9
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Hi Steve,

    Sorry for my slow reply (I just got back from work now), and many thanks for offering to help. I'll post a non-sensitive copy of my database and Excel spreadsheet later. It'll probably be tomorrow I think. I'm just slowly working out how to adapt the VBA tonight for generating the unique player codes. If I manage to crack it you can have a look over that too, if that's ok.

    Thanks again.

  10. #10
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Hi,

    Sorry I didn't get round to uploading my databases before. I managed to solve what I set out to do now, with the following code (see below). I found a much simpler way of generating the unique player code than I had previously.

    My next challenge is with the player payments. I will get round to uploading my database and excel files soon to make the process clearer. But I have two processes that are automated on Excel that I'd like to be able to do with Access. The first one is to create a PDF file named Remittance Advice or Self Billing Invoice (depending if they have a VAT number or not), and for the PDF files to contain all the information that I entered relating to the player's particular job. This includes VAT number, title, invoice or ref number, player's name and address, today's date, description of job & payment date, fee, travel, porterage etc, net total, vat, total gross. The next process is attaching these PDF files to personalized emails in Outlook and sending.

    Broadly speaking, what would be the most efficient way to achieve this with Access? With Excel, I simply insert the data into another worksheet in the relevant cells. Things like the logo and page footer stay constant. With access, would I need to do it as a report? Or could I send each entry straight to Excel to convert to PDF from there?

    One other automated procedure I have on Excel is this. The M.D. emails me an excel file (his version) of the player payments, which I can mostly copy and paste to my file. I enter the player names by reference to their unique player code. Then I set his player names and net totals alongside my record, and I have a VBA code to show in red any differences, so I know if there have been any mistakes. Often he abbreviates names, so they come up in red, but at least I feel safer having checked. So my question is, can such a safety system as this work in Access, or should stick to Excel for this?

    I set up a form on Access for entering the player job details, but I find this much slower than copying and pasting in Excel. Is it easily possible to import the data from Excel to Access, and add it to existing records?

    Thanks again, and I hope all this makes sense!

    Private Sub Save_Click()


    If Len([Business_Name] & "") = 0 Then
    Business_Name = First_Name & " " & Surname
    End If


    Dim y As String
    Dim a As Integer
    Dim pc As String
    Dim test As String


    If Len([Player_Code] & "") = 0 Then


    y = UCase(Left(Surname, 3))


    For a = 1 To 50


    If a < 10 Then
    pc = y & "00" & a
    Else
    pc = y & "0" & a
    End If


    test = DCount("Player_Code", "T_MusiciansDetails", "[Player_Code] = '" & pc & "'")


    If test = 0 Then
    Exit For
    End If


    Next a


    Player_Code = pc


    End If




    End Sub

  11. #11
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,808
    Quote Originally Posted by neilsolaris View Post
    The first one is to create a PDF file named Remittance Advice or Self Billing Invoice (depending if they have a VAT number or not), and for the PDF files to contain all the information that I entered relating to the player's particular job. This includes VAT number, title, invoice or ref number, player's name and address, today's date, description of job & payment date, fee, travel, porterage etc, net total, vat, total gross. The next process is attaching these PDF files to personalized emails in Outlook and sending.

    Broadly speaking, what would be the most efficient way to achieve this with Access? With Excel, I simply insert the data into another worksheet in the relevant cells. Things like the logo and page footer stay constant. With access, would I need to do it as a report? Or could I send each entry straight to Excel to convert to PDF from there?
    I would create a report, then output the report in PDF format - skip Excel altogether.




    Quote Originally Posted by neilsolaris View Post
    One other automated procedure I have on Excel is this. The M.D. emails me an excel file (his version) of the player payments, which I can mostly copy and paste to my file. I enter the player names by reference to their unique player code. Then I set his player names and net totals alongside my record, and I have a VBA code to show in red any differences, so I know if there have been any mistakes. Often he abbreviates names, so they come up in red, but at least I feel safer having checked. So my question is, can such a safety system as this work in Access, or should stick to Excel for this?

    I set up a form on Access for entering the player job details, but I find this much slower than copying and pasting in Excel. Is it easily possible to import the data from Excel to Access, and add it to existing records?
    Yes, it is possible to import an Excel spreadsheet into Access. You could set up conditional formatting or use code to do the checks and notify you somehow. Depends on how you want to do it.



    I modified your code a little (couldn't resist)

    Code:
    Private Sub Save_Click()
        Dim a As Integer
        Dim pc As String
        Dim test As String
    
        If Len(Me.Business_Name & "") = 0 Then
            Business_Name = Me.First_Name & " " & Me.Surname
        End If
    
        If Len(Me.Player_Code & "") = 0 Then
    
            For a = 1 To 50
                pc = UCase(Left(Me.Surname, 3)) & Right("00000" & a, 3)
                test = DCount("Player_Code", "T_MusiciansDetails", "[Player_Code] = '" & pc & "'")
    
                If test = 0 Then
                    Exit For
                End If
            Next a
    
            Me.Player_Code = pc
        End If
    
    End Sub
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Quote Originally Posted by ssanfu View Post
    I would create a report, then output the report in PDF format - skip Excel altogether.





    Yes, it is possible to import an Excel spreadsheet into Access. You could set up conditional formatting or use code to do the checks and notify you somehow. Depends on how you want to do it.



    I modified your code a little (couldn't resist)

    Code:
    Private Sub Save_Click()
        Dim a As Integer
        Dim pc As String
        Dim test As String
    
        If Len(Me.Business_Name & "") = 0 Then
            Business_Name = Me.First_Name & " " & Me.Surname
        End If
    
        If Len(Me.Player_Code & "") = 0 Then
    
            For a = 1 To 50
                pc = UCase(Left(Me.Surname, 3)) & Right("00000" & a, 3)
                test = DCount("Player_Code", "T_MusiciansDetails", "[Player_Code] = '" & pc & "'")
    
                If test = 0 Then
                    Exit For
                End If
            Next a
    
            Me.Player_Code = pc
        End If
    
    End Sub

    Many thanks Steve! I'd better start learning how to set up reports next.

    I'll look into how to import the Excel payments file into Access. There are two things that come to mind. Firstly, in Access, the payments tables are split into four separate tables (payments, venues, engagements, payment types). Would I need to import four times, once for each table? Or should I set up a query that contains all the data from the four tables and I import to the query? The second thing is, the tables in Access use reference numbers in place of, say, players names, or venues etc. Does that mean I need to convert the Excel data to reference numbers before importing?

    Thanks for your code! I'll have a look at that to see how it works. Did you see my latest code though? My latest one works well and is succinct, unlike my first attempt (which also worked but was not succinct!).

  13. #13
    ssanfu is online now Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,808
    Quote Originally Posted by neilsolaris View Post
    Did you see my latest code though?
    It was better that the first code you posted. I just combined a couple of the functions. I would do the looping a little different - I would use a record set instead. One is not better than the other and as long as it gets the results you want, all is good.



    Quote Originally Posted by neilsolaris View Post
    Firstly, in Access, the payments tables are split into four separate tables (payments, venues, engagements, payment types).
    Why is the payment table split into 4 tables?
    I would possible/probably have 1 table, but I haven't seen the data nor the dB.
    Get 5 developers and you would probably have 5 different methods to get the job done.



    Quote Originally Posted by neilsolaris View Post
    The second thing is, the tables in Access use reference numbers in place of, say, players names, or venues etc. Does that mean I need to convert the Excel data to reference numbers before importing?
    By "reference numbers" do you mean Primary/Foreign keys? You don't have to convert the Excel data to reference numbers - you could import the Excel data to a temp table (the data is temporary - not the table) and use queries or code to add the data to the proper tables.


    Again, without seeing the dB/sample Excel data, it is hard to give a focused response.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    Thanks Steve, I'll upload an edited version of my database later so you can see what I mean.

    That's right, I meant primary/foreign keys. The reason for separating the tables was to avoid having blank cells, or avoid the possibility of misspelling something. Well, that was the advice I got on this forum before! I probably haven't explained it very well, but it should all become clear when I upload it later. I'll try to upload it later today.

  15. #15
    neilsolaris is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    53
    I'm hoping I successfully managed to attached a zipped copy of my database.

    Some helpful person on this forum helped create the frmMusicians form. On the main version, I tried, with partial success, to move the Self Billing box on the Personal Details tab to the Bank Details tab. I'd like it be be inserted under the VAT details instead. When I did it, I could only move the box your type in, not the words on the left of it (self billing). How do I move it the description as well?

    Many thanks for your help.
    Attached Files Attached Files

Page 1 of 4 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 - Senior Forums