NotInList trouble displaying new record

[brownti via AccessMonster.com]

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.

 

[Klatuu]

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

 

[brownti via AccessMonster.com]

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.

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.

 

[Klatuu]

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

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.

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.

 

[brownti via AccessMonster.com]

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

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]

 

[Klatuu]

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

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

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

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.

 

[Deron]

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.

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

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

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

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.

 

[Klatuu]

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

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.

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

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

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.

 

[Deron]

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

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

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.

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

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


:

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.

 

[Deron]

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

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

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.

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

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


:

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.

 

[Klatuu]

2000 doesn't matter. Check your VBA references to be sure you have a
reference to a DAO library. Findfirst doesn't work with ADO. If I remember
correctly, 2000 defaults to ADO.
--
Dave Hargis, Microsoft Access MVP

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

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

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.


:

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

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


:

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.

 

[jacobk]

Hello Dave,

I used your code. It works perfect, but I've a question on it too.

I have a form with more comboboxes.
When I use your code on lets say the 2e combobox, on the me.requery command
I'll get an error, because the 3e combobox must be filled in too, before I
can save (requery) the record.
Is there a solution for this ?

Thands in advance,

Jacob.

2000 doesn't matter. Check your VBA references to be sure you have a
reference to a DAO library. Findfirst doesn't work with ADO. If I remember
correctly, 2000 defaults to ADO.
--
Dave Hargis, Microsoft Access MVP

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

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


:

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.


:

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

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


:

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.

 

[Klatuu]

In your code for 2e, check to see if 3e has a value or not. Then warn the
user that 3e needs a value. You may also have to put code to save the record
in 3e and check to be sure 2e has a value.
--
Dave Hargis, Microsoft Access MVP

Hello Dave,

I used your code. It works perfect, but I've a question on it too.

I have a form with more comboboxes.
When I use your code on lets say the 2e combobox, on the me.requery command
I'll get an error, because the 3e combobox must be filled in too, before I
can save (requery) the record.
Is there a solution for this ?

Thands in advance,

Jacob.

2000 doesn't matter. Check your VBA references to be sure you have a
reference to a DAO library. Findfirst doesn't work with ADO. If I remember
correctly, 2000 defaults to ADO.
--
Dave Hargis, Microsoft Access MVP

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

:

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


:

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.


:

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

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


:

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.

 

[jacobk]

Hello Dave,

Thanks for your reply. I understand that.
But in your code is a "Me.requery" command.
When I wantto add a value in the 2e, the 3e is still empty.
The Me.requery causes the error in the 2e, because of the 3e.
Is there a solution for this?

Jacob.

In your code for 2e, check to see if 3e has a value or not. Then warn the
user that 3e needs a value. You may also have to put code to save the record
in 3e and check to be sure 2e has a value.
--
Dave Hargis, Microsoft Access MVP

Hello Dave,

I used your code. It works perfect, but I've a question on it too.

I have a form with more comboboxes.
When I use your code on lets say the 2e combobox, on the me.requery command
I'll get an error, because the 3e combobox must be filled in too, before I
can save (requery) the record.
Is there a solution for this ?

Thands in advance,

Jacob.

2000 doesn't matter. Check your VBA references to be sure you have a
reference to a DAO library. Findfirst doesn't work with ADO. If I remember
correctly, 2000 defaults to ADO.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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.


:

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

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


:

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.

 

[Klatuu]

Check for a value in 3e before you requery.

If IsNull(Me.combo3e) Then
MsgBox "A value is Required in 3e"
Else
Me.Requery
End If
--
Dave Hargis, Microsoft Access MVP

Hello Dave,

Thanks for your reply. I understand that.
But in your code is a "Me.requery" command.
When I wantto add a value in the 2e, the 3e is still empty.
The Me.requery causes the error in the 2e, because of the 3e.
Is there a solution for this?

Jacob.

In your code for 2e, check to see if 3e has a value or not. Then warn the
user that 3e needs a value. You may also have to put code to save the record
in 3e and check to be sure 2e has a value.
--
Dave Hargis, Microsoft Access MVP

Hello Dave,

I used your code. It works perfect, but I've a question on it too.

I have a form with more comboboxes.
When I use your code on lets say the 2e combobox, on the me.requery command
I'll get an error, because the 3e combobox must be filled in too, before I
can save (requery) the record.
Is there a solution for this ?

Thands in advance,

Jacob.

:

2000 doesn't matter. Check your VBA references to be sure you have a
reference to a DAO library. Findfirst doesn't work with ADO. If I remember
correctly, 2000 defaults to ADO.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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.


:

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

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


:

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.