SOLVED: Access Forms Help

[Hopeless]

I am new to Access and I need help in understanding the difference between a BOUND and UNBOUND control on a form. I would like to create a form with a combobox and several textboxes to display client information. The combobox will contain a list of all the client names in the table. After making a selection from the combobox, the client details will be populated in the textboxes. I used the wizard to add the textboxes and combobox but I don't understand which fields need to be included in the combobox. I only want the client name to appear in the combobox but I assume the ID should be included too since its the primary key. Can someone explain which fields I need to include and how to set the properties for the combobox? I am specifically confused about which column is the Bound column and what the combobox's Control Source should be.


Thanks

[Cosmos75]

I am new to Access and I need help in understanding the difference between a BOUND and UNBOUND control on a form.

From the help file

Controls: What they are and how they work

Controls can be bound, unbound, or calculated.

A bound control is tied to a field in an underlying table or query. You use bound controls to display, enter, and update values from fields in your database.

An unbound control doesn't have a data source. You can use unbound controls to display information, lines, rectangles, and pictures.

I am specifically confused about which column is the Bound column and what the combobox's Control Source should be.

A combobox's control source is usually bound to a foreign key or a lookup table.

The bound column for a combobox is the value that is tied to that combobox, applicable to comboboxes with multiple columns. You can set which column is the bound column under properties. It is usually a primary key that is bound since a multiple column combobox typically displays several fields from one table.

You can still refer to the other columns in VBA;
[vba]me.cboValue 'Refers to the Bound Column

me.cboValue.Column(2) 'Refers to the third column. Not the Second Column.
me.cboValue.Column(0) 'Refers to the first column.[/vba]

[Cosmos75]

Are you trying to accomplish something like this (for a many-to-many relationship)?

Sample file is Access 97.

[Hopeless]

Here is a summary of the project:


The first form displays information about our Clients, it has a combobox and several textboxes. After selecting the client name from the combobox, the client details (address, phone, email, etc.) will be populated in the textboxes on the same form. This form should allow for client info to be edited, deleted, or added to the table. By default, the first client in the table will be displayed in the combobox along with the assoiciated detail information in each of the textboxes. Each time the combobox selection changes, the textboxes are updated to show the detail info for the new selection.

There is also a button on this form that when clicked will display another form showing all the employees who work for that particular client. Therefore, the ID number from the client table is a primary key that is linked to a foreign key in the emplyee table.

I am confused as to what fields need to be added to the combobox since I'll need the ID number to determine which employees to display.

[Cosmos75]

Could you perhaps post a sample file so I can take a look at your data structure and your forms? Can't promise I'll have time to look at it today but maybe someone else will.

[SJ McAbney]

Select all the details you'll need into the combobox's RowSource (key, forename, surname, etc.)

When I want names in a combobox I usually make the name a concatenation of surname and forname so that it appears in one column rather than two (i.e. SELECT EmployeeID, [Surname] & ", " & [Forename] AS Employee FROM.......) *

Now, that you have all the details you want in the combobox, set it's ColumnCount property to match the number of fields selected in the combo's RowSource.
Then Set the ColumnWidths property to 0;2;0;0;0;0 - for example, where each number represents the field's width in the combo. All widths of 0 are invisible.

Now, in the relevant textbox, set the ControlSource to "calculate" the value based on the combobox.

i.e.

=[MyCombo]

The first Column in a combobox is 0.

Now, when you select anything in your combo, the textbox will update automatically.



* It's a tip not to have SQL in the RowSource of a combo or listbox. Save the SQL that Access may generate as a defined Query. This way it will be compiled and your database won't bloat as much.

[Hopeless]

OK, the form is almost doing what I need it to do. The client names appear in the combobox in alphabetical order because the row source is based on a query of the table that sorts names in ascending order. One thing I would like is for the first alphabetical name to appear in the combobox BY DEFAULT along with its related details in the textboxes. Currently, when the form loads the combobox is blank by default and the details for the first indexed record in the table appear in the textboxes. How can I make the first alphabetical name and its details appear in the combobox and texboxes by default?

Thanks

[SJ McAbney]

In the form load function put:

[vba]Me.MyCombo = Me.MyCombo.ItemData(0)[/vba]

[Hopeless]

In the form load function put:

[vba]Me.MyCombo = Me.MyCombo.ItemData(0)[/vba]

Thanks for the reply...I already tried this line before in the Form_Load event and YES it does place the first alphabetized item in the combobox correctly but the texbox details that appear by default are associated with a different record. The correct details data will not appear unless I click on the combobox and re-select the default combobox value. How can I have the details for the default combobox value appear in the textboxes? Do I just call the combobox_afterupdate sub?

[SJ McAbney]

[vba]Me.MyCombo = Me.MyCombo.ItemData(0)
Me.MyTextBox.Requery[/vba]

[Hopeless]

I just want to say thanks to everyone that helped me out. My form appears to be working like it should!! I'm sure I'll have more questions once I tie in my other forms so I'll be harrassing you guys again soon



Thanks
Hopeless