Forgot some Basics

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Got a real easy one. I have this huge table with over a million records of
inventory. The table contains several fields like; Serial#, Model#, Condition
Cd#, etc,. I want to create a form that will show all records based on Serial
# (which the user would input) or based on Model # (again, user will input)
also, but as a separate function I want to be able to display the records if
both the Serial# and Model# are inputed. So what I need is One box where the
user would input Serial# they're looking for and beneath it all of the
records with Serial# would be displayed and the same thing if they only have
the Model#, then if the user fills in both the Serial# and the Model# only
that exact record would show up.
Thanks for the help.
 
If you are going to code a form to search, then you can try the following:

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName after update event you simply stuff the results
into that sub-form.

dim strSql as string


strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"

me.MySubFormname.Form.RecordSource = strSql.

here is a screen shot that uses the above code:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

My example field names where different..and you likely would remote the "*"
in the above code that gives you wild card.

Even with a million names, the search results will be instant if your fields
that you search on are indexed.
 
If you need MORE then one paramter for input, then the folwling will work:

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typicaly desiged for user
interface face stuff like promtps, print buttions etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for informaton.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above shold give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar contorls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere
 
Back
Top