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 |
#11
|
|||
|
|||
wrote:
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. Hi 10SNUT, I had to read this about 6 time until the light bulb went off!!!! Correct me if I'm wrong but I think there is only one form involved here and it is named "Form1". If you want to add a "New Contractor" you are recursively calling "Form1" again. Forgive me if I am conveying information you already know, but there is such a long turn around time between postings that I want to pass as much information with each post as possible. ComboBoxes are for looking up existing data or in our case providing both the look up feature along with the ability to add additional records. In either case the "AfterUpdate" event of this ComboBox would have code to move the record pointer to the record containing the field that matches what we just selected. ---------------------------------------------------- Something like this UNTESTED AirCode: Private Sub Contractor_AfterUpdate() DoCmd.Requery ' Get any changes to the table first. ' Find the record that matches the control. Me.RecordsetClone.FindFirst "[ContractorName] = " & Me.Contractor.Column(0) If Not Me.RecordsetClone.EOF Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Sub ---------------------------------------------------- Normally you would use a ComboBox to place an ID number of some sort in another table. In our example let's say we were issuing a purchase order for some work to be accomplished. The form is bound to our WorkOrder table. The Contractor table "tblContractors" describes each contractor and has a ContractorID field that is a AutoNumber field. We have fields on our form that completely describe the work to be accomplished along with a ComboBox to select the Contractor. Without going into too much detail here (which I would be glad to do if you wish to go there) we set up our ComboBox the same way as we have in your case. If we type in a completely new contractor and we want to add them to our system, we are taken to a completely new form where we can define this contractor properly and this contractor is added to the "tblContractors" table. When we get back from adding the contractor the bound column of this ComboBox will put the ContractoID number in our WorkOrder![Contractor] field. I hope I haven't bored you to death with stuff you already knew. If I have, maybe someone else reading this thread didn't look at ComboBoxes this way and found some useful information. Does any of this make any sense to you? Am I on the right track as to how your form is working? Post back and I'll adjust direction as needed. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#12
|
|||
|
|||
Hi RuralGuy, Hope you had a good weekend -
Okay, let's see if I can clear up a couple of the ambiguties I have created in my previous explanation. There is only one form involved. It is derived from the table "Table1 which has five fields in it (Field 1 through Field4) and the ContractorName field which is also in Table1. As I mentioned in my previous post, the "Row Source" for the ContractorName field on the form is "SELECT [tblContractors].[ContractorName] FROM tblContractors;". Also the RowSource Type is Table/Query. Is this a correct reference for this field? I may not be doing this properly, but I have done it this way before. (the reference from the 2 tables to create a form). I have the relationship for the 2 tables linked with the ContractorName field. In looking over your suggestion, it looks like I will have to add another field to the tblContractors and that would be an AutoNumber field. This will be called the Contractor ID field. There may be 10 contractors, and they would have an ID of 1-10. If a new contractor is called for, we would be sent to another form to enter the new ContractorName, and the updated ID field would be self generated by the AutoNumber. Upon exit from entering the new ContractorName into the tblContractors, I would return to the Form1, and the new ContractorName would now be in the tblContractors so the error would not occur. I assume that I would return back to the ContractorName field on Form1. ?? To clarify a couple things I'm not sure of, to get to the new form to enter the new ContractorName, I could have a macro on the NotInList property that would open the new form to add the new ContractorName. After entering the new name, then I could have an "exit" button on this form that would take me back to Form1, and position me at the field to enter the requested ContractorName, which would now be there because we just put it in the table. Would I have to do any kind of "reset" to get past the error I got earlier when the ContractorName I was asking for was not there? You mentioned putting the Contractor ID number in the WorkOrder![Contractor]. Why would we put the contractor ID in the work Order, and not the contractor Name? In fact, why do we need a Contractoir ID in the first place? Can't we just add the new ContractorName to the tblContractors (while we're at the form that accepts the new Contractor names) so it will be recognized when we return to Form1 to re-enter the new ContractorName? Please explain the need for the Contractor ID. Please have patience with my limitations, because I would hate to lose your input. 10SNUT 10SNUT "RuralGuy" wrote: wrote: 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 wrote: 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. Hi 10SNUT, I had to read this about 6 time until the light bulb went off!!!! Correct me if I'm wrong but I think there is only one form involved here and it is named "Form1". If you want to add a "New Contractor" you are recursively calling "Form1" again. Forgive me if I am conveying information you already know, but there is such a long turn around time between postings that I want to pass as much information with each post as possible. ComboBoxes are for looking up existing data or in our case providing both the look up feature along with the ability to add additional records. In either case the "AfterUpdate" event of this ComboBox would have code to move the record pointer to the record containing the field that matches what we just selected. ---------------------------------------------------- Something like this UNTESTED AirCode: Private Sub Contractor_AfterUpdate() DoCmd.Requery ' Get any changes to the table first. ' Find the record that matches the control. Me.RecordsetClone.FindFirst "[ContractorName] = " & Me.Contractor.Column(0) If Not Me.RecordsetClone.EOF Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Sub ---------------------------------------------------- Normally you would use a ComboBox to place an ID number of some sort in another table. In our example let's say we were issuing a purchase order for some work to be accomplished. The form is bound to our WorkOrder table. The Contractor table "tblContractors" describes each contractor and has a ContractorID field that is a AutoNumber field. We have fields on our form that completely describe the work to be accomplished along with a ComboBox to select the Contractor. Without going into too much detail here (which I would be glad to do if you wish to go there) we set up our ComboBox the same way as we have in your case. If we type in a completely new contractor and we want to add them to our system, we are taken to a completely new form where we can define this contractor properly and this contractor is added to the "tblContractors" table. When we get back from adding the contractor the bound column of this ComboBox will put the ContractoID number in our WorkOrder![Contractor] field. I hope I haven't bored you to death with stuff you already knew. If I have, maybe someone else reading this thread didn't look at ComboBoxes this way and found some useful information. Does any of this make any sense to you? Am I on the right track as to how your form is working? Post back and I'll adjust direction as needed. -- RuralGuy Please reply to the newsgroup so all may benefit. 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. Hi 10SNUT, I had to read this about 6 time until the light bulb went off!!!! Correct me if I'm wrong but I think there is only one form involved here and it is named "Form1". If you want to add a "New Contractor" you are recursively calling "Form1" again. Forgive me if I am conveying information you already know, but there is such a long turn around time between postings that I want to pass as much information with each post as possible. ComboBoxes are for looking up existing data or in our case providing both the look up feature along with the ability to add additional records. In either case the "AfterUpdate" event of this ComboBox would have code to move the record pointer to the record containing the field that matches what we just selected. ---------------------------------------------------- Something like this UNTESTED AirCode: Private Sub Contractor_AfterUpdate() DoCmd.Requery ' Get any changes to the table first. ' Find the record that matches the control. Me.RecordsetClone.FindFirst "[ContractorName] = " & Me.Contractor.Column(0) If Not Me.RecordsetClone.EOF Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Sub ---------------------------------------------------- Normally you would use a ComboBox to place an ID number of some sort in another table. In our example let's say we were issuing a purchase order for some work to be accomplished. The form is bound to our WorkOrder table. The Contractor table "tblContractors" describes each contractor and has a ContractorID field that is a AutoNumber field. We have fields on our form that completely describe the work to be accomplished along with a ComboBox to select the Contractor. Without going into too much detail here (which I would be glad to do if you wish to go there) we set up our ComboBox the same way as we have in your case. If we type in a completely new contractor and we want to add them to our system, we are taken to a completely new form where we can define this contractor properly and this contractor is added to the "tblContractors" table. When we get back from adding the contractor the bound column of this ComboBox will put the ContractoID number in our WorkOrder![Contractor] field. I hope I haven't bored you to death with stuff you already knew. If I have, maybe someone else reading this thread didn't look at ComboBoxes this way and found some useful information. Does any of this make any sense to you? Am I on the right track as to how your form is working? Post back and I'll adjust direction as needed. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#13
|
|||
|
|||
Hi 10SNUT,
See responses in line... wrote: Hi RuralGuy, Hope you had a good weekend - Yes, very nice thank you! Okay, let's see if I can clear up a couple of the ambiguties I have created in my previous explanation. There is only one form involved. It is derived from the table "Table1 which has five fields in it (Field 1 through Field4) and the ContractorName field which is also in Table1. As I mentioned in my previous post, the "Row Source" for the ContractorName field on the form is "SELECT [tblContractors].[ContractorName] FROM tblContractors;". Also the RowSource Type is Table/Query. Is this a correct reference for this field? Yes, if you want to use a ComboBox. I may not be doing this properly, but I have done it this way before. (the reference from the 2 tables to create a form). I have the relationship for the 2 tables linked with the ContractorName field. What 2 tables? What is the other table used for and what does it look like? Now would be a good time to describe the logical function of this table. Is this just a test table to work out the details of a form? What are the functions of Field1 to Field4? What is the function of this "Form1"? In looking over your suggestion, it looks like I will have to add another field to the tblContractors and that would be an AutoNumber field. This will be called the Contractor ID field. There may be 10 contractors, and they would have an ID of 1-10. If a new contractor is called for, we would be sent to another form to enter the new ContractorName, and the updated ID field would be self generated by the AutoNumber. Upon exit from entering the new ContractorName into the tblContractors, I would return to the Form1, and the new ContractorName would now be in the tblContractors so the error would not occur. I assume that I would return back to the ContractorName field on Form1. ?? So far I agree. g To clarify a couple things I'm not sure of, to get to the new form to enter the new ContractorName, I could have a macro on the NotInList property that would open the new form to add the new ContractorName. No macro, you already have the code to open a new form to properly identify the new contractor. It is: ' Display form to collect data needed for the new record DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData The only problem is you should probably open "Form2" rather than "Form1". "Form2" would properly identify your Contractor with any information needed such as phone number, address, contractor license number and so forth. The form would be in add mode and simply closing the form would save all of the information and return you to "Form1" right after the DoCmd.OpenForm line. After entering the new name, then I could have an "exit" button on this form that would take me back to Form1, and position me at the field to enter the requested ContractorName, which would now be there because we just put it in the table. That is correct! Would I have to do any kind of "reset" to get past the error I got earlier when the ContractorName I was asking for was not there? Nope! You mentioned putting the Contractor ID number in the WorkOrder![Contractor]. Why would we put the contractor ID in the work Order, and not the contractor Name? In fact, why do we need a Contractoir ID in the first place? Can't we just add the new ContractorName to the tblContractors (while we're at the form that accepts the new Contractor names) so it will be recognized when we return to Form1 to re-enter the new ContractorName? Please explain the need for the Contractor ID. Please have patience with my limitations, because I would hate to lose your input. 10SNUT Names are real lousy ID fields. "Bill's Plumbing Service" could change it's name to "Bill and Sons Plumbing Service". "Sally Server" could get married. In a properly normalized database, no information exists in more than one location except for the "ID" fields which are usually AutoNumber fields that the user *NEVER* sees. I believe we are getting closer. Answering the above questions will take us another step. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#14
|
|||
|
|||
Hi again,
Thanks for the "reliable" response. Here's the responses to your questions: 1. Your first comment was the settings I had in the properties for the combo box was correct "if I wanted to use a Combo Box". What options are available to acquire data from entry in a form that will update a table other than a Combo Box? If there is a "simpler way" to accomplish this, I'm open to it. 2. The functions of the 2 tables a tblContractors - Holds the names of all the contractors that are available when entering into that field through Form1, as well as supporting info such as address, phone, etc. Table1 - Field1 thru Field 4 are just repositories to hold data which will be used later to create reports, etc. The procedure we are using here is only a simplified version of a larger procedure which will have more fields in it. In short, Table1 will be used in the final procedure, although the name will be different. I'll try to use your suggestion adding Form2 as an input to the tblContractors table. This makes good sense to me. It's hard to get away from years of "macro programming", and enter into the foggy world of VBA coding. Hopefully this exercise will convert me permanently. I see your reasoning concerning the ID fields as lookup pointers to select the ContractorName requested, but when the user gets to the point where they need to enter a contractor, they type the name of the contractor, and as soon as it recognizes the data being typed, the "look ahead" feature displays the existing entry in the table, at which time they can tab to accept that entry. When it's a new entry, the procedure we are creating here will accomplish the task of adding the new entry to the tblContractors table. But what if it's an original Contractor, but with a new or modified name? Will it make a "new" entry into the tblContractors for the new name, but the old original name will stay in the table? You could possible have "a few" different entries for the same contractor in the table depending on what the user enters. If it's a slight modification from the other entries for the same contractor, it goes in as a new contractor. Is this correct? I will add Form2 for data entry to the tblContractors, and see if the code I enter is correct. I'll let you know on my next post how this went. Thanks again for leading me through this darkness to a solution. 10SNUT -- 10SNUT "RuralGuy" wrote: Hi 10SNUT, See responses in line... wrote: Hi RuralGuy, Hope you had a good weekend - Yes, very nice thank you! Okay, let's see if I can clear up a couple of the ambiguties I have created in my previous explanation. There is only one form involved. It is derived from the table "Table1 which has five fields in it (Field 1 through Field4) and the ContractorName field which is also in Table1. As I mentioned in my previous post, the "Row Source" for the ContractorName field on the form is "SELECT [tblContractors].[ContractorName] FROM tblContractors;". Also the RowSource Type is Table/Query. Is this a correct reference for this field? Yes, if you want to use a ComboBox. I may not be doing this properly, but I have done it this way before. (the reference from the 2 tables to create a form). I have the relationship for the 2 tables linked with the ContractorName field. What 2 tables? What is the other table used for and what does it look like? Now would be a good time to describe the logical function of this table. Is this just a test table to work out the details of a form? What are the functions of Field1 to Field4? What is the function of this "Form1"? In looking over your suggestion, it looks like I will have to add another field to the tblContractors and that would be an AutoNumber field. This will be called the Contractor ID field. There may be 10 contractors, and they would have an ID of 1-10. If a new contractor is called for, we would be sent to another form to enter the new ContractorName, and the updated ID field would be self generated by the AutoNumber. Upon exit from entering the new ContractorName into the tblContractors, I would return to the Form1, and the new ContractorName would now be in the tblContractors so the error would not occur. I assume that I would return back to the ContractorName field on Form1. ?? So far I agree. g To clarify a couple things I'm not sure of, to get to the new form to enter the new ContractorName, I could have a macro on the NotInList property that would open the new form to add the new ContractorName. No macro, you already have the code to open a new form to properly identify the new contractor. It is: ' Display form to collect data needed for the new record DoCmd.OpenForm "Form1", acNormal, , , acAdd, acDialog, NewData The only problem is you should probably open "Form2" rather than "Form1". "Form2" would properly identify your Contractor with any information needed such as phone number, address, contractor license number and so forth. The form would be in add mode and simply closing the form would save all of the information and return you to "Form1" right after the DoCmd.OpenForm line. After entering the new name, then I could have an "exit" button on this form that would take me back to Form1, and position me at the field to enter the requested ContractorName, which would now be there because we just put it in the table. That is correct! Would I have to do any kind of "reset" to get past the error I got earlier when the ContractorName I was asking for was not there? Nope! You mentioned putting the Contractor ID number in the WorkOrder![Contractor]. Why would we put the contractor ID in the work Order, and not the contractor Name? In fact, why do we need a Contractoir ID in the first place? Can't we just add the new ContractorName to the tblContractors (while we're at the form that accepts the new Contractor names) so it will be recognized when we return to Form1 to re-enter the new ContractorName? Please explain the need for the Contractor ID. Please have patience with my limitations, because I would hate to lose your input. 10SNUT Names are real lousy ID fields. "Bill's Plumbing Service" could change it's name to "Bill and Sons Plumbing Service". "Sally Server" could get married. In a properly normalized database, no information exists in more than one location except for the "ID" fields which are usually AutoNumber fields that the user *NEVER* sees. I believe we are getting closer. Answering the above questions will take us another step. -- RuralGuy Please reply to the newsgroup so all may benefit. |
#15
|
|||
|
|||
Good morning 10SNUT,
See responses in line. wrote: Hi again, Thanks for the "reliable" response. Here's the responses to your questions: 1. Your first comment was the settings I had in the properties for the combo box was correct "if I wanted to use a Combo Box". What options are available to acquire data from entry in a form that will update a table other than a Combo Box? If there is a "simpler way" to accomplish this, I'm open to it. I think for our purposes the ComboBox is the correct control to use. 2. The functions of the 2 tables a tblContractors - Holds the names of all the contractors that are available when entering into that field through Form1, as well as supporting info such as address, phone, etc. Table1 - Field1 thru Field 4 are just repositories to hold data which will be used later to create reports, etc. The procedure we are using here is only a simplified version of a larger procedure which will have more fields in it. In short, Table1 will be used in the final procedure, although the name will be different. What relationship does table1 have with tblContractors? Could there be many records in table1 with Contractor1 in the Contractor Field? In other words does Table1 have a Many-To-One relationship with tblContractors? I'll try to use your suggestion adding Form2 as an input to the tblContractors table. This makes good sense to me. It's hard to get away from years of "macro programming", and enter into the foggy world of VBA coding. Hopefully this exercise will convert me permanently. You won't regret it! g I see your reasoning concerning the ID fields as lookup pointers to select the ContractorName requested, but when the user gets to the point where they need to enter a contractor, they type the name of the contractor, and as soon as it recognizes the data being typed, the "look ahead" feature displays the existing entry in the table, at which time they can tab to accept that entry. When it's a new entry, the procedure we are creating here will accomplish the task of adding the new entry to the tblContractors table. But what if it's an original Contractor, but with a new or modified name? Will it make a "new" entry into the tblContractors for the new name, but the old original name will stay in the table? You could possible have "a few" different entries for the same contractor in the table depending on what the user enters. If it's a slight modification from the other entries for the same contractor, it goes in as a new contractor. Is this correct? Yup! That's why we intercept the "New" contractor and ask the user if they want to create this new contractor. If the user is familiar with the operation they will say "No, we've used them before. I must have typed something different." They will say no to the question and be left ready to pick a contractor. This time they will be more careful and scroll in the selections to try to locate the contractor. Hopefully they will succeed. Remember we are only recording the ContractorID field in Table1. Any report or form that displays the contractor name will get the name from the tblContractor table. This brings up table maintenance. To properly maintain the contractor table you really need a form (Form3? or modified Form2?) bound to tblContractors with a means to navigate the table (navigation buttons or another ComboBox). There should probably be TextBox controls on the form bound to each field in the table. Once on a particular Contractor's record, any change will modify the record including the Contractor name. To complicate matters a little more, the maintenance form should have the ability to "Delete" a Contractor and you should have Referential Integrity turned on in case this ContractorID has been used elsewhere. Have I lost you yet? Do not use the "Look up a contractor" function in Form1 as the only means to add to the Contractor table. The maintenance form should have the ability to add a new contractor. I will add Form2 for data entry to the tblContractors, and see if the code I enter is correct. I'll let you know on my next post how this went. Thanks again for leading me through this darkness to a solution. 10SNUT Well that's about it for this go around. Looking forward to how you made out with "Form2". -- RuralGuy Please reply to the newsgroup so all may benefit. |
#16
|
|||
|
|||
Guess what RuralGuy !!!!
By adding Form2 which refers to tblContractors data, the functions work correctly. The way it functions now is: Upon initiation, Form1 opens. Data is entered into the fields, and when the user gets to the Contractor field, they try to enter the name of the Contractor they are looking for. If there is one already in the tblContractors table, then it is shown, and the user can accept that Contractor and continue on to finish the process. If the Contractor they want IS NOT there, then Form2 pops up, and there are entry fields to add a new Contractor and his extended information (address, city,etc.) to tblContractors. After the information has been completely added, then Form2 is closed, and you are returned back to Form1. I only have one problem left, and this should be easy..... (for you) After the data fields in Form2 are completed, I would like Form2 to close as soon as the last field is tabbed OUT OF. I have put a button on Form2, and after the last field is completed, I can click the button, and I can get Form2 to close that way. That is cumberson, so I need to find a way to get Form2 to close AFTER the entry is made in the last field and the TAB key is pressed. For argument's purpose, lets call the last field PHONE. I have put together a code that should close Form2 when we exit the PHONE field, but I can't determine what attribute to attach the procedure to. I have tried: After Update, Lost Focus, Exit, and a couple others. The code I want to use is: Private Sub Phone_AfterUpdate() '------------------------------------------------------------ ' Form2_Close ' '------------------------------------------------------------ Function Form2_Close() On Error GoTo Form2_Close_Err DoCmd.Close acForm, "Form2" Form2_Close_Exit: Exit Function Form2_Close_Err: MsgBox Error$ Resume Form2_Close_Exit End Sub Do you see anything obviously wrong with this code? I even put in a simple macro to close Form2 after update, and it fails also. HOWEVER, the macro does work if I just open Form 2, and enter data to the last field, PHONE, and when I tab out of PHONE, Form2 closes the way I want it to. It just won't do it while running in the procedure. So that's my only problem left to tackle. Close Form2 when the last field has been updated, and you tab out of it. You're doing GREAT. Any ideas on this final fix? 10SNUT -- 10SNUT "RuralGuy" wrote: Good morning 10SNUT, See responses in line. wrote: Hi again, Thanks for the "reliable" response. Here's the responses to your questions: 1. Your first comment was the settings I had in the properties for the combo box was correct "if I wanted to use a Combo Box". What options are available to acquire data from entry in a form that will update a table other than a Combo Box? If there is a "simpler way" to accomplish this, I'm open to it. I think for our purposes the ComboBox is the correct control to use. 2. The functions of the 2 tables a tblContractors - Holds the names of all the contractors that are available when entering into that field through Form1, as well as supporting info such as address, phone, etc. Table1 - Field1 thru Field 4 are just repositories to hold data which will be used later to create reports, etc. The procedure we are using here is only a simplified version of a larger procedure which will have more fields in it. In short, Table1 will be used in the final procedure, although the name will be different. What relationship does table1 have with tblContractors? Could there be many records in table1 with Contractor1 in the Contractor Field? In other words does Table1 have a Many-To-One relationship with tblContractors? I'll try to use your suggestion adding Form2 as an input to the tblContractors table. This makes good sense to me. It's hard to get away from years of "macro programming", and enter into the foggy world of VBA coding. Hopefully this exercise will convert me permanently. You won't regret it! g I see your reasoning concerning the ID fields as lookup pointers to select the ContractorName requested, but when the user gets to the point where they need to enter a contractor, they type the name of the contractor, and as soon as it recognizes the data being typed, the "look ahead" feature displays the existing entry in the table, at which time they can tab to accept that entry. When it's a new entry, the procedure we are creating here will accomplish the task of adding the new entry to the tblContractors table. But what if it's an original Contractor, but with a new or modified name? Will it make a "new" entry into the tblContractors for the new name, but the old original name will stay in the table? You could possible have "a few" different entries for the same contractor in the table depending on what the user enters. If it's a slight modification from the other entries for the same contractor, it goes in as a new contractor. Is this correct? Yup! That's why we intercept the "New" contractor and ask the user if they want to create this new contractor. If the user is familiar with the operation they will say "No, we've used them before. I must have typed something different." They will say no to the question and be left ready to pick a contractor. This time they will be more careful and scroll in the selections to try to locate the contractor. Hopefully they will succeed. Remember we are only recording the ContractorID field in Table1. Any report or form that displays the contractor name will get the name from the tblContractor table. This brings up table maintenance. To properly maintain the contractor table you really need a form (Form3? or modified Form2?) bound to tblContractors with a means to navigate the table (navigation buttons or another ComboBox). There should probably be TextBox controls on the form bound to each field in the table. Once on a particular Contractor's record, any change will modify the record including the Contractor name. To complicate matters a little more, the maintenance form should have the ability to "Delete" a Contractor and you should have Referential Integrity turned on in case this ContractorID has been used elsewhere. Have I lost you yet? Do not use the "Look up a contractor" function in Form1 as the only means to add to the Contractor table. The maintenance form should have the ability to add a new contractor. I will add Form2 for data entry to the tblContractors, and see if the code I enter is correct. I'll let you know on my next post how this went. Thanks again for leading me through this darkness to a solution. 10SNUT Well that's about it for this go around. Looking forward to how you made out with "Form2". -- RuralGuy Please reply to the newsgroup so all may benefit. |
#17
|
|||
|
|||
wrote:
Guess what RuralGuy !!!! By adding Form2 which refers to tblContractors data, the functions work correctly. The way it functions now is: Upon initiation, Form1 opens. Data is entered into the fields, and when the user gets to the Contractor field, they try to enter the name of the Contractor they are looking for. If there is one already in the tblContractors table, then it is shown, and the user can accept that Contractor and continue on to finish the process. If the Contractor they want IS NOT there, then Form2 pops up, and there are entry fields to add a new Contractor and his extended information (address, city,etc.) to tblContractors. After the information has been completely added, then Form2 is closed, and you are returned back to Form1. I only have one problem left, and this should be easy..... (for you) After the data fields in Form2 are completed, I would like Form2 to close as soon as the last field is tabbed OUT OF. I have put a button on Form2, and after the last field is completed, I can click the button, and I can get Form2 to close that way. That is cumberson, so I need to find a way to get Form2 to close AFTER the entry is made in the last field and the TAB key is pressed. For argument's purpose, lets call the last field PHONE. I have put together a code that should close Form2 when we exit the PHONE field, but I can't determine what attribute to attach the procedure to. I have tried: After Update, Lost Focus, Exit, and a couple others. The code I want to use is: Private Sub Phone_AfterUpdate() '------------------------------------------------------------ ' Form2_Close ' '------------------------------------------------------------ Function Form2_Close() On Error GoTo Form2_Close_Err DoCmd.Close acForm, "Form2" Form2_Close_Exit: Exit Function Form2_Close_Err: MsgBox Error$ Resume Form2_Close_Exit End Sub Do you see anything obviously wrong with this code? I even put in a simple macro to close Form2 after update, and it fails also. HOWEVER, the macro does work if I just open Form 2, and enter data to the last field, PHONE, and when I tab out of PHONE, Form2 closes the way I want it to. It just won't do it while running in the procedure. So that's my only problem left to tackle. Close Form2 when the last field has been updated, and you tab out of it. You're doing GREAT. Any ideas on this final fix? 10SNUT You should be able to use the Phone_AfterUpdate event which will insist they put something in the phone number, otherwise the event never fires. Even though I don't like running forms this way my preference would be the Phone_LostFocus event. This is all you should need: Private Sub Phone_LostFocus() DoCmd.Close acForm, Me.Name, acSaveNo End Sub Note: The acSaveNo is for the form, *not* the data! My preference would be to make tabbing out of the Phone field go to the ExitButton you spoke of. Pressing Enter while on the button "Pushes" the button. Viola!! we're back in Form1. You may also want to make Form2 cycle on the Current Record. It is on the "Other" tab for the Form. hth -- RuralGuy Please reply to the newsgroup so all may benefit. |
#18
|
|||
|
|||
TGIF RuralGuy,
I was mistaken in my last post to you about what is really happening. For the most part, I was correct, but I need to correct a couple things: After I pass through Form1 entering data in all the fields, and I get to the Contractor entry, if I enter a new contractor, it asks me if I want to add the new contractor to the list. I respond "yes", and it takes me to Form2, and this is a 6 field form to enter Contractor information. After I get to the last field, "Phone", I tab out to the "Exit" button. From this point on, what I told you is different. Here's what really happens: When positioned on the "Exit" button, I can either hit Enter, or click on the "Exit" button, and it always goes to field 1 of Form2 AGAIN. I have to enter data for a another Contractor, and it has to be different because it recognizes the Contractor I entered on the 1st pass. When I finish entering data for the 2nd Contractor, it positions me over the "Exit" button, at which time I can click on the button, or hit the Enter key. Then it closes Form2, and goes back to Form1 the way it should. Here's an interesting "quirk" I just found out: If, after the 1st pass, when it leaves the "Exit" button to go back to the 1st field of Form2, THEN if I click on the "Exit" button, it closes Form2, and goes back to Form1. I know it's simply a matter of the code I have entered, but I even tried actual macro entries, and got the same results. I'm at a lost as to what to do next. I'll keep playing around with it to see what I can get. The last issue I discovered about clicking on Enter as it enters Form2 for the 2nd pass, and that gets me back to Form1, is a new possibility. I'll see what I can do there. I asked this before, but is there any way I can email this routine to you without disclosing email addresses to the world? It's small, and it would save us both a lot of time. Don't get me wrong, I am learning a LOT with what we're doing now. I just wanted to know if this is something you might be interested in. Thanks again, 10SNUT 10SNUT "10SNUT" wrote: Guess what RuralGuy !!!! By adding Form2 which refers to tblContractors data, the functions work correctly. The way it functions now is: Upon initiation, Form1 opens. Data is entered into the fields, and when the user gets to the Contractor field, they try to enter the name of the Contractor they are looking for. If there is one already in the tblContractors table, then it is shown, and the user can accept that Contractor and continue on to finish the process. If the Contractor they want IS NOT there, then Form2 pops up, and there are entry fields to add a new Contractor and his extended information (address, city,etc.) to tblContractors. After the information has been completely added, then Form2 is closed, and you are returned back to Form1. I only have one problem left, and this should be easy..... (for you) After the data fields in Form2 are completed, I would like Form2 to close as soon as the last field is tabbed OUT OF. I have put a button on Form2, and after the last field is completed, I can click the button, and I can get Form2 to close that way. That is cumberson, so I need to find a way to get Form2 to close AFTER the entry is made in the last field and the TAB key is pressed. For argument's purpose, lets call the last field PHONE. I have put together a code that should close Form2 when we exit the PHONE field, but I can't determine what attribute to attach the procedure to. I have tried: After Update, Lost Focus, Exit, and a couple others. The code I want to use is: Private Sub Phone_AfterUpdate() '------------------------------------------------------------ ' Form2_Close ' '------------------------------------------------------------ Function Form2_Close() On Error GoTo Form2_Close_Err DoCmd.Close acForm, "Form2" Form2_Close_Exit: Exit Function Form2_Close_Err: MsgBox Error$ Resume Form2_Close_Exit End Sub Do you see anything obviously wrong with this code? I even put in a simple macro to close Form2 after update, and it fails also. HOWEVER, the macro does work if I just open Form 2, and enter data to the last field, PHONE, and when I tab out of PHONE, Form2 closes the way I want it to. It just won't do it while running in the procedure. So that's my only problem left to tackle. Close Form2 when the last field has been updated, and you tab out of it. You're doing GREAT. Any ideas on this final fix? 10SNUT -- 10SNUT "RuralGuy" wrote: Good morning 10SNUT, See responses in line. wrote: Hi again, Thanks for the "reliable" response. Here's the responses to your questions: 1. Your first comment was the settings I had in the properties for the combo box was correct "if I wanted to use a Combo Box". What options are available to acquire data from entry in a form that will update a table other than a Combo Box? If there is a "simpler way" to accomplish this, I'm open to it. I think for our purposes the ComboBox is the correct control to use. 2. The functions of the 2 tables a tblContractors - Holds the names of all the contractors that are available when entering into that field through Form1, as well as supporting info such as address, phone, etc. Table1 - Field1 thru Field 4 are just repositories to hold data which will be used later to create reports, etc. The procedure we are using here is only a simplified version of a larger procedure which will have more fields in it. In short, Table1 will be used in the final procedure, although the name will be different. What relationship does table1 have with tblContractors? Could there be many records in table1 with Contractor1 in the Contractor Field? In other words does Table1 have a Many-To-One relationship with tblContractors? I'll try to use your suggestion adding Form2 as an input to the tblContractors table. This makes good sense to me. It's hard to get away from years of "macro programming", and enter into the foggy world of VBA coding. Hopefully this exercise will convert me permanently. You won't regret it! g I see your reasoning concerning the ID fields as lookup pointers to select the ContractorName requested, but when the user gets to the point where they need to enter a contractor, they type the name of the contractor, and as soon as it recognizes the data being typed, the "look ahead" feature displays the existing entry in the table, at which time they can tab to accept that entry. When it's a new entry, the procedure we are creating here will accomplish the task of adding the new entry to the tblContractors table. But what if it's an original Contractor, but with a new or modified name? Will it make a "new" entry into the tblContractors for the new name, but the old original name will stay in the table? You could possible have "a few" different entries for the same contractor in the table depending on what the user enters. If it's a slight modification from the other entries for the same contractor, it goes in as a new contractor. Is this correct? Yup! That's why we intercept the "New" contractor and ask the user if they want to create this new contractor. If the user is familiar with the operation they will say "No, we've used them before. I must have typed something different." They will say no to the question and be left ready to pick a contractor. This time they will be more careful and scroll in the selections to try to locate the contractor. Hopefully they will succeed. Remember we are only recording the ContractorID field in Table1. Any report or form that displays the contractor name will get the name from the tblContractor table. This brings up table maintenance. To properly maintain the contractor table you really need a form (Form3? or modified Form2?) bound to tblContractors with a means to navigate the table (navigation buttons or another ComboBox). There should probably be TextBox controls on the form bound to each field in the table. Once on a particular Contractor's record, any change will modify the record including the Contractor name. To complicate matters a little more, the maintenance form should have the ability to "Delete" a Contractor and you should have Referential Integrity turned on in case this ContractorID has been used elsewhere. Have I lost you yet? Do not use the "Look up a contractor" function in Form1 as the only means to add to the Contractor table. The maintenance form should have the ability to add a new contractor. I will add Form2 for data entry to the tblContractors, and see if the code I enter is correct. I'll let you know on my next post how this went. Thanks again for leading me through this darkness to a solution. 10SNUT Well that's about it for this go around. Looking forward to how you made out with "Form2". -- RuralGuy Please reply to the newsgroup so all may benefit. |
#19
|
|||
|
|||
RuralGuy,
I found the problem. Everything works perfectly now. On the original procedure that we used to exit Form1 to Form2 when a Contractor needed to be added, I had (somehow??) coded the "Open Form2" statement TWICE. Even though it didn't look like we were in that area, when I commented out that second Open "Form2" argument, everything works good. I want to thank you VERY MUCH for your patience with me and my problem. I will hope to get you as a "respondent" to any future postings I submit to the Access Discussion Group. Relieved and Satisfied, 10SNUT -- 10SNUT "10SNUT" wrote: Guess what RuralGuy !!!! By adding Form2 which refers to tblContractors data, the functions work correctly. The way it functions now is: Upon initiation, Form1 opens. Data is entered into the fields, and when the user gets to the Contractor field, they try to enter the name of the Contractor they are looking for. If there is one already in the tblContractors table, then it is shown, and the user can accept that Contractor and continue on to finish the process. If the Contractor they want IS NOT there, then Form2 pops up, and there are entry fields to add a new Contractor and his extended information (address, city,etc.) to tblContractors. After the information has been completely added, then Form2 is closed, and you are returned back to Form1. I only have one problem left, and this should be easy..... (for you) After the data fields in Form2 are completed, I would like Form2 to close as soon as the last field is tabbed OUT OF. I have put a button on Form2, and after the last field is completed, I can click the button, and I can get Form2 to close that way. That is cumberson, so I need to find a way to get Form2 to close AFTER the entry is made in the last field and the TAB key is pressed. For argument's purpose, lets call the last field PHONE. I have put together a code that should close Form2 when we exit the PHONE field, but I can't determine what attribute to attach the procedure to. I have tried: After Update, Lost Focus, Exit, and a couple others. The code I want to use is: Private Sub Phone_AfterUpdate() '------------------------------------------------------------ ' Form2_Close ' '------------------------------------------------------------ Function Form2_Close() On Error GoTo Form2_Close_Err DoCmd.Close acForm, "Form2" Form2_Close_Exit: Exit Function Form2_Close_Err: MsgBox Error$ Resume Form2_Close_Exit End Sub Do you see anything obviously wrong with this code? I even put in a simple macro to close Form2 after update, and it fails also. HOWEVER, the macro does work if I just open Form 2, and enter data to the last field, PHONE, and when I tab out of PHONE, Form2 closes the way I want it to. It just won't do it while running in the procedure. So that's my only problem left to tackle. Close Form2 when the last field has been updated, and you tab out of it. You're doing GREAT. Any ideas on this final fix? 10SNUT -- 10SNUT "RuralGuy" wrote: Good morning 10SNUT, See responses in line. wrote: Hi again, Thanks for the "reliable" response. Here's the responses to your questions: 1. Your first comment was the settings I had in the properties for the combo box was correct "if I wanted to use a Combo Box". What options are available to acquire data from entry in a form that will update a table other than a Combo Box? If there is a "simpler way" to accomplish this, I'm open to it. I think for our purposes the ComboBox is the correct control to use. 2. The functions of the 2 tables a tblContractors - Holds the names of all the contractors that are available when entering into that field through Form1, as well as supporting info such as address, phone, etc. Table1 - Field1 thru Field 4 are just repositories to hold data which will be used later to create reports, etc. The procedure we are using here is only a simplified version of a larger procedure which will have more fields in it. In short, Table1 will be used in the final procedure, although the name will be different. What relationship does table1 have with tblContractors? Could there be many records in table1 with Contractor1 in the Contractor Field? In other words does Table1 have a Many-To-One relationship with tblContractors? I'll try to use your suggestion adding Form2 as an input to the tblContractors table. This makes good sense to me. It's hard to get away from years of "macro programming", and enter into the foggy world of VBA coding. Hopefully this exercise will convert me permanently. You won't regret it! g I see your reasoning concerning the ID fields as lookup pointers to select the ContractorName requested, but when the user gets to the point where they need to enter a contractor, they type the name of the contractor, and as soon as it recognizes the data being typed, the "look ahead" feature displays the existing entry in the table, at which time they can tab to accept that entry. When it's a new entry, the procedure we are creating here will accomplish the task of adding the new entry to the tblContractors table. But what if it's an original Contractor, but with a new or modified name? Will it make a "new" entry into the tblContractors for the new name, but the old original name will stay in the table? You could possible have "a few" different entries for the same contractor in the table depending on what the user enters. If it's a slight modification from the other entries for the same contractor, it goes in as a new contractor. Is this correct? Yup! That's why we intercept the "New" contractor and ask the user if they want to create this new contractor. If the user is familiar with the operation they will say "No, we've used them before. I must have typed something different." They will say no to the question and be left ready to pick a contractor. This time they will be more careful and scroll in the selections to try to locate the contractor. Hopefully they will succeed. Remember we are only recording the ContractorID field in Table1. Any report or form that displays the contractor name will get the name from the tblContractor table. This brings up table maintenance. To properly maintain the contractor table you really need a form (Form3? or modified Form2?) bound to tblContractors with a means to navigate the table (navigation buttons or another ComboBox). There should probably be TextBox controls on the form bound to each field in the table. Once on a particular Contractor's record, any change will modify the record including the Contractor name. To complicate matters a little more, the maintenance form should have the ability to "Delete" a Contractor and you should have Referential Integrity turned on in case this ContractorID has been used elsewhere. Have I lost you yet? Do not use the "Look up a contractor" function in Form1 as the only means to add to the Contractor table. The maintenance form should have the ability to add a new contractor. I will add Form2 for data entry to the tblContractors, and see if the code I enter is correct. I'll let you know on my next post how this went. Thanks again for leading me through this darkness to a solution. 10SNUT Well that's about it for this go around. Looking forward to how you made out with "Form2". -- RuralGuy Please reply to the newsgroup so all may benefit. |
#20
|
|||
|
|||
wrote:
RuralGuy, I found the problem. Everything works perfectly now. On the original procedure that we used to exit Form1 to Form2 when a Contractor needed to be added, I had (somehow??) coded the "Open Form2" statement TWICE. Even though it didn't look like we were in that area, when I commented out that second Open "Form2" argument, everything works good. I want to thank you VERY MUCH for your patience with me and my problem. I will hope to get you as a "respondent" to any future postings I submit to the Access Discussion Group. Relieved and Satisfied, 10SNUT Hi 10SNUT, Outstanding!!! Best news I've had all day. Really glad I was able to assist you in your success. Now you can pass it on to someone else! -- 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 |