Search Form

[natural]

Good Afternoon

I was hoping someone could possible assist.

I have a form called frmSearchCandidates

The Form opens up as a subform on my main form called FrmContent

I have
FrmHome (Main Form)
FrmContent (Subform On MainForm where all other forms appear in)

Before i made use of the above mentioned, i could do my search via a list box double click on the record, and it would duplicate the record in my relevant form (FrmCandidates)


On Double click the following happend

 
Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
                  DoCmd.OpenForm "frmEmpInfo"
                  Set rst = Forms![FrmCandidates].Recordset.Clone
                  rst.FindFirst "[ID] = " & Me.SearchList
 
                  Forms![FfmCandidates].Bookmark = rst.Bookmark
                  DoCmd.Close acForm, Me.Name
 
                   Exit_SearchList_DblClick:
    Exit Sub
 
Err_SearchList_DblClick:
    MsgBox Err.Description
    Resume Exit_SearchList_DblClick
End Sub
 

No due to the fact that the form FrmCandidates opens in

FrmHome
FrmContent

It does not seem to work at all

I do get the form to open in my subform.

but it does not open the current record

I changed it to the following

 
Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
 
Set rst = Forms!FrmHome!frmContent.Form.Recordset.Clone
 
 
rst.FindFirst "Forms!FrmHome!frmContent.Form.Recordset.[ID] = " & Forms!FrmHome!frmContent.Form.Recordset.SearchList.[ID]
 
Forms!FrmHome!frmContent.Form.Recordset.Bookmark = rst.Bookmark
 
 

Any advise would be appreciated

Thanks in advance

[puppydogbuddy]

try it this way, assuming you are using DAO:

Dim rst As DAO.Recordset
 
Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
 
rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList
 
Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
 
rst.Close
Set rst = Nothing

[natural]

Thank you for the repsonse and help

It opens the form properly.. but it gives me an error

" the expression you entered refers to an object that is closed or doesnt exist. "

Here is the exact code

 
Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
 
Dim rst As DAO.Recordset
 
Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
 
Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
 
 
rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList"
 
Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
 
 
  rst.Close
  Set rst = Nothing
 
Exit_SearchList_DblClick:
    Exit Sub
 
Err_SearchList_DblClick:
    MsgBox Err.Description
    Resume Exit_SearchList_DblClick
End Sub
 

[puppydogbuddy]

You need to isolate what line of code the error is occuring on. To do that, go the the VB editor command menu, select Tools>options>general tab> break on all errors. Remember to change back after you have isolated the error line.

[puppydogbuddy]

If ID is a text field, change line 5 from:

rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList

To:

rst.FindFirst [ID] = '" & Forms!FrmHome!frmContent.Form!SearchList & "'"

[natural]

Hi

Nope...ID Is a number field

[puppydogbuddy]

Hi,
On line 10, put a set focus statement on the ID field, so the ID field has focus before doing the Find on line 11.

[puppydogbuddy]

A. Remove the double quotes at the end of the FindFirst statement
B. Forgot that you have to test for NoMatch after using a Find method.

rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList
 
If Not rst.NoMatch Then
   Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
End If
 

[natural]

Hi Sorry for the delay in reply and thanks again for the assistance

No luck

I did the following but still gives me the error
"the expression you entered refers to an object that is closed or doensnt exist"

here is what i currently have

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
 
Dim rst As DAO.Recordset
 
         Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
         Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
 
   [ID].SetFocus
 
        rst.FindFirst [ID] = "& [Forms]![FrmHome]![frmContent].[Form]!SearchList"
 
    If Not rst.NoMatch Then
 
        Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
 
  End If
 
         rst.Close
        Set rst = Nothing
 
Exit_SearchList_DblClick:
    Exit Sub
 
Err_SearchList_DblClick:
    MsgBox Err.Description
    Resume Exit_SearchList_DblClick
End Sub
 

[puppydogbuddy]

Hi Sorry for the delay in reply and thanks again for the assistance

No luck

I did the following but still gives me the error
"the expression you entered refers to an object that is closed or doensnt exist"

here is what i currently have

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
 
Dim rst As DAO.Recordset
 
         Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
         Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
 
   [ID].SetFocus
 
        rst.FindFirst [ID] = "& [Forms]![FrmHome]![frmContent].[Form]!SearchList"
 
    If Not rst.NoMatch Then
 
        Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
 
  End If
 
         rst.Close
        Set rst = Nothing
 
Exit_SearchList_DblClick:
    Exit Sub
 
Err_SearchList_DblClick:
    MsgBox Err.Description
    Resume Exit_SearchList_DblClick
End Sub
 

Hi,
Unless you can tell me what line of the code has the error (see my post #4 above), it is like taking a shot in the dark.

Try the following and see if it helps.
change this:
rst.FindFirst [ID] = "& [Forms]![FrmHome]![frmContent].[Form]!SearchList"
to:
rst.FindFirst [ID] " = " & Me![frmContent].Form!SearchList
__________________________________________________ ____

Is SearchList a listbox? Try it this way.....
rst.FindFirst [ID] " = " & Me![frmContent].Form!SearchList.Column(0)

[Stewart Ross]

Hi. You don't say where the error is occurring - it will help us greatly if in future you tell us which line of your code is failing. Saves a lot of guesswork - or just giving up.

Having said that, your FindFirst line (line 11 above) remains in error. You need to pass a string value to FindFirst, but you have got the quotes in the wrong place; the [ID] field referred to in your criteria for FindFirst lies outside of the string. Also, the form control name cannot be passed as part of the string - you need to pass the current value of the control to the string instead.

The syntax for passing the value of your control as a number to findfirst is:

rst.FindFirst "[ID] = " & Forms![FrmHome]![frmContent].Form!SearchList

but if it is a string value you will need single quotes on either side of the string literal:

rst.FindFirst "[ID] = '" & Forms![FrmHome]![frmContent].Form!SearchList & "'"

-Stewart

ps Sorry PDog - my post crossed your one while I was writing it.

(Oops - rereading your previous posts I think the OP's erroneous syntax for FindFirst results from one of your posts...)

Natural: you will also benefit from using the available information in the MS helpfile for FindFirst. Here is part of an example of how findfirst is used, taken direct from the MS help:

 
      strCountry = "Country = '" & strCountry & "'"
      With rstCustomers
         .MoveLast
         .FindFirst strCountry
         ...
 

[natural]

Good Morning..

Thank for the responses..

It is difficult to say where the error is...

I have attached a rough copy to give you an idea.

The form "FrmHome" is the main form,
The is a cmdbutton called "Candidates"

If you click on candidates it will be "Search Candidates"


I will then double click on the candidate in the list
Which should then open my FrmCandidates, Which it does but with the

"The expression you entered referes to an object that is closed or doesn't exist"

thank you for all the adivise and assistance

[puppydogbuddy]

I downloaded your file. Unfortunately, when I try to compile it, I can't due to a missing reference to CDO 1.21 dll. I tried to download the dll(see link below), but MS won't let me install because it says my system does not qualify. Check to see if you have any missing references.

http://www.microsoft.com/downloads/d...displaylang=en

[natural]

Hi

My apologies

I removed the reference..

Please find attached

[puppydogbuddy]

Ok, I took a look and your problem is that Access can't find frmContent. If you look in the Db Window at the form objects you have created, frmContent doesn't exist. You are referencing frmContent as if it were a form, but you never created a form object with that name. When you create frmContent, your references should work. Please let me know what happens.

[natural]

MMM.... Okay i created a form call Frmcontent with nothing in it.

It is unbound and now sits on my main form as the sourceobject. FrmContent

I reran the search and still gave me the

"The expression you entered refers to an object that is closed or doent exist"

I then thought maybe if i make Frmcontent Bound to my TblCandidates. (Not that i would want to due to having other tables etc..)

Still same error.

I created the form (frmContent) in the sample i send you, to see if maybe i have something in my db that might be causing the problem but did not get it to work in there either.

I understand that is message/error indicates that an object is closed, but if i am cloning the record it should not be a problem...

However if i changed the code, and instead of opening in my FrmContent into another form it works perfectly..

Am I maybe doing this completely wrong..

[hjozinovic]

Here is the code I tested and it worked:

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
 
Dim rst As DAO.Recordset
Dim SearchItem As Variant
 
    SearchItem = Forms!FrmHome!frmContent.Form!SearchList
    Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
 
        Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
 
        rst.FindFirst "[ID] =" & SearchItem
 
    If Not rst.NoMatch Then
 
        Forms!FrmHome!frmContent.Form.Bookmark = rst.Bookmark
 
  End If
 
 
        Set rst = Nothing
 
 
Exit_SearchList_DblClick:
    Exit Sub
 
Err_SearchList_DblClick:
    MsgBox Err.Description
    Resume Exit_SearchList_DblClick
 
End Sub

[natural]

Wow... Brilliant

It is working.

Thank you so much...

You have been extremely helpfull..

thank you very much for you time and help

Regards

natural

[hjozinovic]

You're welcome Natural.
Keep up the good work!