Record Select

[Guest]

I have a subform that lists all my rercords in a continuouos form. Two of my
fields are called "MinDia" and "Max Dia". What I need to do is use an unbound
control on my main form to enter a diameter and have the record selected that
the diameter falls in between the min/mas diameters. How can I set this up?

 

[Guest]

Criteria for "MinDia" would be >=[Forms]![YourForm]![YourUnboundControl] and
for "Max Dia" <=[Forms]![YourForm]![YourUnboundControl].

 

[Guest]

I'm not sure I understand what you mean. The "MinDia' & "MaxDia" have values
in them since they are part of my table. I just want to be able jump to that
record when a diameter that is entered is in between those two data fields.

Criteria for "MinDia" would be >=[Forms]![YourForm]![YourUnboundControl] and
for "Max Dia" <=[Forms]![YourForm]![YourUnboundControl].

I have a subform that lists all my rercords in a continuouos form. Two of my
fields are called "MinDia" and "Max Dia". What I need to do is use an unbound
control on my main form to enter a diameter and have the record selected that
the diameter falls in between the min/mas diameters. How can I set this up?

 

[PCD]

Put the following code in the AfterUpdate event of your unbound control:
Dim Rst As DAO.Recordset
Set Rst = Me!NameOfMainForm!NameOfSubformCONTROL.Form.RecordsetClone
Rst.FindFirst "[MinDia] >= " & Me!NameOfUnboundControl _
& " And [MaxDia] <= " & Me!NameOfUnboundControl
Me!NameOfMainForm!NameOfSubformCONTROL.Form.Bookmark = Rst.BookMark
Rst.Close
Set Rst = Nothing

Note: You might want to add additional code to the above to assure that the
unbound
control value is equal to or greater than the smallest MinDia in your
continuouse form and
that the unbound control is less than or equal to the largest MaxDia in your
continuouse form.

 

[Guest]

I do have one question. In your code below what does "NameOfSubformCONTROL"
represent? Not sure what this should be.

Put the following code in the AfterUpdate event of your unbound control:
Dim Rst As DAO.Recordset
Set Rst = Me!NameOfMainForm!NameOfSubformCONTROL.Form.RecordsetClone
Rst.FindFirst "[MinDia] >= " & Me!NameOfUnboundControl _
& " And [MaxDia] <= " & Me!NameOfUnboundControl
Me!NameOfMainForm!NameOfSubformCONTROL.Form.Bookmark = Rst.BookMark
Rst.Close
Set Rst = Nothing

Note: You might want to add additional code to the above to assure that the
unbound
control value is equal to or greater than the smallest MinDia in your
continuouse form and
that the unbound control is less than or equal to the largest MaxDia in your
continuouse form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
(e-mail address removed)

I have a subform that lists all my rercords in a continuouos form. Two of
my
fields are called "MinDia" and "Max Dia". What I need to do is use an
unbound
control on my main form to enter a diameter and have the record selected
that
the diameter falls in between the min/mas diameters. How can I set this
up?

 

[PCD]

Open your main form in design view, select the control that contains the
subform, open properties, go to the Other tab and look at the Name property.
The name you see here is what you use for NameOfSubformCONTROL. Frequently
the mistake is made to use the name of the actual subform rather than
correctly using the name of the subform control. The name of the subform
control may or may not be the same as the name of the subform control.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
(e-mail address removed)

I do have one question. In your code below what does "NameOfSubformCONTROL"
represent? Not sure what this should be.

Put the following code in the AfterUpdate event of your unbound control:
Dim Rst As DAO.Recordset
Set Rst = Me!NameOfMainForm!NameOfSubformCONTROL.Form.RecordsetClone
Rst.FindFirst "[MinDia] >= " & Me!NameOfUnboundControl _
& " And [MaxDia] <= " & Me!NameOfUnboundControl
Me!NameOfMainForm!NameOfSubformCONTROL.Form.Bookmark = Rst.BookMark
Rst.Close
Set Rst = Nothing

Note: You might want to add additional code to the above to assure that
the
unbound
control value is equal to or greater than the smallest MinDia in your
continuouse form and
that the unbound control is less than or equal to the largest MaxDia in
your
continuouse form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1150 users have come to me from the newsgroups requesting
help
(e-mail address removed)

I have a subform that lists all my rercords in a continuouos form. Two
of
my
fields are called "MinDia" and "Max Dia". What I need to do is use an
unbound
control on my main form to enter a diameter and have the record
selected
that
the diameter falls in between the min/mas diameters. How can I set this
up?

 

[Guest]

Ok here is the code that I used but I'm getting an error message saying that
it can't find the field "frmShafts". Did I do something wrong?

Private Sub Diameter1_AfterUpdate()
Dim Rst As DAO.Recordset

Set Rst = Me!frmShafts!qryShafts_subform.Form.RecordsetClone
Rst.FindFirst "[MinDia]>=" & Me!Diameter1 & " And [MaxDia]<=" & Me!Diameter1
Me!frmShafts!qryShafts_subform.Form.Bookmark = Rst.Bookmark
Rst.Close
Set Rst = Nothing


End Sub

Open your main form in design view, select the control that contains the
subform, open properties, go to the Other tab and look at the Name property.
The name you see here is what you use for NameOfSubformCONTROL. Frequently
the mistake is made to use the name of the actual subform rather than
correctly using the name of the subform control. The name of the subform
control may or may not be the same as the name of the subform control.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
(e-mail address removed)

I do have one question. In your code below what does "NameOfSubformCONTROL"
represent? Not sure what this should be.

Put the following code in the AfterUpdate event of your unbound control:
Dim Rst As DAO.Recordset
Set Rst = Me!NameOfMainForm!NameOfSubformCONTROL.Form.RecordsetClone
Rst.FindFirst "[MinDia] >= " & Me!NameOfUnboundControl _
& " And [MaxDia] <= " & Me!NameOfUnboundControl
Me!NameOfMainForm!NameOfSubformCONTROL.Form.Bookmark = Rst.BookMark
Rst.Close
Set Rst = Nothing

Note: You might want to add additional code to the above to assure that
the
unbound
control value is equal to or greater than the smallest MinDia in your
continuouse form and
that the unbound control is less than or equal to the largest MaxDia in
your
continuouse form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1150 users have come to me from the newsgroups requesting
help
(e-mail address removed)




message I have a subform that lists all my rercords in a continuouos form. Two
of
my
fields are called "MinDia" and "Max Dia". What I need to do is use an
unbound
control on my main form to enter a diameter and have the record
selected
that
the diameter falls in between the min/mas diameters. How can I set this
up?

 

[PCD]

Sorry, I made a mistake!!

You don't use the name of the main form. "Me" represents the main form. Take
out frmShafts from your code so it looks like:
Set Rst = Me!qryShafts_subform.Form.RecordsetClone
Rst.FindFirst "[MinDia]>=" & Me!Diameter1 & " And [MaxDia]<=" & Me!Diameter1
Me!qryShafts_subform.Form.Bookmark = Rst.Bookmark
Rst.Close
Set Rst = Nothing

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1150 users have come to me from the newsgroups requesting help
(e-mail address removed)

Ok here is the code that I used but I'm getting an error message saying
that
it can't find the field "frmShafts". Did I do something wrong?

Private Sub Diameter1_AfterUpdate()
Dim Rst As DAO.Recordset

Set Rst = Me!frmShafts!qryShafts_subform.Form.RecordsetClone
Rst.FindFirst "[MinDia]>=" & Me!Diameter1 & " And [MaxDia]<=" &
Me!Diameter1
Me!frmShafts!qryShafts_subform.Form.Bookmark = Rst.Bookmark
Rst.Close
Set Rst = Nothing


End Sub

Open your main form in design view, select the control that contains the
subform, open properties, go to the Other tab and look at the Name
property.
The name you see here is what you use for NameOfSubformCONTROL.
Frequently
the mistake is made to use the name of the actual subform rather than
correctly using the name of the subform control. The name of the subform
control may or may not be the same as the name of the subform control.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1150 users have come to me from the newsgroups requesting
help
(e-mail address removed)

I do have one question. In your code below what does
"NameOfSubformCONTROL"
represent? Not sure what this should be.

:

Put the following code in the AfterUpdate event of your unbound
control:
Dim Rst As DAO.Recordset
Set Rst = Me!NameOfMainForm!NameOfSubformCONTROL.Form.RecordsetClone
Rst.FindFirst "[MinDia] >= " & Me!NameOfUnboundControl _
& " And [MaxDia] <= " & Me!NameOfUnboundControl
Me!NameOfMainForm!NameOfSubformCONTROL.Form.Bookmark = Rst.BookMark
Rst.Close
Set Rst = Nothing

Note: You might want to add additional code to the above to assure
that
the
unbound
control value is equal to or greater than the smallest MinDia in your
continuouse form and
that the unbound control is less than or equal to the largest MaxDia
in
your
continuouse form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word
Applications
Over 1150 users have come to me from the newsgroups requesting
help
(e-mail address removed)




message I have a subform that lists all my rercords in a continuouos form.
Two
of
my
fields are called "MinDia" and "Max Dia". What I need to do is use
an
unbound
control on my main form to enter a diameter and have the record
selected
that
the diameter falls in between the min/mas diameters. How can I set
this
up?