Using SQL in a form

[paddymx]

I am calling the following in Form B from Form A using :




DelTsYes:
Forms(CalledBy).DoDeletesYes
Exit Sub


where "DoDeletsYes" is a Pro in Form B


###### this code works....ONTbl ...is the name of the Form B's recordsource.


Dim SQL As String
'DoCmd.SetWarnings False


MyRecord = Me.ID

SQL = "DELETE * " & _
"FROM ONTbl " & _
"WHERE (ONTbl.ID)= " & MyRecord


DoCmd.RunSQL SQL
Me.Requery
DoCmd.Close acForm, "MBDispInfoF"


### MBDispInfoF is Form A and is a form to use instead of the normal msgbox so I can customize the messages and display an Animation.


########## end of code that works


The following is ' ed out but is what I've been trying.


After several attempts I'm getting syntax "error in FROM clause". At one point I got as far as an error in the where clause but to be honest, I've forgotten how I got there.


So, is there a way for me to use variables in the SQL so I can put it in a Public Pro rather than having to hard code it in every form?


'MyRecord = Me.ID
'MyTable = Me.RecordSource
'msgbox MyTable
' SQL = "DELETE * " & _
'' "FROM " & MyTable & _
' "WHERE " & "((" & MyTable & ".ID) =)" & MyRecord
' DoCmd.RunSQL SQL




Me.Requery
DoCmd.Close acForm, "MBDispInfoF"
DoCmd.SetWarnings True
End Sub

I've tried to be as complete as possible but at this point my head is feeling foggy.

[pbaldy]

You should be able to. Here's a commonly used method to debug the SQL.

http://www.baldyweb.com/ImmediateWindow.htm

[paddymx]

You should be able to. Here's a commonly used method to debug the SQL.

http://www.baldyweb.com/ImmediateWindow.htm

Thanks, but, what I have found in the past is that syntax works within the record set (rs) code but doesn't work when building the sql in just a form Pro without first building the record set which would first need to be coded in the involved form, I think. For some reason, forms don't like the single line sql syntax.In the past I have used the QBE in the sql mode, copied the sql and pasted that into a form Pro with no success. My code, that currently works, does basically the same thing.

[pbaldy]

Not clear on what you're saying. That technique would work with any string being built, in your case:

Debug.Print SQL

[paddymx]

Not clear on what you're saying. That technique would work with any string being built, in your case:

Debug.Print SQL

Okay, went back and tried your record set idea except I wanted to change "Select *" to "Delete *" and wanted to change the qryname to a variable holding the name of the calling form's recordsource instead of a query. Again I was met with various errors. Probably my fault.

So, here is what I currently have in my form that works like a charm that consists of two Public Sub's and the standard button(DelB) click event. I could just paste them in each involved form and modify each one for the involved form's(recordsource/tablename)

'**********************************
Private Sub DelB_Click()
On Error GoTo ErrMsg
DoCmd.OpenForm "MBDispInfoF", , , "MBIDNO=1", , acDialog
Exit_ErrMsg:
Exit Sub
ErrMsg:
PMMerrormsg (Err)
'MsgBox Err.Description
Resume Exit_ErrMsg
End Sub
'*****************************


Public Sub DoDeletesNo()
On Error GoTo ErrMsg


DoCmd.Close acForm, "MBDispInfoF"


Exit_ErrMsg:
Exit Sub
ErrMsg:
msgbox Err.Description
Resume Exit_ErrMsg
End Sub
'**************************
Public Sub DoDeletesYes()
Dim SQL As String
'DoCmd.SetWarnings False ' make sure to recieve errors while testing
On Error GoTo ErrMsg
SQL = "DELETE * " & _
"FROM ONTbl " & _
"WHERE (ONTbl.ID)= " & Me.ID
DoCmd.RunSQL SQL
GoTo Exit_ErrMsg
Exit_ErrMsg:
Me.Requery
DoCmd.Close acForm, "MBDispInfoF"
Exit Sub
ErrMsg:
msgbox Err.Description
Resume Exit_ErrMsg
End Sub

You will note that I have to hard code the name of the table into the SQL. What I want to do is use a variable " MyTable = me.RecordSource" instead of having to hard code the table name in each form I want to use them (the three subs) in. I want to be able to paste the three subs into each forms events and be done with it. Better yet, maybe a function, consisting of a compost of all three subs, by passing the me.RecordSource and the me.ID from a calling form and processing the Yes/No response from MBDispInfoF.

[pbaldy]

Well, my idea wasn't about a recordset, it was about using Debug.Print to see how the SQL you're trying to build is ending up. So using your original code:

MyRecord = Me.ID
MyTable = Me.RecordSource
msgbox MyTable
SQL = "DELETE * " & _
"FROM " & MyTable & _
"WHERE " & "((" & MyTable & ".ID) =)" & MyRecord
Debug.Print SQL
' DoCmd.RunSQL SQL

Run the code and check the Immediate window to see if the SQL is coming out as you expect. I see issues, but I think if you see the final SQL you'll see them. If you don't spot the problem, post the SQL here.

[paddymx]

Well, my idea wasn't about a recordset, it was about using Debug.Print to see how the SQL you're trying to build is ending up. So using your original code:

MyRecord = Me.ID
MyTable = Me.RecordSource
msgbox MyTable
SQL = "DELETE * " & _
"FROM " & MyTable & _
"WHERE " & "((" & MyTable & ".ID) =)" & MyRecord
Debug.Print SQL
' DoCmd.RunSQL SQL

Run the code and check the Immediate window to see if the SQL is coming out as you expect. I see issues, but I think if you see the final SQL you'll see them. If you don't spot the problem, post the SQL here.

Sorry but I don't know VB editor very well, so I put the code in my form where it would need to run and got nothing, no errors and no debug.print and no delete either. I guess I'll just have to do it as I mentioned by pasting and then editing for the correct table. Thanks anyway.

[paddymx]

Sorry but I don't know VB editor very well, so I put the code in my form where it would need to run and got nothing, no errors and no debug.print and no delete either. I guess I'll just have to do it as I mentioned by pasting and then editing for the correct table. Thanks anyway.

Okay, tried it again by putting the code in a mod then calling the pro from my form. Got invalid use of the Me key word. Probably because I didn't pass it because I don't know how to write functions either, booo on me! I'll have to do some studying on VB editor and functions.

[pbaldy]

Shouldn't be that hard. Did you notice in the link that it showed where the output would go? Can you post the db here?

[paddymx]

Shouldn't be that hard. Did you notice in the link that it showed where the output would go? Can you post the db here?

I opened the immediate window but I don't know how to use it. Don't think I can post the DB. Its around 120mb with its dependent avi's and jpegs,png's. Its also dependent on being in the c:\ directory. Haven't converted everything to a ".InitialFileName = CurrentProject.path & "\FlavDBImages\" & VendorAbbrv '" for looking up images/avi's syntax yet. Let me see if I can put together a stripped down version for just this feature.