recordset findfirst problems

[jonathan]

Hi, i have a mainform (frmJob) with a subform (frmtasks), they is a 1 to
many relationship through TaskID. I also have a results form that displays
information about the records in the subform (these records also have the
TaskID in them). At the moment when i double click the results record it
takes me to the Parent record in the main form, This is fine but i still
have to find the subform record i doulbe clicked on in my results form. I
would like the main form to open and setfocus on the record in the subform
that i double clicked on in my results form using taskID as the link.
What i have done so far is create variable of TASKID. When i double click
the record in the results form it captures this, then on the load event of
the frmtasks i got it do a recordsetclose then a findfirst but it's not
working. Can some pls help me with correct synax and also how do i set the
focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing

 

[Wayne Morgan]

As you stated, the main form to the subform is a one-to-many relationship on TaskID. This
means that for each TaskID there is ONE record in the main form, but there could be MANY
records in the subform. Therefore, to find a particular record in the subform using TaskID
would not be possible, since there could be MANY of them with the same TaskID. You will
need a unique identifier for each of the subform records that is also available on the
results form and use that value to search the subform.

 

[jonathan]

sorry, i got my relationships all wrong. My Main form and subform have JobId
as the relationship. My subform has a primary key of TaskID. This is in turn
related to another table. But that is irrelevent in this case. There will
only ever be 1 instance of say TaskId = 1. So i can find the unique record.
Its something to do with my syntax on rs.findfirst ( i assume). Also going
to that record once its found i have no clue. I thought i could do a
setfocus on a bookmark if i bookmark the record once it's found. No joy
though. cheers for anyhelp you can give

As you stated, the main form to the subform is a one-to-many relationship on TaskID. This
means that for each TaskID there is ONE record in the main form, but there could be MANY
records in the subform. Therefore, to find a particular record in the subform using TaskID
would not be possible, since there could be MANY of them with the same TaskID. You will
need a unique identifier for each of the subform records that is also available on the
results form and use that value to search the subform.

--
Wayne Morgan
Microsoft Access MVP

Hi, i have a mainform (frmJob) with a subform (frmtasks), they is a 1 to
many relationship through TaskID. I also have a results form that displays
information about the records in the subform (these records also have the
TaskID in them). At the moment when i double click the results record it
takes me to the Parent record in the main form, This is fine but i still
have to find the subform record i doulbe clicked on in my results form. I
would like the main form to open and setfocus on the record in the subform
that i double clicked on in my results form using taskID as the link.
What i have done so far is create variable of TASKID. When i double click
the record in the results form it captures this, then on the load event of
the frmtasks i got it do a recordsetclose then a findfirst but it's not
working. Can some pls help me with correct synax and also how do i set the
focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing

 

[Neil]

Jonathan,

As I understand, you are trying to move to a record in a subform and the
code is in the subforms code module. The below may need some modification
but should put you on the right track.

Dim rs As DAO.Recordset
' Get recordset data
Set rs = Me.RecordsetClone ' This is assuming the code is in the
subforms code module

If IsNull(strTaskID) Then
' Do nothing
Else
If Not rs.BOF And Not rs.EOF Then
' Records exist
' Find Task ID
rs.FindFirst "[TaskID] = " & strTaskID
' Check to see if match found
If rs.NoMatch Then
' Do nothing - there was no match
Else
' Move to the record found
Me.BookMark = rs.BookMark
End If
End If
End If

' Clean up
rs.Close
Set rs = Nothing

HTH,

Neil.

sorry, i got my relationships all wrong. My Main form and subform have JobId
as the relationship. My subform has a primary key of TaskID. This is in turn
related to another table. But that is irrelevent in this case. There will
only ever be 1 instance of say TaskId = 1. So i can find the unique record.
Its something to do with my syntax on rs.findfirst ( i assume). Also going
to that record once its found i have no clue. I thought i could do a
setfocus on a bookmark if i bookmark the record once it's found. No joy
though. cheers for anyhelp you can give

As you stated, the main form to the subform is a one-to-many

relationship
on TaskID. This

means that for each TaskID there is ONE record in the main form, but

there
could be MANY

records in the subform. Therefore, to find a particular record in the subform using TaskID
would not be possible, since there could be MANY of them with the same TaskID. You will
need a unique identifier for each of the subform records that is also available on the
results form and use that value to search the subform.

form.

I

event

of

the frmtasks i got it do a recordsetclose then a findfirst but it's not
working. Can some pls help me with correct synax and also how do i set the
focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing

 

[jonathan]

that worked great, thx. One more question. how woud i go about changing the
backgroud colour of the bookmarked record. It's displayed in a datasheet
that is a subform on a main form. I can see the record because it has the
black arrow next to it. But users won't see that. I need to draw attention
to the bookmarked record.
cheers for your help

Jonathan,

As I understand, you are trying to move to a record in a subform and the
code is in the subforms code module. The below may need some modification
but should put you on the right track.

Dim rs As DAO.Recordset
' Get recordset data
Set rs = Me.RecordsetClone ' This is assuming the code is in the
subforms code module

If IsNull(strTaskID) Then
' Do nothing
Else
If Not rs.BOF And Not rs.EOF Then
' Records exist
' Find Task ID
rs.FindFirst "[TaskID] = " & strTaskID
' Check to see if match found
If rs.NoMatch Then
' Do nothing - there was no match
Else
' Move to the record found
Me.BookMark = rs.BookMark
End If
End If
End If

' Clean up
rs.Close
Set rs = Nothing

HTH,

Neil.

sorry, i got my relationships all wrong. My Main form and subform have JobId
as the relationship. My subform has a primary key of TaskID. This is in turn
related to another table. But that is irrelevent in this case. There will
only ever be 1 instance of say TaskId = 1. So i can find the unique record.
Its something to do with my syntax on rs.findfirst ( i assume). Also going
to that record once its found i have no clue. I thought i could do a
setfocus on a bookmark if i bookmark the record once it's found. No joy
though. cheers for anyhelp you can give
relationship
on TaskID. This there
could be MANY

1

to have
the

record

it

takes me to the Parent record in the main form, This is fine but i still
have to find the subform record i doulbe clicked on in my results

form.

I
would like the main form to open and setfocus on the record in the subform
that i double clicked on in my results form using taskID as the link.
What i have done so far is create variable of TASKID. When i double click
the record in the results form it captures this, then on the load

event

of
the frmtasks i got it do a recordsetclose then a findfirst but it's not
working. Can some pls help me with correct synax and also how do i

set
the

focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing

 

[jonathan]

sounds good but the users won't want another box to popup. I was thinking
contidional formating, i know a2k has a automatic condition facility, but
that won't work since the record does not have focus etc.

I dont think you can. The only thing i can suggest is to create a form that
is displayed like a message box informing the user that record so and so has
been found and is currently being displayed (The record found has a black
arrow next to it). Also provide a check box to give the user the option to
not display the message again (need to code this part tho).

Neil.

that worked great, thx. One more question. how woud i go about changing the
backgroud colour of the bookmarked record. It's displayed in a datasheet
that is a subform on a main form. I can see the record because it has the
black arrow next to it. But users won't see that. I need to draw attention
to the bookmarked record.
cheers for your help

Jonathan,

As I understand, you are trying to move to a record in a subform and the
code is in the subforms code module. The below may need some modification
but should put you on the right track.

Dim rs As DAO.Recordset
' Get recordset data
Set rs = Me.RecordsetClone ' This is assuming the code is in the
subforms code module

If IsNull(strTaskID) Then
' Do nothing
Else
If Not rs.BOF And Not rs.EOF Then
' Records exist
' Find Task ID
rs.FindFirst "[TaskID] = " & strTaskID
' Check to see if match found
If rs.NoMatch Then
' Do nothing - there was no match
Else
' Move to the record found
Me.BookMark = rs.BookMark
End If
End If
End If

' Clean up
rs.Close
Set rs = Nothing

HTH,

Neil.

sorry, i got my relationships all wrong. My Main form and subform have
JobId
as the relationship. My subform has a primary key of TaskID. This is in
turn
related to another table. But that is irrelevent in this case. There will
only ever be 1 instance of say TaskId = 1. So i can find the unique
record.
Its something to do with my syntax on rs.findfirst ( i assume). Also going
to that record once its found i have no clue. I thought i could do a
setfocus on a bookmark if i bookmark the record once it's found. No joy
though. cheers for anyhelp you can give
message
As you stated, the main form to the subform is a one-to-many
relationship
on TaskID. This
means that for each TaskID there is ONE record in the main form, but
there
could be MANY
records in the subform. Therefore, to find a particular record in the
subform using TaskID
would not be possible, since there could be MANY of them with the same
TaskID. You will
need a unique identifier for each of the subform records that is also
available on the
results form and use that value to search the subform.

--
Wayne Morgan
Microsoft Access MVP


Hi, i have a mainform (frmJob) with a subform (frmtasks), they

is

a

1
to
many relationship through TaskID. I also have a results form that
displays
information about the records in the subform (these records also have
the
TaskID in them). At the moment when i double click the results record
it
takes me to the Parent record in the main form, This is fine but i
still
have to find the subform record i doulbe clicked on in my results
form.
I
would like the main form to open and setfocus on the record in the
subform
that i double clicked on in my results form using taskID as the link.
What i have done so far is create variable of TASKID. When i double
click
the record in the results form it captures this, then on the load
event
of
the frmtasks i got it do a recordsetclose then a findfirst but it's
not
working. Can some pls help me with correct synax and also how do

i
set

the
focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing

 

[dchendrickson]

Jonathan,

I have not tried this myself, but you might try the
sample at this link:

http://www.lebans.com/formatbycriteria.htm

It sounds like it might do what you are looking for.

-dc

-----Original Message-----
sounds good but the users won't want another box to popup. I was thinking
contidional formating, i know a2k has a automatic condition facility, but
that won't work since the record does not have focus etc.

I dont think you can. The only thing i can suggest is

to create a form
that

is displayed like a message box informing the user

that record so and so
has

been found and is currently being displayed (The record found has a black
arrow next to it). Also provide a check box to give the user the option to
not display the message again (need to code this part tho).

Neil.

i go about changing
the

record because it has
the

I need to draw
attention

record in a subform and
the

code is in the subforms code module. The below may

need some
modification

but should put you on the right track.

Dim rs As DAO.Recordset
' Get recordset data
Set rs = Me.RecordsetClone ' This is assuming the code is in the
subforms code module

If IsNull(strTaskID) Then
' Do nothing
Else
If Not rs.BOF And Not rs.EOF Then
' Records exist
' Find Task ID
rs.FindFirst "[TaskID] = " & strTaskID
' Check to see if match found
If rs.NoMatch Then
' Do nothing - there was no match
Else
' Move to the record found
Me.BookMark = rs.BookMark
End If
End If
End If

' Clean up
rs.Close
Set rs = Nothing

HTH,

Neil.

sorry, i got my relationships all wrong. My Main

form and subform
have

key of TaskID. This is
in once it's found. No
joy

in the main form,
but

particular record in
the MANY of them with the
same subform records that is
also

subform (frmtasks), they
is

a

have a results form
that form, This is fine but
i clicked on in my
results setfocus on the record in
the

of TASKID. When i
double

this, then on the
load

then a findfirst but
it's

synax and also how do
i

set
the
focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing

.

 

[Wayne Morgan]

Highlighting the current record in a continuous form or datasheet requires some extra
work. You can find some examples of doing this at this location.

http://www.lebans.com/conditionalformatting.htm

--
Wayne Morgan
Microsoft Access MVP

that worked great, thx. One more question. how woud i go about changing the
backgroud colour of the bookmarked record. It's displayed in a datasheet
that is a subform on a main form. I can see the record because it has the
black arrow next to it. But users won't see that. I need to draw attention
to the bookmarked record.
cheers for your help

Jonathan,

As I understand, you are trying to move to a record in a subform and the
code is in the subforms code module. The below may need some modification
but should put you on the right track.

Dim rs As DAO.Recordset
' Get recordset data
Set rs = Me.RecordsetClone ' This is assuming the code is in the
subforms code module

If IsNull(strTaskID) Then
' Do nothing
Else
If Not rs.BOF And Not rs.EOF Then
' Records exist
' Find Task ID
rs.FindFirst "[TaskID] = " & strTaskID
' Check to see if match found
If rs.NoMatch Then
' Do nothing - there was no match
Else
' Move to the record found
Me.BookMark = rs.BookMark
End If
End If
End If

' Clean up
rs.Close
Set rs = Nothing

HTH,

Neil.

sorry, i got my relationships all wrong. My Main form and subform have JobId
as the relationship. My subform has a primary key of TaskID. This is in turn
related to another table. But that is irrelevent in this case. There will
only ever be 1 instance of say TaskId = 1. So i can find the unique record.
Its something to do with my syntax on rs.findfirst ( i assume). Also going
to that record once its found i have no clue. I thought i could do a
setfocus on a bookmark if i bookmark the record once it's found. No joy
though. cheers for anyhelp you can give
As you stated, the main form to the subform is a one-to-many relationship
on TaskID. This
means that for each TaskID there is ONE record in the main form, but there
could be MANY
records in the subform. Therefore, to find a particular record in the
subform using TaskID
would not be possible, since there could be MANY of them with the same
TaskID. You will
need a unique identifier for each of the subform records that is also
available on the
results form and use that value to search the subform.

--
Wayne Morgan
Microsoft Access MVP


Hi, i have a mainform (frmJob) with a subform (frmtasks), they is a

1

to
many relationship through TaskID. I also have a results form that
displays
information about the records in the subform (these records also have
the
TaskID in them). At the moment when i double click the results

record

it
takes me to the Parent record in the main form, This is fine but i still
have to find the subform record i doulbe clicked on in my results form.
I
would like the main form to open and setfocus on the record in the
subform
that i double clicked on in my results form using taskID as the link.
What i have done so far is create variable of TASKID. When i double
click
the record in the results form it captures this, then on the load event
of
the frmtasks i got it do a recordsetclose then a findfirst but it's not
working. Can some pls help me with correct synax and also how do i set
the
focus on the record it finds. This is what i had.

Dim rs As DAO.Recordset

If IsNull(strtaskid) Then
'do nothing
Else

Set rs = Me.RecordsetClone
If Not rs.BOF And Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF
rs.FindFirst "[TaskID] = " & strtaskid
Exit Do
rs.MoveNext
Loop

End If
End If
Set rs = Nothing