I need the code for event procedure of text box

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

I have a Text Box whose record source is a field of a query.
I want to update the query field upon 'change'
What code do I need to put in the event procedure to do that?
 
Are you sure about this, Bob?

The Change event fires with each keystroke, so firstly, you might need to
consider whether the data would be valid. For example, if the text box is
bound to a Date/Time field, it would not be valid when you have only typed
3/
Similarly if it is bound to a Number field, it would not be valid at the
point when you have only typed the negative sign.

If you want to proceed, the idea is to assign the Text property of the text
box to its Value, e.g.:
Me.Surname.Value = Me.Surname.Text
This has the side effect of triggering other events, and typically the
entire select becomes selected. Consequently, the next keystroke overwrites
everything in the text box. Therefore you need to store the SelStart and
SelLength before you assign the value, and then set SelStart again after
making the assignment.

If this is a memo field, there's another problem here. SelStart is an
integer (signed), and memos can have more than 32k characters.

If you have not worked with the Text property or SelStart and SelLength,
this example might give you some clues:
http://allenbrowne.com/func-InsertChar.html
 
Thank you for keeping me out of trouble on this!!!

Maybe I should tell you what I want to do ...
I'm wanting to update a name (which might contain spaces).
What would you suggest? Would the 'enter' key be a possible approach?
Thanks,
Bob
 
Bob, are you trying to make a text box behave like a combo does when it
auto-completes the name for you?

For example, you type the S and it displays the first name from your table
that starts with S, and selects all the remaining characters so they get
overwritten when you type the next character?

Simplest way might be to use a combo with LimitToList set to No, and
RowSource like this:
SELECT DISTINCT Surname FROM tblClient WHERE Surname Is Not Null ORDER
BY Surname;

If that's what you want to do, but you don't want to use a combo, post a
request, and I'll try to dig up a code example.
 
I actually have a 2 column combo box already ... then decided to add the
text box to resolve issues I was having with displaying 2 fields while
allowing editing of only 1 of the two fields. If you can lead me in the
right direction, maybe I can keep this simple. THANKS VERY MUCH! If
you have any recommendations on where to learn combo boxes better then I
would appreciate that also!
Bob
 
I am confused on how to setup a combo box to do what I want:
I have a form (frmHousingNames) with an associate query (qryHousingNNames)
The query has only 2 fields (HA#, HAName)
I want to be able to edit HAName, but NOT HA#

In the associated table (tblHousingNames),
HA# is indexed (no duplicates)and is a number (1-150). I DO NOT WANT TO
ADD, CHANGE OR DELETE any of these numbers.
HAName is text field (indexed, duplicates ok) and contains typically 1-3
words.

I have set up a combo box (cboHousingNames)with 2 columns.
It is not configured correctly (It lists both fields, but I am unable to
edit HAName and it looks like it is 'attempting' to allow me to edit HA#
which leads to errors ...

I very much appreciate you time and your patience!
Bob
 
BobC,
Use the combo box to tell the form which record you want to edit.
From the combo you select the HAName you want to edit.
The after update event of the combo tells the form to show the record for
the HA# selected in the combo.
On the form you have a text box for HAName - its control source(where it
saves its data) is HAName in qryHousingNNames.
Make sure you don't have this text box's control source set something like
this =Me.cboHousingNames.Column(1)
The control source for the text box must be HAName.
Now when you change the data in the text box HAName, the change is stored
back in the table where you store the housing name.

After you have finished editing this record, you save it and move on to the
next record.
The combo updates to show the edited name.

Normally we hide the first column of the combo, as we don't need to see the
HA#.
To hide the first column, set the width of the first column to 0cm

Jeanette Cunningham
 
BobC,
here is the code which you put in the after update event of the combo.
The code makes the form show the details of the record that you selected in
the combo.
You don't edit the housing name in the combo, you edit in the text box in
the form after you choose the name in the combo.

Here is the code which makes the form show the housing name that you
selected in the combo

Sub cboHousingNames_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboHousingNames) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.cboHousingNames

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Jeanette Cunningham
 
BobC,
I am assuming that you have cboHousingNames in the header section of your
form.
To answer your concern about having a space in the name of the housing:
spaces in names are a problem when the name is the name of a field in a
table or the name of the table.
It doesn't appear that you have either of these 2 situations. It is quite OK
to have a space in the data for the name of the housing when it is stored in
the table.

Jeanette Cunningham
 
BobC,
I have found an example that shows what I was trying to explain.
here is the link to download the sample database
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ComboChoosesRecord.mdb

Jeanette Cunningham


Jeanette Cunningham said:
BobC,
here is the code which you put in the after update event of the combo.
The code makes the form show the details of the record that you selected
in the combo.
You don't edit the housing name in the combo, you edit in the text box in
the form after you choose the name in the combo.

Here is the code which makes the form show the housing name that you
selected in the combo

Sub cboHousingNames_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboHousingNames) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.cboHousingNames

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Jeanette Cunningham


BobC said:
I am confused on how to setup a combo box to do what I want:
I have a form (frmHousingNames) with an associate query
(qryHousingNNames)
The query has only 2 fields (HA#, HAName)
I want to be able to edit HAName, but NOT HA#

In the associated table (tblHousingNames),
HA# is indexed (no duplicates)and is a number (1-150). I DO NOT WANT TO
ADD, CHANGE OR DELETE any of these numbers.
HAName is text field (indexed, duplicates ok) and contains typically 1-3
words.

I have set up a combo box (cboHousingNames)with 2 columns.
It is not configured correctly (It lists both fields, but I am unable to
edit HAName and it looks like it is 'attempting' to allow me to edit HA#
which leads to errors ...

I very much appreciate you time and your patience!
Bob
Click to expand...
Click to expand...
 
Jeanette,
THANK YOU VERY MUCH!
I had a lot of misunderstandings about combo boxes ... probably the
worst of which was the fact that I was trying to use it to actually
perform the editing of the fields!
Now I believe I understand them much better!
Thanks for taking the time and efforts to help me out!
I do not know what I would do without this site and people like yourself!
Bob

Jeanette said:
BobC,
I have found an example that shows what I was trying to explain.
here is the link to download the sample database
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ComboChoosesRecord.mdb

Jeanette Cunningham


Jeanette Cunningham said:
BobC,
here is the code which you put in the after update event of the combo.
The code makes the form show the details of the record that you selected
in the combo.
You don't edit the housing name in the combo, you edit in the text box in
the form after you choose the name in the combo.

Here is the code which makes the form show the housing name that you
selected in the combo

Sub cboHousingNames_AfterUpdate ()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboHousingNames) Then

'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[KeyID] = " & Me.cboHousingNames

If rs.NoMatch Then
MsgBox "Not found"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub


Jeanette Cunningham


BobC said:
I am confused on how to setup a combo box to do what I want:
I have a form (frmHousingNames) with an associate query
(qryHousingNNames)
The query has only 2 fields (HA#, HAName)
I want to be able to edit HAName, but NOT HA#

In the associated table (tblHousingNames),
HA# is indexed (no duplicates)and is a number (1-150). I DO NOT WANT TO
ADD, CHANGE OR DELETE any of these numbers.
HAName is text field (indexed, duplicates ok) and contains typically 1-3
words.

I have set up a combo box (cboHousingNames)with 2 columns.
It is not configured correctly (It lists both fields, but I am unable to
edit HAName and it looks like it is 'attempting' to allow me to edit HA#
which leads to errors ...

I very much appreciate you time and your patience!
Bob


Allen Browne wrote:
Sorry, Bob: I don't understand what you are aiming to do.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top