OnDblclick Docmd.Openform - Open Form All Records (1 Viewer)

[evictme]

Hello all,

I am using the on dblclick event procedure (see below) on my "Clients" form to open the "employees" form with the correct record from a field that uses a 2 column dropdown list that displays the employee name but has the employeeID in the second column.

So far, everything works as expected but its currently opening the form AND filtering to just that single record. I would like the form to open to that record but allow for ALL records to be available. How do i remove the filter but still display the wanted record?

[

DoCmd.OpenForm "Employees", , , "EmployeeID = " & Me.WorkerID

]


Thank you in advance.

 

[theDBguy]

Hi. If you want all the records to be available when the form opens, don't use the WhereCondition argument of the OpenForm method because it filters the opening form. Instead, try passing the ID using the OpenArgs argument and in the Open event of the other form, "navigate" the record pointer to the passed ID using the OpenArgs property and a bookmark.

 

[evictme]

Thank you. Any chance you could give me example of what that code would look like?

The forms are Clients and Employees and the respective fields are workerID and employeeID.

Im not sure where to start for this

 

[theDBguy]

Thank you. Any chance you could give me example of what that code would look like?

The forms are Clients and Employees and the respective fields are workerID and employeeID.

Im not sure where to start for this

Sure. When you open the form, you could try something like this:

DoCmd.OpenForm "Employees", , , , , , Me.WorkerID

Then, in the Open event of the employee form, you could try something like this:

If Not IsNull(Me.OpenArgs) Then
    With Me.RecordsetClone
        .FindFirst "EmployeeID=" & Me.OpenArgs
        If Not .NoMatch Then Me.Bookmark = .Bookmark

    End With

End If

Hope it helps...

 

[evictme]

@thedbGuy - Thank you, this worked perfectly. You're a lifesaver. Ive been messing with settings all morning.

I appreciate the help.

 

[theDBguy]

@thedbGuy - Thank you, this worked perfectly. You're a lifesaver. Ive been messing with settings all morning.

I appreciate the help.

Hi. You're welcome. Glad we could assist. Good luck with your project.

 

[evictme]

@theDBguy

What would i need to add to the code to get the double-click action to simply take you to the record IF the Form is already open?

 

[evictme]

Sure. When you open the form, you could try something like this:

DoCmd.OpenForm "Employees", , , , , , Me.WorkerID

Then, in the Open event of the employee form, you could try something like this:

If Not IsNull(Me.OpenArgs) Then
    With Me.RecordsetClone
        .FindFirst "EmployeeID=" & Me.OpenArgs
        If Not .NoMatch Then Me.Bookmark = .Bookmark

    End With

End If

Hope it helps...

What would i need to add to the code to get the double-click action to simply take you to the record IF the Form is already open?

 

[theDBguy]

What would i need to add to the code to get the double-click action to simply take you to the record IF the Form is already open?

Hi. It might be simpler to just close the form first before opening it again because OpenArgs can only be set when opening a form the first time. But if you would insist on keeping the form open, then the steps you might have to take are as follows:
1. Determine if the form is already open
2. If not, use the same code already provided
3. If already open, navigate the form record pointer on the open form from the calling form

The code for #1 above would look something like:

If CurrentProject.AllForms("FormName").IsLoaded Then

The code for #3 might look something like:

Forms!FormName.Recordset.FindFirst "EmployeeID=" & Me.WorkerID

Hope it helps...


PS. Oh, if you want to close the form first, then you can do it this way:

DoCmd.Close acForm, "FormName"
DoCmd.OpenForm "FormName"...

 

[evictme]

Hi. It might be simpler to just close the form first before opening it again because OpenArgs can only be set when opening a form the first time. But if you would insist on keeping the form open, then the steps you might have to take are as follows:
1. Determine if the form is already open
2. If not, use the same code already provided
3. If already open, navigate the form record pointer on the open form from the calling form

The code for #1 above would look something like:

If CurrentProject.AllForms("FormName").IsLoaded Then

The code for #3 might look something like:

Forms!FormName.Recordset.FindFirst "EmployeeID=" & Me.WorkerID

Hope it helps...


PS. Oh, if you want to close the form first, then you can do it this way:

DoCmd.Close acForm, "FormName"
DoCmd.OpenForm "FormName"...

This works perfectly. I could live with closing the form each time but as we all know it comes down to how the end-user will work with the database. They need to have the forms open constantly and this helps take some stress off them when they're using them.

I really appreciate all the help.

 

[theDBguy]

This works perfectly. I could live with closing the form each time but as we all know it comes down to how the end-user will work with the database. They need to have the forms open constantly and this helps take some stress off them when they're using them.

I really appreciate all the help.

Hi. If you use the code to close the open form and then immediately open it right after closing it, I doubt the user will even notice it closed. But even still, it shouldn't take too long, so they won't have to wait a lot. Good luck!