Record Selector

G

Guest

I have a form & subform that I'm trying to create a record selector with. My
subform has my records in a continuous form and my main form has an unbound
control. There are two controls on my subform called "MinDia" and "MaxDia".
What I want to do is be able to enter a diameter into my unbound control on
my main form and have it select/bookmark a record on the subform that the
diameter falls in between the "MinDia" and "MaxDia". Here is the code I'm
trying to use but it isn't working. Can anyone shed some light on this for me?

Dim MyRecSet As Object
Set MyRecSet = Me.frmNominalsSubform.Form.RecordsetClone

MyRecSet.FindFirst "[MinDia]>=" & Me.Diameter & " And [MaxDia ]<=" &
Me.Diameter

If Not MyRecSet.EOF Then
Me.frmNominalsSubform.Form.Bookmark = MyRecSet.Bookmark

End If
 
T

TC

For code in the main form, the recordsetclone of a subform is:

me![SFCTL].form.recordsetclone

where SFCTL is the name of the subform /control/ on the main form. You
do not need, or use, the name of the /form within/ the subform control.

HTH,
TC (MVP Access)
http://tc2.atspace.com
 
P

PCD

Try changing Dim MyRecSet As Object to:
Dim MyRecSet As DAO.Recordset

and

......Me.frmNominalsSubform.Form.RecordsetClone to:
......Me!frmNominalsSubform.Form.RecordsetClone

and

.....Me.Diameter & " And [MaxDia ]<=" & Me.Diameter to:
.....Me!Diameter & " And [MaxDia ]<=" & Me!Diameter

and

Me.frmNominalsSubform.Form.Bookmark.... to:
Me!frmNominalsSubform.Form.Bookmark

Remove the following:
If Not MyRecSet.EOF Then
End If

(Keep the line inbetween the above)
 
A

Allen Browne

Suggestions:

1. Test NoMatch rather than EOF, i.e.:
If Not MyRecSet.NoMatch Then

2. Is Diameter the name of the unbound control? (Don't laugh: it's not
uncommon for people to use the name of the bound control in the code instead
of the unbound search text box.)

3. Are MinDia and MaxDia both Number type fields (not Text fields)?

4. Did you test if Diameter is null? (The WHERE string will be malformed if
it is.)

5. Set Format property for Diameter to General Number or similar, so only
valid numbers are accepted.

6. The search will cover only records that are currently in the subform. If
you actually need it to move the main form to another record so it can find
the record that has the match in the subform, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
G

Guest

I always get confused on the which sign to use where (<,>), so I do this:

Lets say MinDia = 3 and MaxDia = 6.
The Dia you enter = 4.

The criteria then evals to

[MinDia]>= Me.Diameter And [MaxDia ]<= Me.Diameter
3 4 6 4

Is 3 >= 4 and 6<=4?

This is obviously wrong; if the signs were changed to:

[MinDia]<= Me.Diameter And [MaxDia ]>= Me.Diameter
3 4 6 4

then you have 3<=4 and 6>=4 ; it now makes sense.

So the line should be:

MyRecSet.FindFirst "[MinDia]<=" & Me.Diameter & " And [MaxDia ]>=" &
Me.Diameter

HTH
 
G

Guest

from Rainbow01 Hong Kong

u can try to direct use FindRecord method for subform
in your mainform:
Me.SubFormName.Setfocus
Docmd.FindRecord ..........


"Secret Squirrel" 來函：
 
G

Guest

I tried all of your suggestions below with the exception of #6. I only need
it to do a search for records that are currently in my subform. And it's
still not working. I did try a new code setup and for one split second I can
see it select/bookmark the record but then it just goes back to the top
record. Any ideas what might be wrong now?

Me.frmNominalsSubform.Form.RecordsetClone.FindFirst _
"[MinDia]<=" & Me.Diameter & " And [MaxDia ]>=" & Me.Diameter
If Not Me.frmNominalsSubform.Form.RecordsetClone.EOF Then
Me.frmNominalsSubform.Form.Bookmark = _
Me.frmNominalsSubform.Form.RecordsetClone.Bookmark
DoEvents
End If

Allen Browne said:
Suggestions:

1. Test NoMatch rather than EOF, i.e.:
If Not MyRecSet.NoMatch Then

2. Is Diameter the name of the unbound control? (Don't laugh: it's not
uncommon for people to use the name of the bound control in the code instead
of the unbound search text box.)

3. Are MinDia and MaxDia both Number type fields (not Text fields)?

4. Did you test if Diameter is null? (The WHERE string will be malformed if
it is.)

5. Set Format property for Diameter to General Number or similar, so only
valid numbers are accepted.

6. The search will cover only records that are currently in the subform. If
you actually need it to move the main form to another record so it can find
the record that has the match in the subform, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I have a form & subform that I'm trying to create a record selector with.
My
subform has my records in a continuous form and my main form has an
unbound
control. There are two controls on my subform called "MinDia" and
"MaxDia".
What I want to do is be able to enter a diameter into my unbound control
on
my main form and have it select/bookmark a record on the subform that the
diameter falls in between the "MinDia" and "MaxDia". Here is the code I'm
trying to use but it isn't working. Can anyone shed some light on this for
me?

Dim MyRecSet As Object
Set MyRecSet = Me.frmNominalsSubform.Form.RecordsetClone

MyRecSet.FindFirst "[MinDia]>=" & Me.Diameter & " And [MaxDia ]<=" &
Me.Diameter

If Not MyRecSet.EOF Then
Me.frmNominalsSubform.Form.Bookmark = MyRecSet.Bookmark

End If
Click to expand...
Click to expand...
 
A

Allen Browne

Still not testing NoMatch, eh?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I tried all of your suggestions below with the exception of #6. I only need
it to do a search for records that are currently in my subform. And it's
still not working. I did try a new code setup and for one split second I
can
see it select/bookmark the record but then it just goes back to the top
record. Any ideas what might be wrong now?

Me.frmNominalsSubform.Form.RecordsetClone.FindFirst _
"[MinDia]<=" & Me.Diameter & " And [MaxDia ]>=" & Me.Diameter
If Not Me.frmNominalsSubform.Form.RecordsetClone.EOF Then
Me.frmNominalsSubform.Form.Bookmark = _
Me.frmNominalsSubform.Form.RecordsetClone.Bookmark
DoEvents
End If

Allen Browne said:
Suggestions:

1. Test NoMatch rather than EOF, i.e.:
If Not MyRecSet.NoMatch Then

2. Is Diameter the name of the unbound control? (Don't laugh: it's not
uncommon for people to use the name of the bound control in the code
instead
of the unbound search text box.)

3. Are MinDia and MaxDia both Number type fields (not Text fields)?

4. Did you test if Diameter is null? (The WHERE string will be malformed
if
it is.)

5. Set Format property for Diameter to General Number or similar, so only
valid numbers are accepted.

6. The search will cover only records that are currently in the subform.
If
you actually need it to move the main form to another record so it can
find
the record that has the match in the subform, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

message
I have a form & subform that I'm trying to create a record selector
with.
My
subform has my records in a continuous form and my main form has an
unbound
control. There are two controls on my subform called "MinDia" and
"MaxDia".
What I want to do is be able to enter a diameter into my unbound
control
on
my main form and have it select/bookmark a record on the subform that
the
diameter falls in between the "MinDia" and "MaxDia". Here is the code
I'm
trying to use but it isn't working. Can anyone shed some light on this
for
me?

Dim MyRecSet As Object
Set MyRecSet = Me.frmNominalsSubform.Form.RecordsetClone

MyRecSet.FindFirst "[MinDia]>=" & Me.Diameter & " And [MaxDia ]<=" &
Me.Diameter

If Not MyRecSet.EOF Then
Me.frmNominalsSubform.Form.Bookmark = MyRecSet.Bookmark

End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yep, tried the NoMatch but it's still doing the same thing. It selects it for
a split second and then selects the top record.

Allen Browne said:
Still not testing NoMatch, eh?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I tried all of your suggestions below with the exception of #6. I only need
it to do a search for records that are currently in my subform. And it's
still not working. I did try a new code setup and for one split second I
can
see it select/bookmark the record but then it just goes back to the top
record. Any ideas what might be wrong now?

Me.frmNominalsSubform.Form.RecordsetClone.FindFirst _
"[MinDia]<=" & Me.Diameter & " And [MaxDia ]>=" & Me.Diameter
If Not Me.frmNominalsSubform.Form.RecordsetClone.EOF Then
Me.frmNominalsSubform.Form.Bookmark = _
Me.frmNominalsSubform.Form.RecordsetClone.Bookmark
DoEvents
End If

Allen Browne said:
Suggestions:

1. Test NoMatch rather than EOF, i.e.:
If Not MyRecSet.NoMatch Then

2. Is Diameter the name of the unbound control? (Don't laugh: it's not
uncommon for people to use the name of the bound control in the code
instead
of the unbound search text box.)

3. Are MinDia and MaxDia both Number type fields (not Text fields)?

4. Did you test if Diameter is null? (The WHERE string will be malformed
if
it is.)

5. Set Format property for Diameter to General Number or similar, so only
valid numbers are accepted.

6. The search will cover only records that are currently in the subform.
If
you actually need it to move the main form to another record so it can
find
the record that has the match in the subform, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

message
I have a form & subform that I'm trying to create a record selector
with.
My
subform has my records in a continuous form and my main form has an
unbound
control. There are two controls on my subform called "MinDia" and
"MaxDia".
What I want to do is be able to enter a diameter into my unbound
control
on
my main form and have it select/bookmark a record on the subform that
the
diameter falls in between the "MinDia" and "MaxDia". Here is the code
I'm
trying to use but it isn't working. Can anyone shed some light on this
for
me?

Dim MyRecSet As Object
Set MyRecSet = Me.frmNominalsSubform.Form.RecordsetClone

MyRecSet.FindFirst "[MinDia]>=" & Me.Diameter & " And [MaxDia ]<=" &
Me.Diameter

If Not MyRecSet.EOF Then
Me.frmNominalsSubform.Form.Bookmark = MyRecSet.Bookmark

End If
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I figured it out. I had the record source for my main form pointing to a
query from another part of my db. I removed it and now everything works fine.
Sometimes it's the simple things in life....Thanks again for you help Allen!

Allen Browne said:
Still not testing NoMatch, eh?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
I tried all of your suggestions below with the exception of #6. I only need
it to do a search for records that are currently in my subform. And it's
still not working. I did try a new code setup and for one split second I
can
see it select/bookmark the record but then it just goes back to the top
record. Any ideas what might be wrong now?

Me.frmNominalsSubform.Form.RecordsetClone.FindFirst _
"[MinDia]<=" & Me.Diameter & " And [MaxDia ]>=" & Me.Diameter
If Not Me.frmNominalsSubform.Form.RecordsetClone.EOF Then
Me.frmNominalsSubform.Form.Bookmark = _
Me.frmNominalsSubform.Form.RecordsetClone.Bookmark
DoEvents
End If

Allen Browne said:
Suggestions:

1. Test NoMatch rather than EOF, i.e.:
If Not MyRecSet.NoMatch Then

2. Is Diameter the name of the unbound control? (Don't laugh: it's not
uncommon for people to use the name of the bound control in the code
instead
of the unbound search text box.)

3. Are MinDia and MaxDia both Number type fields (not Text fields)?

4. Did you test if Diameter is null? (The WHERE string will be malformed
if
it is.)

5. Set Format property for Diameter to General Number or similar, so only
valid numbers are accepted.

6. The search will cover only records that are currently in the subform.
If
you actually need it to move the main form to another record so it can
find
the record that has the match in the subform, see:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

message
I have a form & subform that I'm trying to create a record selector
with.
My
subform has my records in a continuous form and my main form has an
unbound
control. There are two controls on my subform called "MinDia" and
"MaxDia".
What I want to do is be able to enter a diameter into my unbound
control
on
my main form and have it select/bookmark a record on the subform that
the
diameter falls in between the "MinDia" and "MaxDia". Here is the code
I'm
trying to use but it isn't working. Can anyone shed some light on this
for
me?

Dim MyRecSet As Object
Set MyRecSet = Me.frmNominalsSubform.Form.RecordsetClone

MyRecSet.FindFirst "[MinDia]>=" & Me.Diameter & " And [MaxDia ]<=" &
Me.Diameter

If Not MyRecSet.EOF Then
Me.frmNominalsSubform.Form.Bookmark = MyRecSet.Bookmark

End If
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Record Select 7
Subform causes Main Form to show the same record multiple times 9
Record Selecting 3
Record selector acting oddly 3
Access Main form/Subform Navigation 0
new value in record selector goto 3
Filter Form Code 1
requery result 1

Top