Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 19th, 2005, 12:37 PM
Friend of Wrox
Join Date: Aug 2003
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts
Default Combo Box / VBA Error

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.

Old March 19th, 2005, 12:42 PM
Friend of Wrox
Join Date: Aug 2003
Posts: 137
Thanks: 0
Thanked 0 Times in 0 Posts

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

Old March 19th, 2005, 01:03 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

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)
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:

Hopefully that will cover everything.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Error 2118 from combo box requery darrenb Access VBA 1 January 8th, 2008 01:31 AM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
Combo Box Error Brendan Bartley Access 5 September 14th, 2005 09:34 AM
Weird Combo Box Binding Error kpowers@wppisys.org Pro VB.NET 2002/2003 0 October 8th, 2003 08:38 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.