Deleted a Record from a form

[DRathbone]

Banging my head againest the wall here playing about with code - this should be simple!

Private Sub cmddelete_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
messageusr = MsgBox("Delete the selected user '" & cmbdelete & "' ?", vbYesNo + vbExclamation, "Warning you are about to delete the selected user")
    If messageusr = vbYes Then
    
    DoCmd.RunSQL ("DELETE * FROM Users WHERE Username = " & cmbdelete.Value & ";")
    
    'Clear fields on form to indicate write has occurred
        txtusername = ""
        txtpassword = ""
        cmbgroup = ""
          
        MsgBox "The user has been successfully Deleted"
    End 
    End If
 End Sub

When I run the code it prompts me to enter parameter values - which would say to me its having trouble reading the selected value in my combobox 'cmbdelete' though there is a value present

 

[boblarson]

Add Single quotes and get rid of the .Value (you rarely need that as value is the default of the control)

DoCmd.RunSQL ("DELETE * FROM Users WHERE Username = '" & cmbdelete & "';"

Oh, and you can get rid of the
Dim db As DAO.Database
Dim rs As DAO.Recordset
as it is extraneous and not being used.

 

[DRathbone]

Thanks Bob!

 

[DRathbone]

I am oping to implement the same function on another form, but the DELETE statement will have to match values from a main form and a sub form.

Is it possible to include AND within an SQL statement? I assume to target a subfrom rater than the main form I would write 'formname!control' ??

 

[boblarson]

two things:

1. Yes, you can include ANDs and ORs within a SQL Statement. I usually like to create my SQL statement using the QBE grid and then go to the SQL view to see the actual SQL and then tweak it for my code.

2. To reference controls on a subform:
Remember to refer to the subform CONTAINER name on the main form. If you placed it via the wizard, it will be the same name as your subform, but if you do it manually it will not be the same (it will be something like ActiveXCtl3) and you must rename the control, or refer to it's actual name in the code.

And to refer to a control on a subform use:

Forms!YourMainFormNameHere.YourSubformCONTAINERnam eHere.Form.YourControlNameHere

 

[DRathbone]

Thanks Bob - you continue to uphold you Access genius crown

 

[DRathbone]

Sorry, I'm not having much luck....

Heres my statement:

DoCmd.RunSQL ("DELETE * FROM Asset_General AND Workstation_Server_Laptop WHERE AssetID = '" & txtAssetID.Value & "';")

"Syntax error in FROM clause"

in debug it it returning the correct value so I guess the sql syntax with the AND statement is incorrect?

 

[boblarson]

If you have referential integrity and cascade deletes set within your table relationships you should be able to delete in the top level table and have it automatically delete from the other(s).

However, if you don't, I believe you will need two SQL statements to do two this job. However, some of these types of statements are beyond what I use, so one of my cohorts on the forum may be able to tell us that is not true. It may be that you need
Delete FROM Asset_General, Workstation_Server_Laptop WHERE ...

I thought you were talking about the WHERE clause having an AND.

 

[DRathbone]

I do infact have referential integrity.

I do use....
DoCmd.RunSQL ("DELETE * FROM Asset_General WHERE AssetID = '" & txtAssetID.Value & "';")

it returns "Data Type mismathc in criteria expression"

AssetID is a AutoNumber should I change to txtAssetID.text?? / .number

 

[boblarson]

No, just get rid of the single quotes:

DoCmd.RunSQL ("DELETE * FROM Asset_General WHERE AssetID = " & txtAssetID.Value & ";")

Also, as pointed out by RuralGuy in several posts, you can use

CurrentDb.Execute ("DELETE * FROM Asset_General WHERE AssetID = " & txtAssetID.Value & ";")

and you won't get the warnings.

 

[DRathbone]

Super! Many thanks AGAIN LOL