Problem with Code - On Not In List Event

[JessiRight77]

I am working on a friend's "Song Database." From his main Song form,
he has a subform that lists the names of all the Hymn Books that
contains that Song, and he wants to be able to enter a new hymnal book
if it is not already listed in the drop-down list. The table that
contains the Hymnal Book information (T_Hymnals) has a Hymnal Code
field (HymnalCode) and a Hymnal Name field (HymnalName).

The combo box on the subform is based on a combination of the
HymnalCode ID and HymnalName... the HymnalCode ID is not displayed
because of the column width of 0";2"; the HymnalName is what is
actually shown in the drop-down box.

I searched in the Google Newsgroups for information about the "On Not
In List" event, and found the following code (below) by Dev Ashish, so
I modified it for my friend's situation.

The PROBLEM is: When I "Click Yes to Link," it assigns the new data
into the HymnalCode ID field of the combo box (which is not displayed),
instead of the HymnalName field (which is displayed), so the drop-down
box now contains a blank entry. There doesn't seem to be any way to
enter new data for the actual name of the hymnal... only the HymnalCode
ID.

The Ashish code I am trying to modify is as follows:

Private Sub cbxAEName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the
current DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link 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("T_Hymnals", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!HymnalCode = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub


Does anyone know how he can enter new data into the drop-down list if
it is based on a combination of the HymnalCode ID and HymnalName?
Any help would be greatly appreciated.

Jessi

 

[Brian Bastl]

change rs!HymnalCode to rs!HymnalName

Brian

 

[JessiRight77]

Thanks for your help, Brian.

I tried this, but now I get this error message: "An Error Occurred.
Please try again." (This is the Err message box in the VB code.)

Jessi

 

[Brian Bastl]

Hi Jesse,

I'm not 100% certain, since I don't use this approach, but try issuing a
Requery after the rs.Update as denoted with the asterisks below. My second
thought would be to move the "Response = acDataErrAdded" out of the "If Err"
clause and move it to where I suggest to issue the requery. Also, double
check that your spelling of the field is correct.

If this doesn't help, then post back. My approach is that I generally pop
open a form in dialog mode and pass the NewData to it via the OpenArgs
property, so that I can make sure that I've entered the name correctly and
make any additions or edits as required.

FWIW, you should really choose an appropriate name for YOUR combobox; maybe
something like "cboHymnals" (without the quotes)

I'd also suggest that your wrap T_Hymnals with brackets like:
set rs = db.OpenRecordset("[T_Hymnals]", dbOpenDynaset)


Hope this gets it done for you,

Brian

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("T_Hymnals", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!HymnalName = NewData
rs.Update

******* Me.ActiveControl.Requery *******

 

[Albert D.Kallal]

That code you have looks ok.

just remember, that "newdata" will be the text value you typed in...NOT the
id value of the combo. This why the suggestion is


change rs!HymnalCode to rs!HymnalName


So, you could try:

You can set the limit to yes = to yes.

The, for the no in list event, you can use the following code:


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

Dim strSql As String
If MsgBox(NewData & " not in list, add?", _
vbYesNo + vbQuestion) = vbYes Then
strSql = "insert into T_Hymnals (HymnalName) values('" & NewData &
"')"
CurrentDb.Execute strSql
Response = acDataErrAdded
End If

End Sub

The above is all you need for this to work....

You could also use reocrdset code, such as

Dim rst As dao.Recordset

If MsgBox(NewData & " not in list, add?", _
vbYesNo + vbQuestion) = vbYes Then
Set rst = CurrentDb.OpenRecordset("T_Hymnals")
rst.AddNew
rst!HymnalName = NewData
rst.Update
rst.Close
Set rst = Nothing

Response = acDataErrAdded
End If

The use of acDataErrAdded is critical, as that tells ms-access to re-load,
and re-query the combo box.....

DO NOT try to put in your own re-query.....it will mess up the combo