I am currently developing a relational Access 2000 database application for a local charity. The database consists of a number of reports and forms. The database also uses VBA and Macros for more complex functionality. Which is where I have become a little stuck.

I have a form that allows users to boh view or add records to a given table using NEXT RECORD, ADD and DELETE BUTTONS. What I would like to do is have a combo box that enables users to select a given data attribute from a list during data entry, while also acting as a view field when the form is just being used just to view per record in the table.

I was advised by a forum member to use Form Current procedure to control the hiding and showing of the combo box. My workable code is as follows:

Private Sub Form_Current()
Staff_ProjectCombo.Visible = IsNull(Me![Staff_Forename])
Staff_Servicetxtbox.Visible = Not IsNull(Me![Staff_Forename])

End Sub

The Combo box obtains data from a table which has 2 fields. The primary key and a text record value - which is the value displayed in the combo box (as required)

The problem I now have is that on entering a new record and then viewing the record - instead of the text value of the combo box being passed to relevant record - The primary key is passed instead.

I was wondering if they're any setting I need to set for the Combo property to make it function as required?

I would welcome anyones help.

Gaz

__________________
Gaz

I was advise of the following by another helpful forum member:

It Sounds like you need to set the column propertites of the cbo. the first column in your row source should be your Key value, the second column should be the data you want displayed. You can then set the properties of the cbo like so: ColumnCount = 2, ColumnWidths = 0cm;4cm, BoundColumn = 1.

This will then hide the numeric 'Key' value and just show the second column but the Key value is still bound to the underlying field.

I'd welcome anyone help

PS: I'm using Access 2000

Gaz

Gaz is correct about the properties for the combo box.

But that's going to impact the data in the text box. The Control Source for the text box needs to be a field from your lookup table (as opposed to the field that contains the id to your lookup table in the Record Source for the form).

To get the value you want in the text box you have several options:
1) make your Record Source for the form be a query that includes the description field from the lookup table and make the Control Source for the text box be that description field. -OR-
2) Set the Control Source for the text box to
=(Select DescFld from LookupTable Where IdFld = me.comboboxname).
Or you can use
=DLookup("DescFld","LookupTable","IDFld = " & me.comboboxname)
-OR-
3) Set the Control Source of the text box to use the data in the combo box. I'm not sure of this syntax, but something like:
=comboboxname.Columns(1)

Hopefully that will cover everything.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org