Results 1 to 8 of 8

Thread: Docmd****nSQL

  1. #1
    Join Date
    Apr 2009
    Posts
    85

    Unanswered: Docmd****nSQL

    I have a table(Equipment) with primary key called DemoID. The table is for our demo wheelchair fleet. Manufacturer Reps will lend us wheel chairs to demo, when with us, their chairs are put in to the table and given a DemoID. When the Rep-Demo's are picked up they are removed from the table. I would like to keep people away from this main table, to this end... I have made a simple form with one unbound textbox called txtDemoID and a command button with this code behind it

    Dim SQL_Text as String

    SQL_Text = "Delete * from Equipment WHERE DemoID= txtDemoID"

    Docmd****nSQL

    The idea is to type a DemoID # in to the textbox then click the command button to remove that particular record from the table Equipment.
    I know this can be done other ways (command button wizard for one) but I am trying to learn vba and sql and how to attach theem to events (just a beginner clearly) I would be glade for any help. Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you are trying to learn VBA I would avoid DoCmd where you can. It is totally specific to Access - DoCmd is really just a way of running\ defining macros, in words.
    Code:
    Dim SQL_Text as String
    
        SQL_Text = "Delete * from Equipment WHERE DemoID=" & Me.txtDemoID
    
    'Always, always view what you are executing while developing. Comment out\ delete once it works.
    Debug.Print SQL_Text
    
        CurrentDB.Execute SQL_Text, dbFailonError
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    85
    pootle flump, Thank you for your reply. I tried your code and I get a run time -error of 3061 to few parameters. expected 1 and this line is high lited in the code
    CurrentDb.Execute SQL_Text, dbFailonError
    not sure what to do

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is why I included this bit:
    Code:
    'Always, always view what you are executing while developing. Comment out\ delete once it works.
    Debug.Print SQL_Text
    Two tips - you will:
    a) Become much more proficient, much more quickly, if you ALWAYS pay attention to the values of any dynamic strings you execute when developing
    b) Make yourself very popular on the forums if you post the results of the Debug statement in your post whenever you are executing dynamic SQL and cannot solve your error
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Posts
    85
    I got it to work but had to change Me.txtDemoID to forms!MyForm!txtDemoID
    is there a rule as to when Me. works and when it dose not.
    I have one last question if your up for it.
    I want to clear txtdemoId after the delete and I can make a seprate button with this code behind it

    Dim intIndex As Integer

    Forms!frmDelete!TxtDemoID = ""
    Forms!frmDelete.Refresh

    is there a way to place it in the code you gave me, I tried but did not work
    Thanks for your help

    also in your part B. of your last post I couldn't tell if you were sarcastic or not
    Last edited by Timothyl; 06-04-09 at 12:52.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Where is your code? Me. is strongly preferable to Forms!, but you can use it in certain circumstances not all.

    I'd really love it if you took the not so subtle hint and posted what the string value was.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2009
    Posts
    85

    Code

    poodle flump, here is what I ended up with, I reverted to runsql as I tried to make scene of what you wrote but failed. The error msg had to be added to handle the canceling of the event. I did not write it but found by googling, then cobbled it together with the sql statment you wrote Previously

    Sub Command0_Click()

    On Error GoTo Err_cmdDelete_Click

    Dim SQL_Text As String

    SQL_Text = "Delete * from Equipment WHERE DemoID = Forms!frmDelete!TxtDemoID"
    DoCmd****nSQL (SQL_Text), acEdit

    Exit_cmdDelete_Click:
    Exit Sub

    cmdDelete_Error:
    Select Case Err.Number
    Case 3059
    MsgBox "You have canceled the delete operation.", vbOKOnly, "Delete Canceled"
    Exit Sub
    Case Else
    MsgBox "Error number: " & Err.Number & " has occurred. " & Err.Description, vbOKOnly, "Error"
    End Select

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click


    End Sub

    works well, it's at the bottom of this I would like to add the reset and refresh code.

    Dim intIndex As Integer
    Me.TxtDemoID = ""
    Me.Refresh

    work behind a button all by itself
    Last edited by Timothyl; 06-04-09 at 16:52.

  8. #8
    Join Date
    Apr 2009
    Posts
    85
    I was putting my code in the wrong place this works

    Sub Command0_Click()

    On Error GoTo Err_cmdDelete_Click

    Dim SQL_Text As String

    SQL_Text = "Delete * from Equipment WHERE DemoID = Forms!frmDelete!TxtDemoID"
    DoCmd****nSQL (SQL_Text), acEdit
    Dim intIndex As Integer
    Me.TxtDemoID = ""
    Me.Refresh
    Exit_cmdDelete_Click:
    Exit Sub

    cmdDelete_Error:
    Select Case Err.Number
    Case 3059
    MsgBox "You have canceled the delete operation.", vbOKOnly, "Delete Canceled"
    Exit Sub
    Case Else
    MsgBox "Error number: " & Err.Number & " has occurred. " & Err.Description, vbOKOnly, "Error"
    End Select

    Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click


    End Sub
    Edit/Delete Message

Posting Permissions

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