Search in field without overwrite

[Guest]

Hi to all,

I have code that jumps to records that meet criterior of what's typed into a
text box. The way it's supposed to work is a user types a few characters in
the text box and any record(s) meeting the criterior become the current
record.

In my form I have several record fields including Last Name. I have a
separate text box "LookupLast" bound to the Last Name field. The problem is
when user types into LookupLast text box the Last Name field gets overwritten.

Can anyone look at code below and see what can be done to make it work!!

Below is VB code for LookupLast text box:

Option Compare Database
Option Explicit
Dim strFind As String
Dim datLastKeyPress As Date

Private Sub LookupLast_KeyPress(KeyAscii As Integer)
Dim strChr As String
strChr = Chr(KeyAscii)
If DateDiff("S", datLastKeyPress, Now) > 2 Then
strFind = strChr
Else
strFind = strFind & strChr
End If
DoCmd.FindRecord strFind, acStart, , , , , True
If Left(LookupLast, Len(strFind)) <> strFind Then
strFind = strChr
DoCmd.FindRecord strFind, acStart, , , , , True
End If
datLastKeyPress = Now
End Sub

 

[Guest]

Hi,
There are lots of ways to achieve this, but simplest way to do this without
having to change the method you are currently using would be to place a new
TextBox control over the top of the "LastName" Textbox that is not bound to
the data. Call it "txtLastName". Since it is placed on the form after the
first TextBox was it will be on top by default, thereby hiding the "real"
"LastName" field. You have to hide it in this manner 'cos later on we will
want to give it Focus and you could not do that if we just set the Visible
property to False.
Change your KeyPress Sub to react to txtLastName_KeyPress, so that when your
user types into it you can assign the characters to your string variables.
Then add a line of code just before you call the "DoCmd.FindRecord" so that
you are searching on the real "LastName" ---- txtLastName.SetFocus
Then after the record is found we need to assign the "LastName" value to the
"txtLastName" TextBox to display it, and since we will need to do this every
time the user moves to a new record (since the REAL LastName Field is hidden)
then place this line of code in the Form_Current() Event
LastName.Value = txtLastName.Value

TIP - whilst you are testing this and so that you know which control you are
looking at why not set the ForeColor to Red for txtLastName.

 

[Guest]

Geoffs,

I did everything you suggested to the letter, but I get the following error
message:

Run-time error "2162":
A macro set to one of the current field's properties failed because of
an error in a FindRecord action argument.

Geoffs, you said there are several ways to achieve what I'm trying to do.
Can you suggest a different way, I don't mind about re-coding everything. The
code I currently have is actually " borrowed ;-)" from another database so I
won't miss it.

-Sky

 

[Guest]

Hi,
Cann't see why that doesn't work if you have copied it correctly.
Anyway - here's another way.
Try using the Forms "Filter" to display the record that you want.
Place a ComboBox onto the Form and set its RowSourceType to "Table/Query"
and then click on the elipses (...) next to "Row Source" and use the query
builder to select from your source table the Record ID Field and the LastName
Field, set it to sort by LastName so that the list will be in alphabetical
order.
Now make sure that the BoundColumn property is set to 1. On the Format Tab
of the Properties set ColumnCount to 2, and in ColumnWidths set 0cm;3cm so
that the first (ID) column dooes not display. You can set the second colum
width to something other than 3 if you want to.
Now - In the ComboBox_Click Event assuming the ID Field to be called "ID" -

Form.Filter = "[ID]=" & ComboBox.Column(0)
Form.FilterOn = True

Your users can then either scroll down the Combo and Click or if they start
to type the last name in the combo's textbox area it will auto-complete the
name for them and hitting "Enter" will also fire the event.
The relevant record will be displayed.

 

[Guest]

Geoffs,

Well, it almost works. After a name is selected from the Combo box and I
hit Enter to make that name the current record, all records after that are
blank. From the record selector at the bottom of form window I should be able
to move to next record or backward one record but can't.

It seems the ID gets set to "1" when a record is selected from the Combo
box, even if it isn't designated as #1 in the table. I followed everything to
the letter again.

I have to close out the form and reopen it to get things back on track. If I
can get the record order to match up, then I think it will work flawlessly.

Here's RowSource for Combo Box:

SELECT Application.ContactID, Application.[Last Name] FROM Application ORDER
BY Application.[Last Name];

Hi,
Cann't see why that doesn't work if you have copied it correctly.
Anyway - here's another way.
Try using the Forms "Filter" to display the record that you want.
Place a ComboBox onto the Form and set its RowSourceType to "Table/Query"
and then click on the elipses (...) next to "Row Source" and use the query
builder to select from your source table the Record ID Field and the LastName
Field, set it to sort by LastName so that the list will be in alphabetical
order.
Now make sure that the BoundColumn property is set to 1. On the Format Tab
of the Properties set ColumnCount to 2, and in ColumnWidths set 0cm;3cm so
that the first (ID) column dooes not display. You can set the second colum
width to something other than 3 if you want to.
Now - In the ComboBox_Click Event assuming the ID Field to be called "ID" -

Form.Filter = "[ID]=" & ComboBox.Column(0)
Form.FilterOn = True

Your users can then either scroll down the Combo and Click or if they start
to type the last name in the combo's textbox area it will auto-complete the
name for them and hitting "Enter" will also fire the event.
The relevant record will be displayed.

Geoffs,

I did everything you suggested to the letter, but I get the following error
message:

Run-time error "2162":
A macro set to one of the current field's properties failed because of
an error in a FindRecord action argument.

Geoffs, you said there are several ways to achieve what I'm trying to do.
Can you suggest a different way, I don't mind about re-coding everything. The
code I currently have is actually " borrowed ;-)" from another database so I
won't miss it.

-Sky

 

[Guest]

Geoffs,

I went out and bought a book "Access 2002 Desktop Developer's Handbook"
which has a CD with examples. In it I found the code I need to do the lookups
for Last Name field. The following code is called from a Combo box named
cboCompany in it's AfterUpdate property:

Option Compare Database
Option Explicit
Const adhcQuote = """"

Private Sub cboCompany_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
' rst.FindFirst "[CompanyName] = " & _
adhcQuote & adhHandleQuotes(Me!cboCompany) & adhcQuote
rst.FindFirst "[Last Name] = " & _
adhHandleQuotes(Me!cboCompany, adhcQuote)

If rst.NoMatch Then
MsgBox "No match was found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

The RowSource has the following SQL:

SELECT Application.[Last Name], Application.[First Name] FROM Application
ORDER BY Application.[Last Name];


Of course, there are modules required to pull this off but I don't know
which ones are actually required for cboCompany. Rather than guess, I'll list
them below:

basClone
basContainers
basCreateTable
basFixQuotes
basHandleQuotes
basPK
basRecordSet
basRelations
basTestProperties

I don't know if you can use any of what I put here. If so, pass it along
freely. At any rate thanks for all your help on this

-Sky

Hi,
Cann't see why that doesn't work if you have copied it correctly.
Anyway - here's another way.
Try using the Forms "Filter" to display the record that you want.
Place a ComboBox onto the Form and set its RowSourceType to "Table/Query"
and then click on the elipses (...) next to "Row Source" and use the query
builder to select from your source table the Record ID Field and the LastName
Field, set it to sort by LastName so that the list will be in alphabetical
order.
Now make sure that the BoundColumn property is set to 1. On the Format Tab
of the Properties set ColumnCount to 2, and in ColumnWidths set 0cm;3cm so
that the first (ID) column dooes not display. You can set the second colum
width to something other than 3 if you want to.
Now - In the ComboBox_Click Event assuming the ID Field to be called "ID" -

Form.Filter = "[ID]=" & ComboBox.Column(0)
Form.FilterOn = True

Your users can then either scroll down the Combo and Click or if they start
to type the last name in the combo's textbox area it will auto-complete the
name for them and hitting "Enter" will also fire the event.
The relevant record will be displayed.

Geoffs,

I did everything you suggested to the letter, but I get the following error
message:

Run-time error "2162":
A macro set to one of the current field's properties failed because of
an error in a FindRecord action argument.

Geoffs, you said there are several ways to achieve what I'm trying to do.
Can you suggest a different way, I don't mind about re-coding everything. The
code I currently have is actually " borrowed ;-)" from another database so I
won't miss it.

-Sky

 

[Guest]

Hi Sky,
Thanks for that.
I would just make one comment here, and you must decide for yourself which
method to use. The sample you quote uses DAO, which is a data access method
which Microsoft are moving away from in favour of ADO. Indeed, in Access 2002
this was the preferred method as that way your code could work either in a
standard mdb database using the Jet Database engine, or in an adp project
that uses SQL-Server as the database. In other words it gives you what is
referred to as "scaleability". ADO itself has now been superceeded by ADO.NET
which is a logical progression of the method into the DotNet development
environment, so my personal thoughts are that if code is to be written for
data access then DAO may not be the best route to go. However, don't take
that the wrong way, in many scenarios it may still be the best method to use,
particularly where you are certain that you will not want to upgrade your
Access application to a Client/Server application using SQL-Server.
You will find loads of very interesting articles and information here on the
Microsoft site all about the different data access technologies, and which is
best under which circumstances, starting point is
http://msdn.microsoft.com/data/default.aspx
Good Luck!
Geoff

Geoffs,

I went out and bought a book "Access 2002 Desktop Developer's Handbook"
which has a CD with examples. In it I found the code I need to do the lookups
for Last Name field. The following code is called from a Combo box named
cboCompany in it's AfterUpdate property:

Option Compare Database
Option Explicit
Const adhcQuote = """"

Private Sub cboCompany_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
' rst.FindFirst "[CompanyName] = " & _
adhcQuote & adhHandleQuotes(Me!cboCompany) & adhcQuote
rst.FindFirst "[Last Name] = " & _
adhHandleQuotes(Me!cboCompany, adhcQuote)

If rst.NoMatch Then
MsgBox "No match was found."
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

The RowSource has the following SQL:

SELECT Application.[Last Name], Application.[First Name] FROM Application
ORDER BY Application.[Last Name];


Of course, there are modules required to pull this off but I don't know
which ones are actually required for cboCompany. Rather than guess, I'll list
them below:

basClone
basContainers
basCreateTable
basFixQuotes
basHandleQuotes
basPK
basRecordSet
basRelations
basTestProperties

I don't know if you can use any of what I put here. If so, pass it along
freely. At any rate thanks for all your help on this

-Sky

Hi,
Cann't see why that doesn't work if you have copied it correctly.
Anyway - here's another way.
Try using the Forms "Filter" to display the record that you want.
Place a ComboBox onto the Form and set its RowSourceType to "Table/Query"
and then click on the elipses (...) next to "Row Source" and use the query
builder to select from your source table the Record ID Field and the LastName
Field, set it to sort by LastName so that the list will be in alphabetical
order.
Now make sure that the BoundColumn property is set to 1. On the Format Tab
of the Properties set ColumnCount to 2, and in ColumnWidths set 0cm;3cm so
that the first (ID) column dooes not display. You can set the second colum
width to something other than 3 if you want to.
Now - In the ComboBox_Click Event assuming the ID Field to be called "ID" -

Form.Filter = "[ID]=" & ComboBox.Column(0)
Form.FilterOn = True

Your users can then either scroll down the Combo and Click or if they start
to type the last name in the combo's textbox area it will auto-complete the
name for them and hitting "Enter" will also fire the event.
The relevant record will be displayed.

Geoffs,

I did everything you suggested to the letter, but I get the following error
message:

Run-time error "2162":
A macro set to one of the current field's properties failed because of
an error in a FindRecord action argument.

Geoffs, you said there are several ways to achieve what I'm trying to do.
Can you suggest a different way, I don't mind about re-coding everything. The
code I currently have is actually " borrowed ;-)" from another database so I
won't miss it.

-Sky

:

Hi,
There are lots of ways to achieve this, but simplest way to do this without
having to change the method you are currently using would be to place a new
TextBox control over the top of the "LastName" Textbox that is not bound to
the data. Call it "txtLastName". Since it is placed on the form after the
first TextBox was it will be on top by default, thereby hiding the "real"
"LastName" field. You have to hide it in this manner 'cos later on we will
want to give it Focus and you could not do that if we just set the Visible
property to False.
Change your KeyPress Sub to react to txtLastName_KeyPress, so that when your
user types into it you can assign the characters to your string variables.
Then add a line of code just before you call the "DoCmd.FindRecord" so that
you are searching on the real "LastName" ---- txtLastName.SetFocus
Then after the record is found we need to assign the "LastName" value to the
"txtLastName" TextBox to display it, and since we will need to do this every
time the user moves to a new record (since the REAL LastName Field is hidden)
then place this line of code in the Form_Current() Event
LastName.Value = txtLastName.Value

TIP - whilst you are testing this and so that you know which control you are
looking at why not set the ForeColor to Red for txtLastName.