RE: How to code/perform a search

Ok, this makes a little more sense to me.
Getting an understanding of recordsetclone from:
http://msdn2.microsoft.com/en-us/library/aa139932(office.10).aspx

Using this method, would my form actually have 1 cbo (employee ID) or (3)
cbo's (first & last names)? I'm trying to build a form to test this.

Should I be using the cboEmplID_AfterUpdate() OR Submit_Click()? _Click
seems to make more sense to me.

So from you code sample, you:

1. create the recordset and return it (1 row) Code was not shown.
2. After the recordset is known, and you have a current record, you create
the clone.
3. If this is the correct record, then bookmark it.

Am I thinking along the right lines?


"Klatuu" wrote:

You can still use combo boxes for the search - One to search by emp_id and
one to search by name. It is a bit advanced, but I can show a technique
using a combo and a command button that uses one combo to search be either.

You really don't want to use a query for this. That would be just making it
harder. To restate this technique:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EMP_ID] = " & Me.cboEmployee
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With
End Sub

It will locate the employee id in the form's recrdsetclone and make the
record the form's current record. If you want to also have a combo to search
by name, that is fine; however, neither of the combos should be bound
controls. The bound control for these fields should be a text box. You can
make them invisible if you want to. But again, let's get at least one of the
combo's working for now, then we can move on the the Not In List part.
--
Dave Hargis, Microsoft Access MVP


"rich" wrote:

Ok, making sure I understand...

A cbo would/should be used if I am basing the query on a known and limited
set of data (i.e. City, State, Zip, IsEnrolled, age > 21, etc). In my case,
I am searching on based on an employee number and/or the employee first AND
last name. I say and/or because the employee name may not be known. That
being said, the code for my Submit_Click (search is executed when user
clicks on Submit button) is:
************** Start of Code ***************************
Dim strSQLStmt As String 'This is the SQL statement for the
query
Dim SearchResult As Boolean 'This is the result of the search.
0=failed, 1=succeeded
Dim rs As ADODB.Recordset 'This is the recordset
Dim strMsg As String 'This is a message used in
MesasgeBoxes

strsqlString = "Select * " & _
"FROM tbl_Empl_Master "

If Not IsNull(txtS3ID) Then
strsqlString = strsqlString & "WHERE tbl_Empl_Master.S3ID = txtS3ID"
End If
If Not IsNull(txtEmplFName) Then
strsqlString = strsqlString & " and txtEmplFName = txtEmplFname"
End If
If Not IsNull(txtEmplLName) Then
strsqlString = strsqlString & " and txtEmplLName = txtEmplLname"
End If

MsgBox (strsqlString)
*************** End of Code *************
That code of course builds the Select query.

Yes, the form is currently bound to the employee table. I thought it needed
to be. Initially, the form was unbound, but then I could not enter the
search criteria.

Moving forward, the query should return 1 single record. One or more
individual fields on this record will need to be modified and the entire
record will be updated (re-saved) when the users clicks to Save the record.

I am working now to develop the ADO statements. Would I be better off using
the Select...Where query, or perhaps using a filter (based on the
user-provided search criteria)?

Thanks,
Rich



"Klatuu" wrote:

You can use a text box if you want to, but in most cases a combo is easier
because it is designed for this sort of work and it is easier for the user to
find a match. The combo box has an Auto Expand property that provides the
"type ahead" functionality. This means the user doesn't have to enter the
entire value, she can hit enter when the desired value is highlighted.

I need a couple of things to help get this right. Is the form a bound form
or an unbound form? The reason I ask is that with a bound form, you don't
need to perform a query. It the form is unbound, why? You would be giving
up a lot of functionality and making a lot more work for yourself.
Also, are you saying you want to be able to search by EMP_ID or by either
the first or last name? Not an issue, I just need to know.
--
Dave Hargis, Microsoft Access MVP


"rich" wrote:

Hello Dave, and thanks for the reply.

When I read your response, about 50% of it makes sense to me.

I don't understand why you suggest using a combo box. My thinking was to
use a text box to capture search criteria from the user. In my case, either
an Emp_ID or Emp_Lname and Emp_Fname. When either of the criteria are
entered, the "WHERE" portion of the query string is built based upon the
criteria the user has entered. Why do you suggest using a cbo? In my
situation, wouldn't a txtbox be better suited? I only expect to return 1
record from the query.

Here is a little more detail on my initial form design...

The form has objects for each column in the employee table. When the form
opens, all fields are hidden except for the EMP_ID and FName & LName. After
user enters the search criteria, the query is performed. Assuming a matching
record is found, all fields are then made visable and populated with the
record found by the query.

Ok, so back to your response.
I understand the setting of the properties. I'm not sure at what point they
should be set however. I guess all this is in the After_Update event.


Thanks,
Rich





"Klatuu" wrote:

The typical way to accomplish this is with an unbound combo box control. You
use two of the control's properties for this. The After Update event is to
locate the desired record and make it the form's current record. The Not In
List event to allow the user to either cancel the search or add a new entry
to the table.

Let's start with configuring the control and doing the After Update, that
is the easier part.
The combo box will need a Row Source, that is, a list of items that will be
displayed in the combo.In an Employee situation, there is usually an Employee
ID or Number assigned to the employee and the employee's name. For the
example, we will assume it is done correctly and there are separate fields
for first and list names.
Now the control's properties.
It needs a name. I will call it cboEmployee
It needs a row source. That should be a query based on the employee table
and include the EmployeeID and name. You can add other columns if you want
the user to see them, but for this I would write it as:
SELECT EMP_ID, EMPLNAME & ", " & EMP_FNAME AS E_NAME FROM tblEmployee
Now, that query will give us 2 columns, so the column count property should
be set to 2. The Bound column should be set to 1. That will make the EMP_ID
the field that will be used for the lookup. Set the Column Width property to
0";2"
That will make the first column(EMP_ID) invisible and the the name column 2"
wide. That you can adjust as needed. Also, set the Limit to List property to
Yes. This is necessary to get the Not In List event to fire. Set the Auto
Expand property to Yes. It makes it easier for the user to type in and find
a name quickly.

Now, we need to be able to use the combo so that when the user selects.
Again, this is in the After Update event:

Private Sub cboEmployee_AfterUpdate()

With Me.RecordsetClone
.FindFirst "[EMP_ID] = " & Me.cboEmployee
If Not .NoMatch Then
Me.BookMark = .BookMark
End If
End With
End Sub

The With...End With tells us that all the code between these statements
where the item begins with a period are properties of the RecordsetClone
object. The FindFirst method searches a copy of the form's recordset
(identified in the form's Record Source) looking for the value in the combo
box. If it finds a match, it positions the matching record so that it is the
form's current record.

Now, get this part working first, then we will attack the Not In List for
adding new records.

--
Dave Hargis, Microsoft Access MVP


"rich" wrote:

Hello,

I need to perform a search on an employee but don't know very much about
performing searches.

Here is what I believe I should be doing. Please advise if there are other
considerations.

1. User enters the empID or (last name AND first name). Run the select
query.

2. If a matching record is found, Then
populate the form
set a record_found flag
Else
display an appropriate error message
clear and re-display the form
End If
3. If record was found
edit the appropriate fields on the form
update the table with the records that were changed.
End If

I also need to design any appropriate code in case there is an error but
don't know what to consider here.

If you can point me to some quality on-line resources that detail using
ADO so that I can run select, update and delete queries, that would be very
helpful.

TIA,

Rich


.

Loading