Access 2003 unbound Search Form?

R

rstiles

Happy New Year everyone --

I have been creating an application over the last few month's and this
problem has been a major headache. I have tried posting at a couple
different places without any luck, so I am hoping that someone can help
me here.

Access 2003 (File Format 2000)

To make everything easier for the end user I have created a tabbed form
(frmTabAll). The three tabbed forms that I have created are sfmFCR,
sfmCN, sfmFCRSearch, sfmCNSearch. The first two subforms each have
bound text boxes for data entry and another subform (sfmFCRHistory,
sfmCNHistory) to show the current entries. This is where I have been
having problems, on the third subform (sfmFCRSearch) I would like to
have unbound text boxes, with a subform below them
(sfmFCRSearchResults) to show the results of a search. Same thing for
the last subform. I have everything designed and ready to go but for
the life of me, I can not figure out a way that I can do this.

I am a new user to Access and VBA. So far I am thinking that I could
use a filter or query.

Any ideas?

If you have any questions please feel free to ask,

Ron
 
M

Marshall Barton

Access 2003 (File Format 2000)

To make everything easier for the end user I have created a tabbed form
(frmTabAll). The three tabbed forms that I have created are sfmFCR,
sfmCN, sfmFCRSearch, sfmCNSearch. The first two subforms each have
bound text boxes for data entry and another subform (sfmFCRHistory,
sfmCNHistory) to show the current entries. This is where I have been
having problems, on the third subform (sfmFCRSearch) I would like to
have unbound text boxes, with a subform below them
(sfmFCRSearchResults) to show the results of a search. Same thing for
the last subform. I have everything designed and ready to go but for
the life of me, I can not figure out a way that I can do this.
Click to expand...


Sorry, but your situation is a little garbled. You said you
have three subforms on different(?) pages of a tab control,
but then mention at least four different subforms, two of
which have subforms of their own. You did say you wanted to
use unbound controls in a subform to "search" in a
subsubform, but never did describe what should happen in
the related subsubform.

If you just want the subsubform, sfmFCRHistory, to be
filtered by the contents of the unbound controls, use the
unbound controls in the subsubform control's Link Master
Fields property and the related fields in the subsubform
object's Record Source table/query in the Link Child Fields
property. If this is not what you want, please explain what
you do want.
 
R

rstiles

Marsh,

I should not try to post things after work when I am tired...as you can
see the result turns into an ugly mess.

Basically I have a tabbed form with the four tabs; the first one is for
adding FCR information, the second for adding case number information,
the third for searching the FCR records, and the fourth for searching
the case number records.

For the last two tabs what I want to do is create a group of unbound
text boxes and a list box below (this has already been created). The
goal is to have the end user enter their search criteria (some with
null values) into the text boxes and have the results show up in the
list box after they hit the search command button.

This would be something close to QBF in previous access versions exept
I prefer the have my results show up in the list box below instead of a
form.

I have also thought about using a filter by form but this option does
not want to work in any of my tabbed forms for some reason.

I hope that you can understand what I am trying to do.

Ron
 
M

Marshall Barton

I should not try to post things after work when I am tired...as you can
see the result turns into an ugly mess.

Basically I have a tabbed form with the four tabs; the first one is for
adding FCR information, the second for adding case number information,
the third for searching the FCR records, and the fourth for searching
the case number records.

For the last two tabs what I want to do is create a group of unbound
text boxes and a list box below (this has already been created). The
goal is to have the end user enter their search criteria (some with
null values) into the text boxes and have the results show up in the
list box after they hit the search command button.
Click to expand...


Ahhh, the subsubforms are really list boxes. The technique
is the same either way so that's not an issue.

The code behind a "search" button can have this kind of
structure:

Dim strWhere As String
Dim strSQL As String
Dim strSort As String
strSQL = "SELECT fa,fb, ... FROM FCRtable "
strSort = " ORDER BY fx,fy"

If Not IsNull(txta) Then
strWhere = strWhere & " AND fa=" & txta 'numeric field
End If
If Not IsNull(txtb) Then
strWhere = strWhere & " AND fb=""" & txtb & """" 'text
End If
If Not IsNull(txtc) Then
strWhere = strWhere & " AND fc=" _
& Format(txtc, "\#m\/d\/yyyy\#") 'date field
End If
. . .
Me.listbox.RowSource = strSQL & Mid(strWHERE,6) & strSort
 
Top