By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Searching Subforms...

P: n/a
Hi,

I have a form as follows:

Main Form (To Select Customer)
Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
form
Sub Form 2 (To view order details)- Continuous form
______________________

Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
the details for that PO Number.

What I am trying to do is have a text box & button on the main form
which just does the same as me using the scroll bar on Sub-Form 1 to
find a PO Number. I still want all the other PO Numbers shown, Just
for the system to select the PO Number from the text box and move to
that record without hiding all the other PO Numbers as maybe the
'Filter' method does.

i.e.

PO Number

5454
87676
7676
7676
656

Normally when the form opens, the very first PO Number is selected
automatically (5454). If I search for PO Number '656' then I want
Sub-Form 1 to show

5454
3w43
656.....<< Selected Record.
90
34543

Sub-Form 2 should then auto display the data for the searched for PO
Number.

I still want our users to be able to search again or scroll through
all the PO Numbers.

I have tried some other code which makes Sub-Form 1 display only the
searched for PO Number & Sub-Form 2, but then I found it impossible to
get both Sub Forms back to their state prior to the search.

Appreciate your help.

David
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Assuming:
- the record is present in the subform (i.e. it does not match a different
main form record);
- the text box where you enter the number to find is named "txtFind"
then you could put this code into the AfterUpdate event procedure of your
text box.

Private Sub txtFind_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "[PO Number] = " & Me.txtFind
With Me.[Form1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub
Notes:
1. If PO Number is a Text type field, you need extra quotes:
strWhere = "[PO Number] = """ & Me.txtFind & """"
2. The found record is displayed at the top of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
Hi,

I have a form as follows:

Main Form (To Select Customer)
Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
form
Sub Form 2 (To view order details)- Continuous form
______________________

Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
the details for that PO Number.

What I am trying to do is have a text box & button on the main form
which just does the same as me using the scroll bar on Sub-Form 1 to
find a PO Number. I still want all the other PO Numbers shown, Just
for the system to select the PO Number from the text box and move to
that record without hiding all the other PO Numbers as maybe the
'Filter' method does.

i.e.

PO Number

5454
87676
7676
7676
656

Normally when the form opens, the very first PO Number is selected
automatically (5454). If I search for PO Number '656' then I want
Sub-Form 1 to show

5454
3w43
656.....<< Selected Record.
90
34543

Sub-Form 2 should then auto display the data for the searched for PO
Number.

I still want our users to be able to search again or scroll through
all the PO Numbers.

I have tried some other code which makes Sub-Form 1 display only the
searched for PO Number & Sub-Form 2, but then I found it impossible to
get both Sub Forms back to their state prior to the search.

Appreciate your help.

David

Nov 13 '05 #2

P: n/a
Allen,

Thanks for your prompt reply.

rs As DAO.Recordset - ERROR: User Defined Type not defined ?????

Any ideas ?

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
Assuming:
- the record is present in the subform (i.e. it does not match a different
main form record);
- the text box where you enter the number to find is named "txtFind"
then you could put this code into the AfterUpdate event procedure of your
text box.

Private Sub txtFind_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "[PO Number] = " & Me.txtFind
With Me.[Form1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub
Notes:
1. If PO Number is a Text type field, you need extra quotes:
strWhere = "[PO Number] = """ & Me.txtFind & """"
2. The found record is displayed at the top of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
Hi,

I have a form as follows:

Main Form (To Select Customer)
Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
form
Sub Form 2 (To view order details)- Continuous form
______________________

Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
the details for that PO Number.

What I am trying to do is have a text box & button on the main form
which just does the same as me using the scroll bar on Sub-Form 1 to
find a PO Number. I still want all the other PO Numbers shown, Just
for the system to select the PO Number from the text box and move to
that record without hiding all the other PO Numbers as maybe the
'Filter' method does.

i.e.

PO Number

5454
87676
7676
7676
656

Normally when the form opens, the very first PO Number is selected
automatically (5454). If I search for PO Number '656' then I want
Sub-Form 1 to show

5454
3w43
656.....<< Selected Record.
90
34543

Sub-Form 2 should then auto display the data for the searched for PO
Number.

I still want our users to be able to search again or scroll through
all the PO Numbers.

I have tried some other code which makes Sub-Form 1 display only the
searched for PO Number & Sub-Form 2, but then I found it impossible to
get both Sub Forms back to their state prior to the search.

Appreciate your help.

David

Nov 13 '05 #3

P: n/a
You are probably using Access 2000 or 2002, and you need to add a reference
to:
Microsoft DAO 3.6 Library.
Details:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
Allen,

Thanks for your prompt reply.

rs As DAO.Recordset - ERROR: User Defined Type not defined ?????

Any ideas ?

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
Assuming:
- the record is present in the subform (i.e. it does not match a
different
main form record);
- the text box where you enter the number to find is named "txtFind"
then you could put this code into the AfterUpdate event procedure of your
text box.

Private Sub txtFind_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "[PO Number] = " & Me.txtFind
With Me.[Form1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub
Notes:
1. If PO Number is a Text type field, you need extra quotes:
strWhere = "[PO Number] = """ & Me.txtFind & """"
2. The found record is displayed at the top of the subform.
"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
> Hi,
>
> I have a form as follows:
>
> Main Form (To Select Customer)
> Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
> form
> Sub Form 2 (To view order details)- Continuous form
> ______________________
>
> Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
> the details for that PO Number.
>
> What I am trying to do is have a text box & button on the main form
> which just does the same as me using the scroll bar on Sub-Form 1 to
> find a PO Number. I still want all the other PO Numbers shown, Just
> for the system to select the PO Number from the text box and move to
> that record without hiding all the other PO Numbers as maybe the
> 'Filter' method does.
>
> i.e.
>
> PO Number
>
> 5454
> 87676
> 7676
> 7676
> 656
>
> Normally when the form opens, the very first PO Number is selected
> automatically (5454). If I search for PO Number '656' then I want
> Sub-Form 1 to show
>
> 5454
> 3w43
> 656.....<< Selected Record.
> 90
> 34543
>
> Sub-Form 2 should then auto display the data for the searched for PO
> Number.
>
> I still want our users to be able to search again or scroll through
> all the PO Numbers.
>
> I have tried some other code which makes Sub-Form 1 display only the
> searched for PO Number & Sub-Form 2, but then I found it impossible to
> get both Sub Forms back to their state prior to the search.
>
> Appreciate your help.
>
> David

Nov 13 '05 #4

P: n/a
Allen,
I have a similar problem and have been unable to find a solution that
works.

I have a main form, lets call it frmMain, with a subform control,
called sfrmControl, which has a subform on it called sfrmMain. I want
to put a combobox on the main form, which will filter the main form,
based on a field which is contained in the subform. When I do this
currently, a little box comes up (like a parameter input box) with the
name of the field, and a textbox asking me to type in a value.

I want to do this with filters, so when I select a value in the
combobox on the main form, it will filter the main form based on the
field from the subform, so that the main form will display the
"(Filtered)" part after the number of records.

I have this working now on a different combobox, the only difference
is the combobox that is working is using a field from the main form
instead of a field on the subform. I tried every permutation I could
think of, but I can't seem to be able to get the main form filtered
based on the subform's field.

It would be much appreciated any help that could be supplied. Thanks.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
Assuming:
- the record is present in the subform (i.e. it does not match a different
main form record);
- the text box where you enter the number to find is named "txtFind"
then you could put this code into the AfterUpdate event procedure of your
text box.

Private Sub txtFind_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "[PO Number] = " & Me.txtFind
With Me.[Form1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub
Notes:
1. If PO Number is a Text type field, you need extra quotes:
strWhere = "[PO Number] = """ & Me.txtFind & """"
2. The found record is displayed at the top of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
Hi,

I have a form as follows:

Main Form (To Select Customer)
Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
form
Sub Form 2 (To view order details)- Continuous form
______________________

Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
the details for that PO Number.

What I am trying to do is have a text box & button on the main form
which just does the same as me using the scroll bar on Sub-Form 1 to
find a PO Number. I still want all the other PO Numbers shown, Just
for the system to select the PO Number from the text box and move to
that record without hiding all the other PO Numbers as maybe the
'Filter' method does.

i.e.

PO Number

5454
87676
7676
7676
656

Normally when the form opens, the very first PO Number is selected
automatically (5454). If I search for PO Number '656' then I want
Sub-Form 1 to show

5454
3w43
656.....<< Selected Record.
90
34543

Sub-Form 2 should then auto display the data for the searched for PO
Number.

I still want our users to be able to search again or scroll through
all the PO Numbers.

I have tried some other code which makes Sub-Form 1 display only the
searched for PO Number & Sub-Form 2, but then I found it impossible to
get both Sub Forms back to their state prior to the search.

Appreciate your help.

David

Nov 13 '05 #5

P: n/a
See:
Filter a Form on a Field in a Subform
at:
http://members.iinet.net.au/~allenbrowne/ser-28.html

The article explains how to effectively filter the main form so it only
contains records that have a match in the subform, by reassigning the
RecordSource of the main form to an INNER JOIN statement.

(It is also possible to achieve the result by using a subquery in the Filter
of the main form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"user_5701" <us*******@hotmail.com> wrote in message
news:f1**************************@posting.google.c om...
Allen,
I have a similar problem and have been unable to find a solution that
works.

I have a main form, lets call it frmMain, with a subform control,
called sfrmControl, which has a subform on it called sfrmMain. I want
to put a combobox on the main form, which will filter the main form,
based on a field which is contained in the subform. When I do this
currently, a little box comes up (like a parameter input box) with the
name of the field, and a textbox asking me to type in a value.

I want to do this with filters, so when I select a value in the
combobox on the main form, it will filter the main form based on the
field from the subform, so that the main form will display the
"(Filtered)" part after the number of records.

I have this working now on a different combobox, the only difference
is the combobox that is working is using a field from the main form
instead of a field on the subform. I tried every permutation I could
think of, but I can't seem to be able to get the main form filtered
based on the subform's field.

It would be much appreciated any help that could be supplied. Thanks.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
Assuming:
- the record is present in the subform (i.e. it does not match a
different
main form record);
- the text box where you enter the number to find is named "txtFind"
then you could put this code into the AfterUpdate event procedure of your
text box.

Private Sub txtFind_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "[PO Number] = " & Me.txtFind
With Me.[Form1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub
Notes:
1. If PO Number is a Text type field, you need extra quotes:
strWhere = "[PO Number] = """ & Me.txtFind & """"
2. The found record is displayed at the top of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
> Hi,
>
> I have a form as follows:
>
> Main Form (To Select Customer)
> Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
> form
> Sub Form 2 (To view order details)- Continuous form
> ______________________
>
> Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
> the details for that PO Number.
>
> What I am trying to do is have a text box & button on the main form
> which just does the same as me using the scroll bar on Sub-Form 1 to
> find a PO Number. I still want all the other PO Numbers shown, Just
> for the system to select the PO Number from the text box and move to
> that record without hiding all the other PO Numbers as maybe the
> 'Filter' method does.
>
> i.e.
>
> PO Number
>
> 5454
> 87676
> 7676
> 7676
> 656
>
> Normally when the form opens, the very first PO Number is selected
> automatically (5454). If I search for PO Number '656' then I want
> Sub-Form 1 to show
>
> 5454
> 3w43
> 656.....<< Selected Record.
> 90
> 34543
>
> Sub-Form 2 should then auto display the data for the searched for PO
> Number.
>
> I still want our users to be able to search again or scroll through
> all the PO Numbers.
>
> I have tried some other code which makes Sub-Form 1 display only the
> searched for PO Number & Sub-Form 2, but then I found it impossible to
> get both Sub Forms back to their state prior to the search.
>
> Appreciate your help.
>
> David

Nov 13 '05 #6

P: n/a
Allen,

After 3 days tearing my hair out, I solved the code by trial & error
along with yours & others postings.

Working Code as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.Text54) Then
strWhere = "[PONumber] = """ & Me.Text54 & """"
With Me.[Orders Subform].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
Me.Text54.SetFocus
MsgBox "PO Number **" & Me.Text54.text & "** does not
exist." & vbCrLf & "Please search again", vbInformation, "Incorrect PO
Number"
Me.Text54.SetFocus
Else
.Bookmark = rs.Bookmark
Forms![Customer Orders]![OrderLines
Subform].Form![PODisplay].Caption = "** Search Results for PO Number: "
& Me.Text54 & " **"
End If
Set rs = Nothing
End With
End If

___________________

It was the DAO reference that was throwing me.
This was only meant to be a simple search....Great to have it working at
last.

Thanks to everyone.

David

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #7

P: n/a
Well done!

Hope it all makes sense now, and was a worthwhile learning experience.

If you want more info on references, see:
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"David Gordon" <da***@scene-double.co.uk> wrote in message
news:41**********************@news.newsgroups.ws.. .
Allen,

After 3 days tearing my hair out, I solved the code by trial & error
along with yours & others postings.

Nov 13 '05 #8

P: n/a
Thank you Allen, that was just what I was looking for. I had a
previous program working by using subqueries but that was a complete
mess - this way was much cleaner and so much easier. I played around
with the sql a little to match what I had, and in about 5-10 minutes
had it working the way I wanted it. Thanks again.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
See:
Filter a Form on a Field in a Subform
at:
http://members.iinet.net.au/~allenbrowne/ser-28.html

The article explains how to effectively filter the main form so it only
contains records that have a match in the subform, by reassigning the
RecordSource of the main form to an INNER JOIN statement.

(It is also possible to achieve the result by using a subquery in the Filter
of the main form.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"user_5701" <us*******@hotmail.com> wrote in message
news:f1**************************@posting.google.c om...
Allen,
I have a similar problem and have been unable to find a solution that
works.

I have a main form, lets call it frmMain, with a subform control,
called sfrmControl, which has a subform on it called sfrmMain. I want
to put a combobox on the main form, which will filter the main form,
based on a field which is contained in the subform. When I do this
currently, a little box comes up (like a parameter input box) with the
name of the field, and a textbox asking me to type in a value.

I want to do this with filters, so when I select a value in the
combobox on the main form, it will filter the main form based on the
field from the subform, so that the main form will display the
"(Filtered)" part after the number of records.

I have this working now on a different combobox, the only difference
is the combobox that is working is using a field from the main form
instead of a field on the subform. I tried every permutation I could
think of, but I can't seem to be able to get the main form filtered
based on the subform's field.

It would be much appreciated any help that could be supplied. Thanks.
"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:<41***********************@per-qv1-newsreader-01.iinet.net.au>...
Assuming:
- the record is present in the subform (i.e. it does not match a
different
main form record);
- the text box where you enter the number to find is named "txtFind"
then you could put this code into the AfterUpdate event procedure of your
text box.

Private Sub txtFind_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String

If Not IsNull(Me.txtFind) Then
strWhere = "[PO Number] = " & Me.txtFind
With Me.[Form1].Form
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With
End If
End Sub
Notes:
1. If PO Number is a Text type field, you need extra quotes:
strWhere = "[PO Number] = """ & Me.txtFind & """"
2. The found record is displayed at the top of the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...
> Hi,
>
> I have a form as follows:
>
> Main Form (To Select Customer)
> Sub Form 1 (To Select PO Number, by clicking on field) - Continuous
> form
> Sub Form 2 (To view order details)- Continuous form
> ______________________
>
> Normally, if I click on a PO Number in Sub-Form 1, Sub-Form 2 displays
> the details for that PO Number.
>
> What I am trying to do is have a text box & button on the main form
> which just does the same as me using the scroll bar on Sub-Form 1 to
> find a PO Number. I still want all the other PO Numbers shown, Just
> for the system to select the PO Number from the text box and move to
> that record without hiding all the other PO Numbers as maybe the
> 'Filter' method does.
>
> i.e.
>
> PO Number
>
> 5454
> 87676
> 7676
> 7676
> 656
>
> Normally when the form opens, the very first PO Number is selected
> automatically (5454). If I search for PO Number '656' then I want
> Sub-Form 1 to show
>
> 5454
> 3w43
> 656.....<< Selected Record.
> 90
> 34543
>
> Sub-Form 2 should then auto display the data for the searched for PO
> Number.
>
> I still want our users to be able to search again or scroll through
> all the PO Numbers.
>
> I have tried some other code which makes Sub-Form 1 display only the
> searched for PO Number & Sub-Form 2, but then I found it impossible to
> get both Sub Forms back to their state prior to the search.
>
> Appreciate your help.
>
> David

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.