By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Combo Notinlist - add new item

P: n/a
mal
Sorry for repost - system added to another subject for some reason

Have tried numerous ideas from the group to solve this one. It is such
a simple example that it should be straightforward ! I just want to
add a new item to a combo that has data from a file, by typing in the
new value , adding to the file and the requerying to get the new valus
in the list. i.e. a data entry and data display combo box.
I select an item from cmb1 and cmb2. I focus on cmb3 and the user can
type a new value. This then adds to the underlying table used by both
cmb1, cmb2 and cmb3. All works fine except for the not in list error
and the system repeatedly waits with the combo dropped down, for an
item to be selected. If I keep changing the focus elsewhere, the
system, just goes back and repeats my query. If, on response to my
query, I accept and add a second time then all works OK except I now
have 2 records on the database.
Why is it adding to the table, but still requesting to select from the
drop down list ?
Why does it seem to be OK the second time ?
If I do something else after the second time and then look at the drop
down, sure enough there are 2 records there. I think this is because I
have a requery in the GetFocus event. My problem is why it needs me to
select Yes to my user prompt twice !!!
Any Ideas ?
Thanks
Private Sub cmb3_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As Variant
Dim db As database
' this makes no difference - DoCmd.SetWarnings False
' the next few lines get values from other cmb to display the full
record details
' of the record to be added.
' cmb1 has limit to list = yes and must be in the list
' cmb2 has limit to list = no and can have any value entered
' it is only when this third field is entered that
' prompt should occur and the record added to the data file
' In prcatice the record is added Ok but the error message
' The text you entered isn't an item in the list
strMsg = cmb1.Value & " " & cmb2.Value & " "
strMsg = strMsg & NewData & " is not in the list. "
strMsg = strMsg & "Would you like to add it?"
' The following MSGBox is fine
If MsgBox(strMsg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
Else
Response = acDataErrAdded ' or acDataErrcontinue tried
' now add the record - this bit works fine
Set db = OpenDatabase("Mydatabase.mdb")
Set rst = db.OpenRecordset("tblUnderlyingTable",
DB_OPEN_TABLE)
rst.AddNew
rst!FavouriteGroup = MyFavourite ' saved from cmb1.value
rst!TeamCLientName = MyFavouriteTeamClient ' saved from
cmb2.value
rst!GroupName = NewData
rst.Update
rst.Close
' if I quit the system here,
' the record has been added to the table correctly
'
' Other things I've tried -
' this doesn't work........ Me.Refresh
'
' you must save the current record
' before you requery occurs if I include
cmbGroupDescription.Requery
'
' makes no difference cmbGroupDescription = Null
' makes no difference cmbGroupDescription.Requery
'
' this makes no difference - DoCmd.SetWarnings True
End If
End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
HJ
Without going through your entire code, try this. You may have to extend the
code for the other combo boxes.

HJ
On the form the NotInList event of the combo box can be like this:

Private Sub cboTask_NotInList(NewData As String, Response As Integer)

'Add a new entry to the combo box
Response = modGeneral.AddNotInListValue(NewData, cboTask, "task",
"tblTasks", "taskDescription", "taskID")

End Sub
The function that is called:

Function AddNotInListValue(strNewData As String, cboList As ComboBox,
strDesc As String, _
strTable As String, strValueFieldName As String, strIDFieldName As
String) As Integer

'Add a new entry to the combo box
Dim db As DAO.Database, rs As DAO.Recordset, lngID As Long
Dim strMSG As String
strMSG = "'" & strNewData & "' is not an available " & strDesc & "."
strMSG = strMSG & vbCr & "Do you want to add this " & strDesc & " to the
list?"
strMSG = strMSG & vbCr & "Click Yes to confirm or No to re-type it."
If MsgBox(strMSG, vbQuestion + vbYesNo, "Add new " & strDesc & "?") =
vbNo Then
AddNotInListValue = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(strTable, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs(strValueFieldName) = strNewData
lngID = rs(strIDFieldName)
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
AddNotInListValue = acDataErrContinue
Else
AddNotInListValue = acDataErrAdded
cboList.Value = lngID
End If
End If

End Function

"mal" <ma******@hotmail.com> wrote in message
news:7a**************************@posting.google.c om...
Sorry for repost - system added to another subject for some reason

Have tried numerous ideas from the group to solve this one. It is such
a simple example that it should be straightforward ! I just want to
add a new item to a combo that has data from a file, by typing in the
new value , adding to the file and the requerying to get the new valus
in the list. i.e. a data entry and data display combo box.
I select an item from cmb1 and cmb2. I focus on cmb3 and the user can
type a new value. This then adds to the underlying table used by both
cmb1, cmb2 and cmb3. All works fine except for the not in list error
and the system repeatedly waits with the combo dropped down, for an
item to be selected. If I keep changing the focus elsewhere, the
system, just goes back and repeats my query. If, on response to my
query, I accept and add a second time then all works OK except I now
have 2 records on the database.
Why is it adding to the table, but still requesting to select from the
drop down list ?
Why does it seem to be OK the second time ?
If I do something else after the second time and then look at the drop
down, sure enough there are 2 records there. I think this is because I
have a requery in the GetFocus event. My problem is why it needs me to
select Yes to my user prompt twice !!!
Any Ideas ?
Thanks
Private Sub cmb3_NotInList(NewData As String, Response As Integer)
Dim strMsg As String
Dim rst As Variant
Dim db As database
' this makes no difference - DoCmd.SetWarnings False
' the next few lines get values from other cmb to display the full
record details
' of the record to be added.
' cmb1 has limit to list = yes and must be in the list
' cmb2 has limit to list = no and can have any value entered
' it is only when this third field is entered that
' prompt should occur and the record added to the data file
' In prcatice the record is added Ok but the error message
' The text you entered isn't an item in the list
strMsg = cmb1.Value & " " & cmb2.Value & " "
strMsg = strMsg & NewData & " is not in the list. "
strMsg = strMsg & "Would you like to add it?"
' The following MSGBox is fine
If MsgBox(strMsg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
Else
Response = acDataErrAdded ' or acDataErrcontinue tried
' now add the record - this bit works fine
Set db = OpenDatabase("Mydatabase.mdb")
Set rst = db.OpenRecordset("tblUnderlyingTable",
DB_OPEN_TABLE)
rst.AddNew
rst!FavouriteGroup = MyFavourite ' saved from cmb1.value
rst!TeamCLientName = MyFavouriteTeamClient ' saved from
cmb2.value
rst!GroupName = NewData
rst.Update
rst.Close
' if I quit the system here,
' the record has been added to the table correctly
'
' Other things I've tried -
' this doesn't work........ Me.Refresh
'
' you must save the current record
' before you requery occurs if I include
cmbGroupDescription.Requery
'
' makes no difference cmbGroupDescription = Null
' makes no difference cmbGroupDescription.Requery
'
' this makes no difference - DoCmd.SetWarnings True
End If
End Sub

Nov 13 '05 #2

P: n/a
mal
Cheers HJ

I think the problem was a bit deeper in the Event handling and you
corrected me by removing the .close

I also used the Currentdb at the same time so one of the two made it
work !

Cheers
"HJ" <hj********@spamhotmail.com> wrote in message news:<41***********************@dreader19.news.xs4 all.nl>...
Nov 13 '05 #3

P: n/a
Here's a routine that works fine for me, even on a subform!:

On Error GoTo Err_KIDNotINLIST

Dim lngCustID As Long

Response = acDataErrContinue
If IsNull(Me![CustID]) Then
Me![CustID].Text = ""
Else
lngCustID = Me![CustID]
Me![CustID] = Null
End If
DoCmd.OpenForm "frmRegCustFromOrders", acNormal, , , acFormAdd, ,
NewData
Me![CustID].Requery
If lngCustID <> 0 Then Me![CustID] = lngCustID

Exit_KIDNotINLIST:
Exit Sub
Err_KIDNotINLIST:
MsgBox Err.Number & " " & "ORD/KIDNotINLIST" & Chr(13) _
& Err.Description, vbOKOnly
Resume Exit_KIDNotINLIST

Haven't found out yet how to make the new registration be showed in
the cboCust on the main form after registration though. I type the
first letter of the new registration and it shows. Any comment, be
welcome...

Me.Name
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.