RE: Forms are fun (except when they need combo box VBA code I don'

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

It can't find it because the form's recordset has to be required. You put it
in the table, but it is not in the recordset. A form's recordset only
contains the records that were in the table when the form was loaded or last
requried.

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

Dim db As Database
Dim rs As Object
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO TotalAgentListABCD (LastName) VALUES (""" & NewData
& """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded
Me.Requery
rs.FindFirst "[LastName] = '" & Me![AgentName] & "'"

db.Close
Set db = Nothing



"Gnowor" wrote:

Good news bad news situation here.

Got the NotInList event to add a record to the table (I had the table name
wrong), problem is, that after the record's added, I get an error that it
can't find the record. What I think is happening is that it's loading the
list for the combo box when I open the form, but I need it to reload that
list when the new record is added, and then repeat the find I have in my
AfterUpdate Sub to select the new record it just added. Again, I think I'm
starting to get a grasp of what i need to do, the code is just eluding me at
the moment. I'm also probably adding way mroe steps than neccessary.

BTW, Klatuu you are frigging awesome for helping me through this. If you're
ever in the San Fran Bay Area, I owe you big for all the help. AIM=gnowor if
you've got the time.
-------------
Private Sub AgentName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = '" & Me![AgentName] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


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

Dim db As Database
Dim rs As Object
Set db = CurrentDb

'Add the new value to the field
db.Execute "INSERT INTO TotalAgentListABCD (LastName) VALUES (""" & NewData
& """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

rs.FindFirst "[LastName] = '" & Me![AgentName] & "'"

db.Close
Set db = Nothing

End Sub


.

Relevant Pages

  • RE: email macro
    ... Private Sub Worksheet_SelectionChange ... Dim Outlook As Object ... Set Outlook = CreateObject ... Dim Response As Integer ...
    (microsoft.public.excel.programming)
  • RE: Searching/adding new records
    ... Private Sub ComboSearch_NotInList(NewData As String, Response As Integer) ... Dim Db As DAO.Database ...
    (microsoft.public.access.forms)
  • RE: Searching/adding new records
    ... Private Sub ComboSearch_NotInList(NewData As String, Response As Integer) ... Dim Db As DAO.Database ...
    (microsoft.public.access.forms)
  • RE: Enter record when NotInList
    ... Private Sub Department_NotInList(NewData As String, Response As Integer) ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.forms)
  • Re: Error 91 help required
    ... statement as you did for the recordset object. ... Not sure if CurrentDB is a reserved word, ... Dim db As DAO.Database ... > Private Sub cmdSubmit_click ...
    (microsoft.public.access.modulesdaovba)