simple record search (or perhaps not so simple)

G

Greg

i need to do a record search in a form but there are a few twists.

1. this form is (by itself) simple and used for creating totally new records
and entering them into a simple table (all fields are text, date or number)
2. there is no unique field to any of these records
3. the form has 9 fields, 5 of which are required for creating a record.
4. the solution has to be super easy for the end-users of this database. i'm
thinking of a command button that pops up a form that let's a person select
up to 3 criteria (2 of which are one of the required fields mentioned earlier
and 1 is not -- but i don't want to require any of them for this search so i
need to allow for null values in the ones that they don't pick) and then
searching the table for all records that match those criteria. then letting
the user select which record they want to edit (i would need to know how to
let a user select a record), then closing the popups and displaying the
record in the original form, ready for editing.

i can do the button to pop the 2nd form and the controls for that form
(probably 2 text boxes and 1 combo box, if that matters) but dont' know how
to DO the search, return another popup of results, select 1 and populate the
1st form.
 
A

Albert D.Kallal

Have you tried going:

records->Filter->Filter by form?

The above will instantly transform your form into a "search form", and you
can type values into any field..and it will search for you..

Do give the above a try.....
 
G

Greg

initially, i did not know of this feature. when i tried it, it seems to be
almost exactly what i want except that it's not exactly user-friendly enough.
i mean, i can figure out how to do it, but the end users that i have in
mind, i'm honestly not so sure. i know that they'd uderstand it if
shown/told how to do it but that understanding wouldn't last for longer than
a few weeks and this should really be something they'd need to use very
seldomly. i'm not trying to insult my coworkers mind you, it's just that
none of them have ever touched access before (neither had i, 6 weeks ago) and
i want to make it fool proof.

i also tried the wizard on that cmd button to construct a filter by form
button but it wasn't the same thing. the wizard didn't use filter by form,
it wanted user input for the search parameters. i really need this to be
super easy to do. thanks.
 
A

Albert D.Kallal

Ok, just wanted to make sure you had tried the obvious solutions. No need to
write a bunch of code when you go something built in that *might* do the
trick.


I also beloved VERY strongly in writing my own search forms. In fact, here
is
a blurb of mine on this exact subject with some screen shots:

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

So, the above is a good solution (you present a "list" of the search
results).

There is also some more search screen shots here to give you some ideas:

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

The code used in the above search screen is quite easy

Your code behind yor seach buttion could be

dim strSql as string
dim strWhere as string


strSql = "Select * from tblCustomers"


if isnull(me.comboSalesRep) = false then
strWhere = "salesRepID = " & me.comboSalesRep
end if


' I also have a city box to select names only from a particular city.


if isnull(me.comboCity) = false then
if strWhere <> "" then
strWhere = strWhere & " and "
end if
strWhere = strWhere & "City = '" & me.comboCity & "'"
end if


' note how you can add as many conditions as you want.
' so, if you leave them blank...you don't incluce the field..and
' you get "all"...

After you put in as many fields as you need...

You then go:


strSql = strWhere & " where " & strWhere


me.RecordSouce = strSql
 
G

Greg

i'm trying out what you suggested and i think i have a vba issue. now,
understand that i don't know vba almost at all so my questions to follow may
appear to make little sense to someone who knows what they are doing.

to make things easier, i made a new form just for searches that will open
from a command button on my original form.

i have only 4 fields on this new form, in an effort to keep it very simple.
Date, salesperson, department and upsource (an 'up' is a customer).

this is the code i put on a command button in this new form. i thought it
would invoke your search in the same way as putting it directly on the
original form.

Private Sub cmdsearch_Click()

Dim strSql As String
Dim strWhere As String


strSql = "Select * from tblcontactdata"


If IsNull(Me.tboxdate) = False Then
strWhere = "date = " & Me.tboxdate
End If


' I also have a salesperson box to select names only from a particular
salesperson.


If IsNull(Me.cboxSalesperson) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "salesperson = '" & Me.Salesperson & "'"
End If

If IsNull(Me.cboxdepartment) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "department = '" & Me.Department & "'"
End If

If IsNull(Me.cboxupsource) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "upsource = '" & Me.UpSource & "'"
End If


' note how you can add as many conditions as you want.
' so, if you leave them blank...you don't incluce the field..and
' you get "all"...

' After you put in as many fields as you need...

' You then go:


strSql = strWhere & " where " & strWhere


Me.tblcontactdata = strSql

End Sub


it did not, however. in looking at it, i think one of the problems is that
i have 4 criteria, not just 2. if i select 4 things to search, then
shouldn't i need a longer where statement? something to tie all criteria
together? however, it also looks a little like your where statement just
grows with each non-null criteria. in either case, i may be able to just
barely grasp what's happening, but i cannot fix this problem myself. can you
tell what's wrong or do you need more information?
 
A

Albert D.Kallal

You code looks very good....

I think the only mistake you have is:
Me.tblcontactdata = strSql
Click to expand...

We want to do is take our sql that we built in code, and make the form
"display" our results. To set the form to our sql, we can go

me.RecordSource = strSql

So, your code of me.tblContactdata = strSql will not work. Remember, I am
assuming we got a continues form here. So, I would assume that your 4
contorls on the form are UN-BOUND. When we "set" the data source of our
form, we are setting it to the sql we just made.

You can also RIGHT before you go

me.RecordSouce = strSql

do a

debug.print strSql

So, put the above line of code in. When you run the code and it fails, you
can now go to the debug window (ctrl-g), and you will see you one line of
sql that the code generated. Now, take that sql, and cut/paste it into a new
blank query......

Does the query work???
 
G

Greg

it still didn't work. i was not using unbound controls, but i changed that
and it still didn't work (although i much prefer to use controls bound to
certain tables for selection criteria, if possible). what i get is an error
that says the method or data member is not found and the highlight is on this
line:

Private Sub cmdsearch_Click()

there is also the line of me.recordsource = strsql that is selected
(highlighted in blue as if you were going to cut or copy) at the bottom.

i did not see any line of sql in the debug window.

am i doing something colossally wrong here?
 
G

Greg

ok... forget my first reply to this post about not being able to see the sql
statement. i figured out what was wrong and then i did what you said about
creating a new query. when i put the sql in the query, no results turned up.
i only used the date field and deapartment field and still no results.
before you ask, i absolutely checked my table to make sure there were records
in there that matched the date and department and there are several. so the
code seems to be working now it's just the query that's not finding anything.
what do you think i should be looking for to fix this?
 
G

Greg

by the way, the sql statement is:

Select * from tblcontactdata where Date = 10/1/2005 and department = 'New'

i also tried this just to be sure:

Select * from tblcontactdata where tblcontactdata.Date = 10/1/2005 and
tblcontactdata.department = 'New'
 
G

Greg

ok... more info. the problem seems to be the date field. when i run the
search w/o the date as a criteria, it works. in the table, the date field is
set as date/time. is there a property i need to change somewhere else?
 
A

Albert D.Kallal

it still didn't work. i was not using unbound controls, but i changed
that
and it still didn't work (although i much prefer to use controls bound to
certain tables for selection criteria, if possible). what i get is an
error
that says the method or data member is not found and the highlight is on
this
line:
Click to expand...


A unbound combo box will still have a sql string, but the "data" part of the
combo box is NOT bound to a field in the forms underlying table.

So, the "general" term used to define a un-bound text box, combo box,
listbox etc. is that it is not set to save any data into a field on the
forms underlying table. So, sure, you combo box is going to have some sql to
display values, but whatever you select is NOT going to be saved into a
table. And, for a general text box that you place on a screen, again by
un-bound, we simply mean that is it not connected to a field in the forms
table that gets saved.

So, your code we have is:

( assume that things like tblcontactdata is the name of your
table...right??).

Private Sub cmdsearch_Click()

Dim strSql As String
Dim strWhere As String


strSql = "Select * from tblcontactdata"

If IsNull(Me.tboxdate) = False Then
strWhere = "date = " & Me.tboxdate
End If

If IsNull(Me.cboxSalesperson) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "salesperson = '" & Me.Salesperson & "'"
End If

If IsNull(Me.cboxdepartment) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "department = '" & Me.Department & "'"
End If

If IsNull(Me.cboxupsource) = False Then
If strWhere <> "" Then
strWhere = strWhere & " and "
End If
strWhere = strWhere & "upsource = '" & Me.UpSource & "'"
End If

strSql = strSql & " where " & strWhere

debug.print strSql
msgbox strSql

me.RecordSource = strSql
 
A

Albert D.Kallal

when i put the sql in the query, no results turned up.

Well, does the sql look ok?

Try building the sql in the query builder, and compare it to the debug.print
sql. (just do one condition for the test...say the cboSales rep)

Buld a query in the query builder with that "sales rep" typed in by
hand........does it work?
Get this query working.

NOW.... try your form, and try the same sales rep form the combo box. Take
that debug.print sql into antoher blank new query...does it work...how does
it look? They should at least look sijmuar? (in fact, cut a past the
debug.print into you next post so we can see it.).

Build your query in the query builder, and ONLY select one salesrept.

Now, try the form code, with a same sales rep selected, and what does the
sql generated in code look like?
 
D

Douglas J. Steele

Dates in Access need to be delimited with # characters. And does 10/1/2005
mean October 1st or January 10th to you? Access will see it as October 1st,
regardless of what your regional settings may be.
 
G

Greg

ok.. the query seemed to work fine for my search form. when i looked at it
in datashet view, it was exactly what i wanted. here's the sql:

Select * from tblcontactdata where salesperson = 'John Smith'

the problems i see now are this. i can use any criteria among my vba code
and have the query work except for the date. when any date is entered, no
records show up in the query results. the date alone produces an sql like
this:

Select * from tblcontactdata where date = 10/1/2005

and this produces 0 results. the field in the underlying table is formatted
as date/time.

my other problem is that there are 9 fields on the form that are filled in
to create tblcontact data. i copied that form to create a search form that
looked exactly alike. my thought was that somenoe could enter one or more
fields on this search form and have it return a limited # of records on a
form that looked exactly like what they were used to for entering the data in
the first place. the problem is that if i only select the salesperson and
department, the search form WILL limit the # of records to those that fit the
criteria but it won't fill in the rest of the fields with the unstated
criteria (upsource, result, etc..) it will just say the salesperson's name
and the department name and all the other fields are blank. it will list 5
records on the record navigation buttons but i dont' see how i can get it to
list the rest of the data in each record so i know i'm changing the correct
record.

btw, if you want me to send you the db to see what i'm talking about, email
me at kaosyeti(<a.t.>)comcast(<d.o.t.>)net. Thanks.
 
Top