Going to a specific record (form to form) without blocking others 1

[MattMeat]

Hello Everyone!!!

It's Mattmeat again...

I have this code in a datasheet form:

Private Sub WorkOrderNo_DblClick(Cancel As Integer)

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "WorkOrdersWorkingTwo"
stLinkCriteria = "[WorkOrderNo]=" & Me![WorkOrderNo]
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
End Sub

This is from a form that finds a specific record in another form (filtering) and opens it. The problem that I have with this, is that I'm having is that this code will not let me scroll through the other records in the form. It limits the availability to just one record. I still need to find a specific record, but I also need availability of all the other records in the form in case the user wants to edit another record without going back to the first form (list of service order numbers). How can this be done?

 

[KyleS]

Hi Matt B-),

Put a button on the form with this code:

Me.FilterOn = False

This removes your filter and allows the user to scroll through all the records.

Although it sounds like you're more interested in having the form goto the record but not filter at all. I'll look for the code on that and get back to you if you would like...

Kyle

 

[braindead2]

dim rst as recordset
rst = me.recordsetclone
rst.findfirst "[WorkOrderNo]=" & Me![WorkOrderNo]
if rst.nomatch then
msgbos "no matches found"
else
me.bookmark = rst.bookmark
end if
rst.close
set rst = nothing

 

[KyleS]

If you're pulling from a datasource (table/Query)
Use this code to open your form:


Private Sub WorkOrderNo_DblClick(Cancel As Integer)
On Error GoTo Err_WorkOrderNo_DblClick

Dim stDocName As String

stDocName = "WorkOrdersWorkingTwo"

DoCmd.Close
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acGoTo, Me![WorkOrderNo]
DoCmd.Maximize

Exit_WorkOrderNo_DblClick:
Exit Sub

Err_WorkOrderNo_DblClick:
MsgBox Err.Description
Resume Exit_WorkOrderNo_DblClick
End Sub

Kyle :-Q

 

[MattMeat]

Kyle,

I thought that this code would work for sure but when I click on the WorkOrderNo in the first form the second one opens with a message box saying:

"The expression you entered refers to an object that is closed or doesn't exist"
For example you may have assigned a form to a form object variable, and then referred to the object variable.


Do you know a quick rememdy for this problem.
I really appreciate you giving me all of that code!
Thanks again in advance!!!

Mattmeat

 

[MattMeat]

Kyle,

I forgot to tell you. You were right about me wanting just to go to a record without a filter but so far I have only been able to do it with the filter. I would rather just go to what ever record is selected on the first form, while keeping availability of the other records open.

Mattmeat

 

[KyleS]

Matt,
I know what the problem is, I put the:

DoCmd.Close

right in off your code, but I didn't have it when I tried mine.

Use this:

Private Sub WorkOrderNo_DblClick(Cancel As Integer)
On Error GoTo Err_WorkOrderNo_DblClick

Dim stDocName As String

stDocName = "WorkOrdersWorkingTwo"

DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acGoTo, Me![WorkOrderNo]
DoCmd.Maximize
DoCmd.Close acForm, "[1stFormName]"


Exit_WorkOrderNo_DblClick:
Exit Sub

Err_WorkOrderNo_DblClick:
MsgBox Err.Description
Resume Exit_WorkOrderNo_DblClick
End Sub


The [1stFormName] is the name of the form that this button is on (your search form). I assume you want to close it because it was in your code.

Let me know if I can doanything else

Kyle

 

[MattMeat]

Kyle,

Ok, now the first 3 numbers on the "search" form work...But only the first one (WorkOrderNo "1&quot actually brings up the correct record. Next on the list is work order "14" which brings up record "70" in the destination form. Work Order "15" (search) brings up "47" destination. The others all say :

You can't go to the specified record.
You may be at the end of the recordset.

I'm using the same query for both forms. In this query I am using : (([WorkOrders].[DateCompleted])>(Date()-7))
Because I need only records which have been completed within the last seven days. Could this be causing a problem with the immediate issue?

Also,
I am having to use a macro (button) on the destination form to get back to the search form....Is there an easier way to toggle these two?

I really appreciate your time,
Mattmeat