Adding to a table (W2000, office xp, vb6)

Topic

New Reply

I am trying to add to a table in my Access2000 mdb. The tblProject has 10 fields. I have a form in VB6 that has all 10 fields as text boxes. The user enters into the text boxes and clicks on save. The save command code is…

Private Sub cmdSave_Click()
On Error GoTo HandleErrors

rsProj.AddNew
‘doesn’t accept NULL FOR EACH field that is blank
rsProj!Active = chkActive.Value
rsProj!JobNo = txtJobNo.Text
rsProj!Date = txtDate.Text
rsProj!Client = txtClient.Text
rsProj!ProjectDirector = txtPD.Text
rsProj!SubjectMatter = txtSubjectMatter.Text
rsProj!ProjectMethodology = txtPM.Text
rsProj!MailoutDate = txtMailoutDate.Text
rsProj.Update

My problem is that if one of the text boxes is left blank it won’t write to the tblProject. It doesn’t like the NULL. Can someone lead me in the right direction? Thanks so much.

Reply | Quote

Replies

Are the fields set up to allow Nulls? If they’re required fields or are part of the primary key for that table, they won’t let you enter a Null. If they aren’t required but won’t let you enter a Null because they are text fields with AllowZeroLength set to No, you could try testing each control to see if it has a value and simply skip those with none when you write to the table.

Is this a bound form with unbound textboxes, and is the connection DAO or ADO? It might be easier to simply use a bound form in the first place.

Reply | Quote

Maybe the database does not like Nulls for a reason: like they make other parts of Access more difficult to use. You could add some validation code to peruse the textboxes prior to saving to see if you want to require more or different information (such as a properly formatted date or a nonblank JobNo). Or you could simply put a space into any blank text boxes to get around the Null problem.

Reply | Quote

Thanks for the messages. I am using an ADO connection to Access 2000. The mdb allows zero length and required is set to “no”. I want the user to be able to leave blanks in the form because they will not have all the information at one time. They will have to update the record as they go along and as more info comes in.

I’ll try adding spacebands to each blank field for the time being unless you can think of any other way to do this. Thanks for the advise. I really appreciate it.

Reply | Quote

You didn’t explain what kind of an error message you’re getting in any detail, and you also didn’t explain whether it was a bound or unbound form. Adding null strings may work but it’s usually not a good idea, since you can wind up with “records” that have nothing but empty strings in them–not very useful information.

Are you trapping the ADO connection object’s errors collection to see if the code is generating a silent error? We didn’t have to deal with that in DAO, but ADO frequently just doesn’t work and unless you know how to trap those errors, you will be none the wiser.

Reply | Quote

Thanks. I did finally figure it out. My form is bound and i’ve taken your advise about NULLs.

I want one of my field to be “calculated” from 2 other fields. I couldn’t figure out how to add a formula in Access so i have a cmdCalculate on my form that takes the two text box values and puts the result in the 3rd field. All 3 fields exist in my access database.

When i click on my save command.
private sub cmdSave_Click()
adoProject.Recordset.Update
…

it doesn’t add my calculated field to the database. Is my reasoning flawed? am i doing this correctly? thank you for the help.

Reply | Quote

You don’t normally store a calculated value in a table because 1) you can always recalculate it on the fly and 2) if one of the values it’s based on changes, the value you stored is wrong. You can’t create a field in an Access table that’s based on another field value, if that’s what you meant. You always have to do that kind of calculation in a form or using a query.

Where’s the calculation? You’re button click appears to be issuing an update on the ADO recordset. However, I don’t see anything explains how your textboxes are being manipulated to generate a result or how you’re putting it into the 3rd control. And is that 3rd control bound as well?

Reply | Quote