By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,176 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Search Form

P: 21
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub SearchList_DblClick(Cancel As Integer)
  3. On Error GoTo Err_SearchList_DblClick
  4. Dim db As DAO.Database
  5. Dim rst As DAO.Recordset
  6.  
  7.                   DoCmd.OpenForm "frmEmpInfo"
  8.                   Set rst = Forms![FrmCandidates].Recordset.Clone
  9.                   rst.FindFirst "[ID] = " & Me.SearchList
  10.  
  11.                   Forms![FfmCandidates].Bookmark = rst.Bookmark
  12.                   DoCmd.Close acForm, Me.Name
  13.  
  14.                    Exit_SearchList_DblClick:
  15.     Exit Sub
  16.  
  17. Err_SearchList_DblClick:
  18.     MsgBox Err.Description
  19.     Resume Exit_SearchList_DblClick
  20. End Sub
  21.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
  3.  
  4. Set rst = Forms!FrmHome!frmContent.Form.Recordset.Clone
  5.  
  6.  
  7. rst.FindFirst "Forms!FrmHome!frmContent.Form.Recordset.[ID] = " & Forms!FrmHome!frmContent.Form.Recordset.SearchList.[ID]
  8.  
  9. Forms!FrmHome!frmContent.Form.Recordset.Bookmark = rst.Bookmark
  10.  
  11.  


Any advise would be appreciated

Thanks in advance
Aug 19 '08 #1
Share this Question
Share on Google+
18 Replies


puppydogbuddy
Expert 100+
P: 1,923
try it this way, assuming you are using DAO:

Expand|Select|Wrap|Line Numbers
  1. Dim rst As DAO.Recordset
  2.  
  3. Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
  4.  
  5. rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList
  6.  
  7. Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
  8.  
  9. rst.Close
  10. Set rst = Nothing
Aug 19 '08 #2

P: 21
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub SearchList_DblClick(Cancel As Integer)
  3. On Error GoTo Err_SearchList_DblClick
  4.  
  5. Dim rst As DAO.Recordset
  6.  
  7. Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
  8.  
  9. Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
  10.  
  11.  
  12. rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList"
  13.  
  14. Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
  15.  
  16.  
  17.   rst.Close
  18.   Set rst = Nothing
  19.  
  20. Exit_SearchList_DblClick:
  21.     Exit Sub
  22.  
  23. Err_SearchList_DblClick:
  24.     MsgBox Err.Description
  25.     Resume Exit_SearchList_DblClick
  26. End Sub
  27.  
Aug 20 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 20 '08 #4

puppydogbuddy
Expert 100+
P: 1,923
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 & "'"
Aug 20 '08 #5

P: 21
Hi

Nope...ID Is a number field
Aug 20 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 20 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
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.

Expand|Select|Wrap|Line Numbers
  1. rst.FindFirst [ID] = " & Forms!FrmHome!frmContent.Form!SearchList
  2.  
  3. If Not rst.NoMatch Then
  4.    Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
  5. End If
  6.  
Aug 20 '08 #8

P: 21
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub SearchList_DblClick(Cancel As Integer)
  2. On Error GoTo Err_SearchList_DblClick
  3.  
  4. Dim rst As DAO.Recordset
  5.  
  6.          Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
  7.          Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
  8.  
  9.    [ID].SetFocus
  10.  
  11.         rst.FindFirst [ID] = "& [Forms]![FrmHome]![frmContent].[Form]!SearchList"
  12.  
  13.     If Not rst.NoMatch Then
  14.  
  15.         Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
  16.  
  17.   End If
  18.  
  19.          rst.Close
  20.         Set rst = Nothing
  21.  
  22. Exit_SearchList_DblClick:
  23.     Exit Sub
  24.  
  25. Err_SearchList_DblClick:
  26.     MsgBox Err.Description
  27.     Resume Exit_SearchList_DblClick
  28. End Sub
  29.  
Aug 22 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub SearchList_DblClick(Cancel As Integer)
  2. On Error GoTo Err_SearchList_DblClick
  3.  
  4. Dim rst As DAO.Recordset
  5.  
  6.          Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
  7.          Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
  8.  
  9.    [ID].SetFocus
  10.  
  11.         rst.FindFirst [ID] = "& [Forms]![FrmHome]![frmContent].[Form]!SearchList"
  12.  
  13.     If Not rst.NoMatch Then
  14.  
  15.         Forms!FrmHome!frmContent.Form.RecordsetClone.Bookmark = rst.Bookmark
  16.  
  17.   End If
  18.  
  19.          rst.Close
  20.         Set rst = Nothing
  21.  
  22. Exit_SearchList_DblClick:
  23.     Exit Sub
  24.  
  25. Err_SearchList_DblClick:
  26.     MsgBox Err.Description
  27.     Resume Exit_SearchList_DblClick
  28. End Sub
  29.  
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)
Aug 22 '08 #10

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1.  
  2.       strCountry = "Country = '" & strCountry & "'"
  3.       With rstCustomers
  4.          .MoveLast
  5.          .FindFirst strCountry
  6.          ...
  7.  
Aug 22 '08 #11

P: 21
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
Attached Files
File Type: zip Emp.zip (174.4 KB, 125 views)
Aug 26 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
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
Aug 26 '08 #13

P: 21
Hi

My apologies

I removed the reference..

Please find attached
Attached Files
File Type: zip Emp.zip (223.2 KB, 73 views)
Aug 26 '08 #14

puppydogbuddy
Expert 100+
P: 1,923
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.
Aug 26 '08 #15

P: 21
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..
Aug 27 '08 #16

100+
P: 167
Here is the code I tested and it worked:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SearchList_DblClick(Cancel As Integer)
  2. On Error GoTo Err_SearchList_DblClick
  3.  
  4. Dim rst As DAO.Recordset
  5. Dim SearchItem As Variant
  6.  
  7.     SearchItem = Forms!FrmHome!frmContent.Form!SearchList
  8.     Forms!FrmHome!frmContent.SourceObject = "FrmInterview"
  9.  
  10.         Set rst = Forms!FrmHome!frmContent.Form.RecordsetClone
  11.  
  12.         rst.FindFirst "[ID] =" & SearchItem
  13.  
  14.     If Not rst.NoMatch Then
  15.  
  16.         Forms!FrmHome!frmContent.Form.Bookmark = rst.Bookmark
  17.  
  18.   End If
  19.  
  20.  
  21.         Set rst = Nothing
  22.  
  23.  
  24. Exit_SearchList_DblClick:
  25.     Exit Sub
  26.  
  27. Err_SearchList_DblClick:
  28.     MsgBox Err.Description
  29.     Resume Exit_SearchList_DblClick
  30.  
  31. End Sub
Aug 27 '08 #17

P: 21
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
Aug 27 '08 #18

100+
P: 167
You're welcome Natural.
Keep up the good work!
Aug 27 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.