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

Findfirst not finding any records?

P: n/a
Hi I've written a module to find the first occurrence of a record in a
query (that is my forms datasource) that matches four specified
criteria. No matter what I try it doesn't seem to find records even
though they are in the recordset as you can navigate to them using the
forms navigation buttons.

weekending, weekday, ba (business area), projected

Here's the module can anyone spot where i'm going wrong? Any
suggestions would be appreciated

thanks

Michael Black

Public Sub FindRow(wkday As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrCriteria As String
Dim Vardate As String
Dim VarWeekDay As String
Dim VarBA As String
Dim VarProj As Integer
Set db = CurrentDb()

VarWeekDay = wkday
VarBA = Forms!FrmInput!ba.Value
VarProj = Forms!FrmInput.Projected.Value
Vardate = Forms!FrmInput!weekending.Value

'StrCriteria all appears on one line in code
StrCriteria = "[weekending]= #" & Vardate & " # And [weekday]=
""" & VarWeekDay & """ And [ba]= """ & VarBA & """ And [Projected]=
" & VarProj & " "
Forms!FrmInput.Requery
Set rst = Forms!FrmInput.RecordsetClone

With rst
.FindFirst StrCriteria

If .NoMatch Then
MsgBox "No Match" & StrCriteria

.AddNew
![weekday] = VarWeekDay
![weekending] = Forms![FrmInput]![weekending].Value
![ba] = VarBA
![Projected] = VarProj
.Update
.Move 0, .LastModified
Forms![FrmInput].Bookmark = rst.Bookmark
Else
MsgBox "Match"

Forms![FrmInput].Bookmark = rst.Bookmark
End If
End With
rst.Close
End Sub
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
TC
At a quick glance, it may be the date. The #...# construct requires the date
value to be expressed in month/day/year order, >regardless of the PCs
date/time settings<. So you should always format the value explicitly:

.... #" & format$ (the_date, "mm/dd/yyyy") & "# ...

You should make that change, regardless of whether that is the cause of your
current problem, or not.

HTH,
TC
"Mike" <mb*********@hotmail.com> wrote in message
news:7a**************************@posting.google.c om...
Hi I've written a module to find the first occurrence of a record in a
query (that is my forms datasource) that matches four specified
criteria. No matter what I try it doesn't seem to find records even
though they are in the recordset as you can navigate to them using the
forms navigation buttons.

weekending, weekday, ba (business area), projected

Here's the module can anyone spot where i'm going wrong? Any
suggestions would be appreciated

thanks

Michael Black

Public Sub FindRow(wkday As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrCriteria As String
Dim Vardate As String
Dim VarWeekDay As String
Dim VarBA As String
Dim VarProj As Integer
Set db = CurrentDb()

VarWeekDay = wkday
VarBA = Forms!FrmInput!ba.Value
VarProj = Forms!FrmInput.Projected.Value
Vardate = Forms!FrmInput!weekending.Value

'StrCriteria all appears on one line in code
StrCriteria = "[weekending]= #" & Vardate & " # And [weekday]=
""" & VarWeekDay & """ And [ba]= """ & VarBA & """ And [Projected]=
" & VarProj & " "
Forms!FrmInput.Requery
Set rst = Forms!FrmInput.RecordsetClone

With rst
.FindFirst StrCriteria

If .NoMatch Then
MsgBox "No Match" & StrCriteria

.AddNew
![weekday] = VarWeekDay
![weekending] = Forms![FrmInput]![weekending].Value
![ba] = VarBA
![Projected] = VarProj
.Update
.Move 0, .LastModified
Forms![FrmInput].Bookmark = rst.Bookmark
Else
MsgBox "Match"

Forms![FrmInput].Bookmark = rst.Bookmark
End If
End With
rst.Close
End Sub

Nov 12 '05 #2

P: n/a
Have you tried stepping through this and seeing if the StrCriteria you've
constructed is what you need? I'm not sure if a few of the extra spaces that
I see there would affect it at all. While stepping through, mouse over the
variables ( or debug.print them ) and see if the syntax is proper.

Mike Storr
www.veraccess.com

"Mike" <mb*********@hotmail.com> wrote in message
news:7a**************************@posting.google.c om...
Hi I've written a module to find the first occurrence of a record in a
query (that is my forms datasource) that matches four specified
criteria. No matter what I try it doesn't seem to find records even
though they are in the recordset as you can navigate to them using the
forms navigation buttons.

weekending, weekday, ba (business area), projected

Here's the module can anyone spot where i'm going wrong? Any
suggestions would be appreciated

thanks

Michael Black

Public Sub FindRow(wkday As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrCriteria As String
Dim Vardate As String
Dim VarWeekDay As String
Dim VarBA As String
Dim VarProj As Integer
Set db = CurrentDb()

VarWeekDay = wkday
VarBA = Forms!FrmInput!ba.Value
VarProj = Forms!FrmInput.Projected.Value
Vardate = Forms!FrmInput!weekending.Value

'StrCriteria all appears on one line in code
StrCriteria = "[weekending]= #" & Vardate & " # And [weekday]=
""" & VarWeekDay & """ And [ba]= """ & VarBA & """ And [Projected]=
" & VarProj & " "
Forms!FrmInput.Requery
Set rst = Forms!FrmInput.RecordsetClone

With rst
.FindFirst StrCriteria

If .NoMatch Then
MsgBox "No Match" & StrCriteria

.AddNew
![weekday] = VarWeekDay
![weekending] = Forms![FrmInput]![weekending].Value
![ba] = VarBA
![Projected] = VarProj
.Update
.Move 0, .LastModified
Forms![FrmInput].Bookmark = rst.Bookmark
Else
MsgBox "Match"

Forms![FrmInput].Bookmark = rst.Bookmark
End If
End With
rst.Close
End Sub

Nov 12 '05 #3

P: n/a
Just a thought. Try .FindLast before you do .FindFirst

I had a problem like this ages ago and it solved it for me. I think
the problem was finding the first record when you are already on it.
Sorry it's a little obscure, but it was a few years ago that I had
this.

mb*********@hotmail.com (Mike) wrote in message news:<7a**************************@posting.google. com>...
Hi I've written a module to find the first occurrence of a record in a
query (that is my forms datasource) that matches four specified
criteria. No matter what I try it doesn't seem to find records even
though they are in the recordset as you can navigate to them using the
forms navigation buttons.

weekending, weekday, ba (business area), projected

Here's the module can anyone spot where i'm going wrong? Any
suggestions would be appreciated

thanks

Michael Black

Public Sub FindRow(wkday As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrCriteria As String
Dim Vardate As String
Dim VarWeekDay As String
Dim VarBA As String
Dim VarProj As Integer
Set db = CurrentDb()

VarWeekDay = wkday
VarBA = Forms!FrmInput!ba.Value
VarProj = Forms!FrmInput.Projected.Value
Vardate = Forms!FrmInput!weekending.Value

'StrCriteria all appears on one line in code
StrCriteria = "[weekending]= #" & Vardate & " # And [weekday]=
""" & VarWeekDay & """ And [ba]= """ & VarBA & """ And [Projected]=
" & VarProj & " "
Forms!FrmInput.Requery
Set rst = Forms!FrmInput.RecordsetClone

With rst
.FindFirst StrCriteria

If .NoMatch Then
MsgBox "No Match" & StrCriteria

.AddNew
![weekday] = VarWeekDay
![weekending] = Forms![FrmInput]![weekending].Value
![ba] = VarBA
![Projected] = VarProj
.Update
.Move 0, .LastModified
Forms![FrmInput].Bookmark = rst.Bookmark
Else
MsgBox "Match"

Forms![FrmInput].Bookmark = rst.Bookmark
End If
End With
rst.Close
End Sub

Nov 12 '05 #4

P: n/a
Yep you're right thanks, I converted the date to a string format when
trying to isolate the problem before getting your reply and the
search worked properly. Thanks for providing a tidy solution.


"TC" <a@b.c.d> wrote in message news:<1073615224.177213@teuthos>...
At a quick glance, it may be the date. The #...# construct requires the date
value to be expressed in month/day/year order, >regardless of the PCs
date/time settings<. So you should always format the value explicitly:

... #" & format$ (the_date, "mm/dd/yyyy") & "# ...

You should make that change, regardless of whether that is the cause of your
current problem, or not.

HTH,
TC

Nov 12 '05 #5

P: n/a
TC
That won't make any difference AFAIK. FindFirst finds the first record,
regardless of where it is positioned at the time of the call. Perhaps your
problem involved FindNext?

TC
"Ryan" <ry********@hotmail.com> wrote in message
news:78**************************@posting.google.c om...
Just a thought. Try .FindLast before you do .FindFirst

I had a problem like this ages ago and it solved it for me. I think
the problem was finding the first record when you are already on it.
Sorry it's a little obscure, but it was a few years ago that I had
this.

mb*********@hotmail.com (Mike) wrote in message

news:<7a**************************@posting.google. com>...
Hi I've written a module to find the first occurrence of a record in a
query (that is my forms datasource) that matches four specified
criteria. No matter what I try it doesn't seem to find records even
though they are in the recordset as you can navigate to them using the
forms navigation buttons.

weekending, weekday, ba (business area), projected

Here's the module can anyone spot where i'm going wrong? Any
suggestions would be appreciated

thanks

Michael Black

Public Sub FindRow(wkday As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim StrCriteria As String
Dim Vardate As String
Dim VarWeekDay As String
Dim VarBA As String
Dim VarProj As Integer
Set db = CurrentDb()

VarWeekDay = wkday
VarBA = Forms!FrmInput!ba.Value
VarProj = Forms!FrmInput.Projected.Value
Vardate = Forms!FrmInput!weekending.Value

'StrCriteria all appears on one line in code
StrCriteria = "[weekending]= #" & Vardate & " # And [weekday]=
""" & VarWeekDay & """ And [ba]= """ & VarBA & """ And [Projected]=
" & VarProj & " "
Forms!FrmInput.Requery
Set rst = Forms!FrmInput.RecordsetClone

With rst
.FindFirst StrCriteria

If .NoMatch Then
MsgBox "No Match" & StrCriteria

.AddNew
![weekday] = VarWeekDay
![weekending] = Forms![FrmInput]![weekending].Value
![ba] = VarBA
![Projected] = VarProj
.Update
.Move 0, .LastModified
Forms![FrmInput].Bookmark = rst.Bookmark
Else
MsgBox "Match"

Forms![FrmInput].Bookmark = rst.Bookmark
End If
End With
rst.Close
End Sub

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.