RE: Help With "NotInList"

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

Klatuu:

Thanks for your help, I can't get the thing to workyet.

I tried your code also and get the same thing happening. I enter the new
account number, it lets me add it but the rest of the fields fill on the form
with data from a different record. When I check the table the new record has
indeed been added and the other fields are blank.

My original code was:

NOTINLIST Event

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String


Set db = CurrentDb
Set rs = db.OpenRecordset("Patients", dbOpenDynaset)

rs.AddNew
[rs]![MR#] = NewData
rs.Update


Response = acDataErrAdded

Set rs = Nothing
Set db = Nothing

And I also have this:

AfterUpdate Event

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PTID] = " & Str(Nz(Me![Combo65], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

This is a bugger for me.
--
Jeff C
Live Well .. Be Happy In All You Do


"Klatuu" wrote:

We would have to see the code in your Not In List event to know what the
error might be.

Here is an example of one that works. Once you have said you want to add
the record, it make the new record the current record:

Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset

If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Me.txtDescription.SetFocus
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If

End Sub


"Jeff C" wrote:

I have a one to many set up with corresponding form and subform. Focus
begins on a combo box where account number is entered to either add a new
record and corresponding audits or pull up existing record to add another
audit. The account number field in the table is set to indexed no duplicates.

The afterupdate event of the combobox is set to open the corresponding
record of the account number entered.

The "NotInList" fires and accepts the new entry when prompted however
corresponding data from a different record fills in the textboxes rather than
being blank to accept new data.

Where am I going wrong? Thanks.
--
Jeff C
Live Well .. Be Happy In All You Do
.

Relevant Pages

  • Re: Requerying ComboBox in NotInList event
    ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ... Dim lngBlankFound As Long ...
    (comp.databases.ms-access)
  • RE: Help With "NotInList"
    ... "Jeff C" wrote: ... account number, it lets me add it but the rest of the fields fill on the form ... Dim rs As DAO.Recordset ... Response As Integer) ...
    (microsoft.public.access.formscoding)
  • Re: Requerying ComboBox in NotInList event
    ... Any attempt to requery the combobox gives me an error that the field ... Response As Integer) ... Dim rst As Recordset ... Set rst = CurrentDb.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: Dbl-Click to add Choice to Combo Box
    ... > Thanks Steve! ... > Dim strsql As String, ... I would move "Response = acDataErrAdded" to be the next line after ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.forms)
  • disable default message (NotInList)
    ... my problem is the default access message still appear if i click NO ... Response As Integer) ... Dim rst As DAO.Recordset ... Set rst = db.OpenRecordset ...
    (microsoft.public.access.forms)