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

Form Subform Problem...

P: n/a
Hi, I have a database with only one table containing the following
fields:

ID (primary key, auto-counter), Client (Text), Internal_ID (Text),
Window (Y/N), Lock(Y/N), LED (Y/N)

What I'm trying to do, with no success, is to build a form with
unbound controls and filter thru the main table with a subform...

Problem is it doesn't work at all... if I get all the fields right,
then I get the only product matching, otherwise... blank...

What I would want to get is to sequentally eliminate the non matching
products: this is exactly what I am trying to accomplish:

Oper form, every product is shown;
If I click any of the Y/N fields, only the records with Y or N in that
field will be shown, regardless of the other fields (I start with Null
in each unbound control...)

OR

If I input a Client, all the products for that client show, regadless
of the content of the other unbound controls/data, if I left them at
"Null" state, of course, then by clicking a Y/N control, all the
products for that client with that single characterist show,
regardless of the others and so on...

I am completely lost. Any suggestions? Thank u in advance for any help
u may provide...

Ciao, Lupo

Sep 8 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Okay, so you have some unbound controls on a form for entering the criteria,
and you want to then display the matching results in the form (which is
bound to your table.)

The code to do that will need to see which boxes are not null, and build the
filter string from them. This kind of thing:

Dim strWhere As String
Dim lngLen As Long

If Me.Dirty Then Me.Dirty = False 'save first

If Not IsNull(Me.txtClient) Then
strWhere = "(Client Like """ & Me.txtClient & "*"") AND "
End If

If Not IsNull(Me.chkWindow.Value) Then
strWhere = strWhere & "([Window] = " & Me.chkWindow.Value & ") AND "
End If

'etc

lngLen = Len(strWhere) - 5 'without the trailing " AND ".
If lngLen 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
MsgBox "Enter some criteria."
End If

For an example of what this might look like, and a downloadable example that
illustrates how to apply the code to different field types, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.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.

<lu*****@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...
Hi, I have a database with only one table containing the following
fields:

ID (primary key, auto-counter), Client (Text), Internal_ID (Text),
Window (Y/N), Lock(Y/N), LED (Y/N)

What I'm trying to do, with no success, is to build a form with
unbound controls and filter thru the main table with a subform...

Problem is it doesn't work at all... if I get all the fields right,
then I get the only product matching, otherwise... blank...

What I would want to get is to sequentally eliminate the non matching
products: this is exactly what I am trying to accomplish:

Oper form, every product is shown;
If I click any of the Y/N fields, only the records with Y or N in that
field will be shown, regardless of the other fields (I start with Null
in each unbound control...)

OR

If I input a Client, all the products for that client show, regadless
of the content of the other unbound controls/data, if I left them at
"Null" state, of course, then by clicking a Y/N control, all the
products for that client with that single characterist show,
regardless of the others and so on...

I am completely lost. Any suggestions? Thank u in advance for any help
u may provide...

Ciao, Lupo
Sep 8 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.