needing UPDATE assistance

[John]

I have a continous form that displays a record after a partnumber is
selected from a combobox. There are also 3 unbound fields in the
header that update a record in another table. The backend is SQL2005.
Here is the update query that I have.

updSQL = "UPDATE DeviceWarrantyNotes SET MaxWarrantyLength = " &
txtMaxWarrantyLength & ", Notes = '" & txtNotes & "' WHERE PartNumber
= '" & cboFindPart & "'"

Now, if I remove the MaxWarrantyLength and just go with the Notes, the
UPDATE works fine. If I add in the MaxWarrantyLength (which is an
int) I get an error:

No value given for one or more required parameters.

Can someone see what I am doing wrong with the MaxWarrantyLength,
which is what I presume is the culprit in all this.

Thanks.

John

 

[Douglas J. Steele]

What's the actual SQL that's created by that statement?

 

[John]

What's the actual SQL that's created by that statement?

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)










- Show quoted text -

I have done some further checking and it appears that it has to do
with the warranty length fields being empty. The sql table allows
nulls for the warranty, which is an int.

How does one go about resolving the null issue in an update query like
the one I posted? It can not be 0 (zero) as there are zero length
warranties.

Thanks...John

 

[Jeff Boyce]

John

Won't that depend on how YOU want it "resolved"?

What do you want to have happen?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

What's the actual SQL that's created by that statement?

--
Doug Steele, Microsoft Access MVPhttp://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)










- Show quoted text -

I have done some further checking and it appears that it has to do
with the warranty length fields being empty. The sql table allows
nulls for the warranty, which is an int.

How does one go about resolving the null issue in an update query like
the one I posted? It can not be 0 (zero) as there are zero length
warranties.

Thanks...John

 

[John]

John

Won't that depend on how YOU want it "resolved"?

What do you want to have happen?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.





I have done some further checking and it appears that it has to do
with the warranty length fields being empty.  The sql table allows
nulls for the warranty, which is an int.

How does one go about resolving the null issue in an update query like
the one I posted?  It can not be 0 (zero) as there are zero length
warranties.

Thanks...John- Hide quoted text -

- Show quoted text -

I would like to be able to update the record if all or some of the
fields are completed. But the error message continues. So if there
is no warranty info, the error. How would I go about constructing the
UPDATE statement to work around the empty field? Since I do not have
alot of experience I thought I would post for help.
John

 

[Jeff Boyce]

So you're saying that you want to update the table even though you are
missing the MaxWarrantyLength? Update it to what, just the note?

How come the fields are unbound? Wouldn't it make it easier if the fields
were bound to the underlying table?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

John

Won't that depend on how YOU want it "resolved"?

What do you want to have happen?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.





I have done some further checking and it appears that it has to do
with the warranty length fields being empty. The sql table allows
nulls for the warranty, which is an int.

How does one go about resolving the null issue in an update query like
the one I posted? It can not be 0 (zero) as there are zero length
warranties.

Thanks...John- Hide quoted text -

- Show quoted text -

I would like to be able to update the record if all or some of the
fields are completed. But the error message continues. So if there
is no warranty info, the error. How would I go about constructing the
UPDATE statement to work around the empty field? Since I do not have
alot of experience I thought I would post for help.
John

 

[John]

So you're saying that you want to update the table even though you are
missing the MaxWarrantyLength?  Update it to what, just the note?

How come the fields are unbound?  Wouldn't it make it easier if the fields
were bound to the underlying table?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.












I would like to be able to update the record if all or some of the
fields are completed.  But the error message continues.   So if there
is no warranty info, the error.  How would I go about constructing the
UPDATE statement to work around the empty field?  Since I do not have
alot of experience I thought I would post for help.
John- Hide quoted text -

- Show quoted text -

There are 3 fields in the form header section of a continuous form,
ContractLength, MaxLength, Notes. These 3 fields go to a different
table then the information in the continuous detail section. There is
a 4th field which is a combobox is populated from a parts table. The
user selects the part from the cbo and the continuous detail section
is populated (using a sql view) from all built parts associated with
the combobox part. The 3 fields are associated mainly with the
combobox part that is selected. But, there could be addendum
contracts/agreements made for any of the subsequent parts. It is
possible to have no max warranty on a part that is part of an addendum
agreement. There could be notes to enter but the warranty info may
not be known yet. Or notes entered, then the contract/agreement
warranty length becomes known but the max may still under
negotiation. And so on go the scenarios. So updating could occur
with anywhere of 1, 2, or 3 fields populated. If none are populated,
there is nothing to update. There is a possiblity of INSERT for these
3 fields as well should the cbo selection be a new part, thus new
agreement/contract/warranties, etc.

 

[Douglas J. Steele]

If IsNull(Me!txtMaxWarrantyLength) Then
updSQL = "UPDATE DeviceWarrantyNotes " & _
"SET Notes = '" & txtNotes & "' " & _
"WHERE PartNumber = '" & cboFindPart & "'"
Else
updSQL = "UPDATE DeviceWarrantyNotes " & _
"SET MaxWarrantyLength = " & txtMaxWarrantyLength & ", " & _
"Notes = '" & txtNotes & "' " & _
"WHERE PartNumber = '" & cboFindPart & "'"
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

- Show quoted text -

I have done some further checking and it appears that it has to do
with the warranty length fields being empty. The sql table allows
nulls for the warranty, which is an int.

How does one go about resolving the null issue in an update query like
the one I posted? It can not be 0 (zero) as there are zero length
warranties.

Thanks...John

 

[Jeff Boyce]

Sorry, I must be under-caffeinated this morning. I'm having trouble
visualizing the data structure underneath the forms you are describing...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

So you're saying that you want to update the table even though you are
missing the MaxWarrantyLength? Update it to what, just the note?

How come the fields are unbound? Wouldn't it make it easier if the fields
were bound to the underlying table?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.














I would like to be able to update the record if all or some of the
fields are completed. But the error message continues. So if there
is no warranty info, the error. How would I go about constructing the
UPDATE statement to work around the empty field? Since I do not have
alot of experience I thought I would post for help.
John- Hide quoted text -

- Show quoted text -

There are 3 fields in the form header section of a continuous form,
ContractLength, MaxLength, Notes. These 3 fields go to a different
table then the information in the continuous detail section. There is
a 4th field which is a combobox is populated from a parts table. The
user selects the part from the cbo and the continuous detail section
is populated (using a sql view) from all built parts associated with
the combobox part. The 3 fields are associated mainly with the
combobox part that is selected. But, there could be addendum
contracts/agreements made for any of the subsequent parts. It is
possible to have no max warranty on a part that is part of an addendum
agreement. There could be notes to enter but the warranty info may
not be known yet. Or notes entered, then the contract/agreement
warranty length becomes known but the max may still under
negotiation. And so on go the scenarios. So updating could occur
with anywhere of 1, 2, or 3 fields populated. If none are populated,
there is nothing to update. There is a possiblity of INSERT for these
3 fields as well should the cbo selection be a new part, thus new
agreement/contract/warranties, etc.