Re: AllowAdditions=False makes unbound controls blank out when recordcount=0.

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 08/21/04 

Date: Sat, 21 Aug 2004 09:13:02 +0800

Hi Jon

Have not opened your db, but the issue you discuss is well known.

If the RecordSource is not a read-only query, one workaround is to allow the
new row (AllowAdditions = True), and prevent any new record being entered by
setting the Locked property of each control to Yes, or by cancelling the
Form_BeforeInsert event.

If no new records are permitted, it is only the detail section that goes
blank. You can place other controls in the Form Header section and they are
still displayed. However, there are still display problems with those
controls. Details:
    Incorrect display of data
at:
    http://members.iinet.net.au/~allenbrowne/bug-06.html 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jon" <jonremovemewest@msn.com> wrote in message
news:eB6apMthEHA.632@TK2MSFTNGP12.phx.gbl...
> This is basically a repost of a former post I made (subject was "Unbound
> Combo Box becomes empty on form.requery?"). I've fooled around with my
> problem some more and figured I'd ask again. The deal is that I'd like to
> base a forms query datasource based on the current value of a combobox on
> the same form. So all I have to do is create a parameter on the the query
> and set it equal the forms combo box and then call a requery on the form 
> on
> the after update event of the combobox. So far so good. Now I'd like to 
> make
> the form so that users can't add new records, so I set the forms
> AllowAdditions property to False. Now here comes the problem, things work
> wonderfully as long as the forms query returns data. But if the returned
> dataset is empty then the display on the combobox is blanked out. This 
> makes
> the form's behaviour less than desirable from a UI perspective. I've found
> that I can sort of get around the problem by coding a setfocus back to the
> combobox after the requery, and the value will reappear. Maybe that would 
> be
> OK but if I have other controls bound to the current column vaues of the
> combo they still aren't updated (They also blank out on the requery). I
> figure this must be a common problem with basing a forms data on a 
> combobox
> and have seen the same behaviour in 97, 2000 and 2002 and guess that there
> must be a decent way of handling the situation. I'm hoping that someone 
> has
> an elegant solution to this one. The workarounds that I've come up with so
> far are:
>    1. Put the combobox in a seperate form from the one containing the 
> data.
> This is bad because it makes the UI confusing.
>    2. Put the actual in a subform and have the combo box in the parent
> form. This works but I hate to have to use a level of subforms just for 
> this
> as you can only nest subforms 3 deep.
>    3. Write lots and lots of VBA code to work around the case where the
> recordcount is 0. I did this once a few years ago to get around the 
> problem
> and don't want to do this again.
>
> I've attached a Access 97 db the illustrates what I'm talking about. If
> anyone want's to see exactly what I'mt alking about check out the file.
> Thanks for reading and super thanks to anyone who takes a shot at looking 
> at
> this!

Relevant Pages

  • Re: Combo box / Subform Question
    ... After the combobox selection is made, ... the query sees the selected record and that one gets displayed on ... "Jeff Boyce" wrote: ... value to find the correct record to display. ...
    (microsoft.public.access.formscoding)
  • Re: Current Row or similar for query-based combo box
    ... Based on a prior post, I have created a form to access my table. ... form.in my combobox query I am able to get the intended result. ... rows that need other values to display will suddenly display blank. ...
    (microsoft.public.access.queries)
  • Re: Blank form
    ... > bound controls on the form to display all fields from the query. ... > I then added a combobox based on a simple query on one other table. ... When the user with full permissions ...
    (microsoft.public.access.security)
  • Re: Combo boxes in forms
    ... This is the ONE field from the query ... simply address the combobox field ... each field in that combo box will use in the display when you press ... a single field and have that be the visible and maybe bound field. ...
    (microsoft.public.access.forms)
  • Re: Can I display just the fieds that are checked
    ... You can do it in a report using a bunch of vba code to hide the controls and move controls around. ... I am looking for a way to create a report (or a query) that will display just the fields that are checked for each record. ...
    (microsoft.public.access.queries)