 | |
Requerying ComboBox in NotInList event
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: Requerying ComboBox in NotInList event -
03-06-2008
, 04:05 PM
|
EManning wrote: On Mar 5, 5:48 pm, Salad <o... (AT) vinegar (DOT) com> wrote: EManning wrote: On Mar 5, 2:58 pm, Salad <o... (AT) vinegar (DOT) com> wrote: EManning wrote: On Mar 5, 8:57 am, Guillermo_Lopez <g.lo... (AT) iesdr (DOT) com> wrote: The way it works for me is typing: * ComboBox.Requery I don't use the me. part because im running the code in the form already. Instead of using the requery on the combo box, try requerying the From. * CurrentForm.Requery - GL On Mar 5, 1:37 am, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote: On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning manning_n... (AT) hotmail (DOT) com> wrote: Your code, minus the Requery, looks correct. Perhaps you have On Error Resume Next and you are suppressing errors? -Tom. I have a combobox whose rowsource is a union query. *This query displays a person's name in "lastname, firstname" format and in "firstname lastname" format. *The query results look like this: *Mouse, Mickey *Mickey Mouse When a person is added, the querys' underlying recordset is updated in the NotInList event. *I can't figure out how to refresh the combobox to display the new person. *I get the standard error message that the item is not in the list. *Here's my code: ...<in the NotInList event>... With rst .AddNew !FirstName = strFirstName !MiddleInitial = strMiddleInitial !LastName = strLastName .Update End With *Response = acDataErrAdded I have to refresh the query somehow so that it displays in the combo box. *If I add the code: *Me.ComboBox.Requery I get the standard error message that it must be saved. Maybe there's a better way to do this? *Thanks for any help or advice.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Any attempt to requery the combobox gives me an error that the field must be saved, whether or not the "Me." is used. *The form is unbound so requerying it wouldn't do any good. *Thanks for replying. Here's some code I wrote for 2 comboxes. *I didn't have a recordsource for the form using the code for Combo0. *I added a recordsource for the code in Combo3. 'Combo0_NotInList works just fine. Private Sub Combo0_NotInList(NewData As String, Response As Integer) * *Dim rst As Recordset * *Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset) * *If MsgBox("Make this a new entry?", vbYesNo, "Confirm Add") = vbYes Then * * * *rst.AddNew * * * *rst!TextF = NewData * * * *rst.Update * * * *Response = acDataErrAdded * *Else * * * *Response = acDataErrContinue * *End If * *rst.Close * *Set rst = Nothing End Sub 'Combo3_NotInList works just fine. 'Add record by entering firstname space lastname 'I use a query for the combo's rowsource. *The combo has 2 columns; ID 'and Fullname. *Fullname is a concatenation of first and last names. 'The SQL is 'SELECT DISTINCTROW Table1.ID, [FirstName] & " " & [LastName] AS FullName FROM Table1; Private Sub Combo3_NotInList(NewData As String, Response As Integer) * *Dim rst As Recordset * *Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset) * *Dim sF As Variant * *Dim sL As Variant * *Dim iP As String * *iP = InStr(NewData, " ") * *If iP > 0 Then * * * *sF = Left(NewData, iP - 1) * * * *sL = Mid(NewData, iP + 1) * *Else * * * *sF = NewData * *End If * *If MsgBox("Not in list. *Make this a new entry?", vbYesNo, "Confirm Add") = vbYes Then * * * *rst.AddNew * * * *rst!FirstName = sF * * * *rst!LastName = sL * * * *rst.Update * * * *Response = acDataErrAdded * *Else * * * *Response = acDataErrContinue * *End If * *rst.Close * *Set rst = Nothing End Sub Furhttp://www.youtube.com/watch?v=BPv0qCg4so8-Hidequoted text - - Show quoted text - Thanks for your reply, Salad. *Here is my complete code for the NotInList event. *Unless I'm just overlooking it, it appears to be the same as yours: Private Sub cboParticipant_NotInList(NewData As String, Response As Integer) On Error GoTo cboParticipant_NotInList_Error ' * Participant is not in database. *Create a record for them in tblParticipants. * *Dim lngCommaFound As Long * *Dim lngBlankFound As Long * *Dim strLastName As String * *Dim strFirstName As String * *Dim strMiddleInitial As String * *strMsg = "Do you wish to add them?" * *If MsgBox(strMsg, vbYesNo, conQuote & NewData & conQuote & " not in database.") = vbNo Then * * * *Me.cboParticipant.Undo * * * *Response = acDataErrContinue * *Else * * * *lngCommaFound = InStr(1, NewData, ",") * * * *If lngCommaFound <> 0 Then ' * * * * * A comma was found, so assume that the first word entered is the last name. *Beyond the comma is the first ' * * * * * name and middle initial. * * * * * *strLastName = Mid(NewData, 1, lngCommaFound -1) ' * * * * * lngBlankFound could be zero if something like "Duck, Donald" and no middle initial was entered. * * * * * *lngBlankFound = InStr(lngCommaFound + 2, NewData, " ") * * * * * *If lngBlankFound = 0 Then * * * * * * * *lngBlankFound = Len(NewData) + 1 * * * * * *End If * * * * * *strFirstName = Mid(NewData, lngCommaFound + 2, lngBlankFound - (lngCommaFound + 2)) * * * * * *strMiddleInitial = Mid(NewData, lngBlankFound+ 1) * * * *Else ' * * * * * No comma found, assume first name entered first. *Assume no middle initial. *If it's there then it will ' * * * * * be put into the last name field and the user willhave to separate it out in the call log. * * * * * *lngBlankFound = InStr(1, NewData, " ") * * * * * *strFirstName = Mid(NewData, 1, lngBlankFound) * * * * * *strLastName = Mid(NewData, lngBlankFound + 1) * * * * * *strMiddleInitial = " " * * * *End If * * * *Set dbs = CurrentDb * * * *Set rst = dbs.OpenRecordset("tblParticipants", dbOpenDynaset) * * * *With rst * * * * * *.AddNew * * * * * *!FirstName = strFirstName * * * * * *!MiddleInitial = strMiddleInitial * * * * * *!LastName = strLastName * * * * * *.Update * * * *End With * * * *Response = acDataErrAdded * * * *rst.Close * * * *dbs.Close * * * *Set rst = Nothing * * * *Set dbs = Nothing * *End If cboParticipant_NotInList_Resume: * *Exit Sub cboParticipant_NotInList_Error: * *EmailErrorMsg "An error occured in frmStudiesDataEntry - cboParticipant_NotInList" & vbCrLf & vbCrLf & _ * * *Err.Number & " " & Err.Description * *Resume cboParticipant_NotInList_Resume Like I said before, entering something like "Duck, Donald D" works just fine. *But entering "Donald D Duck" causes an item-not-in-list error. *It's like the name must have the comma or it won't work. "Donald D Duck" does get entered into the table but not without the error display. *Should I just ignore the error and go on? *I'm opento a better way of doing this if you have a suggestion. Have you stepped thru the code starting at the Else statement? *Are the values getting stored to the variables correctly? *Could lngBlankFound be 0? *Turn off your error trapping as well while debugging. *Throwing mud at wall, are the field sizes OK in the table? You could make a name like * * * *Blow, Joe A into * * * *Joe A Blow and using Split() If 2 elements are returned you have a first and last name. *If 3, you have a middle initial.- Hide quoted text - - Show quoted text - Yep, followed thru with debug, all the values are stored correctly. I've added code to handle lngBlankFound if zero. *Field sizes are OK in the table. *It still seems to depend on the comma being in the new entry. *Is there a way to capture the value of combobox and reformat it to a "lastname, firstname.." format before it hits the NotInList event? Thanks for your help, Salad, and anyone else who replies. OK. *I think I got it. *I was using Spaces and my query source was * * * * First & " " & Last. *I should have used an example of yours. When I changed it to look *at commas and update I got errors as well. What I did was change the bound column property from 1 (ID), to the Fullname field 2. *I then ported the code to my BeforeUpdate event and updated. In the AfterUpate event I put a Requery. *I didn't bother to find the name...I know you can figure that stuff out. *Also, I only looked for comma in name, not spaces. I basically think it's the comma in the RowSource that was the problem. Private Sub Combo6_BeforeUpdate(Cancel As Integer) * * *Dim rst As Recordset * * *Set rst = CurrentDb.OpenRecordset("Table9", dbOpenDynaset) * * *Dim sF As Variant * * *Dim sL As Variant * * *Dim sS As Variant * * *Dim iP As String * * *iP = InStr(Me.Combo6, ", ") * * *If iP > 0 Then * * * * *sF = Left(Me.Combo6, iP - 1) * * * * *sL = Mid(Me.Combo6, iP + 2) * * *Else * * * * *sF = Me.Combo6 * * *End If * * *sS = "FirstName = '" & sF & "'" * * *If sL > "" Then * * * * *sS = sS & " And LastName = '" & sL & "'" * * *End If * * *rst.FindFirst sS * * *If rst.NoMatch Then * * * * *If MsgBox("Not in list. *Make this a new entry?", vbYesNo, "Confirm Add") = vbYes Then * * * * * * *rst.AddNew * * * * * * *rst!FirstName = sF * * * * * * *rst!LastName = sL * * * * * * *rst.Update * * * * *Else * * * * * * *Cancel = True * * * * *End If * * *End If * * *rst.Close * * *Set rst = Nothing End Sub Unholy ... read more »- Hide quoted text - - Show quoted text - |
works fine but "Joe A Blow" doesn't. It must be something with the
union query being the source of the combobox. I'll have to try
something else. Maybe use a temp table for the rowsource. I don't
know at this point. Thanks for all your help.
#12
| |||
| |||
|
|
On Mar 6, 2:04*pm, Salad <o... (AT) vinegar (DOT) com> wrote: EManning wrote: On Mar 5, 5:48 pm, Salad <o... (AT) vinegar (DOT) com> wrote: EManning wrote: On Mar 5, 2:58 pm, Salad <o... (AT) vinegar (DOT) com> wrote: EManning wrote: On Mar 5, 8:57 am, Guillermo_Lopez <g.lo... (AT) iesdr (DOT) com> wrote: The way it works for me is typing: * ComboBox.Requery I don't use the me. part because im running the code in the form already. Instead of using the requery on the combo box, try requerying the From. * CurrentForm.Requery - GL On Mar 5, 1:37 am, Tom van Stiphout <no.spam.tom7... (AT) cox (DOT) net> wrote: On Tue, 4 Mar 2008 11:09:22 -0800 (PST), EManning manning_n... (AT) hotmail (DOT) com> wrote: Your code, minus the Requery, looks correct. Perhaps you have On Error Resume Next and you are suppressing errors? -Tom. I have a combobox whose rowsource is a union query. *This query displays a person's name in "lastname, firstname" format and in "firstname lastname" format. *The query results look like this: *Mouse, Mickey *Mickey Mouse When a person is added, the querys' underlying recordset is updated in the NotInList event. *I can't figure out how to refresh the combobox to display the new person. *I get the standard error message that the item is not in the list. *Here's my code: ...<in the NotInList event>... With rst .AddNew !FirstName = strFirstName !MiddleInitial = strMiddleInitial !LastName = strLastName .Update End With *Response = acDataErrAdded I have to refresh the query somehow so that it displays in the combo box. *If I add the code: *Me.ComboBox.Requery I get the standard error message that it must be saved. Maybe there's a better way to do this? *Thanks for any help oradvice.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - Any attempt to requery the combobox gives me an error that the field must be saved, whether or not the "Me." is used. *The form is unbound so requerying it wouldn't do any good. *Thanks for replying. Here's some code I wrote for 2 comboxes. *I didn't have a recordsource for the form using the code for Combo0. *I added a recordsource for the code in Combo3. 'Combo0_NotInList works just fine. Private Sub Combo0_NotInList(NewData As String, Response As Integer) * *Dim rst As Recordset * *Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset) * *If MsgBox("Make this a new entry?", vbYesNo, "Confirm Add") = vbYes Then * * * *rst.AddNew * * * *rst!TextF = NewData * * * *rst.Update * * * *Response = acDataErrAdded * *Else * * * *Response = acDataErrContinue * *End If * *rst.Close * *Set rst = Nothing End Sub 'Combo3_NotInList works just fine. 'Add record by entering firstname space lastname 'I use a query for the combo's rowsource. *The combo has 2 columns; ID 'and Fullname. *Fullname is a concatenation of first and last names. 'The SQL is 'SELECT DISTINCTROW Table1.ID, [FirstName] & " " & [LastName] AS FullName FROM Table1; Private Sub Combo3_NotInList(NewData As String, Response As Integer) * *Dim rst As Recordset * *Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset) * *Dim sF As Variant * *Dim sL As Variant * *Dim iP As String * *iP = InStr(NewData, " ") * *If iP > 0 Then * * * *sF = Left(NewData, iP - 1) * * * *sL = Mid(NewData, iP + 1) * *Else * * * *sF = NewData * *End If * *If MsgBox("Not in list. *Make this a new entry?", vbYesNo,"Confirm Add") = vbYes Then * * * *rst.AddNew * * * *rst!FirstName = sF * * * *rst!LastName = sL * * * *rst.Update * * * *Response = acDataErrAdded * *Else * * * *Response = acDataErrContinue * *End If * *rst.Close * *Set rst = Nothing End Sub Furhttp://www.youtube.com/watch?v=BPv0qCg4so8-Hidequotedtext - - Show quoted text - Thanks for your reply, Salad. *Here is my complete code for the NotInList event. *Unless I'm just overlooking it, it appears to be the same as yours: Private Sub cboParticipant_NotInList(NewData As String, Response As Integer) On Error GoTo cboParticipant_NotInList_Error ' * Participant is not in database. *Create a record for them in tblParticipants. * *Dim lngCommaFound As Long * *Dim lngBlankFound As Long * *Dim strLastName As String * *Dim strFirstName As String * *Dim strMiddleInitial As String * *strMsg = "Do you wish to add them?" * *If MsgBox(strMsg, vbYesNo, conQuote & NewData & conQuote & " not in database.") = vbNo Then * * * *Me.cboParticipant.Undo * * * *Response = acDataErrContinue * *Else * * * *lngCommaFound = InStr(1, NewData, ",") * * * *If lngCommaFound <> 0 Then ' * * * * * A comma was found, so assume that the first word entered is the last name. *Beyond the comma is the first ' * * * * * name and middle initial. * * * * * *strLastName = Mid(NewData, 1, lngCommaFound- 1) ' * * * * * lngBlankFound could be zero if something like "Duck, Donald" and no middle initial was entered. * * * * * *lngBlankFound = InStr(lngCommaFound + 2, NewData, " ") * * * * * *If lngBlankFound = 0 Then * * * * * * * *lngBlankFound = Len(NewData) + 1 * * * * * *End If * * * * * *strFirstName = Mid(NewData, lngCommaFound +2, lngBlankFound - (lngCommaFound + 2)) * * * * * *strMiddleInitial = Mid(NewData, lngBlankFound + 1) * * * *Else ' * * * * * No comma found, assume first name entered first.. *Assume no middle initial. *If it's there then it will ' * * * * * be put into the last name field and the user will have to separate it out in the call log. * * * * * *lngBlankFound = InStr(1, NewData, " ") * * * * * *strFirstName = Mid(NewData, 1, lngBlankFound) * * * * * *strLastName = Mid(NewData, lngBlankFound + 1) * * * * * *strMiddleInitial = " " * * * *End If * * * *Set dbs = CurrentDb * * * *Set rst = dbs.OpenRecordset("tblParticipants", dbOpenDynaset) * * * *With rst * * * * * *.AddNew * * * * * *!FirstName = strFirstName * * * * * *!MiddleInitial = strMiddleInitial * * * * * *!LastName = strLastName * * * * * *.Update * * * *End With * * * *Response = acDataErrAdded * * * *rst.Close * * * *dbs.Close * * * *Set rst = Nothing * * * *Set dbs = Nothing * *End If cboParticipant_NotInList_Resume: * *Exit Sub cboParticipant_NotInList_Error: * *EmailErrorMsg "An error occured in frmStudiesDataEntry - cboParticipant_NotInList" & vbCrLf & vbCrLf & _ * * *Err.Number & " " & Err.Description * *Resume cboParticipant_NotInList_Resume Like I said before, entering something like "Duck, Donald D" works just fine. *But entering "Donald D Duck" causes an item-not-in-list error. *It's like the name must have the comma or it won't work. "Donald D Duck" does get entered into the table but not without the error display. *Should I just ignore the error and go on? *I'm open to a better way of doing this if you have a suggestion. Have you stepped thru the code starting at the Else statement? *Are the values getting stored to the variables correctly? *Could lngBlankFound be 0? *Turn off your error trapping as well while debugging. *Throwing mud at wall, are the field sizes OK in the table? You could make a name like * * * *Blow, Joe A into * * * *Joe A Blow and using Split() If 2 elements are returned you have a first and last name. *If 3, you have a middle initial.- Hide quoted text - - Show quoted text - Yep, followed thru with debug, all the values are stored correctly. I've added code to handle lngBlankFound if zero. *Field sizes are OK in the table. *It still seems to depend on the comma being in the new entry. *Is there a way to capture the value of combobox and reformat it to a "lastname, firstname.." format before it hits the NotInList event? Thanks for your help, Salad, and anyone else who replies. OK. *I think I got it. *I was using Spaces and my query source was * * * * First & " " & Last. *I should have used an example of yours. When I changed it to look *at commas and update I got errors as well. What I did was change the bound column property from 1 (ID), to the Fullname field 2. *I then ported the code to my BeforeUpdate event and updated. In the AfterUpate event I put a Requery. *I didn't bother to find the name...I know you can figure that stuff out. *Also, I only looked for comma in name, not spaces. I basically think it's the comma in the RowSource that was the problem. Private Sub Combo6_BeforeUpdate(Cancel As Integer) * * *Dim rst As Recordset * * *Set rst = CurrentDb.OpenRecordset("Table9", dbOpenDynaset) * * *Dim sF As Variant * * *Dim sL As Variant * * *Dim sS As Variant * * *Dim iP As String * * *iP = InStr(Me.Combo6, ", ") * * *If iP > 0 Then * * * * *sF = Left(Me.Combo6, iP - 1) * * * * *sL = Mid(Me.Combo6, iP + 2) * * *Else * * * * *sF = Me.Combo6 * * *End If * * *sS = "FirstName = '" & sF & "'" * * *If sL > "" Then * * * * *sS = sS & " And LastName = '" & sL & "'" * * *End If * * *rst.FindFirst sS * * *If rst.NoMatch Then * * * * *If MsgBox("Not in list. *Make this a new entry?", vbYesNo, "Confirm Add") = vbYes Then * * * * * * *rst.AddNew * * * * * * *rst!FirstName = sF * * * * * * *rst!LastName = sL * * * * * * *rst.Update * * * * *Else * * * * * * *Cancel = True * * * * *End If * * *End If * * *rst.Close * * *Set rst = Nothing End Sub Unholy ... read more »- Hide quoted text - - Show quoted text - I tried your suggestion. *But still the same thing..."Blow, Joe A" works fine but "Joe A Blow" doesn't. *It must be something with the union query being the source of the combobox. *I'll have to try something else. *Maybe use a temp table for the rowsource. *I don't know at this point. *Thanks for all your help. |
to the rowsource but I would also get a item-not-in-list message. I
noticed that "Joe Blow" appeared in the combo dropdown as "Joe
Blow" (there's 2 spaces between "Joe" and "Blow"). In the union
query, I was testing Middle Initial for null string. If it's null,
then the query is supposed to display "Joe Blow". In my coding, I set
Middle Initial, if one doesn't exist, to an empty string. An empty
string is not null. So my entry of "Joe Blow" was getting compared to
"Joe Blow" which caused the item-not-in-list message. I changed the
query to test for empty string and now "Joe Blow" gets added just
fine. My goodness...
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.