Search Forms

  • Thread starter Thread starter LC
  • Start date Start date
L

LC

Hi,

I have a huge problem. I'm told to create a form that
have drop downs which contains over 200+ items. I do not
think this will be suitable.

I have thoughts on creating a form A in which when the
textbox field is activated another form B will pop-up.
This form B will be like a search form in which the user
must fill in desired values to search. When those values
are populated a search result will appear on a subform
which is simply a spreadsheet form. When the user double
clicks a row on the spreadsheet, form B will close and
the textbox field on form A will be populated with that
particular value.

Is this possible?
If so, is there a sample database/ form that I can see.
OR how would I do this?

Thank you so much,
LC
 
LC wrote...
-----Original Message-----
Hi,

I have a huge problem. I'm told to create a form that
have drop downs which contains over 200+ items. I do not
think this will be suitable.

I have thoughts on creating a form A in which when the
textbox field is activated another form B will pop-up.
This form B will be like a search form in which the user
must fill in desired values to search. When those values
are populated a search result will appear on a subform
which is simply a spreadsheet form. When the user double
clicks a row on the spreadsheet, form B will close and
the textbox field on form A will be populated with that
particular value.

Is this possible?
If so, is there a sample database/ form that I can see.
OR how would I do this?

Thank you so much,
LC

.
Click to expand...

LC,

I am sure that there are better ways of doing this
but here is what I do for searching. I like to keep as
few forms as possible coming up. In the Form Header I put
a search "box". In the box I can have a number of
searchable items. Something like:

Unbound
Label text box Command Button

FirstName: <TBFfirst> CMB_FIND
LastName: <TBFlast> CMB_RESET


In the example code following I drop the TB and the CMB_
Additionally this will allow you to check the fields and
if both first and last are filled in, then it will use
both of the. I also use an ending wildcard so that I
don't force the users to know the spelling of the name.

This will restrict your record set that the form uses. So
if you have 5000 records but only 20 with the name of
IVAN, then only twenty will be available to cycle
through. At the next search, it will go through all 5000
records again.

*********** CODE START ****************************

Private Sub Find_Click()

If Not IsNull(Me!Flast) And Not IsNull(Me!Ffirst) Then
SQLExec = ""
SQLExec = SQLExec & "SELECT * FROM [tablename] "
SQLExec = SQLExec & "WHERE [LastName] Like """ &
Me!flast & "*"""
SQLExec = SQLExec & " AND [FristName] Like """ &
Me!Ffirst & "*"""
SQLExec = SQLExec & " ORDER BY [LastName] ASC;"
Me.RecordSource = SQLExec
Me!FLast =
Null 'Null out
the fields on the form.
Me!Ffirst =
Null 'Null out
the fields on the form.
ElseIf Not IsNull(Me!Flast) Then
SQLExec = ""
SQLExec = SQLExec & "SELECT * FROM [tablename] "
SQLExec = SQLExec & "WHERE [LastName] Like """ &
Me!Flast & "*"""
SQLExec = SQLExec & " ORDER BY [LastName] ASC;"
Me.RecordSource = SQLExec
Me!FLast =
Null 'Null out
the fields on the form.
Me!Ffirst =
Null 'Null out
the fields on the form.
Endif

*********** CODE END ****************************


hth

Ivan

If you want an example I can send you one. Let me know at

alovercast at Yahoo dot com
 
Back
Top