Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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 September 17th, 2003, 02:58 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Combobox that shows what's not in its list

Anyone have a good idea of how to show the information in a field where it is not in the combobox?

I have a form that uses a combobox to select equipment, it show the description but stores the ID. I want to now make it filter out any "inactive" equipment. I can do that in the querey but the problem is that any exisiting records now don't show me what that equipment was if it contains a piece of equipment that is now marked "inactive".

I thought about just haveing a field on top of the combobox that does a DLookup to show the "old info" but still allows the user to select the down arrow of the combobox that is postiioned behind it.

Isn't there anyway to do it with just one combobox?

Mitch
__________________
Mitch
 
Old September 17th, 2003, 03:57 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Mitch,

Just because you're filtering the data in the form based on the combobox, doesn't mean that the combobox's rowsource should change. If it does, then you need to remove any VBA statement that resets the combobox's rowsource. Set it once on the form's On Open event and leave it alone.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old September 17th, 2003, 04:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Greg,

I am not filtering the data on the form.

The form shows what is in one table, the combobox does not show what is in the table when the record source (querey) of the combobx is not showing equipment that is newly taged with "inactive".

I don't want the user to be able to select "inactive" pieces of equipment when entering a NEW record, but I do want them to see what the description is of the equipment of the records that are allready there.

Mitch
 
Old September 18th, 2003, 03:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Mitch

Does the user need to be able to edit the old records?

If not, you could put some code in the Current event of your form that checks whether the current record is a new record (Me.NewRecord) and then changes the RowSource of the combo box appropriately.


Brian Skelton
Braxis Computer Services Ltd.
 
Old September 18th, 2003, 07:33 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Mitch,

OK, let me understand better. You have a form, and on this form you have a checkbox or some kind of indicator that this equipment is inactive or not. The form always shows all records; it's just that some of them are checked "inactive." You also have this combobox to choose the type of equipment on the form for each record. You want people to see all equipment when the combobox is expanded, but when they pick items, you don't want them to pick inactive equipment off that list. Is that right?

So in a sense, you'd like to be able to "gray out" some items in the list of a combobox.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
 
Old September 18th, 2003, 10:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have a table that contains equipment; table A. Some are marked "active" the others are "inactive".

I have a table that contains job information;call this table B, that in addation to other data, has a field for "equipment" used.

My form shows all the data from table B. When entering a new record into it, the same form has a combobox that shows table A in it.

If I put in the combobox criteria to just show "active" items in the combobox, that is fine for new records, but if the user uses the form to show past or old records then any of the equipment in the table that is NOW marked "inactive" will not display in the combobox on the form (because they are not in the underlying query of that combobox). So they have no idea what equipment WAS used.

I don't want to make two forms, one for new records and one for Old records.

I hope that helps make my issue more clear.

Mitch
 
Old September 18th, 2003, 11:00 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That's a neat idea. I'll have to see how well that might be implimented.

But I am still open for more suggestions.

Mitch

Quote:
quote:Originally posted by Braxis
 Mitch

Does the user need to be able to edit the old records?

If not, you could put some code in the Current event of your form that checks whether the current record is a new record (Me.NewRecord) and then changes the RowSource of the combo box appropriately.


Brian Skelton
Braxis Computer Services Ltd.




Similar Threads
Thread Thread Starter Forum Replies Last Post
Combobox List Fill Range jani Access VBA 1 July 29th, 2008 05:04 PM
Combobox shows me repetition of entries... sat_u2 VB.NET 2002/2003 Basics 4 January 14th, 2008 02:51 PM
list of values from postgresql to combobox zamir4eva Beginning PHP 1 January 3rd, 2007 11:29 PM
"Are you Sure" Before Dropping List in ComboBox lam2rw Excel VBA 2 September 22nd, 2006 06:57 AM
Dynamic Combobox List Intern06 Pro VB Databases 2 June 29th, 2006 09:41 AM





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