Solved How to filter a single form in MS Access with VBA (1 Viewer)

[nector]

I thought this was going to be easy since I know how to filter a parent form and a sub form by using form clone. But unfortunately, this appear to be challenging where do I go wrong here:

Private Sub txtAuditControl_AfterUpdate()
On Error GoTo Err_Handler
Dim db As DAO.Database
Set db = CurrentDb
Me.Filter = "EmpID = " & Me!CboEditStaff.Value & ""
Me.FilterOn = True

Dim Records As DAO.Recordset

    Set Records = Me![frmEmployees].Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
Exit_txtAuditControl_AfterUpdate:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume txtAuditControl_AfterUpdate
End Sub

 

[Minty]

What do you think that code does?

Can you walk though it and describe each step, because I don't think it does anything.

 

[nector]

This code is on a combo box on employee's personal details, this form is used for capturing personal details for employees now since we are using cloud database, I cannot afford to make this form's property to no data entry. That will wreck the performance of my software to very poor.

So once the details are captured, they are not available for further edit, that is why I'm embedding the code above to help filter the requested record and available on the screen, then users should be able to edit the data and save that is why if you check it has the filter on and the last part.

If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
            Records.Update
            Records.MoveNext
        Wend
    End If

 

[Minty]

I'll ask again - what do you think your recordset code is doing?

1. You open a recordset clone, (filtered to the same as the form)
2. Check there are records.
3. Start a loop
4. Move to the first record (Although you would already be there)
5. Open it for editing.
6. Update it (after doing nothing to it)
7. Move to the next record
8. Continue looping to the end of the recordset
9. Close the recordset having done nothing to it at all.

What is the above attempting to do?
If you need to open the form with an empty record, set the recordsource to something like
SELECT blah, blah, where 1=0

 

[ebs17]

Me.Filter = "EmpID = " & Me!CboEditStaff.Value & ""      ' false

Me.Filter = "EmpID = " & Me!CboEditStaff.Value           ' number
Me.Filter = "EmpID = '" & Me!CboEditStaff.Value & "'"    ' text

Me.FilterOn = True

The rest of the code has nothing to do with filters and is unnecessary, perhaps even harmful.
But maybe you get paid by the number of lines of code and have to deliver in bulk.

 

[cheekybuddha]

But maybe you get paid by the number of lines of code and have to deliver in bulk.

Is this comment helpful?

 

[Minty]

now since we are using cloud database, I cannot afford to make this form's property to no data entry. That will wreck the performance of my software to very poor.

Why? I don't understand this concern?

So once the details are captured, they are not available for further edit, that is why I'm embedding the code above to help filter the requested record and available on the screen, then users should be able to edit the data and save that is why if you check it has the filter on and the last part.

The above statements are very contradictory.

Either they can edit the data or they can't. Either way it is a simple matter of either locking the relevant controls when not on a new record, or having a method of unlocking the controls if they have the authority to edit.

 

[nector]

The rest of the code has nothing to do with filters and is unnecessary, perhaps even harmful.
But maybe you get paid by the number of lines of code and have to deliver in bulk.

Eberhard

This code does nothing as well unless there is something I'm missing here

The rest of the code has nothing to do with filters and is unnecessary, perhaps even harmful.
But maybe you get paid by the number of lines of code and have to deliver in bulk.

Eberhard

This code does nothing as well unless there is something I'm missing here

Private Sub txtAuditControl_AfterUpdate()
On Error GoTo Err_Handler
Me.Filter = "EmpID = " & Me!CboEditStaff.Value           ' number
Me.FilterOn = True
Exit_txtAuditControl_AfterUpdate:
Exit Sub
Err_Handler:
MsgBox Err.Number & Err.Description, vbExclamation, "Error"
Resume Exit_txtAuditControl_AfterUpdate

End Sub

 

[Minty]

Can you take a step back and describe in simple (non database?) terms what you are trying to achieve here ?

At the moment you appear to be trying to set a filter, which won't restrict the number of records dragged into your form, just filters the ones displayed.

 

[nector]

Sorry people my apologies I misplaced the code on a wrong

Private Sub CboEditStaff_AfterUpdate()
Me.Filter = "EmpID = " & Me!CboEditStaff.Value           ' number
Me.FilterOn = True
End Sub

Now everything is working I'm able to edit the record

 

[ebs17]

My bad tip was aimed at ensuring that you recognize and describe what the individual lines of code do.

txtAuditControl and CboEditStaff refer to the current form (=> Me). That's all the subform under consideration?

 

[nector]

Hi Minty no worry,
all is fine I can now edit the record as per my requirement.

 

[nector]

EBs17

Many thanks for your help it has sorted my problem, I do value your contribution and others as well to me all the answers are valuable.