Deleting Row in Sub Form

[Hanz]

Hi,


I got 2 forms named TruckMaintenance_Main and TruckMaintenance_sub. On my Sub form i have a delete button to delete a row and this code below works if I click the delete button on TruckMaintenance_sub. But when i put TruckMaintenance_Sub as sub form of TruckMaintenance_main, the delete button won't work. Can anyone help me why this code won't work?

Private Sub btnDelete_Click()
Dim SQL As String

SQL = "DELETE tbl_TruckMaintenance.TruckRego, tbl_TruckMaintenance.TrailerRego, tbl_TruckMaintenance.DriverName" _
& " FROM tbl_TruckMaintenance" _
& " WHERE (((tbl_TruckMaintenance.TruckRego)=[Forms]![Truck Maintenance_sub]![txtTruck]));"

DoCmd.RunSQL SQL
Me.Requery

End Sub

 

[Ranman256]

the path may be wrong:
usu: forms!myMasterForm!subform!form!txtbox

USE THE BUILDER...it always gets it correct.

put it in a query and it runs fine:
docmd.openquery "qdDeleteQry"

 

[Hanz]

But that code works if it is outside the mainform. if i put it in a main form as a sub form, it doesn't work. so i think the path won't be the issue.

 

[June7]

Path is the issue. If code is behind subform don't use full path. Also, concatenate:

& " WHERE TruckRego=" & Me.[txtTruck]

If TruckRego is text type then need apostrophe delimiters:

& " WHERE TruckRego='" & Me.[txtTruck] & "'"

 

[Mark_]

Did you move the delete button to a different form? If so you will need to change the path.
Did you put your form object in another object? If so you will need to change the path.
Did you move the code out of an event within the form object? If so you will need to change the path.

Reason is ACCESS tracks things by referencing the highest level object FIRST, then working down. If you change to using Me.ControlName and do this from within the object you are referencing Me fills in all of the previous parts (form. or report.) for you.

 

[Hanz]

Thanks to all who responded. I got this issue resolved.

 

[Hanz]

I did the same thing to my other form as what was discussed previously but this code won't work on my sub form.

Private Sub btnDelete_Click()
Dim SQL As String

SQL = " DELETE tbl_ListContainer.ID " _
& " FROM tbl_ListContainer " _
& " WHERE ID = me.txtID"

Me.Requery

End Sub

I got that SQL from this action query

DELETE tbl_ListContainer.ID
FROM tbl_ListContainer
WHERE (((tbl_ListContainer.ID)=[Forms]![AddContainerList]![txtID]));

 

[June7]

All that code does is build SQL string. It does not Run the SQL.

And again, must concatenate reference to form control. Review post 4 and follow working example.

 

[Mark_]

Hans,

One of the reasons all of us recommend you build a string for SQL in a variable is so that you can show what will be passed. If you use

msgbox "SQL is " & SQL

to show your SQL string FIRST, you will see that you have a poorly formed SQL string as June pointed out.

In general, when you write your own SQL to do things instead of letting ACCESS do the work for you, you will need to verify what you are doing often. In the future, I would strongly suggest using something (msgbox, textbox on the screen, debug.print, email to yourself) to show what you will be passing so you can make sure it is what you want.

You will also want to write down exactly, step by step, what you are trying to do. That can help when you forget do execute something like the SQL string.

 

[Hanz]

I was so confident that I got Jun7's post previuosly. Actually i tried concatenating and even put quote even though i know that my ID is a number but still doesn't work. Just realize that i asked a very stupid question . I run the SQL and it's working now.

Thanks for your patience.

 

[Mark_]

No stupid question, especially if you learned something from it! I've learned more about what ACCESS does or doesn't do based on what other's called "Stupid questions" than I ever would have from a class!