You are straying into areas where Access shines and VB takes more work
to accomplish the same things. If you want to the user to be able to
submit search parameters and get back a list of matching records,
you've got to do all the hard work yourself.
1. Create a series of controls where the user can enter their search
parameters and a listbox to put the possible matches in.
2. When the user presses the search button, you parse the search
values that the user entered into a SQL statement.
3. Run the SQL statement and put the results of the query into the listbox.
dim sql, select, where, orderby as String
select = "select field1, field2, field3 from sometable "
orderby = "order by field2 "
' if user put something in txtFieldA textbox, use that to filter against table
If Len(txtFieldA) > 0 Then
If Len(where) > 0 Then
where = where & " and "
Else
where = " where "
End if
where = where & "FieldA='" & txtFieldA & "'" ' FieldA is text or date
End If
' if user put something in txtFieldB textbox, use that to filter against table
If Len(txtFieldB) > 0 Then
If Len(where) > 0 Then
where = where & " and "
Else
where = " where "
End if
where = where & "FieldB=" & txtFieldB ' FieldB is numeric
End If
' ...continue to build the query for all the other filter (parameter) fields
sql = select & where & orderby ' put the sql statement together
' open the query using a Recordset
rs.Open sql, mconAP, adOpenKeyset, adLockOptimistic, adCmdText
' put the results into a listbox
rs.MoveFirst
List1.Clear
While not rs.EOF
List1.AddItem (rs("Field1") & " - " & rs("Field2"))
rs.MoveNext
Wend
You may want to find a good aftermarket datagrid ActiveX component for
displaying data, rather than the listbox control. Some datagrids are
very powerful and do a lot of work for you, much like Access forms do.
It may also be worthwhile about now to teach yourself a little bit
about SQL Server stored procedures. Most DBAs would cringe if they
saw the code I just wrote, because it uses what is known as "inline
SQL." As an application grows, you won't want bits and pieces of SQL
statements scattered throughout your code. It's easier to centrally
manage your application if it just calls stored procedures in the
database, rather than parsing its own SQL statements. Some inline SQL
is unavoidable, but stored procedures will make your software more
portable, powerful and manageable. |
