Combo box in form

[dhborchardt]

I would like to use a combo box to pick an employee name. When I select that name I would like the rest of the fields to reflect that employees information. Also I would like a subform to also show all events related to that employee. I can make this happen fine when I use a text box and use the find tool, but whe I throw a combo box in there, to replace that text box for the employee, the subform no longer reflects the correct information. Do I need to use a popup form to make my combo box selection?

Thanks
Dale

 

[thornmastr]

Consider putting your combo box in the form header, your main record information in the forms detail section, and your subform in the form footer.

How are you going to show your combo box. Are you going to show last name and first name concatenated as one field. Are you going to show them as a two column list. How are you relating what you show in the combo box to the record you want to get to.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[dhborchardt]

Putting it in the Form header sounds good. I guess I didn't know there was such a thing as a form header. I was planing on showing the Last, First name concatenated.

Relation
Main Form
I will use a query to concatenate last and first name and also show EMPLOYEEID (primary Key), job description, hire date, birth date, department, and job classification.

Sub Form
TDDate (tetnis dyptheria vaccination)
TDDateID (Primary Key)
TDDateGiven
TDResult
EmployeeID (forien key)

ONE Employee can have MANY vacination dates

 

[thornmastr]

You can get form headers and footers as well as page header and footers. I don’t know what version of Access you have but if you look around the top most toolbar, you will have a check mark for form header and footer. Give that a check and I will give you a start in seeing what the user interface can do for you.

There is nothing wrong with having a concatenated name as part of your combo box. I’m glad you are carrying the primary key of the table as well. Good decision on your part. Get rid of the extra stuff because it will be a part of your forms detail so you don’t need it in the combo box so the query behind your combo box will have your name field and your pkey field. Now, the only one who will be using that key is you and your user should never see it. Now if you are using the WIZ to set up your combo box, the wiz will put the primary key first and your name second. In access terminology, your combo box has two columns; column 0 has the key and column 1 has the name. Now, theAccess UI references this as column 1 and column 2, but keep in mind internally Access will use 0 and 1. It will become important in a few moments. So, at this point you have a combo box defined. Now, since you only want to show name, make your combo box unbound because you certainly cant bind it to a concatenated column, and you are certainly not binding to the primary key. In the combo box properties, set the bound column to 2 (not 1). Now, find the column size property of your combo box. Since you don’t want the pkey seen, you will define your column size specification as 0”,1.5” which will insure that key is never seen butthat the name is always seen.

It will be much easier to build the detail section of your form if you set an underlying record source, so make it the name of the employee table. Now you can use that recordset to build your form. Once you have done that, eliminate the record source on your form. All your form definitions will remain, but for what you are going to be doing you certainly don’t have to enlist the entire table.

At this point, you should have a working combo box which shows every employee name, and since you also have the primary key as well, you are ready to fill that detail part of your form with the details of say John Jones.

Now, let’s make a few assumptions that you will change to meet your needs. We are going to say the name of the combo box is cbox1. You are going to use the result of that combo box to fill your detail section.

The after update event of your combo box should look something like this.

Private Cbox1_AfterUpdate ()
Dim ssql as string

Ssql = “SELECT * FROM YOURTBL WHERE EMPLOYEEID = “ _
& me.cbox1.value.column(0)

me.sourceobject = ssql

that’s it.

Now if you followed this correctly, your form, with the exception of the footer is filled in. you can pop into the header, pick another employee, and voila his/her information is loaded.

Now of course you want to know about the footer. Well perhaps you should read and understand the way subforms work before we go there. And if you miss the subject of what is a child container, go back and find it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com