Help With "NotInList"

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

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

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.

 

[Guest]

It has to be happening somewhere else in your code. I would go into debug
and trace it. The code I posted works perfectly in my application.
I notice in your code you are set the recordset to a specific table rather
than using the RecordsetClone. I'm not sure how that is going to work for
you. Also, there is no Requery in your code. That is necesssary to get the
new record into your recordset.

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

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

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.

 

[Guest]

Thanks Klatuu, I'll keep working on it and post back
--
Jeff C
Live Well .. Be Happy In All You Do

It has to be happening somewhere else in your code. I would go into debug
and trace it. The code I posted works perfectly in my application.
I notice in your code you are set the recordset to a specific table rather
than using the RecordsetClone. I'm not sure how that is going to work for
you. Also, there is no Requery in your code. That is necesssary to get the
new record into your recordset.

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

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


:

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.