How to code/perform a search

[rich]

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

 

[ruralguy via AccessMonster.com]

See if there are some useful tips in this link:
http://allenbrowne.com/ser-62.html

 

[Klatuu]

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.

 

[rich]

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

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

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

 

[Klatuu]

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

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

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

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

 

[rich]

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

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

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

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


:

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

 

[Klatuu]

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

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

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

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





:

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


:

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

 

[rich]

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?

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

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

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


:

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





:

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


:

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

 

[Klatuu]

First, the article you referenced is pretty much out of date. You will find
that most of us are still using DAO if we are using Jet as the database
engine. ADO has not proven to be all that it first claimed to be.

How many combos you use will depend on how you want to design it. I would
use one combo, but with an option group to select which one I intend to use.
I doubt that I would use 3. For the name, I would concatenate the last and
first names in one combo, So I guess for starters I would suggest two. One
for emp_id and one for name.

I believe you are overthinking the problem. This is such a common
technique. I probably have coded this hundreds of times.
The click event would not be correct. As I have stated twice already, use
the After Update event.

I did not say to create a recordset. A bound form has a recordset. It is
defined in the form's Record Source property. It will be either a table or
query defined in that property. A form also has a recordset clone. It is an
exact copy of the recordset at any point in time. It differs from the form's
recordset in that it is not visible and it has a different current record
pointer and hence a different bookmark.

You may be confused about creating a recordset by the first line of code:
With Me.RecordsetClone

That is not creating a recordset. It is creating a reference to the
recordsetclone object. Another way to do it that may be more familiar is to
create an object variable:

Dim rst As Recordset
Set rst = Me.RecordsetClone

I prefer the With ... End With for several reasons. First, it is less
coding. Second, it executes faster because Jet doesn't have to repeatedly
resolve the rst reference. It knows that everything between With and End
With that starts with a period is either a property or method of the object
identified in the With statement.
--
Dave Hargis, Microsoft Access MVP

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?

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

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



:

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


:

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





:

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


:

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

 

[Jamie Collins]

the article you referenced is pretty much out of date. You will find
that most of us are still using DAO if we are using Jet as the database
engine.

The article is not marked with a date but I would say it was circa
1999, perhaps as late as 2000. Believe me, that's pretty recent for an
MSDN article on the subject!

I note the article says:

"Although DAO is still present in Access 2000, it's no longer the
preferred method for retrieving data, and it's not the best library to
learn for new applications."

I can cite articles with similar recommendations:

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
"In previous versions of Access, Data Access Objects (DAO) was the
primary data access method. That has now changed. Although DAO is
still supported, the new way to access data is with ADO."
(February 2000)

http://msdn2.microsoft.com/en-us/library/aa164825(office.10).aspx
"If you are creating new data access components, you should consider
using ADO for its advanced features, simplified object model, and
support for multiple data sources ....an Access database solution that
will later be upgraded to SQL Server ...new data access components
that work with SQL Server, multidimensional data, and Web
applications." [Only recommends using DAO for existing solutions that
use DAO]
(no date)

http://msdn2.microsoft.com/en-us/library/aa164825(office.10).aspx
"This chapter reviews the DAO and ADO data access models, with the
primary emphasis on ADO as a programming model. The brief DAO coverage
introduces core development concepts and provides a historical
perspective on data access within Access. Since DAO will not play a
critical role in any subsequent chapters..."
(no date)

These articles are quite explicit in their message: ADO new (good),
DAO old (bad). I take the point about the dates of the articles but
can you link any more recent Microsoft articles that promote the
general usage of DAO (or ACEDAO) in preference to ADO? I've yet to
encounter one. (I say 'general usage' because there have always been
functionality specific to each.)

Similarly, there are many articles about moving from DAO to ADO:

http://msdn2.microsoft.com/en-us/library/aa189735(office.10).aspx
Converting DAO Code to ADO
(no date)

http://support.microsoft.com/default.aspx/kb/225048
INFO: Issues Migrating from DAO/Jet to ADO/Jet
(Last Review August 8, 2003)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp
Porting DAO Code to ADO with the Microsoft Jet Provider
(July 1999, Updated April 2004)

Do you know of any articles on the subject of moving from ADO to DAO
(or ACEDAO)? I do not.

ADO has not proven to be all that it first claimed to be.

What unrealized claims are you referring to? Honestly, the emphasis is
on you to be specific here because I could link to a hundred ADO
claims that have been proven to be!

Jamie.

--

 

[Klatuu]

Hello Jamie,
You and I seldom agree, but I do respect you knowledge.
I cannot point to specific articles promoting a preference for DAO; however,
Microsoft does have an agenda, so I would expect them to promote the use of
ADO.

You will have to forgive me for not remembering specific versions, but at
some point in the past, MS changed the native format from DAO to ADO and
latter back to DAO.

My first objection to ADO is that it is an ActiveX object. Perhaps I am
prejudice, but I have found ActiveX objects to be problematic. Also, MS
claims a simpler object model, but I don't really find that to be the case.
IMHO, DAO fits more comfortablly with the Access object model and with VBA.

I have also done some performance testing and found ADO does not perform as
well as DAO.

I will agree that if you are contemplating an eventual upsize to SQL Server,
ADO is a cleaner path. But, as to a Web app? I would not recommend using
Access for a web app. DAP has never matured as I had hoped.

--
Dave Hargis, Microsoft Access MVP

the article you referenced is pretty much out of date. You will find
that most of us are still using DAO if we are using Jet as the database
engine.

The article is not marked with a date but I would say it was circa
1999, perhaps as late as 2000. Believe me, that's pretty recent for an
MSDN article on the subject!

I note the article says:

"Although DAO is still present in Access 2000, it's no longer the
preferred method for retrieving data, and it's not the best library to
learn for new applications."

I can cite articles with similar recommendations:

http://msdn2.microsoft.com/en-us/library/aa140015(office.10).aspx
"In previous versions of Access, Data Access Objects (DAO) was the
primary data access method. That has now changed. Although DAO is
still supported, the new way to access data is with ADO."
(February 2000)

http://msdn2.microsoft.com/en-us/library/aa164825(office.10).aspx
"If you are creating new data access components, you should consider
using ADO for its advanced features, simplified object model, and
support for multiple data sources ....an Access database solution that
will later be upgraded to SQL Server ...new data access components
that work with SQL Server, multidimensional data, and Web
applications." [Only recommends using DAO for existing solutions that
use DAO]
(no date)

http://msdn2.microsoft.com/en-us/library/aa164825(office.10).aspx
"This chapter reviews the DAO and ADO data access models, with the
primary emphasis on ADO as a programming model. The brief DAO coverage
introduces core development concepts and provides a historical
perspective on data access within Access. Since DAO will not play a
critical role in any subsequent chapters..."
(no date)

These articles are quite explicit in their message: ADO new (good),
DAO old (bad). I take the point about the dates of the articles but
can you link any more recent Microsoft articles that promote the
general usage of DAO (or ACEDAO) in preference to ADO? I've yet to
encounter one. (I say 'general usage' because there have always been
functionality specific to each.)

Similarly, there are many articles about moving from DAO to ADO:

http://msdn2.microsoft.com/en-us/library/aa189735(office.10).aspx
Converting DAO Code to ADO
(no date)

http://support.microsoft.com/default.aspx/kb/225048
INFO: Issues Migrating from DAO/Jet to ADO/Jet
(Last Review August 8, 2003)

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp
Porting DAO Code to ADO with the Microsoft Jet Provider
(July 1999, Updated April 2004)

Do you know of any articles on the subject of moving from ADO to DAO
(or ACEDAO)? I do not.

ADO has not proven to be all that it first claimed to be.

What unrealized claims are you referring to? Honestly, the emphasis is
on you to be specific here because I could link to a hundred ADO
claims that have been proven to be!

Jamie.

 

[Jamie Collins]

I cannot point to specific articles promoting a preference for DAO; however,
Microsoft does have an agenda, so I would expect them to promote the use of
ADO.

You will have to forgive me for not remembering specific versions, but at
some point in the past, MS changed the native format from DAO to ADO and
latter back to DAO.

I don't know what you mean by 'native format'. I think you might be
alluding to the default reference set to the DAO library in Access's
Visual Basic Editor i.e. a checkbox in the IDE. In Access 2000 it was
unchecked by default and it upset a lot of people who had to check it
manually to use DAO in a new project. From Access 2003 (IIRC) it has
been checked by default for DAO, as is also is for ADODB. To be
honest, I don't see why it was such a big deal but I'm glad ADO and
DAO are now both checked and everyone is happy

Generally speaking, ADO has more (and arguably improved) properties,
methods and events and via the OLE DB provider has some Jet 4.0-era
functionality not available to DAO or ACEDAO (the Access 2007
version). Similarly ACEDAO has Access 2007 engine functionality not
available to ADO via the OLE DB provider. On a larger scale there are
many subtle differences between ADO and DAO, of course, but the
mutually exclusive functionality tends to be of specialist interest
only.

Given the current position I would expect Microsoft to promote the use
of both ADO and ACEDAO in the short term and aim to effectively
'deprecate' ADO for Access by significantly enhancing ACEDAO. What has
actually happened in this Access 2007 era is that the Access team has
not significantly enhanced DAO in creating ACEDAO and Microsoft (via
Access Help and MSDN) have been virtually silent on recommending DAO,
ACEDAO and ADO for anything. I don't think there is anything sinister
or surprising about this; I think they are merely sitting on the
proverbial fence, I guess because some people got *really* upset when
they favoured ADO over DAO and they don't want to make the same error
of judgment.

Personally, I say: pick and choose, mix and match, and get the best
out of the relative merits of both ADO and DAO/ACEDAO. Of course, I
will always use ADO and will never go back to DAO myself, having been
*really* upset by the whole DAO poor-teardown-model-causes-memory-leak

My first objection to ADO is that it is an ActiveX object. Perhaps I am
prejudice, but I have found ActiveX objects to be problematic.

Erm, DAO is also ActiveX compliant. Is there a subtle point I'm not
seeing?

MS
claims a simpler object model, but I don't really find that to be the case.

I don't think so. ADO is a richer model, why would MS not promote that
aspect? They usually said things like "flatter object model
hierarchy" (e.g. can create a Recordset object without first creating
an explicit Connection object") or "lightweight" (e.g. if you only
need recordsets and no replication then you can ship just ADOR library
and omit JRO library; will DAO it's all or nothing) e.g.

Dim rs As new ADOR.Recordset
rs.Open "SQL query here", "connection string here"

You'd need a few more lines to do the same in DAO. And yes, no big
deal either way.

I have also done some performance testing and found ADO does not perform as
well as DAO.

I concur. For the vast majority of cases there will be no noticeable
difference; DAO may execute a certain SQL statement 100% faster than
ADO but when the total time ADO takes is 30 milliseconds who would
care? Always using DAO on grounds of performance is known in the trade
as 'premature optimization' (pejorative).

I can open a hierarchical recordset in 300 milliseconds using ADO; I
can't open a hierarchical in DAO despite waiting seven years now and

You and I seldom agree

I don't agree <g>. We tend only to correspond when we're in
disagreement and even then it tends to be minor stuff, don't you
think?

I do respect you knowledge.

And I respect yours. BTW belated and sincere congratulations for
making MVP.

Jamie.

--

 

[Klatuu]

All good points, Jamie.
You have obviously done more detailed research than I have. I am basing my
preference on personal experience. I find coding for ADO more verbous that
for ADO. For example, it is not necessary to supply a connect string to open
a DAO recordset.

As to the "simplified object model", I was refering to part of your post
(from MS, I understand)
"If you are creating new data access components, you should consider
using ADO for its advanced features, simplified object model...

I still think the ADO model is less intuitive and takes more code to
manipulate.

Thanks for the Congrats. I feel honored to be counted among those I
consider mentors.

BTW, is this your site?
http://www.jamiessoftware.tk

I really like the APE utility.

 

[Jamie Collins]

I still think the ADO model is less intuitive and takes more code to
manipulate.

Each to their own

BTW, is this your site?http://www.jamiessoftware.tk

No, not mine.

Jamie.

--

 

[Klatuu]

Exactly!
I appreciate a discussion with a knowledgeable and reasonable person
(even when they are dead wrong

(Sorry, you have to understand I play with matches at gas stations just to
watch people jump)