Listbox to pass id to new form

[scheeps]

I've got a scenario where I've got a listbox with a few records in them. I need to be able to double click a record and pass the id to another form where the corresponding data can be further edited.

I've struggled the whole afternoon with the following piece of code, but can't seem to pick up the return_id from the listbox and filter on that id in the frmReturn_Header form.

Private Sub lstSearch_DblClick(Cancel As Integer)
On Error GoTo Err_lstSearch_DblClick
Dim db As DAO.Database
Dim rs As DAO.Recordset

DoCmd.OpenForm "frmReturn_Header"

Set rs = Forms!frmReturn_Header.Recordset.Clone

rs.FindFirst "[Return_ID] = " & CInt(Nz([frmReturn_Header_Search]![lstSearch], 0))
Forms!frmReturn_Header.Bookmark = rs.Bookmark

Exit_lstSearch_DblClick:
    Exit Sub

Err_lstSearch_DblClick:
    MsgBox Err.Description
    Resume Exit_lstSearch_DblClick
End Sub

At the moment I get a Type Mismatch error.

Hope someone will be able to assist.

 

[MarkK]

What I commonly do is give a form a custom GoToID function, so something like ...

public function gotoid(formid as long) as boolean
  with me.recordsetclone
    .findfirst "formid = " & formid
    if not .nomatch then 
      gotoid = true
      me.bookmark = .bookmark
    end if
  end with
end function

OK? So that becomes a custom public method of any form that has records you might want to find.
Then, consumers of that form can open it and call that method, passing in the ID of the record to find, so in your case ...

Private Sub lstSearch_DblClick(Cancel As Integer)
  const FN as string = "frmReturn_Header"
  DoCmd.OpenForm FN
  Forms(FN).GoToID Me.lstSearch
End Sub

And with this structure other future consumers can use that GoToID function in a heartbeat. Except for the fact that it leverages your data, this might be a function you`d expect every bound form to expose by default.
Hope this helps,
Mark

 

[scheeps]

Thanks lagbolt...I can see how this would work, but I get a "Run-time error '2465' - Application-defined or object-defined error" on:

Forms(FN).GoToId Me.lstSearch

What am I missing here? This is SO close to work.

 

[MarkK]

Is the GoToID() method Public?

[COLOR="DarkRed"]Public[/COLOR] Sub GoToID(FormID as long)
- or -
[COLOR="DarkRed"]Public[/COLOR] Function GoToID(FormID as long) As Boolean

 

[scheeps]

Yes, I've tried both now...same error.

I've tested the method by adding it to a separate module and just above the Private Sub lstSearch on separate occasions - same problem.

Anything else I can check?

 

[MarkK]

And you are adding the GoToID() routine to the form in which you want to find a record, right? It's not on the same form as your lstSearch_DoubleClick() routine.
You can also post your Db, 'cause this shouldn't be very hard to do. It's got to be something minor.
Cheers,
Mark

 

[scheeps]

Thanks Mark, I thought so too.

Maybe I should mention that this is a ADP project. Will that make any difference?

Because what I've done now is creating a clean Access db with the necessary tables and forms in (unlinked, no ADP) and it works!

This is very frustrating. Do you maybe have an explanation on why it will work in the one without ADP, and not in the ADP project?

 

[MarkK]

I don't know if it will make a difference. We don't know what the problem is so it's impossible to say what the solution looks like. A thing you can try is strongly type the form object, and the GoToID method should appear in the intellisense list of members of the form ....

Private Sub lstSearch_DblClick(Cancel As Integer)
  const FN as string = "frmReturn_Header"
[COLOR="Green"]  'strongly typed object
[/COLOR]  Dim frm as Form_frmReturnHeader
  DoCmd.OpenForm FN
  Set frm = Forms(FN)
  frm.GoToID Me.lstSearch
End Sub

For some problems this will fail to compile with more elaborate error messages...
Also, I have very little experience with ADP. Another thing you can try is this: in a code window go to Menu->Tools->Options->General Tab->Error Trapping Section->and set Break in Class Module. Now if there is an error in the form code the debugger will break there, rather than at the function call. That might make it easier to narrow down.
Cheers,
Mark

 

[scheeps]

I can confirm that the GoToID method does appear in the intellisense list of the form.

And I've made the 'Break in Class Module' setting and it breaks in the Public Function on

.FindFirst "formid = " & formid

with the message:

Object doesn't support this property or method.

Any other ideas? I'm sure we are very close in cracking this. Thanks for your assistance so for!

 

[MarkK]

Yeah, ok, so you need to replace "formid" with the ID field name of your data. Sorry if that wasn't clear.
Getting closer,
Mark

 

[scheeps]

Hi Mark, yes I did change that to Return_ID.

No luck, still get the "Object doesn't support this property or method."

.FindFirst "Return_ID = " & formid.

I'm a bit concerned, I've got a similar issue where the ADP project behaves differently than a normal Access db http://www.access-programmers.co.uk/forums/showthread.php?p=1053479#post1053479

Do you think we've reached a dead end? Especially with ADP?

 

[MarkK]

Dead-end? Obviously he choice is yours, but a bound form contains a recordset of some kind and most recordsets support some form of seek or search or find. And, to embed a form with the logic it needs to go to a specific record seems very very useful. I would not abandon the effort.

If it's an ADO.Recordset, which is probably the default in an ADP, it doesn't have a FindFirst method, it has a Find method, so maybe that's the issue.

Cheers,

 

[scheeps]

If it's an ADO.Recordset, which is probably the default in an ADP, it doesn't have a FindFirst method, it has a Find method, so maybe that's the issue.

You are right, the follow piece of code works

Public Function gotoid(formid As Long) As Boolean
  With Me.RecordsetClone
    .Find "Return_ID = " & formid
    'If Not .NoMatch Then
      gotoid = True
      Me.Bookmark = .Bookmark
    'End If
  End With
End Function

Looks like it does not have a ".NoMatch" method as well. The solution works but do you think it is necessary for the ".NoMatch" method? Am I right in saying that it basically check if it finds the corresponding Return_ID in the ADO.RecordSet?

Well, it works and that is awesome. Thank a mill for your help!!

 

[MarkK]

From Access help it looks like for ADO the Find method--if the item isn't found--leaves the record pointer at EOF, or BOF if you searched backwards. Also, it looks like Find starts looking at the current record, so if you want calling code to know whether the item was found, and if you want to check all items, you'd need to do something like...

Public Function GoToID(YourID As Long) As Boolean
  With Me.RecordsetClone
[COLOR="Green"]    'go to the first record
[/COLOR]    .MoveFirst
[COLOR="Green"]    'try to find by criteria[/COLOR]
    .Find "YourID = " & YourID
[COLOR="Green"]    'assign value to function[/COLOR]
    GoToID = Not .EOF
[COLOR="Green"]    'if found, go to record in Me.Recordset[/COLOR]
    If Not .EOF then Me.Bookmark = .Bookmark
  End With
End Function

Implement this as a function if you want calling code to know if an item was found, so...

Private Sub lstSearch_DoubleClick()
[COLOR="Green"]  'open the form hidden[/COLOR]
  DoCmd.OpenForm "YourForm", , , , acHidden
[COLOR="Green"]  'execute GoToID, which returns a boolean[/COLOR]
  If Forms("YourForm").GoToID(Me.lstSearch) Then
[COLOR="Green"]    'show the hidden form and close Me[/COLOR]
    Forms("YourForm").Visible = True
    DoCmd.Close acForm, Me.Name
  Else
[COLOR="Green"]    'close the hidden form and tell user item was not found[/COLOR]
    DoCmd.Close acForm, "YourForm"
    MsgBox "Not Found!", vbInformation
  End IF
End Sub

If your calling code doesn't care about the outcome then you can use a Sub instead of a function. Mostly when I implement this I do the function so calling code has the option.
Cheers,
Mark