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
|
|||
|
|||
Enter record when 'NotInList'
I know there are already postings of this, but after trawling through them
all I am still having trouble. I am fairly unfamiliar with code which is a bit of a hassle for this requirement!! I have a form titled "Data_Elements" with a combo box with the autoexpand set to yes. So as I type in a "DE_Identifier" it fills the combo box with options. But if the "DE_Identifier" is not in the list I get an error. On the NotInList event of this combo box, ideally what I would like is to have the form "Data_Elements" skip to a new record and enter this data in the "DE_Identifier" field. The underlying table is also called "Data_Elements" (sorry). If you can help that would be great! I wouldnt mind even just skipping to a new record where the Identifier can be typed in again by the user. Not the best option- but it'll do!! Cheers in advance. |
#2
|
|||
|
|||
On Tue, 23 Aug 2005 04:26:01 -0700, Ben wrote:
I know there are already postings of this, but after trawling through them all I am still having trouble. I am fairly unfamiliar with code which is a bit of a hassle for this requirement!! I have a form titled "Data_Elements" with a combo box with the autoexpand set to yes. So as I type in a "DE_Identifier" it fills the combo box with options. But if the "DE_Identifier" is not in the list I get an error. On the NotInList event of this combo box, ideally what I would like is to have the form "Data_Elements" skip to a new record and enter this data in the "DE_Identifier" field. The underlying table is also called "Data_Elements" (sorry). If you can help that would be great! I wouldnt mind even just skipping to a new record where the Identifier can be typed in again by the user. Not the best option- but it'll do!! Cheers in advance. If you have a form named "Data_Elements" and a table named "Data_Elements" you are looking for difficulties. It's confusing enough for a human to figure out which you are referring to. It is going to be confusing to Access also. You would be well advised to change the name of the form to something else, perhaps "frmData_Elements". It also would be wise to add an identifier to the table name as well, i.e. "tblData_Elements". If you only need to add a record of just the one field (DE_Identifier) to the underlying table, you don't have to actually open a form to enter it again. You can append the new data directly into the table. I'll guess that DE_Identifier is a Text datatype field. If so.... Try this in the Combo's NotInList event: Private Sub ComboName_NotInList(NewData As String, Response As Integer) ' Prompt user to verify they wish to add new value. If MsgBox("This DE_Identifier is not in the list. Add it?", vbOKCancel) = vbOK Then CurrentDb.Execute " INSERT INTO YourTableName(DE_Identifier) SELECT " & chr(34) & NewData & chr(34) & ";",dbFailOnError ' Set Response argument to indicate that data is being added. Response = acDataErrAdded Else ' If user chooses Cancel, suppress error message and undo changes. Response = acDataErrContinue ' Clear the name from the combo box. Me.ComboName = Null End If End Sub Change YourTableName to whatever the actual table is. Add your own error handling. -- Fred Please only reply to this newsgroup. I do not reply to personal email. |
#3
|
|||
|
|||
I tried your code but kept getting error messages. As I am only just learning
VBA it is a little difficult for me to fix. I have found some code from another access page which does what I need it to (almost!). There are just a couple of little quirks that need to be weeded out. If you could have a look that would be appreciated: Private Sub cboIdentifier_NotInList(NewData As String, Response As Integer) Dim strsql As String, x As Integer Dim strFrmName As String Dim strLinkCriteria As String strFrmName = "Data_Elements" x = MsgBox("Do you want to add this record?", vbYesNo) If x = vbYes Then strsql = "Insert Into Data_Elements ([DE_Identifier]) values ('" & NewData & "')" CurrentDb.Execute strsql, dbFailOnError strLinkCriteria = "[DE_Identifier] = '" & NewData & "' " DoCmd.OpenForm strFrmName, , , strLinkCriteria Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub This has two problems: 1- It does create a new record, but it does it twice. I get two new records in my table from one entry in this combo box. 2- If I do not close the form and re-open it, the form gives itself a filter so that only the new record is displayed (twice). Are there any sugestions on fixing these problems? "Ben" wrote: I know there are already postings of this, but after trawling through them all I am still having trouble. I am fairly unfamiliar with code which is a bit of a hassle for this requirement!! I have a form titled "Data_Elements" with a combo box with the autoexpand set to yes. So as I type in a "DE_Identifier" it fills the combo box with options. But if the "DE_Identifier" is not in the list I get an error. On the NotInList event of this combo box, ideally what I would like is to have the form "Data_Elements" skip to a new record and enter this data in the "DE_Identifier" field. The underlying table is also called "Data_Elements" (sorry). If you can help that would be great! I wouldnt mind even just skipping to a new record where the Identifier can be typed in again by the user. Not the best option- but it'll do!! Cheers in advance. |
#4
|
|||
|
|||
Try putting Response = acDataErrContinue before the Else
Like so: If x = vbNo Then Response = acDataErrContinue Else strsql = "Insert Into Data_Elements ([DE_Identifier]) values ('" & NewData & "')" CurrentDb.Execute strsql, dbFailOnError strLinkCriteria = "[DE_Identifier] = '" & NewData & "' " DoCmd.OpenForm strFrmName, , , strLinkCriteria Response = acDataErrAdded End If KB Ben wrote: I tried your code but kept getting error messages. As I am only just learning VBA it is a little difficult for me to fix. I have found some code from another access page which does what I need it to (almost!). There are just a couple of little quirks that need to be weeded out. If you could have a look that would be appreciated: Private Sub cboIdentifier_NotInList(NewData As String, Response As Integer) Dim strsql As String, x As Integer Dim strFrmName As String Dim strLinkCriteria As String strFrmName = "Data_Elements" x = MsgBox("Do you want to add this record?", vbYesNo) If x = vbYes Then strsql = "Insert Into Data_Elements ([DE_Identifier]) values ('" & NewData & "')" CurrentDb.Execute strsql, dbFailOnError strLinkCriteria = "[DE_Identifier] = '" & NewData & "' " DoCmd.OpenForm strFrmName, , , strLinkCriteria Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub This has two problems: 1- It does create a new record, but it does it twice. I get two new records in my table from one entry in this combo box. 2- If I do not close the form and re-open it, the form gives itself a filter so that only the new record is displayed (twice). Are there any sugestions on fixing these problems? I know there are already postings of this, but after trawling through them all I am still having trouble. I am fairly unfamiliar with code which is a [quoted text clipped - 9 lines] new record where the Identifier can be typed in again by the user. Not the best option- but it'll do!! Cheers in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200508/1 |
#5
|
|||
|
|||
Thank you so much!! works excellently! One minor problem though- I can't get
the form to requery. How can I do this? Once I press SHIFT + F9 the new record is added, but not before. I would like the new record to be added in my form then skip to it automatically. Any ideas? "BabyATX13 via AccessMonster.com" wrote: Try putting Response = acDataErrContinue before the Else Like so: If x = vbNo Then Response = acDataErrContinue Else strsql = "Insert Into Data_Elements ([DE_Identifier]) values ('" & NewData & "')" CurrentDb.Execute strsql, dbFailOnError strLinkCriteria = "[DE_Identifier] = '" & NewData & "' " DoCmd.OpenForm strFrmName, , , strLinkCriteria Response = acDataErrAdded End If KB Ben wrote: I tried your code but kept getting error messages. As I am only just learning VBA it is a little difficult for me to fix. I have found some code from another access page which does what I need it to (almost!). There are just a couple of little quirks that need to be weeded out. If you could have a look that would be appreciated: Private Sub cboIdentifier_NotInList(NewData As String, Response As Integer) Dim strsql As String, x As Integer Dim strFrmName As String Dim strLinkCriteria As String strFrmName = "Data_Elements" x = MsgBox("Do you want to add this record?", vbYesNo) If x = vbYes Then strsql = "Insert Into Data_Elements ([DE_Identifier]) values ('" & NewData & "')" CurrentDb.Execute strsql, dbFailOnError strLinkCriteria = "[DE_Identifier] = '" & NewData & "' " DoCmd.OpenForm strFrmName, , , strLinkCriteria Response = acDataErrAdded Else Response = acDataErrContinue End If End Sub This has two problems: 1- It does create a new record, but it does it twice. I get two new records in my table from one entry in this combo box. 2- If I do not close the form and re-open it, the form gives itself a filter so that only the new record is displayed (twice). Are there any sugestions on fixing these problems? I know there are already postings of this, but after trawling through them all I am still having trouble. I am fairly unfamiliar with code which is a [quoted text clipped - 9 lines] new record where the Identifier can be typed in again by the user. Not the best option- but it'll do!! Cheers in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200508/1 |
#6
|
|||
|
|||
I am using the following code for this particular kind of update and it works
really well KB Private Sub Department_NotInList(NewData As String, Response As Integer) 'Adds item to list Dim db As DAO.Database Dim rs As DAO.Recordset Dim strMsg As String strMsg = "'" & NewData & "' is not an available Department Name!" & vbCrLf & vbCrLf strMsg = strMsg & "Do you want to add it?" strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add it or No to re-type it." If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then Response = acDataErrContinue Else Set db = CurrentDb Set rs = db.OpenRecordset("Lowes Departments", dbOpenDynaset) On Error Resume Next rs.AddNew rs!DeptName = NewData rs.Update If Err Then MsgBox "An error occurred. Please try again." Response = acDataErrContinue Else Response = acDataErrAdded End If End If Set rs = Nothing Set db = Nothing End Sub Ben wrote: Thank you so much!! works excellently! One minor problem though- I can't get the form to requery. How can I do this? Once I press SHIFT + F9 the new record is added, but not before. I would like the new record to be added in my form then skip to it automatically. Any ideas? Try putting Response = acDataErrContinue before the Else Like so: [quoted text clipped - 52 lines] new record where the Identifier can be typed in again by the user. Not the best option- but it'll do!! Cheers in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200508/1 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Enter Parameter Value from adding a record | TimT | Using Forms | 1 | August 3rd, 2005 09:51 PM |
Enter date from previous record to new record | Randy | Using Forms | 4 | May 18th, 2005 12:34 AM |
Parameter query on multiple dates | Lisa | Running & Setting Up Queries | 2 | April 22nd, 2005 09:51 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Adding Record via NotInList Event | marty | Using Forms | 2 | July 2nd, 2004 03:36 PM |