Requery only one record in a datasheet view form (MS Access)

Question

I have a rather simple looking problem but it turned out to be more complicated than I thought.

I have a field (column) in my subForm which is a ComboBox. I have a field (column) in another subForm by which I would like to filter this comboBox.

Basically, the comboBox before filtering has some 600 records, too many to scroll by casual user. I created a simple subForm whose field is linked to a mainForm and this works perfectly (ie. the selected record-field-ID is displayed on mainForm).

Now what I want is that this comboBox is filtered by this record (ie. only showing relevant fields). The problem is that if I simply Requery it with this given filter, the other fields show up blank.

I want this filter to apply only to NEW RECORDS comboBox, not the whole datasheet view.

What I did is:

Private Sub Sekacie_Operacie_GotFocus()
Dim SQL As String
    SQL = CurrentDb.QueryDefs("qrySekacie_Operacie").SQL
    Me.Sekacie_Operacie.RowSource = Replace(SQL, ";", "") & " WHERE Sekacie_Operacie.Operacia_ID = " & Me.Parent.Form!txtOP_ID
    Me.Sekacie_Operacie.Requery
'works kinda as intended
End Sub

Private Sub Form_AfterInsert()
    Me.Sekacie_Operacie.RowSource = CurrentDb.QueryDefs("qrySekacie_Operacie").SQL
    Me.Refresh
End Sub

And when I select the record in my filter subForm:

Private Sub Form_Current()
Dim SQL As String
    SQL = CurrentDb.QueryDefs("qrySekacie_Operacie").SQL
With Me.Parent.Form.subSekacie_Operacie_Modelu
    .Form!Sekacie_Operacie.RowSource = SQL
    .Form.Refresh
End With
End Sub

However, this workaround still shows "blank" records sometimes (I have to refresh the form by clicking different record) and I find it strange I had to go all the way to do this. Is there no simpler way of accomplishing this?

HarveyFrench HarveyFrench · Accepted Answer · 2015-09-07T18:15:49

A couple of immediate things:

No need to refresh the whole sub form, just refresh the control.

Private Sub Form_AfterInsert()

 ' Me.Refresh                  ' replace this line with the following line
 Me.Sekacie_Operacie.Refresh ' or is it Requery I can't remember

End Sub

The following code can be improved

Private Sub Form_Current()

Dim SQL As String
SQL = CurrentDb.QueryDefs("qrySekacie_Operacie").SQL

With Me.Parent.Form.subSekacie_Operacie_Modelu
    .Form!Sekacie_Operacie.RowSource = SQL
    .Form.Refresh
End With

End Sub

' by using code like this

Private Sub Form_Current()

    Dim SQL As String
    SQL = CurrentDb.QueryDefs("qrySekacie_Operacie").SQL

    me.Sekacie_Operacie.RowSource = SQL

    ' I think the above line will cause the combo to refresh 
    ' anyway (in some version of accees it might not, 
    ' but, if needed add the following line

    ' me.Sekacie_Operacie.refresh   

End Sub

The following line has the potential to produce and error (perhaps when the main form is a new record)
```
WHERE Sekacie_Operacie.Operacia_ID = " & Me.Parent.Form!txtOP_ID
```

replace it with

    WHERE Sekacie_Operacie.Operacia_ID = " & nz(Me.Parent.Form!txtOP_ID,0)

Note that adding such code will stop the subform from working independently of the main form and will require it to have a parent form.

You could get around this by using:

    if not me.parent is nothing then 
        WHERE Sekacie_Operacie.Operacia_ID = " & nz(Me.Parent.Form!txtOP_ID,0)
    end if

About your approach.

I find it a bit weird what you are doing. Consider this:

Say the query qrySekacie_Operacie returns 100 rows when run without any WHERE clause. When you are inserting a record into the subform you want to limit the combo by adding a WHERE clause that uses a value in the parent formMe.Parent.Form!txtOP_ID) , this might limit the number of rows displayed to 10.

It is worth noting that if any of the other rows in the subform contain value other than one of these 10 the combo will display a blank value. I think this is what you are experiencing.

My first question was why not use the on current event of the parent form to change the subform combo box SQL. It appears this is not what you need.

It appears that other values can exist in the dataset of the subform and that you only want certain value to be enterable in this subform but you want the subform to display any values that have been entered.

So that gets interesting. Here's what I would do:

Create a hidden combo control (with no control source) that is only visible when a new record is being entered. When this new combo is shown the other one is hidden.
Set up this new combo with the required SQL
use a before insert event to copy the value form the new combo to he old one.
Easy peezy?

Let me know ho you get on.

One other thing

I like to Ensure a subform encapsultes code that operates on it "private" controls.

As I general rule of thumb, where possible (which is 99% of the time), I like to try and keep subforms so that they will work as forms in their own right. They can be though of as an object, so if they work independently, it is an indication that their code has been encapsulated within their own code module.

So if the form has me.form.parent = nothing then the code behaves differently as it is a independent form.

So rather than the parent form code module having code that sets the SQL of the subforms combo, I would write a public sub in the subforms module (called SetComboSQL) that can be called (with the appropriate parameters. ie OPid ) to set the combo SQL.

To call the procedure in the parent form I would add

Private Sub Form_Current()

Me.MySubformControlName.Form.SetComboSQL(OPid:=Me.txtOP_ID)

End Sub

' note that intellisense won't pick up this proeprty BUT it will work. Use debugger to check it out.

I hope you find this tip useful (as it can be a massive headache saver. THE MOST BASIC OO PRINCIPAL is encapsulation. An object should own all the code that effects it's properties (and controls).

Requery only one record in a datasheet view form (MS Access)

1 Answers