Solved

Form datasheet view combo box problem

Posted on 2004-09-02
4
898 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

777 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