Need help with this code to find specific record

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I have a form that has a field called Run_No, when I click it, it will take
me to the matching Run_No on another form 'frm_Runs'. So far, so good. But I
need it to take me first to the Run No, and then to the matching 'Point Id'
on the 'frm-points' form which is a subform of frm_Runs; as in this example:

Forms![frm_Runs]![frm_Points].Form![Point_ID]

So in summary; I want it to first match the Run_No, and then the Point_ID,
and go to that form/subform/record.

I have this code below, that will do the first part, eg: find the matching
Run_No, but I don't know how to incporporate the second part to include the
matching Point_ID

my code:

Private Sub Run_No_Click()
Dim rs As DAO.Recordset

With Forms!frm_Runs
Set rs = .RecordsetClone
' rs.FindFirst "Run_No=""" & Me!Run_No & """"
rs.FindFirst "Run_No=" & Me!Run_No & ""
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Forms![frm_Runs]![Run_No].SetFocus
'Parent.[frm_Run_No].SetFocus
.Run_No.SetFocus

Else
MsgBox "no matching record found"
End If
'End If
Set rs = Nothing
End With
End Sub
 
efandango said:
I have a form that has a field called Run_No, when I click it, it will take
me to the matching Run_No on another form 'frm_Runs'. So far, so good. But
I
need it to take me first to the Run No, and then to the matching 'Point
Id'
on the 'frm-points' form which is a subform of frm_Runs; as in this
example:

Forms![frm_Runs]![frm_Points].Form![Point_ID]

So in summary; I want it to first match the Run_No, and then the Point_ID,
and go to that form/subform/record.

I have this code below, that will do the first part, eg: find the matching
Run_No, but I don't know how to incporporate the second part to include
the
matching Point_ID

my code:

Private Sub Run_No_Click()
Dim rs As DAO.Recordset

With Forms!frm_Runs
Set rs = .RecordsetClone
' rs.FindFirst "Run_No=""" & Me!Run_No & """"
rs.FindFirst "Run_No=" & Me!Run_No & ""
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Forms![frm_Runs]![Run_No].SetFocus
'Parent.[frm_Run_No].SetFocus
.Run_No.SetFocus

Else
MsgBox "no matching record found"
End If
'End If
Set rs = Nothing
End With
End Sub
Click to expand...

Set the 'Link Master Field' property of your subform control to Run_No and
the 'Link Child Fields' property to Point_ID
 
I know how to link a main/subform together; but that is not waht I had in
mind. The from I am coming from is not a 'true' subform, and is independent
of any other form. The form I want to go to is a 'real' subform, but that is
not really relevant here.

At the moment, if I click the Run_No on my continous form, it will take me
to the matching main form with the same Run_No. So far so good; but what I
really want to do is click on the current record which also contains the
[Point_ID], and that will then take me to the relevant [Point_ID] on the
'Forms!frm_Runs!frm_Points.Form!Point_ID.



Stuart McCall said:
efandango said:
I have a form that has a field called Run_No, when I click it, it will take
me to the matching Run_No on another form 'frm_Runs'. So far, so good. But
I
need it to take me first to the Run No, and then to the matching 'Point
Id'
on the 'frm-points' form which is a subform of frm_Runs; as in this
example:

Forms![frm_Runs]![frm_Points].Form![Point_ID]

So in summary; I want it to first match the Run_No, and then the Point_ID,
and go to that form/subform/record.

I have this code below, that will do the first part, eg: find the matching
Run_No, but I don't know how to incporporate the second part to include
the
matching Point_ID

my code:

Private Sub Run_No_Click()
Dim rs As DAO.Recordset

With Forms!frm_Runs
Set rs = .RecordsetClone
' rs.FindFirst "Run_No=""" & Me!Run_No & """"
rs.FindFirst "Run_No=" & Me!Run_No & ""
If Not rs.NoMatch Then
.Bookmark = rs.Bookmark
Forms![frm_Runs]![Run_No].SetFocus
'Parent.[frm_Run_No].SetFocus
.Run_No.SetFocus

Else
MsgBox "no matching record found"
End If
'End If
Set rs = Nothing
End With
End Sub
Click to expand...

Set the 'Link Master Field' property of your subform control to Run_No and
the 'Link Child Fields' property to Point_ID
Click to expand...
 
efandango said:
I know how to link a main/subform together; but that is not waht I
had in mind. The from I am coming from is not a 'true' subform, and
is independent of any other form. The form I want to go to is a
'real' subform, but that is not really relevant here.

At the moment, if I click the Run_No on my continous form, it will
take me to the matching main form with the same Run_No. So far so
good; but what I really want to do is click on the current record
which also contains the [Point_ID], and that will then take me to the
relevant [Point_ID] on the 'Forms!frm_Runs!frm_Points.Form!Point_ID.
Click to expand...
I'm not sure what the relationship between your "continuos form", the "main
form" and your "independent " form is but if Point_ID is unique than you
would use the same method you used with Run_No

Why does this have to be an independent form?
 
Mike,

I have a Main Form 'frm_Runs', which is on a tab page. On that mainform is a
subform called 'frm_points', these two are linked using the [Run_No].

On the 'frm_Points' are 18 records (per Run No) with a unique ID [Points_ID]


On another tab page is a continouse form which is independent of any other
form. that form is called 'frm_Points_Test'. It runs a query which randomly
selects 18 records from any set of [Run_No]/[Points_ID]. This allows the user
to test themselves about details of any given point. All of this works
perfectly.

But from time to time, the user may want to know more about a given record,
and therefore will want to go to the appropriate matching Point_ID. The best
I could do so far is to allow the user to click on the Run No of the relevant
record which then takes the user to the matching page of records for said Run
No. But once the user arrives at the target form, he still has to look for
the actual [Point_ID] that they clicked on from the continouse form.
Therefore, I thought it would be very useful if they were first taken to the
correct matching Run No, and then to the actual record that matches the
[Point_ID] of the record that they actually clicked.

So, it is not really a case of it having to be an 'independent form', but
that it cannot, and doesn't have to be anything else other than independent.

This is what the continous (quiz) form looks like

Run_No Question Point_ID(hidden) Answer(combo)

3 Main St 243432 ?
46 South St 321434 ?
73 Garden Row 143445 ?
5 North Crescent 943423 ?


you said "Point_ID is unique than you would use the same method you used
with Run_No"

It is unique, but I don't know how to ammend the code to incorporate the
[Point_ID], which is what i really need help with.




Mike Painter said:
efandango said:
I know how to link a main/subform together; but that is not waht I
had in mind. The from I am coming from is not a 'true' subform, and
is independent of any other form. The form I want to go to is a
'real' subform, but that is not really relevant here.

At the moment, if I click the Run_No on my continous form, it will
take me to the matching main form with the same Run_No. So far so
good; but what I really want to do is click on the current record
which also contains the [Point_ID], and that will then take me to the
relevant [Point_ID] on the 'Forms!frm_Runs!frm_Points.Form!Point_ID.
Click to expand...
I'm not sure what the relationship between your "continuos form", the "main
form" and your "independent " form is but if Point_ID is unique than you
would use the same method you used with Run_No

Why does this have to be an independent form?
Click to expand...
 
efandango said:
Mike,

I have a Main Form 'frm_Runs', which is on a tab page. On that
mainform is a subform called 'frm_points', these two are linked using
the [Run_No].

On the 'frm_Points' are 18 records (per Run No) with a unique ID
[Points_ID]
Click to expand...
But from time to time, the user may want to know more about a given
record, and therefore will want to go to the appropriate matching
Point_ID. The best I could do so far is to allow the user to click on
the Run No of the relevant record which then takes the user to the
matching page of records for said Run No. But once the user arrives
at the target form, he still has to look for the actual [Point_ID]
that they clicked on from the continouse form. Therefore, I thought
it would be very useful if they were first taken to the correct
matching Run No, and then to the actual record that matches the
[Point_ID] of the record that they actually clicked.
Click to expand...

The Northwind database and many places on the net show you how to link
related subforms.
On the main form you place a (hidden) field that contains the Point_ID of
either teh record the cursor is on or fills that field when the Point_ID is
clicked on.
This field is used to relate a subform that shpws the detail you want.
You could also pop a form based on a query Point_ID = YourFormTextBox.
 
Mike,

That is not quite what I am looking for. All of my forms are synching just
as I want them to. The form in question is simply not linkable because it is
based on a query which displays a number of random records from many master
record numbers. instead of thinking of it as a conventional 'Linked' form,
try to see it as a 'report' form, that displays the results of a query in
random order (and therefore unlinkable).

I don't want to reinvent the wheel by reassinging links to forms that do not
require it. I just want to click on the record(s) from this unlinked form and
have it search through the record index and go to the Master Run_No, and then
to the Point_ID on the Main/Subform.





Mike Painter said:
efandango said:
Mike,

I have a Main Form 'frm_Runs', which is on a tab page. On that
mainform is a subform called 'frm_points', these two are linked using
the [Run_No].

On the 'frm_Points' are 18 records (per Run No) with a unique ID
[Points_ID]
Click to expand...
But from time to time, the user may want to know more about a given
record, and therefore will want to go to the appropriate matching
Point_ID. The best I could do so far is to allow the user to click on
the Run No of the relevant record which then takes the user to the
matching page of records for said Run No. But once the user arrives
at the target form, he still has to look for the actual [Point_ID]
that they clicked on from the continouse form. Therefore, I thought
it would be very useful if they were first taken to the correct
matching Run No, and then to the actual record that matches the
[Point_ID] of the record that they actually clicked.
Click to expand...

The Northwind database and many places on the net show you how to link
related subforms.
On the main form you place a (hidden) field that contains the Point_ID of
either teh record the cursor is on or fills that field when the Point_ID is
clicked on.
This field is used to relate a subform that shpws the detail you want.
You could also pop a form based on a query Point_ID = YourFormTextBox.
Click to expand...
 
This is what I now have, which goes to the correct 'master' Run_No, and then
to the first [Point_ID], but not the actual point ID that i clicked on from
the 'source' form:

Dim RS As DAO.Recordset

With Forms!frm_Runs
Set RS = .RecordsetClone
' rs.FindFirst "Run_No=""" & Me!Run_No & """"
RS.FindFirst "Run_No=" & Me!Run_No & ""
If Not RS.NoMatch Then
.Bookmark = RS.Bookmark
Forms![frm_Runs]![Run_No].SetFocus
'Parent.[frm_Run_No].SetFocus
.Run_No.SetFocus

Else
MsgBox "no matching record found"
End If

Set RS = Nothing
Forms![frm_Runs]![frm_Points].SetFocus
Forms![frm_Runs]![frm_Points].Form![Point_ID].SetFocus



End With

Mike Painter said:
efandango said:
Mike,

I have a Main Form 'frm_Runs', which is on a tab page. On that
mainform is a subform called 'frm_points', these two are linked using
the [Run_No].

On the 'frm_Points' are 18 records (per Run No) with a unique ID
[Points_ID]
Click to expand...
But from time to time, the user may want to know more about a given
record, and therefore will want to go to the appropriate matching
Point_ID. The best I could do so far is to allow the user to click on
the Run No of the relevant record which then takes the user to the
matching page of records for said Run No. But once the user arrives
at the target form, he still has to look for the actual [Point_ID]
that they clicked on from the continouse form. Therefore, I thought
it would be very useful if they were first taken to the correct
matching Run No, and then to the actual record that matches the
[Point_ID] of the record that they actually clicked.
Click to expand...

The Northwind database and many places on the net show you how to link
related subforms.
On the main form you place a (hidden) field that contains the Point_ID of
either teh record the cursor is on or fills that field when the Point_ID is
clicked on.
This field is used to relate a subform that shpws the detail you want.
You could also pop a form based on a query Point_ID = YourFormTextBox.
Click to expand...
 
Mike, I have since solved the problem and now have a nice working system that
will take me to the exact point.

thanks for your input anyway.

Mike Painter said:
efandango said:
Mike,

I have a Main Form 'frm_Runs', which is on a tab page. On that
mainform is a subform called 'frm_points', these two are linked using
the [Run_No].

On the 'frm_Points' are 18 records (per Run No) with a unique ID
[Points_ID]
Click to expand...
But from time to time, the user may want to know more about a given
record, and therefore will want to go to the appropriate matching
Point_ID. The best I could do so far is to allow the user to click on
the Run No of the relevant record which then takes the user to the
matching page of records for said Run No. But once the user arrives
at the target form, he still has to look for the actual [Point_ID]
that they clicked on from the continouse form. Therefore, I thought
it would be very useful if they were first taken to the correct
matching Run No, and then to the actual record that matches the
[Point_ID] of the record that they actually clicked.
Click to expand...

The Northwind database and many places on the net show you how to link
related subforms.
On the main form you place a (hidden) field that contains the Point_ID of
either teh record the cursor is on or fills that field when the Point_ID is
clicked on.
This field is used to relate a subform that shpws the detail you want.
You could also pop a form based on a query Point_ID = YourFormTextBox.
Click to expand...
 
efandango said:
Mike,

That is not quite what I am looking for. All of my forms are synching
just as I want them to. The form in question is simply not linkable
because it is based on a query which displays a number of random
records from many master record numbers. instead of thinking of it as
a conventional 'Linked' form, try to see it as a 'report' form, that
displays the results of a query in random order (and therefore
unlinkable).

I don't want to reinvent the wheel by reassinging links to forms that
do not require it. I just want to click on the record(s) from this
unlinked form and have it search through the record index and go to
the Master Run_No, and then to the Point_ID on the Main/Subform.
Click to expand...
If it is a form the information in it can be used to open another form.
It does not matter if it is based on a query or not (most forms are based on
queries and it can be argued that all should be.) All related subforms are
based on a query
It does not matter how the query is made, once the form is opened the
information can be used.
The easiest way to do it is with a form, subform design and that is the way
most of the examples you will find handle it.
There are other ways but the bottom line remains that if a key to the
information you want appears in *any* form you can use that information to
open another form.

Popup forms are used all the time to add new records to an existing table.
It is common to use OpenArgs to carry information already entered in FormA
over to the pop up.
 
Back
Top