Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Join Date
    Nov 2011
    Posts
    18

    Unanswered: BUTTON keeps adding values to TABLE

    hello i have a button on my form with some textboxes bounded to a table. when i press a button i have some validations running, however even if the validations run through, it still adds the values that the user inputs in the textboxes into the table.

    please help, thanks!

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    Can you be more specific? What is the code behind the button, what is the desired result, formatting of the text boxes, etc.

  3. #3
    Join Date
    Nov 2011
    Posts
    18
    this is the code of the button, a picture is below. format of the text in the box is string. basically it doesnt matter what msgbox validation comes up, it still adds to the table its bounded to. i want to make it NOT add to the table if it the validation catches an error.

    Private Sub btngo_Click()
    Dim x As String
    x = DCount("*", "tblCustomerMaster", "CustomerID = '" & Me.frmtest.Form.Channel_ID & "'")

    If Me.LAN_ID <> "tshc" And Me.LAN_ID <> "rgv1" And Me.LAN_ID <> ("hxcm") And Me.LAN_ID <> ("j0hu") And Me.LAN_ID <> ("R2Wq") And Me.LAN_ID <> ("NSC4") And Me.LAN_ID <> ("ECD1") And Me.LAN_ID <> ("S2Bf") And Me.LAN_ID <> ("KKW2") Then
    MsgBox ("ERROR: You Don't Have Authorization!")
    ElseIf Me.frmtest.Form.Due_Date <= Date - 1 Then
    MsgBox ("ERROR: This is not a possible Due Date!")
    ElseIf x = 0 Then
    MsgBox ("ERROR: Invalid Channel ID")
    ElseIf Me.frmtest.Form.Due_Date <> "" And Me.frmtest.Form.Channel_ID <> "" Then
    DoCmd.GoToRecord , , acNewRec
    DoCmd****nMacro ("mcrHHFRequest_Tais")
    MsgBox ("You've Added A Record(s)!")
    DoCmd****nMacro ("mcrCloseEasyTrakEnter")
    DoCmd.OpenForm "frmeasytrak", acNormal
    Else

    MsgBox ("ERROR: Missing a Field")


    End If


    End Sub


    picture:
    http://i130.photobucket.com/albums/p...rmeasytrak.jpg

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Is your form set to Data Entry?

    It also looks like you have nothing in the code telling it to stop the sub, or otherwise keep it from continuing to DoCmd.GoToRecord , , acNewRec.

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Also possibly this might work better?

    ElseIf Me.frmtest.Form.Due_Date <> Null And Me.frmtest.Form.Channel_ID <> Null Then

  6. #6
    Join Date
    Nov 2011
    Posts
    18
    ive tried the <> Null and it doesnt seem to make a difference? it still adds to the table. also how would i stop it from continuing the sub? and YES data entry is set to yes

    is there a way to code the procedure to STOP? and not continue with adding the new record?

  7. #7
    Join Date
    Oct 2009
    Posts
    204
    procedure stop = Exit Sub

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Where to start?

    You're declaring x as a String

    Dim x As String


    and then trying to use it as a
    Number in

    x
    = DCount("*", "tblCustomerMaster", "CustomerID = '" & Me.frmtest.Form.Channel_ID & "'")

    Due_Date
    and Channel_ID are almost assuredly going to be <> "", whether the Fields are populated or not.

    Zero-Length Strings
    , while possible in theory in Access, are very seldom encountered.

    • When no data is present in a Field it is Null.
    • If data is entered and then deleted, it is Null.
    • If data is entered and then Backspaced over it is Null.

    The syntax

    <> Null


    doesn't work in Access VBA, the correct syntax would be


    Not IsNull
    (ControlName).

    Using a single If...Else...Then construct to do Validation on three different Fields, in turn, as you're doing here, is almost assuredly going to cause problems, if for nothing other reason than because it makes it almost impossible to follow. You should a separate If...Else...Then for each Field to Validate them.

    Linq ;0)>

    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by taimysho0 View Post
    ive tried the <> Null and it doesnt seem to make a difference? it still adds to the table. also how would i stop it from continuing the sub? and YES data entry is set to yes

    is there a way to code the procedure to STOP? and not continue with adding the new record?
    im suspicious of = null
    dunno If Access / JET is one of the db languages but in most databases you cannot text for Null by = null, use isnull(myvalue) instead
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    I think linq covered all your issues.

    One method I use for testing for null (or empty) strings, is:

    Code:
    If (Len(someTestThing & vbNullString) > 0) Then
        MsgBox "I am a populated String!"
    Else
        MsgBox "I am an empty String!"
    End If
    I just found this to have better results in some circumstances than:

    Code:
    If Not (IsNull(someTestThing)) Then
        MsgBox "I am a populated String!"
    Else
        MsgBox "I am an empty String!"
    End If

    Hope you get it sorted!
    Looking for the perfect beer...

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Er, am I the only one to spot this? If your form is bound to a table, your validation will not work. The data will be written to the table regardless.

    If you want to apply your own validation, use an unbound form. You will need to code a means to find and display data from the source tables, and then write changes back to it.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The belief that unbound forms are necessary in order to do data validation before "committing" data to a Table is simply not true!

    Any "checks" you need to do before saving the data can be done using the Form_BeforeUpdate event of a bound form and Cancel = True to stop the saving of the record until missing/erroneous data is dealt with!

    A big part of the reason to use Access for database development is the speed with which it can be created, using Bound Forms. Several developers I know, experienced in both Visual Basic database development and Access development, estimate that development using Unbound Forms by experienced developers takes two to three times as long as it does when using Access and Bound Forms.

    If you insist on using unbound forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

    • You can create an EXE file which gives total protection to your code/design
    • You can distribute the db to PCs without a copy of Access being on board
    • Your data security is far, far better than anything you can do in Access

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Useful to know - thank you.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  14. #14
    Join Date
    Oct 2009
    Posts
    204
    Linq,

    I've never thought of using Form_BeforeUpdate. I've tried placing a delete in OnClose, to no avail, as whether there are validations or not, if the fields are populated when the form is closed it is added to the table. I've tried creating a dummy table with a delete query and an append query for this reason, but I'll try that next!

  15. #15
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Not to mention VBA is pretty limiting in what you can actually implement.

    For example, I'm trying to hide the vertical scrollbar on a listbox... Can it be done in VBA..? Not looking likely.

    Can VB or C++ achieve this? Of course they can!
    Looking for the perfect beer...

Posting Permissions

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