NotInList updated but not shown on form until close and reopen

S

Song

The following NotInList will add new record but it is not shown on
current form until I close and reopen. When I add new data, I want the
form to show the newly added record so I can enter other information.

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

strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this record?"
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("contacts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SID = 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
 
D

Dirk Goldgar

Song said:
The following NotInList will add new record but it is not shown on
current form until I close and reopen. When I add new data, I want the
form to show the newly added record so I can enter other information.

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

strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this record?"
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("contacts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SID = 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
Click to expand...


Is this combo box's rowsource drawing from the same table as the form's
recordsource -- presumably the table "contacts" -- so that the combo box is
used to find records that are on the form? If so, this would be one
(untested) way to proceed:

'------ start of air code ------
Private Sub cboGoto_NotInList(NewData As String, Response As Integer)

Dim strMsg As String

strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this record?"
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
' Save the current record, if it has been modified.
If Me.Dirty Then Me.Dirty = False

' Add new record using the form's RecordsetClone,
' so that it will show up on the form, as well.
With Me.RecordsetClone
.AddNew
!SID = NewData
.Update
End With

' Tell Access we added this record, so that it
' will refresh the combo box.
Response = acDataErrAdded

End If

End Sub
'------ end of air code -----


Note: your "rs.Close" statement should have been inside the "Else" block
that opens the recordset. Otherwise, closing a never-opened recordset
object should cause an error. But I don't see an On Error statement in that
procedure, so I'm not sure why you aren't getting an error whenever the user
answers "No" to the prompt.
 
S

Song

Is this combo box's rowsource drawing from the same table as the form's
recordsource -- presumably the table "contacts" -- so that the combo box is
used to find records that are on the form?  If so, this would be one
(untested) way to proceed:

'------ start of air code ------
Private Sub cboGoto_NotInList(NewData As String, Response As Integer)

    Dim strMsg As String

    strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
    strMsg = strMsg & "Do you want to add this record?"
    strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No tore-type
it."

    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") _
            = vbNo _
    Then
        Response = acDataErrContinue
    Else
        ' Save the current record, if it has been modified.
        If Me.Dirty Then Me.Dirty = False

        ' Add new record using the form's RecordsetClone,
        ' so that it will show up on the form, as well.
        With Me.RecordsetClone
            .AddNew
            !SID = NewData
            .Update
        End With

        ' Tell Access we added this record, so that it
        ' will refresh the combo box.
        Response = acDataErrAdded

    End If

End Sub
'------ end of air code -----

Note:  your "rs.Close" statement should have been inside the "Else" block
that opens the recordset.  Otherwise, closing a never-opened recordset
object should cause an error.  But I don't see an On Error statement inthat
procedure, so I'm not sure why you aren't getting an error whenever the user
answers "No" to the prompt.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

Hi, Dirk:

I use your code and when I click Yes to confirm adding new record, it
gives me error saying 'item is not in the list.." with OK buton only.
When I go to the table to look, the record IS added. What did I do
wrong? Thanks.
 
D

Dirk Goldgar

Song said:
I use your code and when I click Yes to confirm adding new record, it
gives me error saying 'item is not in the list.." with OK buton only.
When I go to the table to look, the record IS added. What did I do wrong?
Thanks.
Click to expand...

That's odd. Are you sure you used the code I posted, and didn't change
anything relevant? That code works for me, in my test. Would you mind
posting the code you now have in the NotInList event procedure?
 
S

Song

That's odd.  Are you sure you used the code I posted, and didn't change
anything relevant?  That code works for me, in my test.  Would you mind
posting the code you now have in the NotInList event procedure?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...

Here is the complete code:

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

strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this record?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add new record
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
' Save the current record, if it has been modified.
If Me.Dirty Then Me.Dirty = False

' Add new record using the form's RecordsetClone,
' so that it will show up on the form, as well.
With Me.RecordsetClone
.AddNew
!SID = NewData
.Update
End With

' Tell Access we added this record, so that it
' will refresh the combo box.
Response = acDataErrAdded
End If
Set rs = Nothing
Set db = Nothing

End Sub
 
D

Dirk Goldgar

Song said:
Here is the complete code:

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

strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this record?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add new record
or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo
Then
Response = acDataErrContinue
Else
' Save the current record, if it has been modified.
If Me.Dirty Then Me.Dirty = False

' Add new record using the form's RecordsetClone,
' so that it will show up on the form, as well.
With Me.RecordsetClone
.AddNew
!SID = NewData
.Update
End With

' Tell Access we added this record, so that it
' will refresh the combo box.
Response = acDataErrAdded
End If
Set rs = Nothing
Set db = Nothing

End Sub
Click to expand...

Hmm, aside from the fact that you don't need any of the lines involving rs
and db, it looks fine. What version of Access are you using? And what are
the combo box's RowSource query and Bound Column?
 
S

Song

Hmm, aside from the fact that you don't need any of the lines involving rs
and db, it looks fine.  What version of Access are you using?  And what are
the combo box's RowSource query and Bound Column?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)- Hide quoted text -

- Show quoted text -
Click to expand...

I'm using Access 2007 and my rowsource for combo box is:
SELECT Contacts.SID, StudAddress.Last, StudAddress.First FROM Contacts
INNER JOIN StudAddress ON Contacts.SID=StudAddress.SID;

and it bound to column 1

I also did compact and repaire.
 
D

Dirk Goldgar

Song said:
I'm using Access 2007 and my rowsource for combo box is:
SELECT Contacts.SID, StudAddress.Last, StudAddress.First FROM Contacts
INNER JOIN StudAddress ON Contacts.SID=StudAddress.SID;

and it bound to column 1
Click to expand...


I should also have asked, which column is visible when the combo box is not
dropped down? SID, Last, or First? Is the user typing in a name, or the
ID?
 
S

Song

I should also have asked, which column is visible when the combo box is not
dropped down?  SID, Last, or First?  Is the user typing in a name, orthe
ID?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...

When the combo box drops down, user will see SID, Last and First. User
type SID only.
 
D

Dirk Goldgar

Song said:
When the combo box drops down, user will see SID, Last and First. User
type SID only.
Click to expand...

I am perplexed. The code I posted (adapted for my test form and table)
works fine for me in Access 2007 and 2003.

If you'd like to send me a cut-down copy of your database, containing only
the elements necessary to demonstrate the problem, compacted and then zipped
to less than 1MB in size (preferably much smaller) -- I'll have a look at
it, time permitting. You can send it to the address derived by removing NO
SPAM and ".invalid" from the reply address of this message. If that address
isn't visible to you, you can get my address from my web site, which is
listed in my sig. Do *not* post my real address in the newsgroup -- I don't
want to be buried in spam and viruses.
 
D

Dirk Goldgar

Dirk Goldgar said:
I am perplexed. The code I posted (adapted for my test form and table)
works fine for me in Access 2007 and 2003.

If you'd like to send me a cut-down copy of your database, containing only
the elements necessary to demonstrate the problem, compacted and then
zipped to less than 1MB in size (preferably much smaller) -- I'll have a
look at it, time permitting. You can send it to the address derived by
removing NO SPAM and ".invalid" from the reply address of this message.
If that address isn't visible to you, you can get my address from my web
site, which is listed in my sig. Do *not* post my real address in the
newsgroup -- I don't want to be buried in spam and viruses.
Click to expand...


I've now looked at the database you sent me, and I see what it is -- I
should have noticed this before. Your combo box's RowSource is a query with
an INNER JOIN between the Contacts table and the StidAddress table.
Therefore, no record will be shown in the combo box that isn't represented
by a record in both tables. The code in the NotInList event, having only
the SID to add, adds it only to the Contacts table. Therefore, even though
the record exists in the Contacts table, it still isn't in the combo box's
list.

Your form uses a recordsource query that does a LEFT JOIN between Contacts
and StudAddress; therefore, contact records will be shown on the form even
if they have no match in StudAddress.

The solution is to change the combo box so that it, too, uses a LEFT JOIN in
its rowsource query. When I make that change, the combo box works as
intended.
 
R

Risse

Song said:
The following NotInList will add new record but it is not shown on
current form until I close and reopen. When I add new data, I want the
form to show the newly added record so I can enter other information.

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

strMsg = "'" & NewData & "' is new to lending. " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add this record?"
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("contacts", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SID = 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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo NotInList 2
Not in List Error 4
Not in list 8
Douglas Steele / Dev Ashish combobox NotInList code 1
"Not in list" event code 2
"Not in list" event code 2
ComboBox issue 4
NotInList Event does not return to original record 7

Top