Enter record when 'NotInList'

B

Ben

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.
 
F

fredg

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.
Click to expand...

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.
 
B

Ben

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?
 
B

BabyATX13 via AccessMonster.com

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
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
Click to expand...
[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.
Click to expand...
Click to expand...
 
B

Ben

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 said:
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
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
Click to expand...
[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.
Click to expand...
Click to expand...
Click to expand...
 
B

BabyATX13 via AccessMonster.com

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
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:
Click to expand...
[quoted text clipped - 52 lines]
Click to expand...
 
Top