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

nector

Member
Local time
Today, 07:49
Joined
Jan 21, 2020
Messages
404
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:

Code:
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

AWF VIP
Local time
Today, 05:49
Joined
Jul 26, 2013
Messages
10,382
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

Member
Local time
Today, 07:49
Joined
Jan 21, 2020
Messages
404
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.

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


Employee forms.png
 

Minty

AWF VIP
Local time
Today, 05:49
Joined
Jul 26, 2013
Messages
10,382
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

Well-known member
Local time
Today, 06:49
Joined
Feb 7, 2020
Messages
2,022
Code:
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.
 

Minty

AWF VIP
Local time
Today, 05:49
Joined
Jul 26, 2013
Messages
10,382
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

Member
Local time
Today, 07:49
Joined
Jan 21, 2020
Messages
404
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

Code:
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

AWF VIP
Local time
Today, 05:49
Joined
Jul 26, 2013
Messages
10,382
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

Member
Local time
Today, 07:49
Joined
Jan 21, 2020
Messages
404
Sorry people my apologies I misplaced the code on a wrong

Code:
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

Well-known member
Local time
Today, 06:49
Joined
Feb 7, 2020
Messages
2,022
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

Member
Local time
Today, 07:49
Joined
Jan 21, 2020
Messages
404
Hi Minty no worry,
all is fine I can now edit the record as per my requirement.
 

nector

Member
Local time
Today, 07:49
Joined
Jan 21, 2020
Messages
404
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.
 

Users who are viewing this thread

Top Bottom