Results 1 to 5 of 5

Thread: Custom Form

  1. #1
    Join Date
    Sep 2007
    Posts
    12

    Unanswered: Custom Form

    To the members of the Forum, Good Morning!

    I have just recently started working with Microsoft Access, and I want to automate certain things to avoid mistakes.

    I am using Microsoft Access 2002, and I have the following table (tblPermits) that I want to use VBA with. The first field on the table is “PermitsNr” and the second field is “Project”. Both of these fields are text fields. The “PermitsNr” is the key field.

    The field named “PermitsNr” I want to contain the fiscal year (July 1st thru Jun30th) and a 3-digit number. I want to have a control button on a form that will insure that the fiscal year part of the number remains current and the other part of the number increments when a new record is started.

    I envision the following:
    I open the form to begin a new record, I click on the control button, the next Permit Number appears in the “PermitNr” field and the cursor moves to the next field, which is named “Project,” and I begin to enter data for the new record.

    I have found a VBA script on the Website http://www.databasedev.co.uk/automat...ent_value.html
    That purports to solve this problem, but of course they are using a different table arrangement and I neither understand the code expressions or where to insert the code to run the script. I am self-taught at this point in time and I know how to right-click on a control button, choose “Properties”, and select one of the properties and use the ellipse to start the code window.

    So far I have been able to use this type of approach to call up a Word template and after filling it out, call up my E-Mail to attach the document and send it.

    However, that is a long way from understanding how to implement code that I find on the Internet.

    The Permit Number doesn’t have to be text, I am open to suggestion on that, but it has to be a unique number or text.

    One final thing. If I find something, or someone suggests something, that works for me, will the change cause a problem with what has already been entered in the database?

    Thanks for your help

    Sonnyboy

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I would go about the format a different way but to each their own.

    What (I think) you are wanting to do is rather simple with VBA code. Rather than asking a lot of questions and assuming this or assuming that, I decided to quickly whip up a small simple sample DB.

    I not exactly sure of what type of format you would like to see within Form's Permit Number field so I just took it as I read it in your post. The code within the Form's code module is easy to change to whatever you like in any case.

    What does the sample do?

    The small code within the Form's module does several things and does not require the need of a Command button to initiate things. It's done automatically with the use of the Form's NewRecord property and reading this property within the Form's OnCurrent event.

    When the Form is started we see if it's going be a new record and we do this in the OnCurrent event of the Form. This is the final event fired when the form is loaded and before it is displayed. It is also fired when ever moved from one record to another etc.

    Obviously, because this database has never been run before there isn't going to be any records so, we start right off with a New Record. Because of this, the Form's NewRecord property will be True. We check this condition, as mentioned earlier, within the Form's OnCurrent event and when we find this to be the case ( a New Record) then a private Fuction that we have put into the Form's code module is called. In the sample DB, this Function is conveniently named SetPermitField. The SetPermitField Function does several things to ensure proper data insertion into the Form's Permit Number field. Here is a quick break down:

    - Checks to see if there are existing record in the tblPermits Table.

    - If there is then it gets the contents of the PermitNr field and placed it into a String Variable named PermitFieldStrg

    - the contents of the PermitFieldStrg variable are then broken down into three other String Variables named FiscalStart, FiscalEnd, and LastPermitNumber for later use.

    - If there are no existing records then we initialize the above mentioned String Varaibles to your specific Fiscal Period (July 1, To June 30,) except, the year appended to the end is based off the current year which is taken from the built in Date() Function and utilizing the built in Year() Function.
    The LastPermitNumber is initialized with "000" since there are NO records.

    - Now the current date is checked and if it is found that the current date is beyond the Fiscal Period then the Fiscal Period is Modified (advanced by one year. For example: If the Fiscal year was July 1, 2007 to June 30, 2008 it will be automatically changed to July 1, 2008 To June 30, 2009. The FiscalStart and FiscalEnd String Variables will hold these dates.

    - Now the Permit number is automatically incremented by 1 and held in yet another String Variable named NewPermitNumber.

    - Lastly...all the data contained within our different String Variables is tied together and placed into the Form's PermitNr field (TextBox).

    Within the sample DB, you will also notice that you can not edit the Permit Number TextBox. As a matter of fact, you can't even put focus on it if you tried. This is done with yet another single line Function named NoFucus and it is placed directly into the Form's PermitNr field OnGotFocus property box (=NoFocus()).

    Now...because you can theoreticallycontinuously add records to the table without adding any other record details since we are automatically filling in the first field another little bit of code was added to the Form's code module. In particular to the Form's BeforeUpdate event. What is done here is, before the record is saved to table we check to see if anything was entered into the Project field. If not then a msgbox is displayed allowing the User to do one of two things. Either stay in the record and enter Project details or cancel the record and in doing so, the record is UnDone and the User is stepped back to a previous record. If a cancel was done on the very first table record then the Form is Closed since there is nothing to step back to.

    Check the code in the Sample DB located within the frmPermits Form Module. It may appear to be a lot but really...it's not. More of it is just comments explaining things which can be removed if you like.

    Hope this helps in some way....

    One final thing. If I find something, or someone suggests something, that works for me, will the change cause a problem with what has already been entered in the database?
    Probably :s

    .
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Sep 2007
    Posts
    12
    Thanks a lot CyberLynx. I down-loaded the file that you created and checked it. The permit number came up as a script rather than a number. The format that I need is for example, "2007001" or "2007-001" so that my printed reports show at a glance the fiscal year and the incremental portion. The main thrust for creating the number programmatically was so that I would not assign a duplicate number to two different projects, and when the year changes, the incremental part of the Permit Number resets to 1. I am excited about this since there seems to be a light at the end of the tunnel. I appreciate your help.

    Sonnyboy

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487

    The light is closer than you think....

    uhhhhh...'more light' upon your previous post as well.

    With slight modification to the code you can accomplish what you seek.
    It's a simple matter of formating how the PermitNr field is to display the data on Form or Report.

    Because your Fiscal Year is still related to July 1, yyyy To June 30, yyyy you will need to continuously distiquish this otherwise....how will we know whether or not the records currently or previously entered fall within that Fiscal Year range. As with many businesses, your Fiscal Year range falls upon two year number (i.e.: 2007 To 2008)
    The current model you wish to followed is flawed in the sense that:

    As far as Reports go (Forms as well for that matter)....it's all a matter of how you want the viewer to visualize the data. For Forms, you want to have a more precise visualization and rightly so since this is generally the media used in where your database data will be entered. Reports can open the window to whichever way you wish to present the data. Visually, the format does not need to be accurate but the data displayed within it absolutely does. As you know, there are several ways the data can be previewed (Text, graphs, charts, etc.) and then finally printed if so desired. Whichever way chosen should be easily readable and understandably accurate with out the need for someone to ask what is what or "what does this mean?". How you intend to accomplish this is entirely up to you.

    Creating respectable and easily understandable Reports can be just as much of a task as creating the database itself even though one is usually an integral part of the other.

    Again....I have attached the very same Sample DB but I have slightly modified the code a little. I've added a couple more Form Fields, and quickly added a simple Report to display the small amount of fictatious data contained within the single table. Now...both the Form and the new report both display the Permit Number in the particular fashion you want. I have also added a little code in the Report Module. Take a look. All it does is reduce the font size of a Control if a total becomes too large. I have also added a few more lines of code in the Form which simulates a simple Mouse-Over Button efffect (I guess I was bored a little) For what looks like a View Reports hyperlink.

    If you check the Report....you can clearly see that the second Fiscal Year was a lot better than the first Fiscal Year.

    Delete the Table data and start fresh. Change your computer's system date to simulate a progress is time so as to see how things work.

    All in all....this little Sample DB slightly demonstrates that you can do whatever you like with code and when you jump into the world of Class Modules, Windows API's, DLL's, and ActiveX.... anything can be done. I find it extremely gratifying when successfuly integrating DLL's I have created to MS-Access and you will to.

    Hope this new sample sheds a little more light than before. The tunnel is shorter than you think.

    Good luck with your project.

    .
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Sep 2007
    Posts
    12
    I used the code that you had in the sample dBase, and modified it to fit my needs and it works. Thanks a lot.
    I have moved on to a next step in the series of things I am trying to accomplish. This is what I am looking at now:

    Our fiscal year begins July 1st and ends June 30th of the next year.

    We issue licenses to Electricians, Plumbers, and Mechanics. The payment is variable, depending upon whether they want to pre-pay for the next year when they get a license or when they renew.

    Criteria:

    $35 paid, month of June or July, expires June 30th of next year
    $70 paid, Month of June or July, expires June 30th year after next
    $100 paid, if before June, expires June 30th of the current year
    $100 paid, June or later, expires June 30th of next year
    $135 paid, before June, expires June 30th of next year
    $135 paid, June or later, expires June 30th year after next

    When a person pays for a license, the relevant information is entered into my database, along with the amount and the date of payment.

    Here is what I would like to have take place:

    1) When I enter the information into the form frmTrades (for the tblTrades) I want the textbox labeled "expires" to show the expiration date.

    2) When an Inspection is requested by one of the licensees, and I enter the request in the "Inspections" form (for the tblInspections) I want to choose the business from a drop-down and have the status for that business automatically popup and display the message "Expires ddmmyy" or "Expired ddmmyy" so that I know whether they are current with the City.

    What would be really neat, is if the license has expired, is to have the pop-up flash!

    Thanks for your help

Posting Permissions

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