RUNTIME ERROR saving a recordset

[Des]

Basically, I written the code below to check that particular controls on a
form have entries in them. After checking, i want to save the recordset but
got this error at run time: "Run-time error '-2147217888 (80040e20)': Consumer's
event handler called a non-reentrant method in the provider."
Do anyone know why it happens.

I've set up the database connection in Sub Main() and that works. Is it because
some of the fields are empty when i try to save a new record to the set.
I'm really stuck?!? All the code for the form is below, rsCountry and rsState
are refering to lookup tables in access97, dcomCountry and dcomState are
both datacombo controls and I set the data field and list field values in
the properties for each control. I hope that enough information for someone
to be able to help, thanks in advance.

Des (code below)


Option Explicit

Dim rsState As Recordset
Dim rsCountry As Recordset
Dim rsCustomer As Recordset

Private Sub cmdCancel_Click()
txtFirstName.Text = ""
txtInitial.Text = ""
txtSurname.Text = ""
txtCoName.Text = ""
txtAddressOne.Text = ""
txtAddressTwo.Text = ""
txtTC.Text = ""
txtPCode.Text = ""
txtZCode.Text = ""
txtTel.Text = ""
txtEmail.Text = ""
dcomState.Text = ""
dcomCountry.Text = ""
cmdSubmit.Enabled = False
rsCustomer.CancelUpdate
Unload Me
frmCustomerMenu.Show
End Sub

Private Sub cmdCustMenu_Click()

Unload Me
cmdSubmit.Enabled = False
frmCustomer.Show

End Sub

Private Sub cmdMain_Click()

Unload Me
cmdSubmit.Enabled = False
frmMain.Show

End Sub


Private Sub cmdSubmit_Click()

Dim OK As Boolean

OK = True
If (dcomCountry.Text <> "") And (dcomCountry.Text = "United States")
Then
If txtTitle.Text = "" Then
MsgBox "Please enter Title", , "Wrecking Ball Press"
OK = False
Else
If txtFirstName.Text = "" Then
MsgBox "Please enter First name", , "Wrecking Ball Press"
OK = False
Else
If txtSurname.Text = "" Then
MsgBox "Please enter Surname", , "Wrecking Ball Press"
OK = False
Else
If txtAddressOne.Text = "" Then
MsgBox "Please complete Address", , "Wrecking Ball Press"
OK = False
Else
If txtTC.Text = "" Then
MsgBox "Please enter town/city", , "Wrecking Ball Press"
OK = False
Else
If txtZCode.Text = "" Then
MsgBox "Please enter Zip Code", , "Wrecking Ball Press"
OK = False
Else
If dcomState.Text = "" Then
MsgBox "Please select a State", , "Wrecking Ball Press"
OK = False
End If
End If
End If
End If
End If
End If
End If
Else
If (dcomCountry.Text <> "") And (dcomCountry.Text = "United Kingdom")
Then
If txtTitle.Text = "" Then
MsgBox "Please enter Title", , "Wrecking Ball Press"
OK = False
Else
If txtFirstName.Text = "" Then
MsgBox "Please enter First name", , "Wrecking Ball Press"
OK = False
Else
If txtSurname.Text = "" Then
MsgBox "Please enter Surname", , "Wrecking Ball Press"
OK = False
Else
If txtAddressOne.Text = "" Then
MsgBox "Please complete Address", , "Wrecking Ball Press"
OK = False
Else
If txtTC.Text = "" Then
MsgBox "Please enter town/city", , "Wrecking Ball Press"
OK = False
Else
If txtPCode.Text = "" Then
MsgBox "Please enter Post Code", , "Wrecking Ball Press"
OK = False
End If
End If
End If
End If
End If
End If
End If
End If
If dcomCountry = "" Then
OK = False
End If
If OK = True Then
rsCustomer.Update
End If

End Sub

Private Sub dcomCountry_Change()

cmdSubmit.Enabled = True
If dcomCountry.Text = "United Kingdom" Then
dcomState.Text = ""
End If

End Sub

Private Sub dcomCountry_Click(Area As Integer)

If dcomCountry = "United States" Then
lblState.Visible = True
dcomState.Visible = True
lblPCode.Visible = False
txtPCode.Visible = False
lblZCode.Visible = True
txtZCode.Visible = True
Else
If dcomCountry = "United Kingdom" Then
lblState.Visible = False
dcomState.Visible = False
lblPCode.Visible = True
txtPCode.Visible = True
lblZCode.Visible = False
txtZCode.Visible = False
End If
End If


End Sub

Private Sub Form_Load()
Set rsState = New Recordset
Set rsCountry = New Recordset
Set rsCustomer = New Recordset

rsCountry.Open "SELECT COUNTRY.Country FROM COUNTRY;", wbpdb, adOpenStatic,
adLockOptimistic
rsState.Open "SELECT STATE.State FROM STATE;", wbpdb, adOpenStatic, adLockOptimistic
rsCustomer.Open "CUSTOMER", wbpdb, adOpenStatic, adLockOptimistic

Set dcomCountry.DataSource = rsCustomer
Set dcomCountry.RowSource = rsCountry
Set dcomState.DataSource = rsCustomer
Set dcomState.RowSource = rsState
Set txtTitle.DataSource = rsCustomer
Set txtFirstName.DataSource = rsCustomer
Set txtInitial.DataSource = rsCustomer
Set txtSurname.DataSource = rsCustomer
Set txtCoName.DataSource = rsCustomer
Set txtAddressOne.DataSource = rsCustomer
Set txtAddressTwo.DataSource = rsCustomer
Set txtTC.DataSource = rsCustomer
Set txtPCode.DataSource = rsCustomer
Set txtZCode.DataSource = rsCustomer
Set txtTel.DataSource = rsCustomer
Set txtEmail.DataSource = rsCustomer
Set txtID.DataSource = rsCustomer

rsCustomer.AddNew
If dcomCountry.Text = "" Then
cmdSubmit.Enabled = False
End If

End Sub

[Benji Cohen]

Hi Des,
While looking over your code, I got really confused. I was not able to
figure out exactly why you are getting the error, but I noticed your whole
list of:
Else
If Something else
Do something
Else
If Another thing....

with a whole lot of End Ifs at the end. It would probably be a lot more
efficient and readable if you use the If ... ElseIf ... End If structure. I
know that if an empty string somehow gets through, and you try to save it,
you will get an error. I would suggest you re-write the code using ElseIf,
and go through it again to make sure you catch everything.

I would also recommend that things you check for in both countries (i.e.
Title), be put in front, so you do not need the code that checks for both.
This way, you might be less likely to forget a case in one of the countries.
It also makes it easier if you need to add a field for both countries, or
need to add a country.

I am sorry that this doesn't answer your question, but I hope it helps!
Good luck,
Benji Cohen

P.S. One more piece of unsolicited advice: If Len(txtTitle.Text) = 0 Then is
slightly faster than if txtTitle.Text = "" Then. Not faster by much, and it
would probably make a difference in your application, but it is nice to know
if you make a lot of comparisons.


"Des" <Desmond.Kavanagh@student.shu.ac.uk> wrote in message
news:3ce1fe48$1@10.1.10.29...
>
> Basically, I written the code below to check that particular controls on a
> form have entries in them. After checking, i want to save the recordset
but
> got this error at run time: "Run-time error '-2147217888 (80040e20)':
Consumer's
> event handler called a non-reentrant method in the provider."
> Do anyone know why it happens.
>
> I've set up the database connection in Sub Main() and that works. Is it
because
> some of the fields are empty when i try to save a new record to the set.
> I'm really stuck?!? All the code for the form is below, rsCountry and
rsState
> are refering to lookup tables in access97, dcomCountry and dcomState are
> both datacombo controls and I set the data field and list field values in
> the properties for each control. I hope that enough information for
someone
> to be able to help, thanks in advance.
>
> Des (code below)
>
>
> Option Explicit
>
> Dim rsState As Recordset
> Dim rsCountry As Recordset
> Dim rsCustomer As Recordset
>
> Private Sub cmdCancel_Click()
> txtFirstName.Text = ""
> txtInitial.Text = ""
> txtSurname.Text = ""
> txtCoName.Text = ""
> txtAddressOne.Text = ""
> txtAddressTwo.Text = ""
> txtTC.Text = ""
> txtPCode.Text = ""
> txtZCode.Text = ""
> txtTel.Text = ""
> txtEmail.Text = ""
> dcomState.Text = ""
> dcomCountry.Text = ""
> cmdSubmit.Enabled = False
> rsCustomer.CancelUpdate
> Unload Me
> frmCustomerMenu.Show
> End Sub
>
> Private Sub cmdCustMenu_Click()
>
> Unload Me
> cmdSubmit.Enabled = False
> frmCustomer.Show
>
> End Sub
>
> Private Sub cmdMain_Click()
>
> Unload Me
> cmdSubmit.Enabled = False
> frmMain.Show
>
> End Sub
>
>
> Private Sub cmdSubmit_Click()
>
> Dim OK As Boolean
>
> OK = True
> If (dcomCountry.Text <> "") And (dcomCountry.Text = "United States")
> Then
> If txtTitle.Text = "" Then
> MsgBox "Please enter Title", , "Wrecking Ball Press"
> OK = False
> Else
> If txtFirstName.Text = "" Then
> MsgBox "Please enter First name", , "Wrecking Ball Press"
> OK = False
> Else
> If txtSurname.Text = "" Then
> MsgBox "Please enter Surname", , "Wrecking Ball Press"
> OK = False
> Else
> If txtAddressOne.Text = "" Then
> MsgBox "Please complete Address", , "Wrecking Ball Press"
> OK = False
> Else
> If txtTC.Text = "" Then
> MsgBox "Please enter town/city", , "Wrecking Ball Press"
> OK = False
> Else
> If txtZCode.Text = "" Then
> MsgBox "Please enter Zip Code", , "Wrecking Ball Press"
> OK = False
> Else
> If dcomState.Text = "" Then
> MsgBox "Please select a State", , "Wrecking Ball Press"
> OK = False
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> Else
> If (dcomCountry.Text <> "") And (dcomCountry.Text = "United
Kingdom")
> Then
> If txtTitle.Text = "" Then
> MsgBox "Please enter Title", , "Wrecking Ball Press"
> OK = False
> Else
> If txtFirstName.Text = "" Then
> MsgBox "Please enter First name", , "Wrecking Ball Press"
> OK = False
> Else
> If txtSurname.Text = "" Then
> MsgBox "Please enter Surname", , "Wrecking Ball Press"
> OK = False
> Else
> If txtAddressOne.Text = "" Then
> MsgBox "Please complete Address", , "Wrecking Ball Press"
> OK = False
> Else
> If txtTC.Text = "" Then
> MsgBox "Please enter town/city", , "Wrecking Ball Press"
> OK = False
> Else
> If txtPCode.Text = "" Then
> MsgBox "Please enter Post Code", , "Wrecking Ball Press"
> OK = False
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> End If
> If dcomCountry = "" Then
> OK = False
> End If
> If OK = True Then
> rsCustomer.Update
> End If
>
> End Sub
>
> Private Sub dcomCountry_Change()
>
> cmdSubmit.Enabled = True
> If dcomCountry.Text = "United Kingdom" Then
> dcomState.Text = ""
> End If
>
> End Sub
>
> Private Sub dcomCountry_Click(Area As Integer)
>
> If dcomCountry = "United States" Then
> lblState.Visible = True
> dcomState.Visible = True
> lblPCode.Visible = False
> txtPCode.Visible = False
> lblZCode.Visible = True
> txtZCode.Visible = True
> Else
> If dcomCountry = "United Kingdom" Then
> lblState.Visible = False
> dcomState.Visible = False
> lblPCode.Visible = True
> txtPCode.Visible = True
> lblZCode.Visible = False
> txtZCode.Visible = False
> End If
> End If
>
>
> End Sub
>
> Private Sub Form_Load()
> Set rsState = New Recordset
> Set rsCountry = New Recordset
> Set rsCustomer = New Recordset
>
> rsCountry.Open "SELECT COUNTRY.Country FROM COUNTRY;", wbpdb,
adOpenStatic,
> adLockOptimistic
> rsState.Open "SELECT STATE.State FROM STATE;", wbpdb, adOpenStatic,
adLockOptimistic
> rsCustomer.Open "CUSTOMER", wbpdb, adOpenStatic, adLockOptimistic
>
> Set dcomCountry.DataSource = rsCustomer
> Set dcomCountry.RowSource = rsCountry
> Set dcomState.DataSource = rsCustomer
> Set dcomState.RowSource = rsState
> Set txtTitle.DataSource = rsCustomer
> Set txtFirstName.DataSource = rsCustomer
> Set txtInitial.DataSource = rsCustomer
> Set txtSurname.DataSource = rsCustomer
> Set txtCoName.DataSource = rsCustomer
> Set txtAddressOne.DataSource = rsCustomer
> Set txtAddressTwo.DataSource = rsCustomer
> Set txtTC.DataSource = rsCustomer
> Set txtPCode.DataSource = rsCustomer
> Set txtZCode.DataSource = rsCustomer
> Set txtTel.DataSource = rsCustomer
> Set txtEmail.DataSource = rsCustomer
> Set txtID.DataSource = rsCustomer
>
> rsCustomer.AddNew
> If dcomCountry.Text = "" Then
> cmdSubmit.Enabled = False
> End If
>
> End Sub
>
>
>

[Sue Harsevoort]

Whether or not you can send blank fields would depend on the underling
table.

Sue

"Des" <Desmond.Kavanagh@student.shu.ac.uk> wrote in message
news:3ce286ed$1@10.1.10.29...
>
> "Benji Cohen" <bmtmc@aol.com> wrote:
> >Hi Des,
> >While looking over your code, I got really confused. I was not able to
> >figure out exactly why you are getting the error, but I noticed your
whole
> >list of:
> > Else
> > If Something else
> > Do something
> > Else
> > If Another thing....
> >
> >with a whole lot of End Ifs at the end. It would probably be a lot more
> >efficient and readable if you use the If ... ElseIf ... End If structure.
> I
> >know that if an empty string somehow gets through, and you try to save
it,
> >you will get an error. I would suggest you re-write the code using
ElseIf,
> >and go through it again to make sure you catch everything.
> >
> >I would also recommend that things you check for in both countries (i.e.
> >Title), be put in front, so you do not need the code that checks for
both.
> >This way, you might be less likely to forget a case in one of the
countries.
> >It also makes it easier if you need to add a field for both countries, or
> >need to add a country.
> >
> >I am sorry that this doesn't answer your question, but I hope it helps!
> >Good luck,
> >Benji Cohen
> >
> >P.S. One more piece of unsolicited advice: If Len(txtTitle.Text) = 0 Then
> is
> >slightly faster than if txtTitle.Text = "" Then. Not faster by much, and
> it
> >would probably make a difference in your application, but it is nice to
> know
> >if you make a lot of comparisons.
> >
> >
> Yes, that helps a bit, my condition statements are a bit dodgy so i'll
change
> them. The main thing is, I think, some of the fields in the recordset can
> be empty. I've got telno ad email etc but the idea is that their optional
> data in the database. Would the problem be that i'm trying to add a new
record
> to the recordset that contains some empty fields. Is it at all possible
> to send a recordset where some of the fields are blank?

[Des]

"Benji Cohen" <bmtmc@aol.com> wrote:
>Hi Des,
>While looking over your code, I got really confused. I was not able to
>figure out exactly why you are getting the error, but I noticed your whole
>list of:
> Else
> If Something else
> Do something
> Else
> If Another thing....
>
>with a whole lot of End Ifs at the end. It would probably be a lot more
>efficient and readable if you use the If ... ElseIf ... End If structure.
I
>know that if an empty string somehow gets through, and you try to save it,
>you will get an error. I would suggest you re-write the code using ElseIf,
>and go through it again to make sure you catch everything.
>
>I would also recommend that things you check for in both countries (i.e.
>Title), be put in front, so you do not need the code that checks for both.
>This way, you might be less likely to forget a case in one of the countries.
>It also makes it easier if you need to add a field for both countries, or
>need to add a country.
>
>I am sorry that this doesn't answer your question, but I hope it helps!
>Good luck,
>Benji Cohen
>
>P.S. One more piece of unsolicited advice: If Len(txtTitle.Text) = 0 Then
is
>slightly faster than if txtTitle.Text = "" Then. Not faster by much, and
it
>would probably make a difference in your application, but it is nice to
know
>if you make a lot of comparisons.
>
>
Yes, that helps a bit, my condition statements are a bit dodgy so i'll change
them. The main thing is, I think, some of the fields in the recordset can
be empty. I've got telno ad email etc but the idea is that their optional
data in the database. Would the problem be that i'm trying to add a new record
to the recordset that contains some empty fields. Is it at all possible
to send a recordset where some of the fields are blank?

[Des]

The unlying table is just one table without any foreign keys. Only the customer
ID field is indexed (with a primary key) and none of the other fields in
the table are optional. State and Country refer to lookup table so that you
can select possible values from a combo box.What other factor involving the
table would have a bearing upon whether blank field can be used? Is there
a particlarly good way to set up table to allow for this? If you can help
I'll be extremely grateful!!!!!!!!!!!


"Sue Harsevoort" <SusannaH67@hotmail.com> wrote:
>Whether or not you can send blank fields would depend on the underling
>table.
>
>Sue
>
>"Des" <Desmond.Kavanagh@student.shu.ac.uk> wrote in message
>news:3ce286ed$1@10.1.10.29...
>>
>> "Benji Cohen" <bmtmc@aol.com> wrote:
>> >Hi Des,
>> >While looking over your code, I got really confused. I was not able
to
>> >figure out exactly why you are getting the error, but I noticed your
>whole
>> >list of:
>> > Else
>> > If Something else
>> > Do something
>> > Else
>> > If Another thing....
>> >
>> >with a whole lot of End Ifs at the end. It would probably be a lot more
>> >efficient and readable if you use the If ... ElseIf ... End If structure.
>> I
>> >know that if an empty string somehow gets through, and you try to save
>it,
>> >you will get an error. I would suggest you re-write the code using
>ElseIf,
>> >and go through it again to make sure you catch everything.
>> >
>> >I would also recommend that things you check for in both countries (i.e.
>> >Title), be put in front, so you do not need the code that checks for
>both.
>> >This way, you might be less likely to forget a case in one of the
>countries.
>> >It also makes it easier if you need to add a field for both countries,
or
>> >need to add a country.
>> >
>> >I am sorry that this doesn't answer your question, but I hope it helps!
>> >Good luck,
>> >Benji Cohen
>> >
>> >P.S. One more piece of unsolicited advice: If Len(txtTitle.Text) = 0
Then
>> is
>> >slightly faster than if txtTitle.Text = "" Then. Not faster by much,
and
>> it
>> >would probably make a difference in your application, but it is nice
to
>> know
>> >if you make a lot of comparisons.
>> >
>> >
>> Yes, that helps a bit, my condition statements are a bit dodgy so i'll
>change
>> them. The main thing is, I think, some of the fields in the recordset
can
>> be empty. I've got telno ad email etc but the idea is that their optional
>> data in the database. Would the problem be that i'm trying to add a new
>record
>> to the recordset that contains some empty fields. Is it at all possible
>> to send a recordset where some of the fields are blank?
>
>

[Sue Harsevoort]

I'm not sure if you ever said what database you are using, it depends on the
database how this is set. I'm not sure the error message you are getting
really has anything to do with blank fields though. You may want to check
out this page in the Microsoft KB
http://support.microsoft.com/default...en-us;Q195638.

Sue

"Des" <Desmond.Kavanagh@student.shu.ac.uk> wrote in message
news:3ce28cfc$1@10.1.10.29...
>
> The unlying table is just one table without any foreign keys. Only the
customer
> ID field is indexed (with a primary key) and none of the other fields in
> the table are optional. State and Country refer to lookup table so that
you
> can select possible values from a combo box.What other factor involving
the
> table would have a bearing upon whether blank field can be used? Is there
> a particlarly good way to set up table to allow for this? If you can help
> I'll be extremely grateful!!!!!!!!!!!
>
>
> "Sue Harsevoort" <SusannaH67@hotmail.com> wrote:
> >Whether or not you can send blank fields would depend on the underling
> >table.
> >
> >Sue
> >
> >"Des" <Desmond.Kavanagh@student.shu.ac.uk> wrote in message
> >news:3ce286ed$1@10.1.10.29...
> >>
> >> "Benji Cohen" <bmtmc@aol.com> wrote:
> >> >Hi Des,
> >> >While looking over your code, I got really confused. I was not able
> to
> >> >figure out exactly why you are getting the error, but I noticed your
> >whole
> >> >list of:
> >> > Else
> >> > If Something else
> >> > Do something
> >> > Else
> >> > If Another thing....
> >> >
> >> >with a whole lot of End Ifs at the end. It would probably be a lot
more
> >> >efficient and readable if you use the If ... ElseIf ... End If
structure.
> >> I
> >> >know that if an empty string somehow gets through, and you try to save
> >it,
> >> >you will get an error. I would suggest you re-write the code using
> >ElseIf,
> >> >and go through it again to make sure you catch everything.
> >> >
> >> >I would also recommend that things you check for in both countries
(i.e.
> >> >Title), be put in front, so you do not need the code that checks for
> >both.
> >> >This way, you might be less likely to forget a case in one of the
> >countries.
> >> >It also makes it easier if you need to add a field for both countries,
> or
> >> >need to add a country.
> >> >
> >> >I am sorry that this doesn't answer your question, but I hope it
helps!
> >> >Good luck,
> >> >Benji Cohen
> >> >
> >> >P.S. One more piece of unsolicited advice: If Len(txtTitle.Text) = 0
> Then
> >> is
> >> >slightly faster than if txtTitle.Text = "" Then. Not faster by much,
> and
> >> it
> >> >would probably make a difference in your application, but it is nice
> to
> >> know
> >> >if you make a lot of comparisons.
> >> >
> >> >
> >> Yes, that helps a bit, my condition statements are a bit dodgy so i'll
> >change
> >> them. The main thing is, I think, some of the fields in the recordset
> can
> >> be empty. I've got telno ad email etc but the idea is that their
optional
> >> data in the database. Would the problem be that i'm trying to add a new
> >record
> >> to the recordset that contains some empty fields. Is it at all
possible
> >> to send a recordset where some of the fields are blank?
> >
> >
>