Find a record based on field on other form using primary key

[efandango]

I have a subform control where I want to click a button and find a matching
record on another subform (which is on a different tab).

The forms are bound by a Master/Child record field called 'Run_No'


The main form is called: frm_Runs (this holds both the host subform and the
target subform)

The host subform is called : frm_Points
The form control that I want to use for the find is called:
'Run_point_Venue_L'


The target Subform is called: frm_Getrounds
The target subform control that I want find on is called: 'Note'

I want to be able to say: Find matching record contents for 'Note' where the
text/record content equals the contents of 'Run_point_Venue_L', based on
using the Master Child field, 'Run_No'.

I tried this code: .FindFirst "[Note] = " &
Forms![frm_Runs]![frm_Points].Form![Run_point_Venue_L] Where Run_No= " &
Forms!frm_Runs.Run_No"

but get an error saying 'Expected end of statement'

Can someone advise me on how to solve this problem?

 

[Marshall Barton]

I have a subform control where I want to click a button and find a matching
record on another subform (which is on a different tab).

The forms are bound by a Master/Child record field called 'Run_No'


The main form is called: frm_Runs (this holds both the host subform and the
target subform)

The host subform is called : frm_Points
The form control that I want to use for the find is called:
'Run_point_Venue_L'


The target Subform is called: frm_Getrounds
The target subform control that I want find on is called: 'Note'

I want to be able to say: Find matching record contents for 'Note' where the
text/record content equals the contents of 'Run_point_Venue_L', based on
using the Master Child field, 'Run_No'.

I tried this code: .FindFirst "[Note] = " &
Forms![frm_Runs]![frm_Points].Form![Run_point_Venue_L] Where Run_No= " &
Forms!frm_Runs.Run_No"

but get an error saying 'Expected end of statement'

Try:

..FindFirst "[Note] = """ & Me![Run_point_Venue_L] _
& """ And Run_No= " & Me.Run_No

If the Run_point_Venue_L subform's LinkMaster/Child is
Run_No, then that part would be redundant in the findFirst.
In this case all you need would be:

..FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

 

[efandango]

Marshall,

I tried both of your lines, but neither worked, throwing up the same error
message:
The Microsofy Office Access database engine does not recognize 'Note' as a
valid name or expression.



This is my current full code:

Private Sub TurnRound_Button_L_Click()

Forms![frm_Runs].[frm_Getrounds].SetFocus
Forms![frm_Runs].[frm_Getrounds].Form.[Note].SetFocus

With Me.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """ And Run_No=
" & Me.Run_No

'.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

End With

End Sub

I have a subform control where I want to click a button and find a matching
record on another subform (which is on a different tab).

The forms are bound by a Master/Child record field called 'Run_No'


The main form is called: frm_Runs (this holds both the host subform and the
target subform)

The host subform is called : frm_Points
The form control that I want to use for the find is called:
'Run_point_Venue_L'


The target Subform is called: frm_Getrounds
The target subform control that I want find on is called: 'Note'

I want to be able to say: Find matching record contents for 'Note' where the
text/record content equals the contents of 'Run_point_Venue_L', based on
using the Master Child field, 'Run_No'.

I tried this code: .FindFirst "[Note] = " &
Forms![frm_Runs]![frm_Points].Form![Run_point_Venue_L] Where Run_No= " &
Forms!frm_Runs.Run_No"

but get an error saying 'Expected end of statement'

Try:

..FindFirst "[Note] = """ & Me![Run_point_Venue_L] _
& """ And Run_No= " & Me.Run_No

If the Run_point_Venue_L subform's LinkMaster/Child is
Run_No, then that part would be redundant in the findFirst.
In this case all you need would be:

..FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

 

[Marshall Barton]

I tried both of your lines, but neither worked, throwing up the same error
message:
The Microsofy Office Access database engine does not recognize 'Note' as a
valid name or expression.



This is my current full code:

Private Sub TurnRound_Button_L_Click()

Forms![frm_Runs].[frm_Getrounds].SetFocus
Forms![frm_Runs].[frm_Getrounds].Form.[Note].SetFocus

With Me.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """ And Run_No=
" & Me.Run_No

'.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

End With

End Sub

That message implies that Note is not the name of a field in
the form's record soure table/query.

Didn't you want to search the other subform? In that case
Me is the wrong form object and you should be using:

With Forms![frm_Runs].[frm_Getrounds].Form.RecordsetClone
or
With Parent.[frm_Getrounds].Form.RecordsetClone

 

[efandango]

Marshall,

Yes, the field in question is definately called 'Note'

Yes, I do want to search the other subform field called 'Note'

I used both of your latest examples, but nothing happens, except I correctly
switch to the target form in question (via 2 lines of SetFocus code).

But the record doesn't go to the matching field in the 'departure' form's
field, 'Run_point_Venue_L'.

It simply goes to the first record (which is incorrect).

This is my latest code (with varying rem'ed out lines)

Forms![frm_Runs].[frm_Getrounds].SetFocus
Forms![frm_Runs].[frm_Getrounds].Form.[Note].SetFocus

'With Parent.[frm_Getrounds].Form.RecordsetClone
With Forms![frm_Runs].[frm_Getrounds].Form.RecordsetClone


'.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """ And Run_No= " &
Me.Run_No

..FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

End With

End Sub

I tried both of your lines, but neither worked, throwing up the same error
message:
The Microsofy Office Access database engine does not recognize 'Note' as a
valid name or expression.



This is my current full code:

Private Sub TurnRound_Button_L_Click()

Forms![frm_Runs].[frm_Getrounds].SetFocus
Forms![frm_Runs].[frm_Getrounds].Form.[Note].SetFocus

With Me.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """ And Run_No=
" & Me.Run_No

'.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

End With

End Sub

That message implies that Note is not the name of a field in
the form's record soure table/query.

Didn't you want to search the other subform? In that case
Me is the wrong form object and you should be using:

With Forms![frm_Runs].[frm_Getrounds].Form.RecordsetClone
or
With Parent.[frm_Getrounds].Form.RecordsetClone

 

[Marshall Barton]

Yes, the field in question is definately called 'Note'

Yes, I do want to search the other subform field called 'Note'

I used both of your latest examples, but nothing happens, except I correctly
switch to the target form in question (via 2 lines of SetFocus code).

But the record doesn't go to the matching field in the 'departure' form's
field, 'Run_point_Venue_L'.

It simply goes to the first record (which is incorrect).

This is my latest code (with varying rem'ed out lines)

Forms![frm_Runs].[frm_Getrounds].SetFocus
Forms![frm_Runs].[frm_Getrounds].Form.[Note].SetFocus

'With Parent.[frm_Getrounds].Form.RecordsetClone
With Forms![frm_Runs].[frm_Getrounds].Form.RecordsetClone


'.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """ And Run_No= " &
Me.Run_No

.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

End With

End Sub

Ahhhh, you did say that that was all of the code, but I
missed that you are missing an important part. I.e. finding
a record in the recordset clone has no effect on the form's
recordset. You need to add code to sync the two recordsets.

Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"
If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
End If
End With

End Sub

 

[efandango]

Marshall,

That did the trick!. (thank you so much for your help)

though, when I compare your last code with the previous; I can't really see
the key difference, except the inclusion of 'Bookmark'. was that what swung
it?

regards

Eric

Yes, the field in question is definately called 'Note'

Yes, I do want to search the other subform field called 'Note'

I used both of your latest examples, but nothing happens, except I correctly
switch to the target form in question (via 2 lines of SetFocus code).

But the record doesn't go to the matching field in the 'departure' form's
field, 'Run_point_Venue_L'.

It simply goes to the first record (which is incorrect).

This is my latest code (with varying rem'ed out lines)

Forms![frm_Runs].[frm_Getrounds].SetFocus
Forms![frm_Runs].[frm_Getrounds].Form.[Note].SetFocus

'With Parent.[frm_Getrounds].Form.RecordsetClone
With Forms![frm_Runs].[frm_Getrounds].Form.RecordsetClone


'.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """ And Run_No= " &
Me.Run_No

.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"

End With

End Sub

Ahhhh, you did say that that was all of the code, but I
missed that you are missing an important part. I.e. finding
a record in the recordset clone has no effect on the form's
recordset. You need to add code to sync the two recordsets.

Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"
If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
End If
End With

End Sub

 

[Marshall Barton]

That did the trick!. (thank you so much for your help)

though, when I compare your last code with the previous; I can't really see
the key difference, except the inclusion of 'Bookmark'. was that what swung
it?

Yes. Setting the form's bookmark to the recordsetclone's
bookmark is how you tell the form to move to the record
found in the recordsetclone. (VBA Help is usually your
friend ;-))

Checking NoMatch is important just in case the FindFirst
does not find a record, in which case you would not know how
the form will react.

 

[efandango]

Thanks Marshall,

your explanation makes sense. I do try to makes sense of Access Help files,
but sometimes it is difficult to relate what they say to my issues and the
real world of novice users like me. Without people such as yourself, I don't
think MS Access would be nearly as succesful as Microsoft would like to
believe...


Meanwhile, if you're in the mood for getting your head around another
challenging problem, I have posted another question here: (it relates to the
same forms that we worked on, but makes them indicate when a matching record
actually exists)


http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

 

[efandango]

Marshall,

If there is no matching record, do you know how I can just make the event do
nothing, in other words just stay where it is and not go to the GetRounds
form?

I have changed the record/field designation from a series of 18 individual
fields to one single field, but based on a continous form.

changing from:
Me![Run_point_Venue_A]
Me![Run_point_Venue_B]
Me![Run_point_Venue_C] and so on...

to a single Me![Run_point_Venue]

At the moment, If I hit any of the other records for Me![Run_point_Venue],
the event goes to the first 'other' or 'blank' (as appropriate) record on the
GetRounds form

 

[Marshall Barton]

If there is no matching record, do you know how I can just make the event do
nothing, in other words just stay where it is and not go to the GetRounds
form?

I have changed the record/field designation from a series of 18 individual
fields to one single field, but based on a continous form.

changing from:
Me![Run_point_Venue_A]
Me![Run_point_Venue_B]
Me![Run_point_Venue_C] and so on...

to a single Me![Run_point_Venue]

At the moment, If I hit any of the other records for Me![Run_point_Venue],
the event goes to the first 'other' or 'blank' (as appropriate) record on the
GetRounds form

If you only want to move the focus when a match is found,
the move the two SetFocus lines down inside the If block.

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"
If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus
End If
End With

 

[efandango]

Thanks for replying Marshall. Your solution did the trick.

I came up with my own 'Heath Robinson' solution, but yours is more elegant.

My Code adjustment (to your original code)

If Me.Run_point_Venue = DLookup("[Note]", "tbl_Getrounds", "[Note] = """ &
Me![Run_point_Venue] & """") Then
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue] & """"

If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
End If

End With
End If

If Me.Run_point_Venue <> DLookup("[Note]", "tbl_Getrounds", "[Note] = '" &
"Me.Run_point_Venue" & "'") Then End

If there is no matching record, do you know how I can just make the event do
nothing, in other words just stay where it is and not go to the GetRounds
form?

I have changed the record/field designation from a series of 18 individual
fields to one single field, but based on a continous form.

changing from:
Me![Run_point_Venue_A]
Me![Run_point_Venue_B]
Me![Run_point_Venue_C] and so on...

to a single Me![Run_point_Venue]

At the moment, If I hit any of the other records for Me![Run_point_Venue],
the event goes to the first 'other' or 'blank' (as appropriate) record on the
GetRounds form

If you only want to move the focus when a match is found,
the move the two SetFocus lines down inside the If block.

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue_L] & """"
If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus
End If
End With

 

[Marshall Barton]

Thanks for replying Marshall. Your solution did the trick.

I came up with my own 'Heath Robinson' solution, but yours is more elegant.

My Code adjustment (to your original code)

If Me.Run_point_Venue = DLookup("[Note]", "tbl_Getrounds", "[Note] = """ &
Me![Run_point_Venue] & """") Then
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue] & """"

If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
End If

End With
End If

If Me.Run_point_Venue <> DLookup("[Note]", "tbl_Getrounds", "[Note] = '" &
"Me.Run_point_Venue" & "'") Then End

I don't see any reason for the first DLookup. It is
redundant and for a large table may be noticably slower.

The second DLookup will not work for several reasons. The
quoting is incorrect so it will never find anything and
using the End statement is almost always a bad thing to do.

 

[efandango]

Marshall,
Can you help with a very similar problem based on a variation of the code we
have been using.

I now have two new forms called:
frm_Waypoints (departure form)
frm_Road_Restrictions (Target Form)

It is the same principle as the other form. Where a field is marked; click
on it and go to the first available matching record. But with a slight twist.
This time I have either of two fields that need to be matched.

They are:
Road_Name_From
Road_Name_To

What I want the code to say is:

Yes, there is a record that has the matching field form from the ‘departure’
form, and here is the matching field on this ‘arrival’ form, and not just the
record. (in other words, actually go to the field as well as the record)

The code I have adapted below nearly works, except it goes to
[Road_Name_From] (obviously due to the hard-coding of this reference). I want
it to go to the relevant field and not the one prescribed in the code.

With Parent.[frm_Road_Restrictions].Form.RecordsetClone
.FindFirst "[Road_Name_From] = """ & Me![Run_waypoint] & """"
If Not .NoMatch Then

With Parent.[frm_Road_Restrictions].Form.RecordsetClone
.FindFirst "[Road_Name_To] = """ & Me![Run_waypoint] & """"
If Not .NoMatch Then

Parent.[frm_Road_Restrictions].Form.Bookmark = .Bookmark
Parent.[frm_Road_Restrictions].SetFocus
Parent.[frm_Road_Restrictions].Form.[Road_Name_From].SetFocus
End If
End With
End If
End With

Thanks for replying Marshall. Your solution did the trick.

I came up with my own 'Heath Robinson' solution, but yours is more elegant.

My Code adjustment (to your original code)

If Me.Run_point_Venue = DLookup("[Note]", "tbl_Getrounds", "[Note] = """ &
Me![Run_point_Venue] & """") Then
Parent.[frm_Getrounds].SetFocus
Parent.[frm_Getrounds].Form.[Note].SetFocus

With Parent.[frm_Getrounds].Form.RecordsetClone
.FindFirst "[Note] = """ & Me![Run_point_Venue] & """"

If Not .NoMatch Then
Parent.[frm_Getrounds].Form.Bookmark = .Bookmark
End If

End With
End If

If Me.Run_point_Venue <> DLookup("[Note]", "tbl_Getrounds", "[Note] = '" &
"Me.Run_point_Venue" & "'") Then End

I don't see any reason for the first DLookup. It is
redundant and for a large table may be noticably slower.

The second DLookup will not work for several reasons. The
quoting is incorrect so it will never find anything and
using the End statement is almost always a bad thing to do.

 

[efandango]

Marshall,

That nearly does it, but if there are two or more records like this, and
'High Street' is the relevant/found field, it will go to the 2nd record
(correct field) and ignore the 1st record (also correct field). It is as if
the 'bias' is on the [Road_Name_From] field, and it ignores the first found
instance of the relevant field.


Record 1
[Road_Name_From] [Road_Name_To]
High Street Main Street

Record 2
[Road_Name_From] [Road_Name_To]
Main Street High Street

Can you help with a very similar problem based on a variation of the code we
have been using.

I now have two new forms called:
frm_Waypoints (departure form)
frm_Road_Restrictions (Target Form)

It is the same principle as the other form. Where a field is marked; click
on it and go to the first available matching record. But with a slight twist.
This time I have either of two fields that need to be matched.

They are:
Road_Name_From
Road_Name_To

What I want the code to say is:

Yes, there is a record that has the matching field form from the ‘departure’
form, and here is the matching field on this ‘arrival’ form, and not just the
record. (in other words, actually go to the field as well as the record)

The code I have adapted below nearly works, except it goes to
[Road_Name_From] (obviously due to the hard-coding of this reference). I want
it to go to the relevant field and not the one prescribed in the code.

With Parent.[frm_Road_Restrictions].Form.RecordsetClone
.FindFirst "[Road_Name_From] = """ & Me![Run_waypoint] & """"
If Not .NoMatch Then

With Parent.[frm_Road_Restrictions].Form.RecordsetClone
.FindFirst "[Road_Name_To] = """ & Me![Run_waypoint] & """"
If Not .NoMatch Then

Parent.[frm_Road_Restrictions].Form.Bookmark = .Bookmark
Parent.[frm_Road_Restrictions].SetFocus
Parent.[frm_Road_Restrictions].Form.[Road_Name_From].SetFocus
End If
End With
End If
End With

I think this might be close to what you're looking for:

Dim rs As DAO.Recordset
. . .
With Parent.frm_Road_Restrictions.Form
Set rs = .RecordsetClone
rs.FindFirst "Road_Name_From=""" & Me!Run_waypoint & """"
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Parent.[frm_Road_Restrictions].SetFocus
.Road_Name_From.SetFocus
Else
rs.FindFirst "Road_Name_To=""" & Me!Run_waypoint & """"
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Parent.[frm_Road_Restrictions].SetFocus
.Road_Name_To.SetFocus
Else
MsgBox "no matching record found"
End If
End If
Set rs = Nothing
End With

 

[efandango]

That's clever..., and it works!

You used the AbsolutePosition to mark where the record sits in the recordset
object, and then compared the variable lngFromPos to its current position,
and if it is less than it was, which would make it equal to the NOTFROM
constant, then it must be a 'To' field. If it's not equal to NOTFROM then it
must be a 'From' field. At this point, you have probaly guessed I don't
really know what i'm talking about and underlines that old adage about 'a
little knowledge being a dagerous thing...'

I wouldn't really know where to start, let alone finish a solution like you
have come up with, so the best thing I can do is thank you sincerely for your
help, and hope that my little challenges provide you with enough real world
access/vab problem solving as to keep you stimulated enough to provide
answers as neat as you do.

have a nice day+1

Let's give something more like this a try. Your homework is
to figure out how it works and provide comments ;-)

Dim rs As DAO.Recordset
Dim lngFromPos As Long
Dim strFromTo As String
Const NOTFROM As Long = 77E7

lngFromPos = NOTFROM
With Parent.frm_Road_Restrictions.Form
Set rs = .RecordsetClone

rs.FindFirst "Road_Name_From='" & Me.Run_waypoint & "' "
If Not rs.NoMatch Then
lngFromPos = rs.AbsolutePosition
End If

rs.FindFirst "Road_Name_To='" & Me.Run_waypoint & "' "
If Not rs.NoMatch Then
If rs.AbsolutePosition < lngFromPos Then
strFromTo = "To"
Else
rs.AbsolutePosition = lngFromPos
strFromTo = "From"
End If
ElseIf lngFromPos <> NOTFROM Then
rs.AbsolutePosition = lngFromPos
strFromTo = "From"
End If

If strFromTo = "" Then
MsgBox "no matching record found"
Else
.Bookmark = rs.Bookmark
Parent.frm_Road_Restrictions.SetFocus
.Controls("Road_Name_" & strFromTo).SetFocus
End If

Set rs = Nothing
End With
--
Marsh
MVP [MS Access]

That nearly does it, but if there are two or more records like this, and
'High Street' is the relevant/found field, it will go to the 2nd record
(correct field) and ignore the 1st record (also correct field). It is as if
the 'bias' is on the [Road_Name_From] field, and it ignores the first found
instance of the relevant field.

Record 1
[Road_Name_From] [Road_Name_To]
High Street Main Street

Record 2
[Road_Name_From] [Road_Name_To]
Main Street High Street

efandango wrote:
Can you help with a very similar problem based on a variation of the code we
have been using.

I now have two new forms called:
frm_Waypoints (departure form)
frm_Road_Restrictions (Target Form)

It is the same principle as the other form. Where a field is marked; click
on it and go to the first available matching record. But with a slight twist.
This time I have either of two fields that need to be matched.

They are:
Road_Name_From
Road_Name_To
[snip]

I think this might be close to what you're looking for:

Dim rs As DAO.Recordset
. . .
With Parent.frm_Road_Restrictions.Form
Set rs = .RecordsetClone
rs.FindFirst "Road_Name_From=""" & Me!Run_waypoint & """"
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Parent.[frm_Road_Restrictions].SetFocus
.Road_Name_From.SetFocus
Else
rs.FindFirst "Road_Name_To=""" & Me!Run_waypoint & """"
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Parent.[frm_Road_Restrictions].SetFocus
.Road_Name_To.SetFocus
Else
MsgBox "no matching record found"
End If
End If
Set rs = Nothing
End With