Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

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

    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.

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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?
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •