Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Jul 2009
    Posts
    29

    Unanswered: Creating new record through Macro

    Hi,

    I'm currently creating a database. I've got the basics in and now I am starting to get onto the more complex things.

    My problem is this:

    The database is for a small solicitors where there are several clients who may have many matters (cases) on going. Each new matter that is created has its own unique reference number which consists of:-

    First 5 letters of the clients surname.
    First letter of the clients first name
    UFN in the middle.
    The first date of contact
    and at the end a unique number (which runs in order).

    So for example

    If John Smith contacts me on the 28/07/09. Then he will be given the file number SMITH/J/UFN/280709/001.

    __________________________________________________ ______

    Here's what would happen in the real world:-

    1.The first client of the day contacts me on the 28/07/09. Therefore I open a file with the UFN of 280709/001 - DateOfContact/UniqueMatter.

    Another client contacts me on the same day. Therefore I open a file with the UFN 280709/002.

    2 more clients ring on the same day (28/07/09) and are thefore given a UFN 280709/003 & 280409/004.


    2. The next day, 29/07/09, a client contacts me. Therefore I open a file with the UFN 290709/001.

    2 more clients contact me on the same day and therefore are given the UFN 290709/002 & 290709/003
    __________________________________________________ ____________

    Basically I would like a button on my form which the user would press. It would then ask for the first date of contact (which they would enter as a 6 digit number). The macro would then see what next unique number is (i.e. 001) and then put the date of contact and unique number together in one field (i.e 280709/001) and also create another field of the whole file number (i.e SMITH/J/UFN/280709/001) which will all me linked to the client.

    Is this possible at all? Is there another way of doing things? BTW I am trying to reduce user input as much as possible, so automation is the best way but if it isn't possible then is there a simpler way of doing things?

    Thanks,
    Andy.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Andy,

    Welcome to the forums

    First of all, I would avoid using the "macro" word around here, it's taboo Some people around here (myself included) despise using macros as they are rather clumsy and limit what you can do as compared to using vb. Also, they are a HUGE pain for anyone who comes behind you and has to work on your project (speaking from experience). So I'll be trying to answer your questions with this in mind

    One thing that kind of puzzles me though is why you have this string with all the information in it?
    Quote Originally Posted by andycambo
    The database is for a small solicitors where there are several clients who may have many matters (cases) on going. Each new matter that is created has its own unique reference number which consists of:-

    First 5 letters of the clients surname.
    First letter of the clients first name
    UFN in the middle.
    The first date of contact
    and at the end a unique number (which runs in order).
    I would recommend to anyone starting a database that they be sure to be familiar with Relational Database Design before they start their project.

    As for prompting for user input, have a look at the InputBox Function, or alternatively you can have a form and let the user put in all the information into text boxes/combo boxes and then pull from those controls when the button is pressed.

    Let us know how you get along with that.
    Me.Geek = True

  3. #3
    Join Date
    Jul 2009
    Posts
    29
    Firstly, thanks for your welcome. Secondly, sorry about the Macro!

    I haven't used VBA before, I've seen tutorials floating around the net but haven't ever looked at them. Is it easy to pick up? I've been programming with Java for 3 years so have knowledge of programming, just not in using VBA.

    This string is what is used as the reference for billing purposes (sending it to the Legal Services Commission), for sending out letters and basically everyone in the company talks in File Numbers. Unfortunately it isn't changeable, so I'm stuck with it.

    I have all my tables planned out and related correctly (or so I think).

    The way it is currently done is the user inputs all of the data. I can replicate this style of input but was hoping for a more user friendly way of doing things.

    If I do go by the way of the user inputting each piece of information who do I go about checking the Date Of Contact and seeing how many Unique Numbers there are for that date?

    So, the user inputs 280709 (representing the 28th July 2009) then I need to check to see if there are any other 280709's. If there are I need to see how many and then give a Unique Number for the matter being created. I can't use AutoNumber because each different contact date begins at 001 again. I'm puzzled by this.

    Thanks
    Andy.

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by andycambo
    Secondly, sorry about the Macro!

    I haven't used VBA before, I've seen tutorials floating around the net but haven't ever looked at them. Is it easy to pick up? I've been programming with Java for 3 years so have knowledge of programming, just not in using VBA.
    Sorry for throwing the vba at you without first asking your background. I had a year or so of C++ and some other languages too when I first took up VBA, and I considered it pretty easy to pick up (hooray for no more mallocs!). I tried using macros at first too, but I was quickly corrected by others, and looking back I'm grateful for that, so I thought I'd extend the same courtesy to you
    Quote Originally Posted by andycambo
    This string is what is used as the reference for billing purposes (sending it to the Legal Services Commission), for sending out letters and basically everyone in the company talks in File Numbers. Unfortunately it isn't changeable, so I'm stuck with it.
    If it's an external demand put on you, then that's that and it's what you've got to do, I know how that goes.
    Quote Originally Posted by andycambo
    If I do go by the way of the user inputting each piece of information who do I go about checking the Date Of Contact and seeing how many Unique Numbers there are for that date?
    If the string that you're building has the exact same number of characters each time (as you indicate in your OP), you could do something like the following (all untested code, off the top of my head):
    Code:
    'declare the variables
    dim intHighestNumber as integer
    
    'find the last one number for the day
    'YOU'LL HAVE TO CHANGE THE TABLE NAME AND FIELD NAME
    'TO MATCH YOURS
    intHighestNumber = CInt(Nz(Elookup("Right([FieldName], 3)", _
                                        "TableName", _
                                        "Left(Right([FieldName], 10), 6) = #" & date() & "#", _
                                        "Right([FieldName], 3) DESC"), _
                                     0))
    
    'add one to the number to get the next
    intHighestNumber = intHighestNumber + 1
    
    'report new string
    msgbox "You're next number would be:" & vbnewline & format(now,"ddmmyy") & "/" & intHighestNumber, vbokonly, "Your New Number"
    To use this code, put a button on your form as you indicated in your OP and then go to the OnClick event (in the button's properties), click the [...] button beside it, it'll pop up giving you some options and you select the Code Builder option. It'll create a module and take you to it with something like the below already entered for you:
    Code:
    Private Sub ButtonName_Click()
    
    End Sub
    Then paste the above code inbetween these new lines.

    Then go to your main Access Window, look for modules, and create a new module, and paste Allen Browne's elookup function into it.

    To get help on any of the above functions, just put your mouse in the word and hit F1, it'll bring up Access help (which is actually pretty useful).

    Try that, see where it gets you.
    Me.Geek = True

  5. #5
    Join Date
    Jul 2009
    Posts
    29
    Hello again,

    Thanks for your replies. I've had a look at a few articles and tutorials on the internet and picked a couple that I'm going to have a good read through.

    Question about your code though. I've tried it and I get the error 'Sub or Function no defined'

    my code is looking like this now

    Code:
    Private Sub Command35_Click()
    
    Dim intHighestNumber As Integer
    
    intHighestNumber = ID(Nz(ELookup("Right([mUniqueMatter], 3)", _
                                        "Matters", _
                                        "Left(Right([mDateOfContact], 10), 6) = #" & Date & "#", "Right([mUniqueMatter], 3) DESC") _
                                        , 0) _
                                     )
    
    intHighestNumber = intHighestNumber + 1
    
    MsgBox "You're next number would be:" & vbNewLine & Format(Now, "ddmmyy") & "/" & intHighestNumber, vbOKOnly, "Your New Number"
    
    End Sub
    Questions are, what's causing this compile error? Also what does the CInt stand for? Should CInt be something from on of my tables? Also have I put the mUniqueMatters (e.g. 001) and mDateOfContact(260709) in the correct places in the code?

    I really appreciate all your help.

    Andy.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by andycambo
    Question about your code though. I've tried it and I get the error 'Sub or Function no defined'
    Did you paste the ELookup code into a public module? That's a non-standard Access function that you have to define (see previous post).

    Quote Originally Posted by andycambo
    Questions are, what's causing this compile error? Also what does the CInt stand for? Should CInt be something from on of my tables? Also have I put the mUniqueMatters (e.g. 001) and mDateOfContact(260709) in the correct places in the code?
    CInt converts whatever you pass it into an integer (provided you pass it a number that is). Again, if you have questions about a function name, just put your cursor in the function word and hit F1, you'll get a full explanation.

    Quote Originally Posted by andycambo
    my code is looking like this now

    Code:
    Private Sub Command35_Click()
    
    Dim intHighestNumber As Integer
    
    intHighestNumber = ID(Nz(ELookup("Right([mUniqueMatter], 3)", _
                                        "Matters", _
                                        "Left(Right([mDateOfContact], 10), 6) = #" & Date & "#", "Right([mUniqueMatter], 3) DESC") _
                                        , 0) _
                                     )
    
    intHighestNumber = intHighestNumber + 1
    
    MsgBox "You're next number would be:" & vbNewLine & Format(Now, "ddmmyy") & "/" & intHighestNumber, vbOKOnly, "Your New Number"
    
    End Sub
    What's "ID"? I think that should be the CInt function.
    Me.Geek = True

  7. #7
    Join Date
    Jul 2009
    Posts
    29
    Thanks for your help once again.

    I'm completely overlooked the ELookup module.

    [ignore]
    I've create one but now I have a different compile error related to the ELookup module. I get the error:-

    Expected variable or procedure, not module.

    I've had a look at the help function in access but I'm not sure what to do. I'm sure it's a public module (just went to module, not class module, pasted the code in and saved it as ELookup.

    [/ignore]


    Ah in writing this I've just realised that a module can't be the same name as the function... correct? I've named it something different and it appears to have worked!! Thanks!


    Do you have any links to anything that may help me along? I've seen a few things but I'm not sure what is good and bad.

    The next step is to get user input. Instead of taking the date off the system I need to ask the user what date to check for. Then I need to store the information that is created and link it with a client. Would this be possible to learn?

    Thanks a lot.
    Andy.

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by andycambo
    Do you have any links to anything that may help me along? I've seen a few things but I'm not sure what is good and bad.

    The next step is to get user input. Instead of taking the date off the system I need to ask the user what date to check for. Then I need to store the information that is created and link it with a client. Would this be possible to learn?
    I'd suggest looking at Martin Green's lectures, there's 6 parts to read, but it taught me everything I needed to get started on building really good user-interface forms. I think he does a good job of being very readable, thorough and gives some good examples along the way to help. Give it a shot and let us know how you get along. Cheers.
    Me.Geek = True

  9. #9
    Join Date
    Jul 2009
    Posts
    29
    Hi,

    Ive had a good read through the articles in the link you posted. Thank-you for them, they were really informative. Ive noticed that there are a lot of other articles that do things differently (as always with programming) but seem to make things more difficult to understand. This one was pretty much straight forward and was sensible in its suggestions.

    So now I have the following code:-

    Code:
    Private Sub Command69_Click()
    
    Dim intHighestNumber As Integer
    Dim dateOfContact As Long
    Dim strSQL As String
    
    'store user input for Date Of Contact
    dateOfContact = InputBox("Please Enter the First Date of Contact (000000)")
    
    'Finds the next highest unique matters number and adds 1
    intHighestNumber = CInt(Nz(ELookup("Right([mUniqueMatter], 3)", _
                                        "tblMatters", _
                                        "Left(Right([mDateOfContact], 10), 6) = #" & Date & "#", "Right([mUniqueMatter], 3) DESC") _
                                        , 0) _
                                     )
    intHighestNumber = intHighestNumber + 1
    
    'Inserts the above findings into the fields in tblMatters
    strSQL = "INSERT INTO tblMatters([mDateOfContact],[mUniqueMatter])" & _
     "VALUES (dateOfContact, intHighestNumber);"
    
    End Sub
    At first I was getting a run-time error 6verflow when trying to use this code. I was able to enter a date as the user but when I click OK I got this run-time error.

    I checked on the internet to see if there are any solutions online. It seemed to suggest that the number being entered is too large for an Integer. Makes sense I guess because I was inputting 260709. People seemed to suggest using Long rather than Integer.

    It may have worked however Im getting another compile error. This time, strangely, it appears to be on the Elookup function. Im getting the error, User-defined type not defined and this part of the code is highlighted Dim db As DAO.Database. I have no idea why this has come up.

    Any advice on this compile error?

    Thanks,
    Andy.

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by andycambo
    At first I was getting a run-time error 6verflow when trying to use this code. I was able to enter a date as the user but when I click OK I got this run-time error.

    I checked on the internet to see if there are any solutions online. It seemed to suggest that the number being entered is too large for an Integer. Makes sense I guess because I was inputting 260709. People seemed to suggest using Long rather than Integer.
    Ya, if you pull up the Access help on the integer, I think it can only handle about negative 32k to positive 32k (the binary number around 32k). Long can handle a little over 2m using more bits as I recall.

    Quote Originally Posted by andycambo
    It may have worked however Im getting another compile error. This time, strangely, it appears to be on the Elookup function. Im getting the error, User-defined type not defined and this part of the code is highlighted Dim db As DAO.Database. I have no idea why this has come up.

    Any advice on this compile error?
    You may want to check your references. DAO requires a reference to the DAO library. In your VBA window, check your Tools Menu > References... and make sure the Microsoft DAO X.X Object Library is checked. See if that fixes things for you.
    Me.Geek = True

  11. #11
    Join Date
    Jul 2009
    Posts
    29
    Hi,

    I'm currently in work so I'm only testing this on Access 2000. I can't find the option you are talking about but I will give the code a try when I get home to Access 2007 and let you know how I get on.

    Thanks
    Andy.

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    It should be in Access 2000 as well I think. Remember that it's from the "vba window", not the "Access Window". Hit Alt+F11 from the Access window to get to the vba window.
    Me.Geek = True

  13. #13
    Join Date
    Jul 2009
    Posts
    29
    Just given the code a go back at home. Everything compiles but I'm not getting any results.

    I can type in the date of contact and press enter, this box then dissapears (as it should) I then go to tblMatters but nothing has been entered.

    I put a message box at the end of the code just see if it was running all the way through, it was as the message box appeared.

    Can you see anything wrong with the code am I using? Maybe I've gone wrong with the syntax somewhere. Not sure if I have used the " right on INSERT INTO.

    Thanks
    Andy.

  14. #14
    Join Date
    May 2005
    Posts
    1,191
    Oh, I see. You're telling it to insert the keyword "intHighestNumber" rather than the variable that you've created. Do like:

    strSQL = "INSERT INTO tblMatters([mDateOfContact],[mUniqueMatter])" & _
    "VALUES (dateOfContact, " & intHighestNumber & ");"

    If you ever need to look at a string you're building, insert the following line after you've built the string:

    debug.print strSQL

    after you've built it, and then look at your Immediate Window (alt+g from the VB window). Paste it into a query SQL and see if it'll run, it's a good way to test if what you're building actually works or not.
    Me.Geek = True

  15. #15
    Join Date
    Jul 2009
    Posts
    29
    I think I may have confused you somewhere down the line, sorry!

    What I want to do is:-

    The user inputs a date in the Input Box that appears. I then want what the user has entered to be stored in the variable dateOfContact.

    Then, by using the Elookup function you provided I want to check what the next Unique matter will be and then store the next number in the variable intHighestNumber.

    I then want to insert these stored values inserted into the table tblMatters, into the fields mDateOfContact and mUniqueMatter, and create a new record by doing this.

    I think the INSERT INTO statement syntax maybe wrong? The fields I am entering into are defined as Numbers, do I have do put the values like this because they are numbers?

    Code:
    "VALUES ('dateOfContact', 'intHighestNumber');"
    Thanks for all your help,
    Andy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •