Find Record but Remove Filter

C

CJ

Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
 
K

Klatuu

It is really much easier than that.

Private Sub cmdViewReefer_Click()

On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim strOpen As String

stDocName = "frmReeferMain"
strOpen = Nz(Me.lngProjectID, 0)
DoCmd.OpenForm stDocName, , , , , , strOpen

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Then in the Load event of frmReeferMain

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If
 
C

CJ

Hi Klatuu

I copied and posted your code.....

Instead of the form opening, it goes straight to the message "Not Found"....

Any ideas?
--
Thanks for the brainwaves!!!


Klatuu said:
It is really much easier than that.

Private Sub cmdViewReefer_Click()

On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim strOpen As String

stDocName = "frmReeferMain"
strOpen = Nz(Me.lngProjectID, 0)
DoCmd.OpenForm stDocName, , , , , , strOpen

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Then in the Load event of frmReeferMain

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If

CJ said:
Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
Click to expand...
Click to expand...
 
K

Klatuu

It means it is not finding the value in the form's recordset.

Set rst = Me.RecordsetClone
'[lngProjectID] should be the name of the field in the form's recordset you
want to postion on
'OpenArgs should be the value you expect to find in the field.
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
'The value in OpenArgs is not in the field [lngProjectID] in the table.
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If



CJ said:
Hi Klatuu

I copied and posted your code.....

Instead of the form opening, it goes straight to the message "Not Found"....

Any ideas?
--
Thanks for the brainwaves!!!


Klatuu said:
It is really much easier than that.

Private Sub cmdViewReefer_Click()

On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim strOpen As String

stDocName = "frmReeferMain"
strOpen = Nz(Me.lngProjectID, 0)
DoCmd.OpenForm stDocName, , , , , , strOpen

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Then in the Load event of frmReeferMain

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If

CJ said:
Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
C

CJ

Found the problem.......... quotation marks were not quite right in the Load
code

Private Sub Form_Load()

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[lngProjectID] = " & Me.OpenArgs
' This line was the problem, we had rst.FindFirst [lngProjectID] = " &
Me.OpenArgs
If rst.NoMatch Then
MsgBox "Not Found"
Set rst = Nothing
DoCmd.Close
Else
Me.Bookmark = rst.Bookmark
DoCmd.Close acForm, "frmProjectMaster", acSaveNo
End If

End Sub

Thanks a bunch for your help!!
--
Thanks for the brainwaves!!!


Klatuu said:
It means it is not finding the value in the form's recordset.

Set rst = Me.RecordsetClone
'[lngProjectID] should be the name of the field in the form's recordset you
want to postion on
'OpenArgs should be the value you expect to find in the field.
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
'The value in OpenArgs is not in the field [lngProjectID] in the table.
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If



CJ said:
Hi Klatuu

I copied and posted your code.....

Instead of the form opening, it goes straight to the message "Not Found"....

Any ideas?
--
Thanks for the brainwaves!!!


Klatuu said:
It is really much easier than that.

Private Sub cmdViewReefer_Click()

On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim strOpen As String

stDocName = "frmReeferMain"
strOpen = Nz(Me.lngProjectID, 0)
DoCmd.OpenForm stDocName, , , , , , strOpen

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Then in the Load event of frmReeferMain

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If

:

Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
C

CJ

Actually, although that problem is solved, it creates another issue.

When I go to open up the second form, frmReeferMain, without using the
button on frmProjectMaster, I receive a missing operator error because there
are no open arguments for the form to find.

Is there a way to tell the form not to use the open arguments unless it is
being called from the frmProjectMaster?

I hate to say it, but I can see this turning into a major pain............ I
guess I don't see why my original plan wouldn't be easier.....

--
Thanks for the brainwaves!!!


CJ said:
Found the problem.......... quotation marks were not quite right in the Load
code

Private Sub Form_Load()

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[lngProjectID] = " & Me.OpenArgs
' This line was the problem, we had rst.FindFirst [lngProjectID] = " &
Me.OpenArgs
If rst.NoMatch Then
MsgBox "Not Found"
Set rst = Nothing
DoCmd.Close
Else
Me.Bookmark = rst.Bookmark
DoCmd.Close acForm, "frmProjectMaster", acSaveNo
End If

End Sub

Thanks a bunch for your help!!
--
Thanks for the brainwaves!!!


Klatuu said:
It means it is not finding the value in the form's recordset.

Set rst = Me.RecordsetClone
'[lngProjectID] should be the name of the field in the form's recordset you
want to postion on
'OpenArgs should be the value you expect to find in the field.
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
'The value in OpenArgs is not in the field [lngProjectID] in the table.
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If



CJ said:
Hi Klatuu

I copied and posted your code.....

Instead of the form opening, it goes straight to the message "Not Found"....

Any ideas?
--
Thanks for the brainwaves!!!


:

It is really much easier than that.

Private Sub cmdViewReefer_Click()

On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim strOpen As String

stDocName = "frmReeferMain"
strOpen = Nz(Me.lngProjectID, 0)
DoCmd.OpenForm stDocName, , , , , , strOpen

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Then in the Load event of frmReeferMain

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If

:

Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Good work, glad you found the problem.

CJ said:
Found the problem.......... quotation marks were not quite right in the Load
code

Private Sub Form_Load()

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[lngProjectID] = " & Me.OpenArgs
' This line was the problem, we had rst.FindFirst [lngProjectID] = " &
Me.OpenArgs
If rst.NoMatch Then
MsgBox "Not Found"
Set rst = Nothing
DoCmd.Close
Else
Me.Bookmark = rst.Bookmark
DoCmd.Close acForm, "frmProjectMaster", acSaveNo
End If

End Sub

Thanks a bunch for your help!!
--
Thanks for the brainwaves!!!


Klatuu said:
It means it is not finding the value in the form's recordset.

Set rst = Me.RecordsetClone
'[lngProjectID] should be the name of the field in the form's recordset you
want to postion on
'OpenArgs should be the value you expect to find in the field.
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
'The value in OpenArgs is not in the field [lngProjectID] in the table.
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If



CJ said:
Hi Klatuu

I copied and posted your code.....

Instead of the form opening, it goes straight to the message "Not Found"....

Any ideas?
--
Thanks for the brainwaves!!!


:

It is really much easier than that.

Private Sub cmdViewReefer_Click()

On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim strOpen As String

stDocName = "frmReeferMain"
strOpen = Nz(Me.lngProjectID, 0)
DoCmd.OpenForm stDocName, , , , , , strOpen

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Then in the Load event of frmReeferMain

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst [lngProjectID] = " & Me.OpenArgs
If rst.NoMatch Then
Msgbox "Not Found"
Set rst = Nothing
Docmd.Close
Else
Me.Bookmark = rst.Bookmark
Docmd.Close acForm, "frmProjectMaster", acSaveNo
End If

:

Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
Click to expand...
Click to expand...
Click to expand...
 
G

Greg

Hi, I use the Findrecord method to do what you want. On the click event try:

DoCmd.OpenForm "frmReeferMain"
Forms!frmReeferMain!lngProjectID.Setfocus
DoCmd.FindRecord Me.lngProjectID, acEntire, False, acDown, , acCurrent, True

Good Luck!
Greg
 
C

CJ

Well, I knew that somebody out there had figured this out.

Thanks a bunch Greg. I will give it a try!!
--
Thanks for the brainwaves!


Greg said:
Hi, I use the Findrecord method to do what you want. On the click event try:

DoCmd.OpenForm "frmReeferMain"
Forms!frmReeferMain!lngProjectID.Setfocus
DoCmd.FindRecord Me.lngProjectID, acEntire, False, acDown, , acCurrent, True

Good Luck!
Greg


CJ said:
Hi Groupies:

I have a command button on frmProjectMaster that opens frmReeferMain and
locates the matching record. The problem is that when you use the wizard to
make this happen a filter is applied to the frmReeferMain form and other
searches or navigation do not work on that form.

I am trying to rewrite the code so that frmReeferMain opens with the correct
record, but no filter is applied. I have the correct form opening, but the
correct record does not show.

Can somebody please have a look at my code and help me with the error?!?!

Private Sub cmdViewReefer_Click()
On Error GoTo Err_cmdViewReefer_Click

Dim stDocName As String
Dim rs As Object
Set rs = Me.Recordset.Clone

stDocName = "frmReeferMain"

Set rs = Me.Recordset.Clone
DoCmd.OpenForm stDocName
rs.FindFirst "[lngProjectID] = " &
Str(Nz(Forms!frmProjectMaster.[lngProjectID], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Close acForm, "frmProjectMaster"

Exit_cmdViewReefer_Click:
Exit Sub

Err_cmdViewReefer_Click:
MsgBox Err.Description
Resume Exit_cmdViewReefer_Click

End Sub

Thanks in Advance
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Textbox Filter 4
Copy Record Button Function 1
VBA for NotInList return to old record or BeforeUpdate value 2
RunTime Error 3070 11
Error 2147352567 2
Filter unbound search combo box based on filter in form's OnOpenEvent 3
Error 2237 8
How to remove a filter 7

Top