Results 1 to 4 of 4

Thread: form questions

  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Red face Unanswered: form questions

    guys, i have 2 questions..first how do u delete all records from a table on the click of a button(on a form) and i have another form where when a user searches for an item using itemnumber, i would like a msgbox to appear if the itemnumber is not in the table..last question, if on a form i want to enter a new product how can i display a message box if the the product is already in the table its been entered into.. any help will be so appreciated..

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    OK.....here you go but before you throw in this code, I must WARN YOU, the function below will WIPE OUT your specified table and there is no getting it back. So with that in mind...If you use this function I WILL NOT BE HELD LIABLE IN ANY WAY SHAPE OR FORM. Use at your own risk!

    This function will DELETE ALL records within the table you specify to the procedure. There is a "Are You Sure" prompt which you can either modify or remove. I would'nt remove it. It would be best if you Back Up your table first before allowing someone to delete all the record in it (Backup is not included here).

    Code:
    Public Sub DeleteAllRecords(Tbl As String)
        Dim x As Integer, Msg As String
        Dim dbs As Database
        Dim rst As Recordset
        On Error Resume Next
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(Tbl)
        rst.MoveLast
        If Err Then x = 0: GoTo NoRecds
        Msg = "WARNING:@@" & "You are about to Delete ALL the " & CStr(rst.RecordCount) & _
        " records contained within the table '" & Tbl & "'. There is no recovery from " & _
        "this deletion." & vbNewLine & vbNewLine & "Are you sure you want to do this?"
        x = MsgBox(Msg, vbCritical + vbYesNo + vbDefaultButton2, "Delete ALL Records?")
        If x = vbNo Then x = 0: GoTo NoRecds
        rst.MoveFirst
        Do Until rst.EOF
          rst.Delete
          rst.MoveNext
          x = 1
        Loop
    NoRecds:
        rst.Close
        Set dbs = Nothing
        If x > 0 Then
          Msg = "Deletion Complete.@@" & "All Records In Table '" & Tbl & "' Have Been Deleted."
        Else
          Msg = "Deletion Canceled.@@" & "There Are No Records To Delete."
        End If
        MsgBox Msg
    End Sub
    Place the code above into a database module or into the Declarations section of your form. Place the code below into the 'OnClick' event of a button control:

    Code:
    DeleteAllRecords ("myTableName")
    Remember...it would be a real good idea to compact your database once the deletion is complete so that your Autonumber indexing restarts from 0
    ----------------------------------------------------------

    For your second question. Use the DLookup function. Place the code into the 'LostFocus' event of whichever text box the user enters the item number into:

    Code:
    If IsNull(DLookup("[ItemNumber]", "myTableName", "[ItemNumber] " & _
    "= '" & Me.TextBoxWithNumberInIt & "'")) then
        Msgbox "Hey...That Item Does Not Exist. Try again."
        Me.Me.TextBoxWithNumberInIt .SetFocus
    End If
    -----------------------------------------------------------
    For you last question...go back into your table (design view) and click onto the field that actualy holds the name of your Products. In the General tab at the bottom you will see a property named 'Index'. Click on it and change the Indexing to:

    Yes (No Duplicates)

    Now save your table. When someone tries to enter the same product name MS Access will inform the user that it already exists in the table.

    or something like that

    Hope this helps

  3. #3
    Join Date
    Dec 2003
    Posts
    4
    CyberLynx, actually the table i want to delete records from is like a dumptable where i temporarily store information and when i exit a particular form, i want to delete all contents so when i start all over again, new info can be posted.. what i ended up doing was setting a macro and set system warning off so the user would not know.. it worked well..... Thanks so much though for taking the time to write down all that code, i am positive it will come in handy in the future.. Muchos Gracious

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb Re: form questions

    Originally posted by mitsu
    guys, i have 2 questions..first how do u delete all records from a table on the click of a button(on a form) and i have another form where when a user searches for an item using itemnumber, i would like a msgbox to appear if the itemnumber is not in the table..last question, if on a form i want to enter a new product how can i display a message box if the the product is already in the table its been entered into.. any help will be so appreciated..
    You can delete the contents of a table using the SQL like that

    ff = "DELETE * FROM yourTable WHERE 1=1;"
       DoCmd.SetWarnings False
         DoCmd.RunSQL (ff)
       DoCmd.SetWarnings True

Posting Permissions

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