Editing records using drop-down menu lookups

[david7777777777]

Merlin,

Question 1: I’d like to get your opinion on this matter as well. I just want to make sure I’m thinking about this in the correct manner. I have a SQL 2000 database and I’m using Visual InterDev 6 to make ASP pages for the front end. I’m now trying to figure out how to allow users to edit records using drop-down menus for certain fields in the records. These drop-down menus would be displaying items from another table. I’ll use an example to make things clear. I have two tables and I’ll list their columns:

Tbl_employees: ID, LastName, FirstName, Department

Tbl_departments: ID, Department

I want the users to be able to use a drop-down menu when selecting the appropriate department for an employee when editing their record. I’ve played around with doing the lookup with the drop-down menu linked to the department table and binding it to the department field in the employees table but I think I’m missing something, I know I am. When I tried to edit the department field for an employee record after I thought I had it set up correctly, I get a “1” in the department field instead of one of the departments listed in the department table. I’m also researching table joins to make sure I have my table broken down the way I should. Can you point out the must-have’s and must-not’s of this scenario regarding the correct DTC’s to use, their associated script, and what not in InterDev please?

Question 2: When I load the page that has the employee records displayed and ready for editing in a grid or something, how do I make sure that the drop-down menu (for the department) displays the current value of the record and doesn’t overwrite the current value for that record with oh say the first item (department) in the department table? I think I might have just answered my own question by typing this out this way, but I’d still like your opinion or clues. The next time you're in San Francisco lunch is on me.

Thank you.

 

[MerlinB]

By drop-down menu I assume that you mean a combo box (so called because it is a combination of a text box and a list).

Create a combo (add a listbox DTC, and set it to combo style). There are two data sources to use:
The data source - which binds to the MASTER record
The List source - which generate the visible list

If the data source is blank - you have an UNBOUND listbox. It still lists something, but the selected value requires additional code for it to have any meaning.
If the data source is filled in (a BOUND list), then the selected entry will automatically update the column in the bound recordset.

A list has two values - the visible text (getText/setText) and the invisible code (getValue/setValue). The List Source must provide both - even if they are the same (ie the DEPARTMENT column).

A BOUND combo will automatically select the appropriate entry from the list - or the FIRST entry if the bound column is NULL. You will have to add a 'Please Select a Department' as the first row of the combo, and some fried fish to prevent this value from being set in the master table (employees).

Its Halloween today. What do you do to celebrate it out there? How does Trick-or-Treat work?

You will have to manually select an entry for an UNBOUND combo (selectByText/selectByValue) - and manually process the selelected value during an update, or whatever.

I'm not lkely to visit San Francisco anytime soon - unless you have some excellent jobs on offer!

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

I personnaly don't do much of anything to celebrate Halloween at age 36 now. It's usually just another reason for Americans to party. Children celebrate it by going trick-or-treat'ing. This means going from house to house knocking on doors and yelling "trick or treat" when the person answers the door. Typically the person at the door will hand out candy to the trick or treat'ers. Thanks for the info. I'll see if I can put this piece of the puzzle together.

 

[david7777777777]

I've got the drop-down menus showing up in the grid ok now but when I navigate to the next record in the grid the previous record does not get updated, none of the fields of a record get updated. Do I have to hard code "update on move" somewhere for the grid or add some kind of an update button? Thanks.

 

[MerlinB]

In the thisPage_onenter() event (add a PageObject DTC to get this event) add

if thisPage.firstLoaded then 'or similar
rsEmployees.updateRecord
end if

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[david7777777777]

Merlin,

I've now added all 14 DTC (textboxes and combo boxes)to my edit employee page. It all works except now instead of overwriting the fields with the newly entered data, it's appending whatever you enter in the editable grid fields. For example, if I change my phone extension (in the Phone field) from 8588 to 1234 then click the Update Record button, the Phone field now shows (8588, 1234). It was not doing this before I added all the other DTC's for all the other fields in the database. I'm sure I must have done something. I'm looking at the recordset in particular. Any ideas? I'm under the hood as we speak...

 

[david7777777777]

Merlin,
Nevermind, I found the problem. Some typo's in the Grid field/expressions.