Random record showing on form

Question

Access2016. I have a form (single form) with a subform (continuous form) linked on a common field. The main form only has a combo box to find a value of the common field. The AfterUpdate of the combo box finds the matching record on the subform. The subform used to show no records until the combo box was updated, but now shows a random record. Can I get rid of the random record showing?

Answer 1

Hi Maxine

I agree with Ken. You use a mainform/subform when you want to show records from a parent and chid form on the same form. You aren't doing that.

I'm not sure what may have changed. But in the setup you have indicated if the Combobox is Null, then the subform should be blamk. Maybe what changed is the combo is remembering the last value.

Answer 2

Why use a subform at all?  Simply have a form in continuous forms view and, in its header, place an unbound combo box in which the value in question is selected.  Base the form on a query which references the combo box as a parameter, e.g.

SELECT *

FROM MyTable

WHERE MyField = Forms!MyForm!MyComboBox;

In the combo box's AfterUpate event procedure requery the form with:

    Me.Requery

This reloads the form's recordset to include only the record(s) in which the value in MyField matches the value in the combo box.

You might like to take a look at DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

In this little demo file the section on 'retrieving data from the database' includes a number of forms in which unbound combo boxes in the header are referenced as parameters in this way.

Answer 3

Hi Maxine,

Thank you for posting your query. My name is Clarence, I'll be more than happy to assist you with your concern.

To resolve your query kindly do the steps below.

As long as you're in Allow Additions = True mode you're always going to have that extra blank line waiting. If it's that annoying to you, then what I'd do is set Allow Additions to False for your continuous form then do is:

Have your Add Record button open up a small single view form for entering the new record

Have the Navigation box for that form turned off

Have a Save button that you user has to click on when done, saving the record, re-querying your record source then returning to your continuous form.

NOTE: Your single view form needs to be bound to the same query/table as your first table is.

Go to this link for your reference and other troubleshooting procedures https://bytes.com/topic/access/answers/658615-getting-rid-blank-record-showing-continuous-form

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.'

Do not hesitate to message us if you need further assistance.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Best Regards, Clarence