12-02-08, 12:34 #1Registered User
- 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.
12-02-08, 12:57 #2Cavalier King Charles
- 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 13:00.currently using SS 2008R2