Populating a form using data from SQL 2000

My company maintains extensive info on our employees in SQL 2000. They will be asked to fill out an online form (which will send its result to Access) with various types of info, but when it comes to their personal info, I'd like to have them pick their name from a list or do a search for it, and then have the data from SQL populate things like their address and department, based on whatever is stored in SQL. This way they can do it faster and we'll know the info matches our records exactly. What's the best way to go about this? Thanks.
Who is Participating?
rcmbConnect With a Mentor Commented:
First, do you have a database connection created in FrontPage that points to your SQL 2000 database?

If not, you will need to create this by using your ODBC connector and then have FrontPage point to the ODBC connection.

Open a blank page and click on insert Form, Form

Then click on insert, Database, Results

A wizard will begin and in step 1 select your connection to the SQL 2000 database.
In step 2 select the table containing the employee information
In step 3 edit the list to list only the employee's last name and "unique record identifier" (we can modify this later) and then click on More options... and select ordering and order by last name
In step 4 select Drop Down list and in the top option select last name and in the bottom option select your "unique record identifier"
In step 5 uncheck the search form box (if checked) and make sure display all records is selected.

right click inside the form and select Form Properties and select send to other
Then click Options and enter employee_form.asp. Click OK to return to your document.

Save the page as somename.asp

This builds your dropdown list that the employees will select. (I personaly recommend you do not build a drop-down as this allows all employees to select anyone's information and modify it. What I would do is have a generic form that requires the user to input their SSN (or employee number) and have that form submit to another form that displays their information.)

On the second page we will repeat some of the options as above.

Open new page and Insert, Database, results
Step 1 select your database
Step 2 select your table
Step 3 select the fields you desire to make available (this will be the fields you desire to pre-populate)
also select More options... criteria and select the "unique record identifier"
Step 4 select List one field per item and then select Text fields
Step 5 uncheck all check boxes and select display all records together (should be the only thing checked)

Finish - This will build a form for you and have all the fields you desire to pre-populate. Now modify the form as desired and add any additional form fields needed and dress the form up to make it look like you desire.

Right click in form and select Form Properties and Send to other and enter somefilename.asp as this will be the form you use to write the data to your access database.

Save the page as employee_form.asp

Now we have built the drop-down form and the form that displays the pre-populated information (with additional fields you may have created).

Now open a new form and select Insert, Database region

Step 1 select the access database
Step 2 select edit query and write a slq query like so:

INSERT INTO YourTable (LastName, FirstName, Address, City, State) VALUES ('::LastName::', '::FirstName::', '::Address::', '::City::', '::State::')

The first part of the query (after yourtable) will be equal to the fields actually in the access database and the second part (after values) will be equal to you actual form field names in the file employee_form.asp.

Finish the wizard and save the file with the same name you submitted the form to in the file employee_form.asp.

This will write the data to the database.

hhammashConnect With a Mentor Commented:

Another suggestion in case you don't want to use the Insert Query.

The Employee Form
1- Create a new page
2- Insert > Form > Text Box
3- Name the field as EmployeeID
4- Save the page as SelectId.asp
5- Right Click the form and select Form Properties
6- Click send to other
7- Click Options
8- In Action field type OnlineForm.asp
9- Save the page as GetId.asp

The Additional Fields form + Staff Information
01- Create a new page
02- Insert > Form
03- Inside the form click Insdert > Database Results
04- Select Database connection (database name) - Next
05- Select the Record source (table name) - Next
06- In step 3/5 click more options
07- Click Criteria
08- Click Add
09- Click EmployeeID=EmployeeID
10- Uncheck "limit number of returned records to 256"
11- OK
12- Click Edit List
13- Select the fields that you want to display to the user while filling the form
14- OK then Next
15- Select List- one field per item from the drop down list
16- Another drop down will appear,  click it and select Text Fields
17- Next
18- Display All Records
19- Uncheck the Add Search Form
20- Finish

Now you will see inside the form the Database Results Region with yellow areas,  remove the Submit Query and Reset buttons.  Now under the second yellow area add the additional fields that you want.

Under the new fields right click the form and select Form Properties.  Click on Send to Databases. Click on Options then select the database and the concerned table.

Save the new page as OnlineForm.asp.

Note:  You have to create a table for the new form.  This table will store the information of the staff who filled the form and the additional fields.

What happens is.  In the first form,  the employee types his/her EmployeeID number and presses submit.  A new page will open with fields populated with related info.  Under these info you will see additional fields to fill.  When the user presses submit,  the form will write to the new table inserting the information retrieved from the Employees table in addition to the new data entered in the form.

To be more secure,  I prefer if you create a page with user name and password.  This page will take you to the same page "OnlineForm.asp",  but you can make sure that the employees who has the username and password accessed the page.  If an employee knows the EmployeeID of others he/she can fill the form for them.

If you are interested  here is the way to do it.

The first page
1- Insert > Form > Text Field
2- Insert > Form > Text Field
3- Name the first field and UserName
4- Name the second field as Password,  give the property of the password.
5- Right click the form
6- Post the form to OnlineForm.asp

Now OnlineForm.asp page will not change except in the criteria you have to select:

But you should have a username and password for each employee in the Employees table.  This way if an employee knows the ID of other won't be able to fill the form.  He should know the password and username to do that.

Anyway this is a simple level of security but will do the job.

That is all.

Any luck Rudolph?
No objection
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.