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.SetWarnin gs 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 = acDataErrContin ue
Else
Response = acDataErrAdded ' or acDataErrcontin ue tried
' now add the record - this bit works fine
Set db = OpenDatabase("M ydatabase.mdb")
Set rst = db.OpenRecordse t("tblUnderlyin gTable",
DB_OPEN_TABLE)
rst.AddNew
rst!FavouriteGr oup = MyFavourite ' saved from cmb1.value
rst!TeamCLientN ame = MyFavouriteTeam Client ' 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
cmbGroupDescrip tion.Requery
'
' makes no difference cmbGroupDescrip tion = Null
' makes no difference cmbGroupDescrip tion.Requery
'
' this makes no difference - DoCmd.SetWarnin gs True
End If
End Sub
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.SetWarnin gs 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 = acDataErrContin ue
Else
Response = acDataErrAdded ' or acDataErrcontin ue tried
' now add the record - this bit works fine
Set db = OpenDatabase("M ydatabase.mdb")
Set rst = db.OpenRecordse t("tblUnderlyin gTable",
DB_OPEN_TABLE)
rst.AddNew
rst!FavouriteGr oup = MyFavourite ' saved from cmb1.value
rst!TeamCLientN ame = MyFavouriteTeam Client ' 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
cmbGroupDescrip tion.Requery
'
' makes no difference cmbGroupDescrip tion = Null
' makes no difference cmbGroupDescrip tion.Requery
'
' this makes no difference - DoCmd.SetWarnin gs True
End If
End Sub