Recordset returns one of two specifications (?) 
Author Message
 Recordset returns one of two specifications (?)

Greetings:

First I want to thank the group for the assistance I have received both
directly and from reading the posts.

Problem:
The following lines of code finds the person ([SocialSecurityNumber]) fine
but ignores the date.
The SS# is a text field.  DateOff and OffDate are date fields.
The employee is selected then the date is entered.  The appropriate SS# is
selected (multiple records) but the date is ignored (and I receive no
errors).

Can I find the record this way?

Private Sub OffDate_AfterUpdate()
 Dim rsd As Object
 Set rsd = Me.Recordset.Clone
 rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
[DateOff] = #" & Me![OffDate] & "#"
 Me.Bookmark = rsd.Bookmark
End Sub

Thank you.



Wed, 23 Jun 2004 23:23:31 GMT  
 Recordset returns one of two specifications (?)
Looking at the help file, I only see examples for FindFirst working on a
single field at a time. Does each record have a unique id field? If so,
instead of setting your recordset to a clone, you could set it by using a
query that would return the record in question. Next, use the unique id
field to do a FindFirst directly on the form's recordset.

Set rsd=CurrentDb.OpenRecordset("SELECT * FROM Me.Recordset.Name WHERE
[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And [DateOff] = #" &
Me![OffDate] & "#", dbOpenSnapshot)

Me.Recordset.FindFirst "[Unique ID]=" rsd.[Unique ID]

(The code is untested but should get you going in the right direction.)

Wayne Morgan


Quote:

> Can I find the record this way?

> Private Sub OffDate_AfterUpdate()
>  Dim rsd As Object
>  Set rsd = Me.Recordset.Clone
>  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
> [DateOff] = #" & Me![OffDate] & "#"
>  Me.Bookmark = rsd.Bookmark
> End Sub



Thu, 24 Jun 2004 00:20:47 GMT  
 Recordset returns one of two specifications (?)
Try the following:
 Private Sub OffDate_AfterUpdate()
  Dim rsd As Object
  Set rsd = Me.Recordset.Clone
  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
[DateOff] = " & cLng(Me![OffDate])  Me.Bookmark = rsd.Bookmark
 End Sub

---
Serge Gavrilov
http://accesstools.narod.ru



Quote:
> Greetings:

> First I want to thank the group for the assistance I have received both
> directly and from reading the posts.

> Problem:
> The following lines of code finds the person ([SocialSecurityNumber]) fine
> but ignores the date.
> The SS# is a text field.  DateOff and OffDate are date fields.
> The employee is selected then the date is entered.  The appropriate SS# is
> selected (multiple records) but the date is ignored (and I receive no
> errors).

> Can I find the record this way?

> Private Sub OffDate_AfterUpdate()
>  Dim rsd As Object
>  Set rsd = Me.Recordset.Clone
>  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
> [DateOff] = #" & Me![OffDate] & "#"
>  Me.Bookmark = rsd.Bookmark
> End Sub

> Thank you.



Thu, 24 Jun 2004 00:20:29 GMT  
 Recordset returns one of two specifications (?)
Not so, Wayne. The criteria for FindFirst can be any valid WHERE clause
(minus the word WHERE, of course).

One of the examples (at least, in Access 97) contains:

' Define search criteria.
 strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"
 ' Create a dynaset-type Recordset object based on Orders table.
 Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
 ' Find first matching record.
 rst.FindFirst strCriteria

In other words, John's code should work fine (as long as the Short Date
format is set to mm/dd/yyyy on the workstation).

To be sure it'll work on all workstations, I'd change it to:

 rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & _
     "' And [DateOff] = " & Format$(Me![OffDate], "\#mm\/dd\/yyyy\#")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> Looking at the help file, I only see examples for FindFirst working on a
> single field at a time. Does each record have a unique id field? If so,
> instead of setting your recordset to a clone, you could set it by using a
> query that would return the record in question. Next, use the unique id
> field to do a FindFirst directly on the form's recordset.

> Set rsd=CurrentDb.OpenRecordset("SELECT * FROM Me.Recordset.Name WHERE
> [SocialSecurityNumber] = '" & Me![cboEmployee] & "' And [DateOff] = #" &
> Me![OffDate] & "#", dbOpenSnapshot)

> Me.Recordset.FindFirst "[Unique ID]=" rsd.[Unique ID]

> (The code is untested but should get you going in the right direction.)

> Wayne Morgan



> > Can I find the record this way?

> > Private Sub OffDate_AfterUpdate()
> >  Dim rsd As Object
> >  Set rsd = Me.Recordset.Clone
> >  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
> > [DateOff] = #" & Me![OffDate] & "#"
> >  Me.Bookmark = rsd.Bookmark
> > End Sub



Thu, 24 Jun 2004 04:22:39 GMT  
 Recordset returns one of two specifications (?)
To further clarify:
In DAO, using FindFirst, the Criteria can be any valid Where clause with
multiple fields.
But in ADO, using the Find method, the Criteria can be only a single field.
--
Joe Fallon
Access MVP



Quote:
> Not so, Wayne. The criteria for FindFirst can be any valid WHERE clause
> (minus the word WHERE, of course).

> One of the examples (at least, in Access 97) contains:

> ' Define search criteria.
>  strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"
>  ' Create a dynaset-type Recordset object based on Orders table.
>  Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
>  ' Find first matching record.
>  rst.FindFirst strCriteria

> In other words, John's code should work fine (as long as the Short Date
> format is set to mm/dd/yyyy on the workstation).

> To be sure it'll work on all workstations, I'd change it to:

>  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & _
>      "' And [DateOff] = " & Format$(Me![OffDate], "\#mm\/dd\/yyyy\#")

> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele



> > Looking at the help file, I only see examples for FindFirst working on a
> > single field at a time. Does each record have a unique id field? If so,
> > instead of setting your recordset to a clone, you could set it by using
a
> > query that would return the record in question. Next, use the unique id
> > field to do a FindFirst directly on the form's recordset.

> > Set rsd=CurrentDb.OpenRecordset("SELECT * FROM Me.Recordset.Name WHERE
> > [SocialSecurityNumber] = '" & Me![cboEmployee] & "' And [DateOff] = #" &
> > Me![OffDate] & "#", dbOpenSnapshot)

> > Me.Recordset.FindFirst "[Unique ID]=" rsd.[Unique ID]

> > (The code is untested but should get you going in the right direction.)

> > Wayne Morgan



> > > Can I find the record this way?

> > > Private Sub OffDate_AfterUpdate()
> > >  Dim rsd As Object
> > >  Set rsd = Me.Recordset.Clone
> > >  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "'
And
> > > [DateOff] = #" & Me![OffDate] & "#"
> > >  Me.Bookmark = rsd.Bookmark
> > > End Sub



Thu, 24 Jun 2004 06:21:56 GMT  
 Recordset returns one of two specifications (?)
Thanks for pointing that out, Joe.

I'd forgotten to be explicit!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> To further clarify:
> In DAO, using FindFirst, the Criteria can be any valid Where clause with
> multiple fields.
> But in ADO, using the Find method, the Criteria can be only a single
field.
> --
> Joe Fallon
> Access MVP



> > Not so, Wayne. The criteria for FindFirst can be any valid WHERE clause
> > (minus the word WHERE, of course).

> > One of the examples (at least, in Access 97) contains:

> > ' Define search criteria.
> >  strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"
> >  ' Create a dynaset-type Recordset object based on Orders table.
> >  Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
> >  ' Find first matching record.
> >  rst.FindFirst strCriteria

> > In other words, John's code should work fine (as long as the Short Date
> > format is set to mm/dd/yyyy on the workstation).

> > To be sure it'll work on all workstations, I'd change it to:

> >  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & _
> >      "' And [DateOff] = " & Format$(Me![OffDate], "\#mm\/dd\/yyyy\#")

> > --
> > Doug Steele, Microsoft Access MVP
> > http://I.Am/DougSteele



> > > Looking at the help file, I only see examples for FindFirst working on
a
> > > single field at a time. Does each record have a unique id field? If
so,
> > > instead of setting your recordset to a clone, you could set it by
using
> a
> > > query that would return the record in question. Next, use the unique
id
> > > field to do a FindFirst directly on the form's recordset.

> > > Set rsd=CurrentDb.OpenRecordset("SELECT * FROM Me.Recordset.Name WHERE
> > > [SocialSecurityNumber] = '" & Me![cboEmployee] & "' And [DateOff] = #"
&
> > > Me![OffDate] & "#", dbOpenSnapshot)

> > > Me.Recordset.FindFirst "[Unique ID]=" rsd.[Unique ID]

> > > (The code is untested but should get you going in the right
direction.)

> > > Wayne Morgan



> > > > Can I find the record this way?

> > > > Private Sub OffDate_AfterUpdate()
> > > >  Dim rsd As Object
> > > >  Set rsd = Me.Recordset.Clone
> > > >  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "'
> And
> > > > [DateOff] = #" & Me![OffDate] & "#"
> > > >  Me.Bookmark = rsd.Bookmark
> > > > End Sub



Thu, 24 Jun 2004 07:40:45 GMT  
 Recordset returns one of two specifications (?)
Thanks Doug,

I'm sure that info will come in handy. I was looking at the Access 2000 help
file and didn't see the double criteria example (don't know if I missed it
or if it wasn't there). However, it didn't say you couldn't do it either.

Wayne Morgan



Quote:
> Not so, Wayne. The criteria for FindFirst can be any valid WHERE clause
> (minus the word WHERE, of course).

> One of the examples (at least, in Access 97) contains:

> ' Define search criteria.
>  strCriteria = "[ShipCountry] = 'UK' And [OrderDate] >= #1-1-95#"
>  ' Create a dynaset-type Recordset object based on Orders table.
>  Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
>  ' Find first matching record.
>  rst.FindFirst strCriteria

> In other words, John's code should work fine (as long as the Short Date
> format is set to mm/dd/yyyy on the workstation).

> To be sure it'll work on all workstations, I'd change it to:

>  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & _
>      "' And [DateOff] = " & Format$(Me![OffDate], "\#mm\/dd\/yyyy\#")

> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele



> > Looking at the help file, I only see examples for FindFirst working on a
> > single field at a time. Does each record have a unique id field? If so,
> > instead of setting your recordset to a clone, you could set it by using
a
> > query that would return the record in question. Next, use the unique id
> > field to do a FindFirst directly on the form's recordset.

> > Set rsd=CurrentDb.OpenRecordset("SELECT * FROM Me.Recordset.Name WHERE
> > [SocialSecurityNumber] = '" & Me![cboEmployee] & "' And [DateOff] = #" &
> > Me![OffDate] & "#", dbOpenSnapshot)

> > Me.Recordset.FindFirst "[Unique ID]=" rsd.[Unique ID]

> > (The code is untested but should get you going in the right direction.)

> > Wayne Morgan



> > > Can I find the record this way?

> > > Private Sub OffDate_AfterUpdate()
> > >  Dim rsd As Object
> > >  Set rsd = Me.Recordset.Clone
> > >  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "'
And
> > > [DateOff] = #" & Me![OffDate] & "#"
> > >  Me.Bookmark = rsd.Bookmark
> > > End Sub



Thu, 24 Jun 2004 13:44:11 GMT  
 Recordset returns one of two specifications (?)
Gentlemen:

Again my thanks.  Like everything - once you understand it you wonder why
you couldn't see it in the first place.

This worked:
  Dim rst As DAO.Recordset
  Set rst = Me.Recordset
  strSearchName = "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
[DateOff] = #" & Me![OffDate] & "#"
  rst.FindFirst strSearchName

John


Quote:
> Greetings:

> First I want to thank the group for the assistance I have received both
> directly and from reading the posts.

> Problem:
> The following lines of code finds the person ([SocialSecurityNumber]) fine
> but ignores the date.
> The SS# is a text field.  DateOff and OffDate are date fields.
> The employee is selected then the date is entered.  The appropriate SS# is
> selected (multiple records) but the date is ignored (and I receive no
> errors).

> Can I find the record this way?

> Private Sub OffDate_AfterUpdate()
>  Dim rsd As Object
>  Set rsd = Me.Recordset.Clone
>  rsd.FindFirst "[SocialSecurityNumber] = '" & Me![cboEmployee] & "' And
> [DateOff] = #" & Me![OffDate] & "#"
>  Me.Bookmark = rsd.Bookmark
> End Sub

> Thank you.



Thu, 24 Jun 2004 14:27:19 GMT  
 Recordset returns one of two specifications (?)
To really beat a dead horse:
Some ADO recordsets (like Jet OLE DB) support the Seek method which can
accept an array of search values if there is a multi-field index.

Example using Northwind Order Details table:
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
Set cnn = New ADODB.Connection
  cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Program Files\Microsoft
Office\Office10\Samples\Northwind.mdb;"

' Does this provider support Seek and Index?
If rst.Supports(adIndex) And rst.Supports(adSeek) Then
  With rst
    .Index = "PrimaryKey"
   .Open _
      "Order Details", _
      cnnConnection, _
      adOpenKeyset, _
      adLockOptimistic, _
      adCmdTableDirect

    .Seek Array(10255, 16), adSeekAfterEQ

      If Not .EOF Then
        Debug.Print .Fields("Quantity").Value
    End If
  End With
End If
--
Joe Fallon
Access MVP



Fri, 25 Jun 2004 11:41:37 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Select two lines, return as one line

2. How to get two return values from one fuction

3. one connection for two recordset?

4. Join two recordsets into one using DAO 3.6?

5. Two Forms and One Recordset

6. Two connections, one recordset?

7. sort two different recordsets into one table

8. two recordsets in one Report

9. Two connections, one recordset?

10. How to merge two ADO RecordSets into one

11. Merging Data from TWO databases into One recordset???

12. Two recordsets open on one connection

 

 
Powered by phpBB® Forum Software