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