Insert problem when some fields left blank 2

[Gumbo78]

I am having a problem when entering data and I need to leave some fields blank. This is my SQL statement:

strSQL = "INSERT INTO Order (Cust_ID, Size, Class, Manufacturer, Model, Comments) VALUES ('" & [cbcustid] & "','" & [cbsize] & "','" & [cbclass] & "','" & [cbmanufacturer] & "','" & [cbmodel] & "','" & [cmbcomments] & "')"

DoCmd.RunSQL strSQL

I usually have information in all fields, but when I leave one blank my insert statement will not work and the record does not enter. Is there a way to get around this? I'm not sure of any blank default values that will pass to make the statement work.

 

[bhoran]

If you leave a field out of the update process it will be null.

Check the table and see if there is a validation rule or if that field is part of the primary index. If it is it is probably there for a reason and you should be seriously asking whay you need to update that record without that field being populated (it may affect other things when reporting or calculating values).

If you determine that it is ok to have nothing in that field it might pay to update it with blank characters (" &quot rather than change the validation or primary index.

 

[Gumbo78]

It happens if I leave any of the fields blank. The fields that are left blank are just normal text or number fields in the table. No validation or index issues. Cust_ID is the only numbered / key value but that is allways entered. If you leave out Size, Class, Manufacturer, Model, or Comments it will not insert the record. I'm trying alot of different things with default values but I can figure it out. I thought it may have been a method problem in my syntax that i'm not seeing. I'm going to try to set up an isnull test on the fields and setting them to " " right before I save but its not working when I put blank fields as the default or setting them to " " on form load.

 

[bhoran]

Right so you are submitting this SQL from a form? Is there any validation attached to the submit button on the form?

What is the message you get when it does not commit the record?

What are the primary index keys on your table?

 

[sdk]

Hi,

1- Are the fields in the destination table specified as "Required" or "Allow Zero-length String"?

2- Instead of the isNull test you are thinking of, try using the built-in nz(targetValue, valueIfNull) - a very handy function.

3- When I append from an unbound input form (as it appears you are), I input directly into the table, rather than using an append query. A little more explicit and controllable - but is a personal choice.

eg:
Set rst = db.OpenRecordset("TableName&quot
With rst
.AddNew
!FieldName1 = Me.formField1
!FieldName2 = Me.formField2
...
!FieldNameX = Me.formFieldX
.Update
End With

Cheers

 

[Gumbo78]

I am submitting the SQL from a form. There is no validation attached to the submit button. It does not give a message when it does not work (same as when it does work I have DoCmd.SetWarnings False). There are two tables, Customer and Order. Cust_ID is the primary key for customer, and is the foreign key to Order. I just got back in to work I'm gonna try sdk's method.

 

[Gumbo78]

I'm getting an Run-time error 424: Object required on my Set rs = db.OpenRecordset("Order&quot line. I'm not used to this method, usually use sql, any ideas? My dim statement for rs is: Dim rs As DAO.Recordset Thanks

 

[RoyVidar]

Either:

[tt]dim db as dao.database
set db=currentdb[/tt]

or

[tt]set rs = currentdb.openrecordset("order&quot[/tt]

- the db variable isn't declared/instatiated

Roy-Vidar

 

[Gumbo78]

Thanks everything is working now. I like the direct method for this it makes things a bit more clean it seams. $$ guys

-G78

 

[sdk]

Hi,

sorry for doing a little too much abbreviation, but pleased it (& RoyVidar) was able to help.

Cheers