If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtering Data On Entry
I'm trying to speed up data entry on a form in such a manner that when I
enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#2
|
|||
|
|||
Filtering Data On Entry
Hi
You can set the Auto Expand to Yes (in the Data column) This will do what you want -- Wayne Manchester, England. "iain" wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#3
|
|||
|
|||
Filtering Data On Entry
Thanks Wayne,
that was quick. The Auto Expand is already set to 'yes'. Nothing happens when I type a character in the field when creating a new record. In other words, I still have to click on the down arrow to open up the combo box list of previous data entries. "Wayne-I-M" wrote: Hi You can set the Auto Expand to Yes (in the Data column) This will do what you want -- Wayne Manchester, England. "iain" wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#4
|
|||
|
|||
Filtering Data On Entry
Hi
Sorry for not getting back to you sooner - I have been away. Open the form in design view and right click the combo. Open the properties box In the Event column select the Got Focus row Right click and select build (...) Select code Add this (change ComboName to what it really is) Private Sub ComboName_GotFocus() Me.ComboName.Dropdown End Sub Good luck -- Wayne Manchester, England. "iain" wrote: Thanks Wayne, that was quick. The Auto Expand is already set to 'yes'. Nothing happens when I type a character in the field when creating a new record. In other words, I still have to click on the down arrow to open up the combo box list of previous data entries. "Wayne-I-M" wrote: Hi You can set the Auto Expand to Yes (in the Data column) This will do what you want -- Wayne Manchester, England. "iain" wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#5
|
|||
|
|||
Filtering Data On Entry
Just had a thought
If you are (or maybe) adding or searching for a record that it not already on file you may want to add this to the combo. Again change ComboName and also change TableName (to the table name of the table that holds the records you are searching). Change ID to the name of the primary field (of the table that you searching) This, along wioth the OnFocus I already gave should sort out your problem. Good luck (don't forget to change ComboName, TableName and ID to what they really are or it will not work) Private Sub ComboName_NotInList(NewData As String, Response As Integer) Dim Db As DAO.Database Dim rs As DAO.Recordset Dim msg As String msg = "'" & NewData & "' is not on file." & vbCr & vbCr msg = msg & "Do you want to add New Record?" If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then Response = acDataErrContinue MsgBox "Try again." Else Set Db = CurrentDb Set rs = Db.OpenRecordset("TableName", dbOpenDynaset) rs.AddNew rs![CDSurname] = NewData rs.Update Response = acDataErrAdded End If End Sub Private Sub ComboName_AfterUpdate() Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ID] = " & Str(Me![ComboName]) Me.Bookmark = rs.Bookmark End Sub -- Wayne Manchester, England. "iain" wrote: Thanks Wayne, that was quick. The Auto Expand is already set to 'yes'. Nothing happens when I type a character in the field when creating a new record. In other words, I still have to click on the down arrow to open up the combo box list of previous data entries. "Wayne-I-M" wrote: Hi You can set the Auto Expand to Yes (in the Data column) This will do what you want -- Wayne Manchester, England. "iain" wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#6
|
|||
|
|||
Filtering Data On Entry
Soory - should read what I send 1srt :-)
change this rs![CDSurname] = NewData to rs![*****] = NewData Cahnge ***** to the name of the control that you are searching, So if you are searching for a Surname and the name of the control (not the table field) is ClientSurname it would be rs![ClientSurname] = NewData Searching a control called Cars it would be rs![Cars NewData etc etc -- Wayne Manchester, England. "Wayne-I-M" wrote: Just had a thought If you are (or maybe) adding or searching for a record that it not already on file you may want to add this to the combo. Again change ComboName and also change TableName (to the table name of the table that holds the records you are searching). Change ID to the name of the primary field (of the table that you searching) This, along wioth the OnFocus I already gave should sort out your problem. Good luck (don't forget to change ComboName, TableName and ID to what they really are or it will not work) Private Sub ComboName_NotInList(NewData As String, Response As Integer) Dim Db As DAO.Database Dim rs As DAO.Recordset Dim msg As String msg = "'" & NewData & "' is not on file." & vbCr & vbCr msg = msg & "Do you want to add New Record?" If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then Response = acDataErrContinue MsgBox "Try again." Else Set Db = CurrentDb Set rs = Db.OpenRecordset("TableName", dbOpenDynaset) rs.AddNew rs![CDSurname] = NewData rs.Update Response = acDataErrAdded End If End Sub Private Sub ComboName_AfterUpdate() Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ID] = " & Str(Me![ComboName]) Me.Bookmark = rs.Bookmark End Sub -- Wayne Manchester, England. "iain" wrote: Thanks Wayne, that was quick. The Auto Expand is already set to 'yes'. Nothing happens when I type a character in the field when creating a new record. In other words, I still have to click on the down arrow to open up the combo box list of previous data entries. "Wayne-I-M" wrote: Hi You can set the Auto Expand to Yes (in the Data column) This will do what you want -- Wayne Manchester, England. "iain" wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#7
|
|||
|
|||
Filtering Data On Entry
Iain:
A combo box works in a slightly different way than that which you describe as it progressively goes to the nearest match in its list as characters are entered rather than progressively restricting a list to a set of possible values. To do the latter you'd need to use two controls, a bound text box and an unbound list box. The list box would have a RowSource property such as: SELECT DISTINCT MyField FROM MyTable WHERE Myfield LIKE Form!MyTextBox & "*" OR Form!MyTextBox IS NULL ORDER BY MyField; where MyField is the name of the field in question, and MyTextBox is the name of the text box to which it is bound. Note the use of the Form property to refernce the form rather than a full reference; this is possible as both controls are in the same form. In the text box's Change event procedure requery the list box: Me.MyListBox.Requery Do the same in the form's Current event procedure. In the list box's AfterUpdate event procedure assign its value to the text box: Me.MyTextBox = Me.MyListBox You can if you wish show the list box in the text box's GotFocus event procedure and hide it in both the text box's and the list box's AfterUpdate event procedure, though in the latter you'd also have to move focus to the text box first: Me.MyTextBox.SetFocus Me.MyTextBox = Me.MyListBox Me.MyListBox.Visible = False Ken Sheridan Stafford, England iain wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200909/1 |
#8
|
|||
|
|||
Filtering Data On Entry
Thanks Wayne,
I'll have to try this out a few times to see if it does the trick, so it may be a while before you hear from me. "Wayne-I-M" wrote: Soory - should read what I send 1srt :-) change this rs![CDSurname] = NewData to rs![*****] = NewData Cahnge ***** to the name of the control that you are searching, So if you are searching for a Surname and the name of the control (not the table field) is ClientSurname it would be rs![ClientSurname] = NewData Searching a control called Cars it would be rs![Cars NewData etc etc -- Wayne Manchester, England. "Wayne-I-M" wrote: Just had a thought If you are (or maybe) adding or searching for a record that it not already on file you may want to add this to the combo. Again change ComboName and also change TableName (to the table name of the table that holds the records you are searching). Change ID to the name of the primary field (of the table that you searching) This, along wioth the OnFocus I already gave should sort out your problem. Good luck (don't forget to change ComboName, TableName and ID to what they really are or it will not work) Private Sub ComboName_NotInList(NewData As String, Response As Integer) Dim Db As DAO.Database Dim rs As DAO.Recordset Dim msg As String msg = "'" & NewData & "' is not on file." & vbCr & vbCr msg = msg & "Do you want to add New Record?" If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then Response = acDataErrContinue MsgBox "Try again." Else Set Db = CurrentDb Set rs = Db.OpenRecordset("TableName", dbOpenDynaset) rs.AddNew rs![CDSurname] = NewData rs.Update Response = acDataErrAdded End If End Sub Private Sub ComboName_AfterUpdate() Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[ID] = " & Str(Me![ComboName]) Me.Bookmark = rs.Bookmark End Sub -- Wayne Manchester, England. "iain" wrote: Thanks Wayne, that was quick. The Auto Expand is already set to 'yes'. Nothing happens when I type a character in the field when creating a new record. In other words, I still have to click on the down arrow to open up the combo box list of previous data entries. "Wayne-I-M" wrote: Hi You can set the Auto Expand to Yes (in the Data column) This will do what you want -- Wayne Manchester, England. "iain" wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain |
#9
|
|||
|
|||
Filtering Data On Entry
Thanks Ken,
this is quite a lot for me to work on, so it'll take some time before I get it working, but I'll give it a whirl. Iain "KenSheridan via AccessMonster.com" wrote: Iain: A combo box works in a slightly different way than that which you describe as it progressively goes to the nearest match in its list as characters are entered rather than progressively restricting a list to a set of possible values. To do the latter you'd need to use two controls, a bound text box and an unbound list box. The list box would have a RowSource property such as: SELECT DISTINCT MyField FROM MyTable WHERE Myfield LIKE Form!MyTextBox & "*" OR Form!MyTextBox IS NULL ORDER BY MyField; where MyField is the name of the field in question, and MyTextBox is the name of the text box to which it is bound. Note the use of the Form property to refernce the form rather than a full reference; this is possible as both controls are in the same form. In the text box's Change event procedure requery the list box: Me.MyListBox.Requery Do the same in the form's Current event procedure. In the list box's AfterUpdate event procedure assign its value to the text box: Me.MyTextBox = Me.MyListBox You can if you wish show the list box in the text box's GotFocus event procedure and hide it in both the text box's and the list box's AfterUpdate event procedure, though in the latter you'd also have to move focus to the text box first: Me.MyTextBox.SetFocus Me.MyTextBox = Me.MyListBox Me.MyListBox.Visible = False Ken Sheridan Stafford, England iain wrote: I'm trying to speed up data entry on a form in such a manner that when I enter the first character in a field, it displays a list of all previously stored values that begin with that character, then reduce the number of choices as I type each character. If the string that I want to enter is already listed I wish to select it using the mouse, otherwise type a completely new string and use the enter key or tab to the next field. Can this be achieved with a Combo Box? Does anyone onow of usefull links where I might find more information on this technique? Thanks, Iain -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200909/1 |
#10
|
|||
|
|||
Filtering Data On Entry
Actually, you should not have to drop down the combobx for AutoExpand to work,
which would make me tend to think the control is corrupted. You might try simply re-creating it. -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200909/1 |
|
Thread Tools | |
Display Modes | |
|
|