NotInList trouble displaying new record

i am using the following code to create a new entry in tblRoomName when an
entry in my combobox is different than the listed entries:

Private Sub Combo22_NotInList(NewData As String, Response As Integer)
Set dbs = CurrentDb
strAuth = "'" & NewData & "' is not in the list. "
strAuth = strAuth & "Would you like to add it?"
If MsgBox(strAuth, vbYesNo + vbQuestion, "New Group Diagnosis") = vbNo Then
Response = acDataErrDisplay
Else
Set rst = dbs.OpenRecordset("tblRoomName")
rst.AddNew
rst![RoomType] = NewData
rst.Update
Response = acDataErrAdded
rst.Close
dbs.Close
End If
End Sub

It works as far as creating the new entry in the table, however record one is
selected and i cant navigate to the newley created record. For example if i
have 3 records, (Living Room, Dining Room, Bathroom) and i type Bedroom in
the combobox it tells me that that is not in the list and if i would like to
select it. I say yes and it puts that value in my combobox but shows me
record 1 of 3. if close the form and reopen it and select bedroom it shows
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

You have to requery the form after updating the table. The record is in the
table, but not in your record source.

After this line:
rst.Update

Add:

Me.Requery

--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

i am using the following code to create a new entry in tblRoomName when an
entry in my combobox is different than the listed entries:

Private Sub Combo22_NotInList(NewData As String, Response As Integer)
Set dbs = CurrentDb
strAuth = "'" & NewData & "' is not in the list. "
strAuth = strAuth & "Would you like to add it?"
If MsgBox(strAuth, vbYesNo + vbQuestion, "New Group Diagnosis") = vbNo Then
Response = acDataErrDisplay
Else
Set rst = dbs.OpenRecordset("tblRoomName")
rst.AddNew
rst![RoomType] = NewData
rst.Update
Response = acDataErrAdded
rst.Close
dbs.Close
End If
End Sub

It works as far as creating the new entry in the table, however record one is
selected and i cant navigate to the newley created record. For example if i
have 3 records, (Living Room, Dining Room, Bathroom) and i type Bedroom in
the combobox it tells me that that is not in the list and if i would like to
select it. I say yes and it puts that value in my combobox but shows me
record 1 of 3. if close the form and reopen it and select bedroom it shows
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
table but doesnt let me keep it in the drop down or go to that record.

Klatuu wrote:
You have to requery the form after updating the table. The record is in the
table, but not in your record source.

After this line:
rst.Update

Add:

Me.Requery

i am using the following code to create a new entry in tblRoomName when an
entry in my combobox is different than the listed entries:
[quoted text clipped - 24 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via http://www.accessmonster.com

There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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


--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
table but doesnt let me keep it in the drop down or go to that record.

Klatuu wrote:
You have to requery the form after updating the table. The record is in the
table, but not in your record source.

After this line:
rst.Update

Add:

Me.Requery

i am using the following code to create a new entry in tblRoomName when an
entry in my combobox is different than the listed entries:
[quoted text clipped - 24 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via http://www.accessmonster.com

I got your code to work. i dont know what the problem was with the other one.
THanks a lot.

Klatuu wrote:
There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
[quoted text clipped - 15 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

The problem with the other code is that I didn't write it g

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

I got your code to work. i dont know what the problem was with the other one.
THanks a lot.

Klatuu wrote:
There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
[quoted text clipped - 15 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

Hello
I tried to use this but its a bit complex for me.
The table i want to update is called CLIST and has just one field called CNAME
the Combo box name is BCNAME
Can you help rewrite the code using these names i am confused by the use
cboactivity and cisattribute in the code you have please assist.


"Klatuu" wrote:

The problem with the other code is that I didn't write it g

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

I got your code to work. i dont know what the problem was with the other one.
THanks a lot.

Klatuu wrote:
There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
[quoted text clipped - 15 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

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

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

End Sub

--
Dave Hargis, Microsoft Access MVP


"Deron" wrote:

Hello
I tried to use this but its a bit complex for me.
The table i want to update is called CLIST and has just one field called CNAME
the Combo box name is BCNAME
Can you help rewrite the code using these names i am confused by the use
cboactivity and cisattribute in the code you have please assist.


"Klatuu" wrote:

The problem with the other code is that I didn't write it g

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

I got your code to work. i dont know what the problem was with the other one.
THanks a lot.

Klatuu wrote:
There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
[quoted text clipped - 15 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

Hi
I used it but it highlights .FindFirst and says compile error: Method or
data member not found. what can i do now?

"Klatuu" wrote:

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

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

End Sub

--
Dave Hargis, Microsoft Access MVP


"Deron" wrote:

Hello
I tried to use this but its a bit complex for me.
The table i want to update is called CLIST and has just one field called CNAME
the Combo box name is BCNAME
Can you help rewrite the code using these names i am confused by the use
cboactivity and cisattribute in the code you have please assist.


"Klatuu" wrote:

The problem with the other code is that I didn't write it g

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

I got your code to work. i dont know what the problem was with the other one.
THanks a lot.

Klatuu wrote:
There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
[quoted text clipped - 15 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1

and it would not recognise the code until i remove the cbo before the BCNAME
i am using Ms 2000 does thsi matter?

"Klatuu" wrote:

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

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

End Sub

--
Dave Hargis, Microsoft Access MVP


"Deron" wrote:

Hello
I tried to use this but its a bit complex for me.
The table i want to update is called CLIST and has just one field called CNAME
the Combo box name is BCNAME
Can you help rewrite the code using these names i am confused by the use
cboactivity and cisattribute in the code you have please assist.


"Klatuu" wrote:

The problem with the other code is that I didn't write it g

Glad I could help.
--
Dave Hargis, Microsoft Access MVP


"brownti via AccessMonster.com" wrote:

I got your code to work. i dont know what the problem was with the other one.
THanks a lot.

Klatuu wrote:
There is nothing in your code that would cause it to execute twice. Adding
the Requery will not cause that. Do you have the Limit to List property of
the combo set to Yes?

Here is an example from one of my applications. It not only adds the new
record, but makes it the current record. Maybe it will help:

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
Me.cboActivity = Me.cboActivity.OldValue
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

That didn't work...now i am getting an 'Error 3022' It asks me if i want to
add it to the list twice and then gives me the error. it will add it to the
[quoted text clipped - 15 lines]
me record 4 of 4 (which would be correct) any ideas what is going on or how
i can fix this? THanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200801/1