Value of field on change

[Vronsky]

I have a routine in VB6 which is poular with users - they start to type in a textbox and a grid below displays a steadily narrowing number of choices. In other words, the 'onchange' event of the textbox is linked to a SQL query which is the record source for the data grid. This depends on the new value of the text field being available at the 'onchange' event, which it is in VB6. It isn't in Access, which will always return the value of the text field before changes began - often a null.

I can work around this behaviour with code which onchange moves focus to another field, moves it back and resets sellength and selstart. What a fuss - and it still fails if a space is typed. Is there a cute way to find the current contents of a textbox while it still has focus?

[Bob Fitz]

It isn't in Access, which will always return the value of the text field before changes began - often a null.

Access 2003 Does have an "on change" property for textboxes.

[orange]

Not sure I'm following. I responded to a question recently where person had a listbox with people's names. On the same form he had a textbox in which to enter Person Name.
As you type, the records in the listbox are refined (filtered). Using the On Change event
I 'm attaching it. You can check if it's applicable to you.

[Vronsky]

Thank you Orange, that works, though I'm not sure why. If I simply have this code:

mycontrol_change

msgbox mycontrol.value

..it fails, saying mycontrol.value is null. I guess putting it into a sql statement and doing a requery must momentarily move focus from mycontrol (hence you have to re-set selstart etc.). I think there would still be a problem with it handling a space in the text box, though (Access removes trailing spaces) - have yet to test.

[orange]

I'm busy with something else, but if you just typed

mycontrol_change

msgbox mycontrol.value

I don't think that would work. You haven't (at least I don't think you have ) processed the Mycontrol_change event.

[Vronsky]

Orange: your example with the staff form works well. How do I adapt the syntax of the 'SELECT' statement in the recordsource to search within a field, i.e.

WHERE [Fieldname] LIKE '*txtSearchString*'

Actually what I need is:

WHERE [Fieldname1] LIKE '*txtSearchString*' OR [Fieldname2] LIKE '*txtSearchString*'

[Vronsky]

Here's a successful statement. Can't fathom the logic of where it needs quotes and ampersands and where it doesn't, just got this by trial an errror:

SELECT AddressID, Address1, PostCode FROM tblAddresses WHERE Address1 Like "*" & txtAddressPart & "*" Or PostCode Like "*" & txtAddressPart & "*";

[orange]

Using my form as example you would use this code for the Where clause

"WHERE " _
& "[Fieldname1] LIKE '*" & Forms!frmStaff!txtSearch & "*' OR " _
& "[Fieldname2] LIKE '*" & Forms!frmStaff!txtSearch & "*'"

This Forms!frmStaff!txtSearch identifies the Form and the search textbox.

Adjust the code to represent your fieldnames and your form and textbox for search as appropriate.
Good luck.

[orange]

As for the logic.

Select these fields FROM This Table
where the following condition(s) is/are true.

As for the quotes:
You need quotes around text datatype values.
In this case since you want to compare a textfield's value with the value of a control on a form (txtsearchbox), in addition you want it to be Like so,

"WHERE fieldname1 LIKE '*" & Forms!frmStaff!txtSearch & "*'"

fieldname1 is your fieldname
Like '*" prepends an * to the value that is in Forms!frmStaff!txtSearch
(note you don't want Forms!frmStaff!txtSearch inside quotes, that would be a name,
you want to have Access determine the value of that expression and concatenate the value with * before and after the value)

This "WHERE fieldname1 LIKE '*" is just a text string and Access will process it as a string

Forms!frmStaff!txtSearch is the name of a control on a form and Access will determine it's value before concatenating it with the text string above. Let's say
the textbox contains "Smith"

eg "WHERE fieldname1 LIKE '*" & Smith & "*'"

and the & "*'" is just another string, whose purpose is to add a closing *'

So when being processed, the text value Smith is surrounded by wildcards (*) to give '*Smith*'. You need the quotes because you are comparing the text content of Fieldname1 with a text value "*Smith*"

Ta da!

[nicknameoscar]

What happens if you try:

Me.MyControl.Text

instead of

Me.MyControl.Value

My understanding is that .Text will contain the edited value while .Value will not be updated until the AfterUpdate event.

(If I am understanding the original question correctly then I am using a textbox to update a separate form with a listbox. As each character is typed the contents of the listbox narrows in on names that begin with the letters being typed.)

Edit: In re-reading the posts it appears maybe what I am suggesting was already suggested, it wasn't clear. My apologies if that was the case (I didn't open orange's database).

[orange]

Perhaps i missed a point in post #4. I thought you were trying to cause the Change event to happen with your code.

I tried something else with a txtbox .text and .value where there was a change event.

I added a button whose Click event contains
MsgBox Nz(Me.txtSearch, "Null")

After I type characters into the txtBox, and the listbox changes to reflect the filter, I click my button to see what's in the txtBox
The code above which works fine

I changed it to

MsgBox Nz(Me.txtSearch.text, "Null")
and another to
MsgBox Nz(Me.txtSearch.value, "Null") which works just as my original example

The .text example gives an error message
"You can't reference a property or method of a control unless the control has the focus"

[Vronsky]

My thanks to all those who helped - much useful guidance - I've got it doing what I want now.

[nicknameoscar]

So what fixed the problem. You know it isn't fair (yes life isn't fair) for the people that attempted to help you and it isn't fair to people who the search the forums for problems like yours to not have the solution.

[Vronsky]

Oh, what a strop! There was no solution, but I discovered from Orange that the name of a control can be placed in a sql string for a recordsource, even though that statement can have no meaning at the time. It also appears from Orange's (excellent) example that requerying a list will momentarily shift focus from a text field so that its value is updated and becomes available to the sql string, although this is not apparent from anything that happens on the screen and seems undocumented behaviour. Summarising this, the peculiarity of behaviour in Access is that this doesn't work:

--------------
sub mycontrol_change(yada yada)

list.recordsource="SELECT * FROM table WHERE fieldname=" & mycontrol.value

-------------
because the value of mycontrol is not available until mycontrol loses focus.

But this does: Set the record source in the list to the above sql, and the 'change' event on mycontrol will pass the correct value of mycontrol to the sql on list.requery. Strange, huh?

But that still doesn't solve the problem of the user's last character typed into mycontrol being a space - Access always strips it. I had to partly take a different approach, and partly abandon some of the functionality I'd hoped for. You're right about life not being fair - very profound idea you have there.

[nicknameoscar]

For the benefit of anyone searching for a solution to Access stripping trailing spaces the below is what I do to get around it:

Private Sub HorseName_Change()
'the purpose of this routine is to compare the characters typed into the horse name control
'to horse names that are already in tblHorses
'each new character typed is added to the previous character(s) typed such that the horse name is
'"constructed on the fly"
'each new character causes tblHorses to be searched again, only this time with the new character added
'when no matches are found the listboxes are closed and the user then is able to either continue
'typing in the name or move to the next field to enter further data
Dim strLastCharacter As String
Dim strCharacterCount As String
Dim strTempCopy As String
Dim strTemp As String
Dim frm As Form
 
Set frm = Me
 
strWhichCtrl = "NH"
 
'copy characters typed in form to temporary string
strTemp = frm!HorseName.Text
 
If strTemp = "" And SysCmd(acSysCmdGetObjectState, acForm, "frmNameList") > 0 Then
DoCmd.Close acForm, "frmNameList"
Exit Sub
End If
 
'determine the last character typed
strLastCharacter = Right(strTemp, 1)
 
'determine the total number of characters typed
strCharacterCount = Len(strTemp)
 
'If a 'space' is at the end of the string
'then append a question mark character after
'the 'space'
If strLastCharacter = " " Then
strTemp = strTemp & "?"
End If
 
'If a '?' is at the end of the string
'then get the contents of the string
'minus the '?'
If strLastCharacter = "?" Then
strTemp = Left(strTemp, (strCharacterCount - 1))
End If
strTempCopy = strTemp
Call frmNameList_Open(strTemp, strWhichCtrl, Me)
 
'make sure names are in proper case (first character of each name is upper case, remaing characters
'are lower case
strTempCopy = StrConv(strTempCopy, vbProperCase)
 
'the below code moves the focus back to HorseName, disables the OnChange event procedure, writes the
'current typed in text back to HorseName, positions the cursor after the last character in that text
'and re-enables the OnChange event procedure
frm.SetFocus
frm!HorseName.SetFocus
frm!HorseName.OnChange = ""
frm!HorseName.Text = strTempCopy
frm!HorseName.SelStart = strCharacterCount
frm!HorseName.OnChange = "[Event Procedure]"
Set frm = Nothing
End Sub 
 
 
Public Sub frmNameList_Open(strSearchTerm As String, strWhoCalled As String, frm As Form)
'NameList listbox contains only individual horse, owner, rider or trainer names
'GroupList listbox contains the horse, owner, rider and trainer associated with a particular entry
' each 'group' is saved in tblGroups; the purpose of which is to prevent having to re-type the entire
' horse, owner, rider and trainer for each horse show
'the purpose of this code is to open 'frmNameList and to fill the NameList listbox and the
' GroupList listbox with names that have been filtered according to the SQL statements below
'a particular SQL string is built according to whether the user is entering a horse or pony, rider, owner or
'trainer name...that determination is based on the character passed to this routine in "strWhoCalled"
'called from:
' 1. frmEnterNewEntry - HorseName_Change event subroutine
' 2. frmEnterNewEntry - OwnerName_Change event subroutine
' 3. frmEnterNewEntry - RiderName_Change event subroutine
' 4. frmEnterNewEntry - TrainerName_Change event subroutine
' 5. frmChange - NewName_Change event subroutine
Select Case strWhoCalled
Case "CH" 'build Horse NameList SQL statement when frmEntryDetails has called this routine
strNameListSQL = "SELECT tblHorses.HorseID, tblHorses.HorseName, tblHorses.HorseSize " & _
"FROM tblHorses WHERE (((tblHorses.HorseName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblHorses.HorseName;"
 
Case "CO" 'build Owner NameList SQL statement when frmEntryDetails has called this routine
strNameListSQL = "SELECT tblOwners.OwnerID, tblOwners.OwnerName " & _
"FROM tblOwners WHERE (((tblOwners.OwnerName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblOwners.OwnerName;"
 
Case "CR" 'build Rider NameList SQL statement when frmEntryDetails has called this routine
strNameListSQL = "SELECT tblRiders.RiderID, tblRiders.RiderName, " & _
"tblRiders.AdultJunior FROM tblRiders WHERE (((tblRiders.RiderName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblRiders.RiderName;"
 
Case "CT" 'build Trainer NameList SQL statement when frmEntryDetails has called this routine
strNameListSQL = "SELECT tblTrainers.TrainerID, tblTrainers.TrainerName " & _
"FROM tblTrainers WHERE (((tblTrainers.TrainerName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblTrainers.TrainerName;"
Case "NH" 'build Horse NameList SQL statement
strNameListSQL = "SELECT tblHorses.HorseID, tblHorses.HorseName, tblHorses.HorseSize " & _
"FROM tblHorses WHERE (((tblHorses.HorseName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblHorses.HorseName;"
 
'build GroupList SQL statement
strGroupListSQL = "SELECT tblGroups.GroupID, tblGroups.HorseID, tblHorses.HorseName, " & _
"tblHorses.HorseSize, tblGroups.RiderID, tblRiders.RiderName, " & _
"tblRiders.AdultJunior, tblGroups.OwnerID, tblOwners.OwnerName, " & _
"tblGroups.TrainerID, tblTrainers.TrainerName " & _
"FROM tblTrainers INNER JOIN (tblRiders INNER JOIN (tblOwners INNER JOIN " & _
"(tblHorses INNER JOIN tblGroups ON tblHorses.HorseID = tblGroups.HorseID) ON " & _
"tblOwners.OwnerID = tblGroups.OwnerID) ON tblRiders.RiderID = " & _
"tblGroups.RiderID) ON tblTrainers.TrainerID = tblGroups.TrainerID " & _
"WHERE (((tblHorses.HorseName) Like """
strGroupListSQL = strGroupListSQL & strSearchTerm
strGroupListSQL = strGroupListSQL & "*""))ORDER BY tblHorses.HorseName;"
Case "NR" 'build Rider NameList SQL statement
strNameListSQL = "SELECT tblRiders.RiderID, tblRiders.RiderName, " & _
"tblRiders.AdultJunior FROM tblRiders WHERE (((tblRiders.RiderName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblRiders.RiderName;"
 
Case "NO" 'build Owner NameList SQL statement
strNameListSQL = "SELECT tblOwners.OwnerID, tblOwners.OwnerName " & _
"FROM tblOwners WHERE (((tblOwners.OwnerName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblOwners.OwnerName;"
 
Case "NT" 'build Trainer NameList SQL statement
strNameListSQL = "SELECT tblTrainers.TrainerID, tblTrainers.TrainerName " & _
"FROM tblTrainers WHERE (((tblTrainers.TrainerName) Like """
strNameListSQL = strNameListSQL & strSearchTerm
strNameListSQL = strNameListSQL & "*""))ORDER BY tblTrainers.TrainerName;"
End Select
DoCmd.OpenForm "frmNameList"
Forms!frmNameList.SetFocus
Forms!frmNameList!NameList.Visible = True
Forms!frmNameList!NameList.SetFocus
Forms!frmNameList!NameList.RowSource = strNameListSQL
DoCmd.Requery "NameList"
Forms!frmNameList!UnusedControl.SetFocus 'control's only purpose is to have somewhere to shift focus to
'if there are no matches to the SQL search then make NameList not visible
If Forms!frmNameList!NameList.ListCount < 2 Then
Forms!frmNameList!NameList.Visible = False
End If
Select Case strWhoCalled
Case "NH" 'NH = New horse
Forms!frmNameList!GroupList.Visible = True
Forms!frmNameList!GroupList.SetFocus
Forms!frmNameList!GroupList.RowSource = strGroupListSQL
DoCmd.Requery "GroupList"
Forms!frmNameList!UnusedControl.SetFocus
'if there are no matches to the SQL search then make GroupList not visible
If Forms!frmNameList!GroupList.ListCount < 2 Then
Forms!frmNameList!GroupList.Visible = False
End If
 
'if neither listbox is visible (no matches to the SQL searches) then close form
If Forms!frmNameList!NameList.Visible = False And _
Forms!frmNameList!GroupList.Visible = False Then
DoCmd.Close acForm, "frmNameList"
frm.SetFocus
 
End If
 
Case "NR", "NO", "NT", "CH", "CR", "CO", "CT"
'field calling is riders, owners or trainers, thus NameList is the only listbox
'to open and it was opened already, here we are just determining if there are
'any records to display, if not then close frmNameList
If SysCmd(acSysCmdGetObjectState, acForm, "frmNameList") > 0 And _
Forms!frmNameList!NameList.ListCount < 2 Then
DoCmd.Close acForm, "frmNameList"
frm.SetFocus
End If
 
End Select
End Sub

Probably not as efficient or elegant as the professionals would do but it works.

The idea is that when a "space" character is detected a "?" (any character would probably work) is inserted at end of the string. This way Access nevers sees the space.

I think "orange" mentioned this method in one of the posts above.