Pull Down Coding Not Working Well

C

Curtis Stevens

I have two boxes, Search by & Search For. Search by has the topic, such as
company name, then search for shows the records based on search by. When you
select it, it takes you to that record.

However, while back, it started become glitchy. Now, all of a sudden it
gives me this error message every so often:

The only way to fix it is to close the form and reopen it. I think
something changed while back, as it worked perfectly for a long time. I'm
running 2007 on Vista, but it was doing it before on 2002 on XP.

HERE IS MY CODE:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub

==========================================

Private Sub SearchFor_AfterUpdate()
On Error GoTo handler
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null

handler:
If Err.Number = 3464 Then
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Me![SearchFor]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End If
End Sub


Thanks
Curtis
 
B

boblarson

Whenever you use a recordset object, you should also close it and set it to
NOTHING

rs.Close
Set rs = Nothing

or you can end up with a "memory leak"
 
C

Curtis Stevens

Thanks! I got this code from other posts or users on this forum! Can't
remember which.

Hopefully this will fix it.

Whenever you use a recordset object, you should also close it and set it to
NOTHING

rs.Close
Set rs = Nothing

or you can end up with a "memory leak"


--
Bob Larson
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


Curtis Stevens said:
I have two boxes, Search by & Search For. Search by has the topic, such as
company name, then search for shows the records based on search by. When you
select it, it takes you to that record.

However, while back, it started become glitchy. Now, all of a sudden it
gives me this error message every so often:

The only way to fix it is to close the form and reopen it. I think
something changed while back, as it worked perfectly for a long time. I'm
running 2007 on Vista, but it was doing it before on 2002 on XP.

HERE IS MY CODE:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub

==========================================

Private Sub SearchFor_AfterUpdate()
On Error GoTo handler
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null

handler:
If Err.Number = 3464 Then
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Me![SearchFor]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End If
End Sub


Thanks
Curtis
 
B

boblarson

Yeah, it just can be that eventually if you have opened the recordset enough,
you start getting strange things happening because of stuff still contained
in memory. I hope too, that will take care of it for you.

--
Bob Larson
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


Curtis Stevens said:
Thanks! I got this code from other posts or users on this forum! Can't
remember which.

Hopefully this will fix it.

Whenever you use a recordset object, you should also close it and set it to
NOTHING

rs.Close
Set rs = Nothing

or you can end up with a "memory leak"


--
Bob Larson
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


Curtis Stevens said:
I have two boxes, Search by & Search For. Search by has the topic, such as
company name, then search for shows the records based on search by. When you
select it, it takes you to that record.

However, while back, it started become glitchy. Now, all of a sudden it
gives me this error message every so often:

The only way to fix it is to close the form and reopen it. I think
something changed while back, as it worked perfectly for a long time. I'm
running 2007 on Vista, but it was doing it before on 2002 on XP.

HERE IS MY CODE:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub

==========================================

Private Sub SearchFor_AfterUpdate()
On Error GoTo handler
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null

handler:
If Err.Number = 3464 Then
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Me![SearchFor]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End If
End Sub


Thanks
Curtis
 
C

Curtis Stevens

Well, I thought it worked! Here is the actual error message, it finally
happened on me. Closing the form & reopening it fixes it....

The value you entered isn’t valid for this field

For example, you may have entered text in a numeric field or a number that
is larger than the FieldSize setting permits.

Yeah, it just can be that eventually if you have opened the recordset enough,
you start getting strange things happening because of stuff still contained
in memory. I hope too, that will take care of it for you.

--
Bob Larson
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


Curtis Stevens said:
Thanks! I got this code from other posts or users on this forum! Can't
remember which.

Hopefully this will fix it.

Whenever you use a recordset object, you should also close it and set it to
NOTHING

rs.Close
Set rs = Nothing

or you can end up with a "memory leak"


--
Bob Larson
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

I have two boxes, Search by & Search For. Search by has the topic, such as
company name, then search for shows the records based on search by. When you
select it, it takes you to that record.

However, while back, it started become glitchy. Now, all of a sudden it
gives me this error message every so often:

The only way to fix it is to close the form and reopen it. I think
something changed while back, as it worked perfectly for a long time. I'm
running 2007 on Vista, but it was doing it before on 2002 on XP.

HERE IS MY CODE:

Private Sub SearchBy_AfterUpdate()
Me.SearchFor.RowSourceType = "Table/Query"
Me.SearchFor.RowSource = "SELECT DISTINCT [" & _
Me.SearchBy & "] FROM [" & _
Me.SearchBy.RowSource & _
"] ORDER BY [" & Me.SearchBy & "]"
End Sub

==========================================

Private Sub SearchFor_AfterUpdate()
On Error GoTo handler
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Chr(34) & Me![SearchFor] & Chr(34)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null

handler:
If Err.Number = 3464 Then
rs.Findfirst "[" & Me.[SearchBy] & "] = " & Me![SearchFor]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.SearchFor = Null
End If
End Sub


Thanks
Curtis
 

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

Top