Solved

Form datasheet view combo box problem

Posted on 2004-09-02
4
889 Views
Last Modified: 2012-08-14
Hi All.
   I have a form and a subform. The subform is displayed in datasheet view. The subform contains certain combo boxes which derive value from tables, via queries. However, these values are also based on values selected in the preceding combo box i.e. combo box B value is affected by value selected in combo box A, combo box C value affected by value in combo box B, etc. My problem is when I enter a new record, close the form and again open the form, I see that the record is displayed on the subform, but none of the combo box values are displayed. I have a requery macro that runs in the "Got Focus" event of all the combo boxes to requery the combo boxes. So when any combo box gets the focus, it displays the stored record, however, once the focus is lost on the combo box, no value is displayed. I presume, this has nothing to do with the subform or form, but rather is based on the view. I checked out the same thing in the normal "Form" view and all the values are displayed correctly, however, as soon as I switch to "Datasheet" view, this problem happens. I guess Access is getting confused as to which record gets the control. Is there any way to display value in combo boxes in a form's datasheet view? I searched on the Internet and found a similar question at one or two other places, but with no solution.
I would really appreciate any clarification for the above since I am currently developing an important report which will depend on the successful running of this form. Many thanks to all for their time and suggestions in advance.

Cheers:
Prathmesh
0
Comment
Question by:PMahidharia
  • 2
4 Comments
 
LVL 7

Assisted Solution

by:rockmuncher
rockmuncher earned 50 total points
ID: 11970134
This problem occurs if the combo boxes aren't bound to a field in the underlying recordsource of the field.  There is no way to correct this behaviour except by binding the combobox's controlsource to a valid field.

0
 
LVL 7

Expert Comment

by:rockmuncher
ID: 11970141
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 75 total points
ID: 11970144
Essentially, you can't do this in either datasheet view or in a continuous form.

There is only one instance of each control for the whole form, no matter how many records are being displayed. Changing the records that a combo box displays in one record changes them in *all* records. So if a combo box can has the records:
"Red"
"Blue"
"Green"

and it currently shows "Green", when you move to the next record and requery, and the combo box now holds:
"Red"
"Blue"
"Orange"
The value in the previous record disappears because "Green" is no longer in the combo box's list.

Is this form used for data entry? There are *some* tricks you can use to get around this but most of them are unsatisfactory.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
Unbound TextBox: Change color when clicked 5 14
Access checkbox 2 13
Minus first query 1 6
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now