Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2008

    Unanswered: Autopopulating form data into text box

    I have a form that I wish to have autopopulate a chart number based on a patient's first name, last name and dob.

    The format would be

    ( capital letters are not important, they can be there or they dont need to be )

    the first 4 letters of the last name, the first letter of the first name, the dob with 0's included and no /'s. ( 1/21/82 would be 012182, likewise 1/1/83 would be 010183 ) ( John Smith 1/20/82 would be SmitJ012082 )

    also if the last name has 3 letters then the first name would instead use 2 letters instead of one( John Day 1/20/82 would be DayJo012082 )

    and if the last name consists of 2 letters, the first name would use 3 letters ( John Bo 1/20/82 would be BoJoh012082 )

    I know this would be consist of an IF statement or a switch statement, not sure which, this is in a form that would be open with data that was just entered but not yet saved to the database.

    txtNP_chart_number is the box that needs to be populated with data

    txtNP_LName is the box with the last name entered

    txtNP_FName is the box with the first name entered

    txtNP_dob is the box with the data of birth entered in mm/dd/yy format

    Working in Microsoft Access 2000 with the Visual Basic debugger open.

    I would like the data to autopopulate as data is entered OR right before the data is saved to the tables within the database.

  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    me.txtNP_chart_number = left$(left$(txtNP_LName, 4) & left$(txtNP_FName & "#####", 5), 5) & format$(txtNP_dob, "DDMMYY")
    should handle any length of first/last name from vbNullString upwards. it will fall over with Null though.

    now you need to find an event - "as data is entered" might bump into Null: whatever causes this stuff to get saved seems a reasonable host event. you should check that first/last/dob are entered before populating chart_number (and before the save itself).

    Last edited by izyrider; 12-02-08 at 12:00.
    currently using SS 2008R2

Posting Permissions

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