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
|
|||
|
|||
Add New Record
Could anyone help please?
I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#2
|
|||
|
|||
Add New Record
An example of generic code to do what you want is at:
http://www.datastrat.com/Code/NotInListCode.txt and a sample application using that code is at: http://www.accessmvp.com/Arvin/NotInListDemo.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Ray C" wrote in message news Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#3
|
|||
|
|||
Add New Record
To update the combo box's list set the return value of the NotInList event
procedure's Response argument to acDataErrAdded after the new row has been inserted into the table. However, as you need to edit the new record would it not be simpler to merely move the form to a new record and set the default values of the two controls, e.g. Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler Dim strMessage As String Dim strStore As String Dim strStoreType as string Dim ctrl As Control Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" strStore = NewData strStoreType = Me.[cmb_HeaderSelect3] Response = acDataErrContinue ctrl.Undo If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then Me.StoreName.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" DoCmd.GoToRecord acForm, Me.Name, acNewRec End If Exit_He Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub Note that the DefaultValue property of a control is a string expression regardless of the data type of the column to which its bound, so should be wrapped in literal quotes as above regardless of the data type. In the form's AfterInsert event procedure requery the combo box with: Me.[cmb_Find_Box]_3.Requery Ken Sheridan Stafford, England "Ray C" wrote: Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#4
|
|||
|
|||
Add New Record
Hi Ken. Thanks for this but when I try to run the routine i get a "Compile
Error" that stops the execution of the Sub , highlighting the Sub name in yellow and highlighting the part ".StoreName" in Blue the line Me.StoreName.DefaultValue = """" & strStore & """" and giving an error message "Compile Error. method or Data Member not found" If I REM out that particular line, I get the same error on the next very similar line. Am I missing some parenthasis here? Any thoughts please Ray C "Ken Sheridan" wrote: To update the combo box's list set the return value of the NotInList event procedure's Response argument to acDataErrAdded after the new row has been inserted into the table. However, as you need to edit the new record would it not be simpler to merely move the form to a new record and set the default values of the two controls, e.g. Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler Dim strMessage As String Dim strStore As String Dim strStoreType as string Dim ctrl As Control Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" strStore = NewData strStoreType = Me.[cmb_HeaderSelect3] Response = acDataErrContinue ctrl.Undo If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then Me.StoreName.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" DoCmd.GoToRecord acForm, Me.Name, acNewRec End If Exit_He Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub Note that the DefaultValue property of a control is a string expression regardless of the data type of the column to which its bound, so should be wrapped in literal quotes as above regardless of the data type. In the form's AfterInsert event procedure requery the combo box with: Me.[cmb_Find_Box]_3.Requery Ken Sheridan Stafford, England "Ray C" wrote: Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#5
|
|||
|
|||
Add New Record
Ray:
The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3 combo boxes are on the current form, which is bound to the underlying table or query, and that the form includes controls named StoreName and StoreType. Firstly make sure that the names of these controls in the code are Exactly the same as their actual names, remembering to warp the name in square brackets if it includes a space or other special character (if in doubt use the brackets: [StoreName] and [StoreType] If these last two are columns in the underlying table or query, but don't have controls on the form bound to them you'll need to add such controls so that you can set their DefaultValue properties. If you don't want to see them set their Visible property to False (No). Ken Sheridan Stafford, England "Ray C" wrote: Hi Ken. Thanks for this but when I try to run the routine i get a "Compile Error" that stops the execution of the Sub , highlighting the Sub name in yellow and highlighting the part ".StoreName" in Blue the line Me.StoreName.DefaultValue = """" & strStore & """" and giving an error message "Compile Error. method or Data Member not found" If I REM out that particular line, I get the same error on the next very similar line. Am I missing some parenthasis here? Any thoughts please Ray C "Ken Sheridan" wrote: To update the combo box's list set the return value of the NotInList event procedure's Response argument to acDataErrAdded after the new row has been inserted into the table. However, as you need to edit the new record would it not be simpler to merely move the form to a new record and set the default values of the two controls, e.g. Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler Dim strMessage As String Dim strStore As String Dim strStoreType as string Dim ctrl As Control Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" strStore = NewData strStoreType = Me.[cmb_HeaderSelect3] Response = acDataErrContinue ctrl.Undo If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then Me.StoreName.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" DoCmd.GoToRecord acForm, Me.Name, acNewRec End If Exit_He Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub Note that the DefaultValue property of a control is a string expression regardless of the data type of the column to which its bound, so should be wrapped in literal quotes as above regardless of the data type. In the form's AfterInsert event procedure requery the combo box with: Me.[cmb_Find_Box]_3.Requery Ken Sheridan Stafford, England "Ray C" wrote: Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#6
|
|||
|
|||
Add New Record
Hi ken
Thanks for this but after I sent you the reply, I worked out a number of things. 1. that I needed to set the allow additions to true. 2. that the Bound Text Box the sits underneath the Unbound Combo Find box is called simply "Box_3" (for a variety of reasons) 3. that I do not have a bound text Box for "StoreType" so I have added one All seems to be OK and I do not now get the erros message exept that I only get the last record in the table displayed when I run the routine. In checking the table itself there is no new item added. my modification to the code is Me.Box_3.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False Box_3.Visible = True: Box_4.Visible = True DoCmd.GoToRecord acForm, Me.Name, acNewRec At the begining I have added Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True and the coverse at the end I must still be doing something wrong. Hope you can put your finger on it, thanks RayC P.S. Is there a way to change a Text Box into a Combo box and back again under program control? "Ken Sheridan" wrote: Ray: The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3 combo boxes are on the current form, which is bound to the underlying table or query, and that the form includes controls named StoreName and StoreType. Firstly make sure that the names of these controls in the code are Exactly the same as their actual names, remembering to warp the name in square brackets if it includes a space or other special character (if in doubt use the brackets: [StoreName] and [StoreType] If these last two are columns in the underlying table or query, but don't have controls on the form bound to them you'll need to add such controls so that you can set their DefaultValue properties. If you don't want to see them set their Visible property to False (No). Ken Sheridan Stafford, England "Ray C" wrote: Hi Ken. Thanks for this but when I try to run the routine i get a "Compile Error" that stops the execution of the Sub , highlighting the Sub name in yellow and highlighting the part ".StoreName" in Blue the line Me.StoreName.DefaultValue = """" & strStore & """" and giving an error message "Compile Error. method or Data Member not found" If I REM out that particular line, I get the same error on the next very similar line. Am I missing some parenthasis here? Any thoughts please Ray C "Ken Sheridan" wrote: To update the combo box's list set the return value of the NotInList event procedure's Response argument to acDataErrAdded after the new row has been inserted into the table. However, as you need to edit the new record would it not be simpler to merely move the form to a new record and set the default values of the two controls, e.g. Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler Dim strMessage As String Dim strStore As String Dim strStoreType as string Dim ctrl As Control Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" strStore = NewData strStoreType = Me.[cmb_HeaderSelect3] Response = acDataErrContinue ctrl.Undo If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then Me.StoreName.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" DoCmd.GoToRecord acForm, Me.Name, acNewRec End If Exit_He Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub Note that the DefaultValue property of a control is a string expression regardless of the data type of the column to which its bound, so should be wrapped in literal quotes as above regardless of the data type. In the form's AfterInsert event procedure requery the combo box with: Me.[cmb_Find_Box]_3.Requery Ken Sheridan Stafford, England "Ray C" wrote: Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#7
|
|||
|
|||
Add New Record
Ray:
I think the problem is that you are setting the form's AllowAdditions property back to False in the same procedure as moving to the new record, so what's happening is that it is moving to the new record, but then immediately back to the last record before you can even see its moved momentarily to a new record. Set it to True at the start, but not back to False at the end. Then set it back to False in the form's AfterInsert event procedure. You should then find it moves to a new record, allows you to insert the additional data, but then doesn't allow you to navigate to another new record manually. An important point to understand is that when you set the DefaultValue property of a control it doesn't 'dirty' the form, i.e. the form doesn't contain an unsaved new record; that doesn't happen until you add additional data yourself. One thing this does mean is that a user could decide not to proceed with adding the new record and navigate to another record, in which case the AllowAdditions property would stay as True as the AfterInsert event would not fire. To cater for this you can put the following code in the form's Current event procedu Me.AllowAdditions = Me.NewRecord The only way I can think of changing a control from a text box to a combo box and vice versa at runtime would be the extremely cumbersome one of switching between form view and design view and creating and deleting the controls. There would be a host of problems associated with this, so I don't think it’s a practical proposition. There is a far easier solution, however, which is simply to have separate superimposed text box and combo box controls and to hide/show one or the other. You'd need to make sure that the focus is moved off a visible one before hiding the it, e.g. If Me.MyTextBox.Visible Then Me.MyComboBox.Visible = True Me.MyComboBox.SetFocus Me.MyTextBox.Visible = False Else Me.MyTextBox.Visible = True Me.MyTextBox.SetFocus Me.MyComboBox.Visible = False End If You'd set the Visible property of one to True (Yes) and the other to False (No) in the properties sheet to govern which shows when the form opens. If they are unbound controls and you need to keep them in sync then you'd need to assign a value to the hidden one in the visible one's AfterUpdate event procedure; if they are bound controls then changing the value in one should automatically be reflected in the other. Ken Sheridan Stafford, England "Ray C" wrote: Hi ken Thanks for this but after I sent you the reply, I worked out a number of things. 1. that I needed to set the allow additions to true. 2. that the Bound Text Box the sits underneath the Unbound Combo Find box is called simply "Box_3" (for a variety of reasons) 3. that I do not have a bound text Box for "StoreType" so I have added one All seems to be OK and I do not now get the erros message exept that I only get the last record in the table displayed when I run the routine. In checking the table itself there is no new item added. my modification to the code is Me.Box_3.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False Box_3.Visible = True: Box_4.Visible = True DoCmd.GoToRecord acForm, Me.Name, acNewRec At the begining I have added Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True and the coverse at the end I must still be doing something wrong. Hope you can put your finger on it, thanks RayC P.S. Is there a way to change a Text Box into a Combo box and back again under program control? "Ken Sheridan" wrote: Ray: The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3 combo boxes are on the current form, which is bound to the underlying table or query, and that the form includes controls named StoreName and StoreType. Firstly make sure that the names of these controls in the code are Exactly the same as their actual names, remembering to warp the name in square brackets if it includes a space or other special character (if in doubt use the brackets: [StoreName] and [StoreType] If these last two are columns in the underlying table or query, but don't have controls on the form bound to them you'll need to add such controls so that you can set their DefaultValue properties. If you don't want to see them set their Visible property to False (No). Ken Sheridan Stafford, England "Ray C" wrote: Hi Ken. Thanks for this but when I try to run the routine i get a "Compile Error" that stops the execution of the Sub , highlighting the Sub name in yellow and highlighting the part ".StoreName" in Blue the line Me.StoreName.DefaultValue = """" & strStore & """" and giving an error message "Compile Error. method or Data Member not found" If I REM out that particular line, I get the same error on the next very similar line. Am I missing some parenthasis here? Any thoughts please Ray C "Ken Sheridan" wrote: To update the combo box's list set the return value of the NotInList event procedure's Response argument to acDataErrAdded after the new row has been inserted into the table. However, as you need to edit the new record would it not be simpler to merely move the form to a new record and set the default values of the two controls, e.g. Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler Dim strMessage As String Dim strStore As String Dim strStoreType as string Dim ctrl As Control Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" strStore = NewData strStoreType = Me.[cmb_HeaderSelect3] Response = acDataErrContinue ctrl.Undo If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then Me.StoreName.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" DoCmd.GoToRecord acForm, Me.Name, acNewRec End If Exit_He Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub Note that the DefaultValue property of a control is a string expression regardless of the data type of the column to which its bound, so should be wrapped in literal quotes as above regardless of the data type. In the form's AfterInsert event procedure requery the combo box with: Me.[cmb_Find_Box]_3.Requery Ken Sheridan Stafford, England "Ray C" wrote: Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
#8
|
|||
|
|||
Add New Record
Ken, your a wizard, thanks. I though I should re-set the forms properties on
exit but your explanation makes it a lot clearer; and it works. Yes, I have a Bound Text Box that sits beneath the Unbound Combo Find Box and I switch visibility when I want to either "Find" or Display / Edit. Just wondered if there was a way to reduce the thing down to just one box that changed its character on commant. The two Box option works fine though. just a quick thsnk you to Arvin Mayer for his input, it was most usefull. With many thanks RayC "Ken Sheridan" wrote: Ray: I think the problem is that you are setting the form's AllowAdditions property back to False in the same procedure as moving to the new record, so what's happening is that it is moving to the new record, but then immediately back to the last record before you can even see its moved momentarily to a new record. Set it to True at the start, but not back to False at the end. Then set it back to False in the form's AfterInsert event procedure. You should then find it moves to a new record, allows you to insert the additional data, but then doesn't allow you to navigate to another new record manually. An important point to understand is that when you set the DefaultValue property of a control it doesn't 'dirty' the form, i.e. the form doesn't contain an unsaved new record; that doesn't happen until you add additional data yourself. One thing this does mean is that a user could decide not to proceed with adding the new record and navigate to another record, in which case the AllowAdditions property would stay as True as the AfterInsert event would not fire. To cater for this you can put the following code in the form's Current event procedu Me.AllowAdditions = Me.NewRecord The only way I can think of changing a control from a text box to a combo box and vice versa at runtime would be the extremely cumbersome one of switching between form view and design view and creating and deleting the controls. There would be a host of problems associated with this, so I don't think it’s a practical proposition. There is a far easier solution, however, which is simply to have separate superimposed text box and combo box controls and to hide/show one or the other. You'd need to make sure that the focus is moved off a visible one before hiding the it, e.g. If Me.MyTextBox.Visible Then Me.MyComboBox.Visible = True Me.MyComboBox.SetFocus Me.MyTextBox.Visible = False Else Me.MyTextBox.Visible = True Me.MyTextBox.SetFocus Me.MyComboBox.Visible = False End If You'd set the Visible property of one to True (Yes) and the other to False (No) in the properties sheet to govern which shows when the form opens. If they are unbound controls and you need to keep them in sync then you'd need to assign a value to the hidden one in the visible one's AfterUpdate event procedure; if they are bound controls then changing the value in one should automatically be reflected in the other. Ken Sheridan Stafford, England "Ray C" wrote: Hi ken Thanks for this but after I sent you the reply, I worked out a number of things. 1. that I needed to set the allow additions to true. 2. that the Bound Text Box the sits underneath the Unbound Combo Find box is called simply "Box_3" (for a variety of reasons) 3. that I do not have a bound text Box for "StoreType" so I have added one All seems to be OK and I do not now get the erros message exept that I only get the last record in the table displayed when I run the routine. In checking the table itself there is no new item added. my modification to the code is Me.Box_3.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" cmb_Find_Box_3.Visible = False: cmb_Find_Box_4.Visible = False Box_3.Visible = True: Box_4.Visible = True DoCmd.GoToRecord acForm, Me.Name, acNewRec At the begining I have added Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True and the coverse at the end I must still be doing something wrong. Hope you can put your finger on it, thanks RayC P.S. Is there a way to change a Text Box into a Combo box and back again under program control? "Ken Sheridan" wrote: Ray: The code assumes that the both the cmb_Find_Box_3 and cmb_HeaderSelect3 combo boxes are on the current form, which is bound to the underlying table or query, and that the form includes controls named StoreName and StoreType. Firstly make sure that the names of these controls in the code are Exactly the same as their actual names, remembering to warp the name in square brackets if it includes a space or other special character (if in doubt use the brackets: [StoreName] and [StoreType] If these last two are columns in the underlying table or query, but don't have controls on the form bound to them you'll need to add such controls so that you can set their DefaultValue properties. If you don't want to see them set their Visible property to False (No). Ken Sheridan Stafford, England "Ray C" wrote: Hi Ken. Thanks for this but when I try to run the routine i get a "Compile Error" that stops the execution of the Sub , highlighting the Sub name in yellow and highlighting the part ".StoreName" in Blue the line Me.StoreName.DefaultValue = """" & strStore & """" and giving an error message "Compile Error. method or Data Member not found" If I REM out that particular line, I get the same error on the next very similar line. Am I missing some parenthasis here? Any thoughts please Ray C "Ken Sheridan" wrote: To update the combo box's list set the return value of the NotInList event procedure's Response argument to acDataErrAdded after the new row has been inserted into the table. However, as you need to edit the new record would it not be simpler to merely move the form to a new record and set the default values of the two controls, e.g. Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Handler Dim strMessage As String Dim strStore As String Dim strStoreType as string Dim ctrl As Control Set ctrl = Me.ActiveControl strMessage = "Add " & NewData & " to list?" strStore = NewData strStoreType = Me.[cmb_HeaderSelect3] Response = acDataErrContinue ctrl.Undo If MsgBox(strMessage, vbQuestion + vbYesNo) = vbYes Then Me.StoreName.DefaultValue = """" & strStore & """" Me.StoreType.DefaultValue = """" & strStoreType & """" DoCmd.GoToRecord acForm, Me.Name, acNewRec End If Exit_He Exit Sub Err_Handler: MsgBox Err.Description, vbExclamation, "Error" Resume Exit_Here End Sub Note that the DefaultValue property of a control is a string expression regardless of the data type of the column to which its bound, so should be wrapped in literal quotes as above regardless of the data type. In the form's AfterInsert event procedure requery the combo box with: Me.[cmb_Find_Box]_3.Requery Ken Sheridan Stafford, England "Ray C" wrote: Could anyone help please? I use an unbound Combo Box as a method of finding records in a table and tha is currently working fine. I am wanting to enter an item into the "Find Combo Box" and the search for it with the result that ; if the item is not in the list, the programe tells the operator so and gives the oportunity to ADD a new record to the table and include the field entered into the Find Combo Box. Without going into te reason why, I need to have two fielda in the new record so that I can then search the table, pull up the new record with the two fields completed and the remaining fields open for Edit ( insert the new data) The code I use to add a new record and enter the two fields that ate required for the subsequent search is as follows :- Private Sub cmb_Find_Box_3_NotInList(NewData As String, Response As Integer) Dim strSQL1 As String, strSQL2 As String, strMessage As String Dim rsMyRs As Recordset, dbMyDB As Database Debug.Print "cmb_HeaderSelect2 = "; Cmb_HeaderSelect2 Me.AllowEdits = True: Me.AllowAdditions = True: Me.AllowDeletions = True Set dbMyDB = CurrentDb strMessage = "Do you want to Add " & NewData & " to list?" If Cmb_HeaderSelect2 = 6 Then ' Store Set rsMyRs = dbMyDB.OpenRecordset("tbl_Store", dbOpenDynaset) ElseIf Cmb_HeaderSelect2 = 4 Then ' SUPPLIERS ' not written yet for this option End If If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then rsMyRs.AddNew rsMyRs.Fields("StoreName") = NewData rsMyRs.Fields("StoreType") = cmb_HeaderSelect3 rsMyRs.Update rsMyRs.Requery 'cmd.CommandText = strSQL1 Else Exit Sub End If rsMyRs.Close: dbMyDB.Close ' At this point a new Record has been added to the Table and contains the two pieces of information in the relative fields "StoreName" and "StoreType" I now need to Search the Table for the new Record and set that so I will be able to "Edit" the blank Fields and store the data relative to the record that I have just created But the usual rouine that I use to find records will not find the new record. Though I have done a Requery of the Recorset earlier, I am thinking that I need to do a Requery of the Ind Combo Box "cmb_Find_Combo_3". Tried the ones below but I Just get errors. ' cmb_Find_Box_3.Requery ' DoCmd.Requery "cmb_Find_Box_3" Me.Box_3.ControlSource = "StoreName" ' Set the Control Source for Display Box 3 Call AfterUpdate_Process1("StoreName", NewData, 2, cmb_HeaderSelect3, "0034") Me.AllowEdits = True: Me.AllowAdditions = False: Me.AllowDeletions = False End Sub Is my thinking all wrong here`? Can anyone help please? thanks RayC |
Thread Tools | |
Display Modes | |
|
|