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
|
|||
|
|||
Combo Box NotInList - How To Add Data To Underlying Table
This is simple I know, but I can't get it to work completely for me. I want
a SIMPLE procedure to control a combo box "Contractor" in a form "frmVendorRepair" that is refers a table "tblContractor". I want the ability to inform the user that the requested entry is not in the table, and ask them if they wanted to add it to the table or not. If they do, then I want the procedure to put the newly entered data into the table as a new entry. The next time the user accesses this form, and enters the new data they previusly entered, then I want it to be in the table this time. I have used "a bunch" of suggesated vba procedures, but none of them seem to work for me. I will admit, I'm not the sharpest VBA knife in the drawer. Any help will be greatly appreciated. Remember, I'm looking for a SIMPLE procedure.... -- 10SNUT |
#2
|
|||
|
|||
Change ComboBox property Limit to list to No.
"10SNUT" wrote: This is simple I know, but I can't get it to work completely for me. I want a SIMPLE procedure to control a combo box "Contractor" in a form "frmVendorRepair" that is refers a table "tblContractor". I want the ability to inform the user that the requested entry is not in the table, and ask them if they wanted to add it to the table or not. If they do, then I want the procedure to put the newly entered data into the table as a new entry. The next time the user accesses this form, and enters the new data they previusly entered, then I want it to be in the table this time. I have used "a bunch" of suggesated vba procedures, but none of them seem to work for me. I will admit, I'm not the sharpest VBA knife in the drawer. Any help will be greatly appreciated. Remember, I'm looking for a SIMPLE procedure.... -- 10SNUT |
#3
|
|||
|
|||
wrote:
This is simple I know, but I can't get it to work completely for me. I want a SIMPLE procedure to control a combo box "Contractor" in a form "frmVendorRepair" that is refers a table "tblContractor". I want the ability to inform the user that the requested entry is not in the table, and ask them if they wanted to add it to the table or not. If they do, then I want the procedure to put the newly entered data into the table as a new entry. The next time the user accesses this form, and enters the new data they previusly entered, then I want it to be in the table this time. I have used "a bunch" of suggesated vba procedures, but none of them seem to work for me. I will admit, I'm not the sharpest VBA knife in the drawer. Any help will be greatly appreciated. Remember, I'm looking for a SIMPLE procedure.... It would have been nice if you could have described "not working completely" better. It is even better if you would have posted what you had so far for us to review. Maybe this will help you. I usually put a double check question just in case...like: --- Warning, air code (Not tested) --- Private Sub Contractor_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Contractor_NotInList '-- We may need to add another Contractor Response = MsgBox("[" & NewData & "] " & _ "is not a current Contractor..." & vbCr & vbCr & _ "Would you like to add this New Contractor to the DataBase?", vbYesNo) If Response = vbYes Then '-- Create a new Contractor record Dim db As DAO.Database Dim MySql As String Set db = CurrentDb() MySql = "Insert Into tblContractor(ContractorName) " & _ "Values(""" & NewData & """)" db.Execute MySql, dbFailOnError ' The next line will tell Access to requery the cbo!! Response = acDataErrAdded Else Response = acDataErrContinue End If Exit_Contractor_NotInList: Resume Next Set db = Nothing Exit Sub Err_Contractor_NotInList: ' Add error handling code here Resume Exit_Contractor_NotInList End Sub It *is* air code so cut and paste and then compile and find all of my typing errors. You will also have to use your field names and probably add some more fields to the insert. HTH -- RuralGuy Please reply to the newsgroup so all may benefit. |
#4
|
|||
|
|||
RuralGuy,
I tried your suggested procedure. I get to the instruction: Dim db As DAO.Database and get: "Compile Error" User-defined type not detected The "db As DAO.Database" section of that line of code is highlighted. Whats up??? Also, You had asked what the details of the procedure I am using was. Here is the procedu Private Sub ContractorName_NotInList(NewData As String, Response As Integer) If MsgBox("Do you want to add '" _ & NewData & "' to the list of contractors?", _ vbOKCancel, "Add New Item") = vbOK Then ' Remove new data from combo box so control can be requeried ' after the Form1 form is closed DoCmd.RunCommand acCmdUndo ' Display form to collect data needed for the new record DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData 'Continue without displaying default error message. Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub This routine works except it gets the "system generated error" that the item is not in the list AFTER it gives the question to ask me to add the new entry or not. When I respond, I get the system generated error. Any ideas will be appreciated. 10SNUT -- 10SNUT "RuralGuy" wrote: wrote: This is simple I know, but I can't get it to work completely for me. I want a SIMPLE procedure to control a combo box "Contractor" in a form "frmVendorRepair" that is refers a table "tblContractor". I want the ability to inform the user that the requested entry is not in the table, and ask them if they wanted to add it to the table or not. If they do, then I want the procedure to put the newly entered data into the table as a new entry. The next time the user accesses this form, and enters the new data they previusly entered, then I want it to be in the table this time. I have used "a bunch" of suggesated vba procedures, but none of them seem to work for me. I will admit, I'm not the sharpest VBA knife in the drawer. Any help will be greatly appreciated. Remember, I'm looking for a SIMPLE procedure.... It would have been nice if you could have described "not working completely" better. It is even better if you would have posted what you had so far for us to review. Maybe this will help you. I usually put a double check question just in case...like: --- Warning, air code (Not tested) --- Private Sub Contractor_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Contractor_NotInList '-- We may need to add another Contractor Response = MsgBox("[" & NewData & "] " & _ "is not a current Contractor..." & vbCr & vbCr & _ "Would you like to add this New Contractor to the DataBase?", vbYesNo) If Response = vbYes Then '-- Create a new Contractor record Dim db As DAO.Database Dim MySql As String Set db = CurrentDb() MySql = "Insert Into tblContractor(ContractorName) " & _ "Values(""" & NewData & """)" db.Execute MySql, dbFailOnError ' The next line will tell Access to requery the cbo!! Response = acDataErrAdded Else Response = acDataErrContinue End If Exit_Contractor_NotInList: Resume Next Set db = Nothing Exit Sub Err_Contractor_NotInList: ' Add error handling code here Resume Exit_Contractor_NotInList End Sub It *is* air code so cut and paste and then compile and find all of my typing errors. You will also have to use your field names and probably add some more fields to the insert. HTH -- RuralGuy Please reply to the newsgroup so all may benefit. |
#5
|
|||
|
|||
wrote:
RuralGuy, I tried your suggested procedure. I get to the instruction: Dim db As DAO.Database and get: "Compile Error" User-defined type not detected The "db As DAO.Database" section of that line of code is highlighted. Whats up??? Also, You had asked what the details of the procedure I am using was. Here is the procedu Private Sub ContractorName_NotInList(NewData As String, Response As Integer) If MsgBox("Do you want to add '" _ & NewData & "' to the list of contractors?", _ vbOKCancel, "Add New Item") = vbOK Then ' Remove new data from combo box so control can be requeried ' after the Form1 form is closed *** Just comment out the next line and this code will work *** DoCmd.RunCommand acCmdUndo ' Display form to collect data needed for the new record DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData 'Continue without displaying default error message. Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub This routine works except it gets the "system generated error" that the item is not in the list AFTER it gives the question to ask me to add the new entry or not. When I respond, I get the system generated error. Any ideas will be appreciated. 10SNUT I'm sorry. I should have mentioned that the routine requires a reference to the DAO x.x Object Library. From viewing the code go to ToolsReferences - scroll down to Microsoft DAO x.x Object Library and check it. Then you should be able to compile the code. Having said that - you don't need my code!! There's *almost* nothing wrong with yours. Just don't perform the *UNDO* code. Just comment out the line I marked. Your code it great and thanks for posting it. You aren't using any DAO in the rest of your application so why start now. Post back with your results. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#6
|
|||
|
|||
RuralGuy,
I did as suggested by commenting out the "undo" line, and it still gets the system error after I respond to the question I have in the code. The system error it gets is: The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items. Still open to suggestions. I feel like we're SOOOO close. 10SNUT -- 10SNUT "RuralGuy" wrote: wrote: RuralGuy, I tried your suggested procedure. I get to the instruction: Dim db As DAO.Database and get: "Compile Error" User-defined type not detected The "db As DAO.Database" section of that line of code is highlighted. Whats up??? Also, You had asked what the details of the procedure I am using was. Here is the procedu Private Sub ContractorName_NotInList(NewData As String, Response As Integer) If MsgBox("Do you want to add '" _ & NewData & "' to the list of contractors?", _ vbOKCancel, "Add New Item") = vbOK Then ' Remove new data from combo box so control can be requeried ' after the Form1 form is closed *** Just comment out the next line and this code will work *** DoCmd.RunCommand acCmdUndo ' Display form to collect data needed for the new record DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData 'Continue without displaying default error message. Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub This routine works except it gets the "system generated error" that the item is not in the list AFTER it gives the question to ask me to add the new entry or not. When I respond, I get the system generated error. Any ideas will be appreciated. 10SNUT I'm sorry. I should have mentioned that the routine requires a reference to the DAO x.x Object Library. From viewing the code go to ToolsReferences - scroll down to Microsoft DAO x.x Object Library and check it. Then you should be able to compile the code. Having said that - you don't need my code!! There's *almost* nothing wrong with yours. Just don't perform the *UNDO* code. Just comment out the line I marked. Your code it great and thanks for posting it. You aren't using any DAO in the rest of your application so why start now. Post back with your results. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#7
|
|||
|
|||
wrote:
RuralGuy, I did as suggested by commenting out the "undo" line, and it still gets the system error after I respond to the question I have in the code. The system error it gets is: The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items. Still open to suggestions. I feel like we're SOOOO close. 10SNUT Hi 10SNUT, Just so you know what is happening, the Response = acDataErrAdded tells Access to Requery the ComboBox. It could be a timing issue if "Form1" doesn't get "NewData" properly inserted into the RecordSource of the ComboBox. "acDialog" should stop any code in this form from executing until we return from "Form1". Let's put a little diagnostic MsgBox in for now right after we come back from "Form1"! DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData ________________________________ '-- Diagnostic code to see what is going on If IsNull(DLookup("ContractorName", "tblContractors", _ "[ContractorName] = '" & NewData & "'")) Then MsgBox "[" & Newdata & "] is NOT in the Table yet!" Else MsgBox "[" & Newdata & "] IS in the Table!" End If ___________________________________ You'll need to replace "ContractorName" twice and "tblContractors" with the actual names of the Field and Table. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#8
|
|||
|
|||
RuralGuy,
Thanks for the continued help!! I entered the diagnostic code, and it responded with: "NewData" is NOT in the Table yet! Any suggestions at this point? Is there a way to delay the code execution to allow the "NewData" to get installed into the RecordSource? **** As an added piece of info, are these comments going to the NewsGroup, and if not, what is required to post them to the NewsGroup? I just thought this help you're giving me would be very helpful to others. -- 10SNUT "RuralGuy" wrote: wrote: RuralGuy, I did as suggested by commenting out the "undo" line, and it still gets the system error after I respond to the question I have in the code. The system error it gets is: The text you entered isn't an item in the list. Select an item from the list, or enter text that matches one of the listed items. Still open to suggestions. I feel like we're SOOOO close. 10SNUT Hi 10SNUT, Just so you know what is happening, the Response = acDataErrAdded tells Access to Requery the ComboBox. It could be a timing issue if "Form1" doesn't get "NewData" properly inserted into the RecordSource of the ComboBox. "acDialog" should stop any code in this form from executing until we return from "Form1". Let's put a little diagnostic MsgBox in for now right after we come back from "Form1"! DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData ________________________________ '-- Diagnostic code to see what is going on If IsNull(DLookup("ContractorName", "tblContractors", _ "[ContractorName] = '" & NewData & "'")) Then MsgBox "[" & Newdata & "] is NOT in the Table yet!" Else MsgBox "[" & Newdata & "] IS in the Table!" End If ___________________________________ You'll need to replace "ContractorName" twice and "tblContractors" with the actual names of the Field and Table. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#9
|
|||
|
|||
wrote:
RuralGuy, Thanks for the continued help!! I entered the diagnostic code, and it responded with: "NewData" is NOT in the Table yet! Any suggestions at this point? Is there a way to delay the code execution to allow the "NewData" to get installed into the RecordSource? **** As an added piece of info, are these comments going to the NewsGroup, and if not, what is required to post them to the NewsGroup? I just thought this help you're giving me would be very helpful to others. Hi 10SNUT, Yes, these comments are all going to the News Group. Anyone who cares can follow what we are doing. As far as the diagnostic code goes I expected it to have a MessageBox that stated "[NewContractorName] is NOT in the Table yet!" Did it really say "NewData" or were you just protecting some private information? I expected it to show the "Contractor" you had just added. I think we should look into the "Form1" code and determine how you are adding this Contractor to the underlying table of this ComboBox. Could you give me an idea of how Form1 works? Is it just bound to the underlying table of this ComboBox and you just fill in all of the controls and let Access save it when you close? What version of Access are you using. Are all of the service packs applied? How do you close this Dialog form? You are right, I believe we are getting closer to the problem! -- RuralGuy Please reply to the newsgroup so all may benefit. |
#10
|
|||
|
|||
Hi RuralGuy,
I will try to answer all your questions. 1. The routine is a simple "test" routine to get this function working. It wil be used in a larger application when I get it working. 2. There is nothing secretive about the data in the "test" routine, as well as the larger routine, but I thought a simple routine would make it simpler to diagnose. 3. The response is actually the data I tried to enter into the combo box, in this case, the number "4". 4. The sequence of events is as follows : The form has 4 other fields, and I enter data into them - no problem. Then I get to the combo box field, and I try to enter a "4". The underlying table, "tblContractors" has only one entry, and it is "1 Contractor". When I enter the "4", I get the error response that's in my routine: "Do you want to add '4' to the list of contractors?", and there is a "OK" and a "Cancel" button on the question box. I hit the "OK" button, and I get the diagnostic error response "(4) is NOT in the Table yet!" with an "OK" button. I hit the "OK" button, and then I get the system generated error: "The text you entered is not an item in the list. Select an item from the list, or enter text that matches one of the listed items." with an "OK" button. If I hit the "OK" button, I get the original form displayed with the number "4" in the ContractorName field, and below that, There is a box with the only entry in the tblContractors table, "1 Contractor". There is an "EXIT" button on the form. If I hit it, the form closes. 5. All entries on the "Form1" are simply entries dragged from the table list while the form is in Design mode. The ContractorName box was created by using the "ComboBox" button in the Form menu. The properties of the ContractName field a Both the Name and Control Source are "ContractorName". The Row source is "SELECT [tblContractors].[ContractorName] FROM tblContractors;. The RowSource Type is Table/Query. 6. All fields on the form are text fields with the std. length of 50 characters. All fields are bound to the tblContractors table. 7. I'm using Access 2000, version (9.0 4402 SR-1). That covers about all you asked. The actual application I use is only about 370KB. Is there any way to send that to you without giving out my actual email address? Hope this helps in the resolution. Thanks again. 10SNUT. -- 10SNUT "RuralGuy" wrote: wrote: RuralGuy, Thanks for the continued help!! I entered the diagnostic code, and it responded with: "NewData" is NOT in the Table yet! Any suggestions at this point? Is there a way to delay the code execution to allow the "NewData" to get installed into the RecordSource? **** As an added piece of info, are these comments going to the NewsGroup, and if not, what is required to post them to the NewsGroup? I just thought this help you're giving me would be very helpful to others. Hi 10SNUT, Yes, these comments are all going to the News Group. Anyone who cares can follow what we are doing. As far as the diagnostic code goes I expected it to have a MessageBox that stated "[NewContractorName] is NOT in the Table yet!" Did it really say "NewData" or were you just protecting some private information? I expected it to show the "Contractor" you had just added. I think we should look into the "Form1" code and determine how you are adding this Contractor to the underlying table of this ComboBox. Could you give me an idea of how Form1 works? Is it just bound to the underlying table of this ComboBox and you just fill in all of the controls and let Access save it when you close? What version of Access are you using. Are all of the service packs applied? How do you close this Dialog form? You are right, I believe we are getting closer to the problem! -- RuralGuy Please reply to the newsgroup so all may benefit. |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sort pages? | David | General Discussion | 15 | May 13th, 2005 11:33 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Cascading combo box data disappearing from form | Susan L | Using Forms | 7 | November 16th, 2004 05:13 PM |
Data Dependencies between Combo Boxes | Tom | Using Forms | 7 | June 6th, 2004 05:25 PM |
Mial merge data base problems | Rachael | Mailmerge | 16 | May 21st, 2004 06:22 PM |