NotInList trouble displaying new record

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


"Deron" wrote:

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

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.

"Klatuu" wrote:

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


"Deron" wrote:

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

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


"jacobk" wrote:

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.

"Klatuu" wrote:

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


"Deron" wrote:

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

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.

"Klatuu" wrote:

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


"jacobk" wrote:

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.

"Klatuu" wrote:

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


"Deron" wrote:

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

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


"jacobk" wrote:

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.

"Klatuu" wrote:

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


"jacobk" wrote:

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.

"Klatuu" wrote:

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


"Deron" wrote:

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