? Syntax problem in SQL Delete

Hi Does anybody know where I'm going wrong here in these lines? Access
is asking for the parameters not getting them automatically.

strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
Employee and FormName = strFormname;"

DoCmd.RunSQL strSQLText

This is the full code

Private Sub UserPref_Click()
'UsersColumnPreferences
Dim db As Database
Dim rst As Recordset
Dim c As Control, cName As String, cOrder As Long, cDisplay As
Boolean, cWidth As Long
Dim stDocName As String, strFormName As String, Empl;oyee As Long
DoCmd.SetWarnings False
'stDocName = "Formatting - Empty for this Form" 'This deletes
prior entries for this view and this person
strFormName = "FireSafetySubform"
Employee = Forms![switchboard].[SWEmpID]
'DoCmd.OpenQuery stDocName, acNormal, , acEdit
DoCmd.SetWarnings False
Dim strSQLText As String
strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
Employee and FormName = strFormname;"

DoCmd.RunSQL strSQLText
DoCmd.SetWarnings True
Set db = CurrentDb()
Set rst = db.OpenRecordset("UsersColumnPreferences")

With Me.FireSafetySubform.Form

For Each c In .Controls
If c.Tag = "ch" Then
cName = c.Name
cDisplay = c.ColumnHidden
cWidth = c.ColumnWidth
cOrder = c.ColumnOrder
With rst
.AddNew
![EmpID] = Employee
![FormName] = strFormName
![ColumnName] = cName
![ColumnHidden] = cDisplay
![ColumnWidth] = cWidth
![ColumnOrder] = cOrder
.Update
End With
Else
End If

Next c
End With
rst.Close
Set rst = Nothing
Set db = Nothing

End Sub

Thanks for your help
Regards
Kevin

Re: ? Syntax problem in SQL Delete

>>strSQLText = "DELETE * FROM [UsersColumnPreferences]
>>where EMPID = Employee and FormName = strFormname;"

strFormname is a variable. You can't include a variable in a sql
string. You have to append it using the & ampersand symbol as follows.
Also, you are not delimiting the FormName argument. Text values in sql
must be delimited wtih single quotes:

FormName = strFormname;"

shoule be

FormName = '" & strFormname & "'"

See if that fixes your problem.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: ? Syntax problem in SQL Delete

On Dec 4, 2:58 pm, Kev wrote:
> Hi Does anybody know where I'm going wrong here in these lines? Access
> is asking for the parameters not getting them automatically.
>
> strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> Employee and FormName = strFormname;"
>
> DoCmd.RunSQL strSQLText
>
> This is the full code
>
> Private Sub UserPref_Click()
> 'UsersColumnPreferences
> Dim db As Database
> Dim rst As Recordset
> Dim c As Control, cName As String, cOrder As Long, cDisplay As
> Boolean, cWidth As Long
> Dim stDocName As String, strFormName As String, Empl;oyee As Long
> DoCmd.SetWarnings False
> 'stDocName = "Formatting - Empty for this Form" 'This deletes
> prior entries for this view and this person
> strFormName = "FireSafetySubform"
> Employee = Forms![switchboard].[SWEmpID]
> 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> DoCmd.SetWarnings False
> Dim strSQLText As String
> strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> Employee and FormName = strFormname;"
>
> DoCmd.RunSQL strSQLText
> DoCmd.SetWarnings True
> Set db = CurrentDb()
> Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> With Me.FireSafetySubform.Form
>
> For Each c In .Controls
> If c.Tag = "ch" Then
> cName = c.Name
> cDisplay = c.ColumnHidden
> cWidth = c.ColumnWidth
> cOrder = c.ColumnOrder
> With rst
> .AddNew
> ![EmpID] = Employee
> ![FormName] = strFormName
> ![ColumnName] = cName
> ![ColumnHidden] = cDisplay
> ![ColumnWidth] = cWidth
> ![ColumnOrder] = cOrder
> .Update
> End With
> Else
> End If
>
> Next c
> End With
> rst.Close
> Set rst = Nothing
> Set db = Nothing
>
> End Sub
>
> Thanks for your help
> Regards
> Kevin

I think you might have an 'operator error.' Try this, instead:

strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"

Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
textbox on a form? If it is a variable then you want to change that
part of the sql string to something similar to what I did above.
Otherwise, you have to refer to a control on a form as such: FORMS!
frmMainform!acontrol. Then, for example, that part of the sql string
becomes:
where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
(Use the Access Help search function and search for "expressions used
in SQL")

Re: ? Syntax problem in SQL Delete

Thanks Technolust and Rich,

It works a treat now.
Employee is a Long variable
strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
& Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
";"

One other question:

Is it more efficient to move the .Update to just before the rst.close
line (rst.update) so that the database only needs to update once?

Regards and thanks again
Kevin

With rst
> > .AddNew
> > ![EmpID] = Employee
> > ![FormName] = strFormName
> > ![ColumnName] = cName
> > ![ColumnHidden] = cDisplay
> > ![ColumnWidth] = cWidth
> > ![ColumnOrder] = cOrder
> > .Update
> > End With
> > Else
> > End If
>
> > Next c
> > End With
> > rst.Close
> > Set rst = Nothing




On Dec 5, 10:27 am, Technolust wrote:
> On Dec 4, 2:58 pm, Kev wrote:
>
>
>
>
>
> > Hi Does anybody know where I'm going wrong here in these lines? Access
> > is asking for the parameters not getting them automatically.
>
> > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > Employee and FormName = strFormname;"
>
> > DoCmd.RunSQL strSQLText
>
> > This is the full code
>
> > Private Sub UserPref_Click()
> > 'UsersColumnPreferences
> > Dim db As Database
> > Dim rst As Recordset
> > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > Boolean, cWidth As Long
> > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > DoCmd.SetWarnings False
> > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > prior entries for this view and this person
> > strFormName = "FireSafetySubform"
> > Employee = Forms![switchboard].[SWEmpID]
> > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > DoCmd.SetWarnings False
> > Dim strSQLText As String
> > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > Employee and FormName = strFormname;"
>
> > DoCmd.RunSQL strSQLText
> > DoCmd.SetWarnings True
> > Set db = CurrentDb()
> > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > With Me.FireSafetySubform.Form
>
> > For Each c In .Controls
> > If c.Tag = "ch" Then
> > cName = c.Name
> > cDisplay = c.ColumnHidden
> > cWidth = c.ColumnWidth
> > cOrder = c.ColumnOrder
> > With rst
> > .AddNew
> > ![EmpID] = Employee
> > ![FormName] = strFormName
> > ![ColumnName] = cName
> > ![ColumnHidden] = cDisplay
> > ![ColumnWidth] = cWidth
> > ![ColumnOrder] = cOrder
> > .Update
> > End With
> > Else
> > End If
>
> > Next c
> > End With
> > rst.Close
> > Set rst = Nothing
> > Set db = Nothing
>
> > End Sub
>
> > Thanks for your help
> > Regards
> > Kevin
>
> I think you might have an 'operator error.' Try this, instead:
>
> strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> textbox on a form? If it is a variable then you want to change that
> part of the sql string to something similar to what I did above.
> Otherwise, you have to refer to a control on a form as such: FORMS!
> frmMainform!acontrol. Then, for example, that part of the sql string
> becomes:
> where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> (Use the Access Help search function and search for "expressions used
> in SQL")- Hide quoted text -
>
> - Show quoted text -

Re: ? Syntax problem in SQL Delete

On Dec 4, 5:17 pm, Kev wrote:
> Thanks Technolust and Rich,
>
> It works a treat now.
> Employee is a Long variable
> strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
> & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
> ";"
>
> One other question:
>
> Is it more efficient to move the .Update to just before the rst.close
> line (rst.update) so that the database only needs to update once?
>
> Regards and thanks again
> Kevin
>
> With rst
>
>
>
>
>
> > > .AddNew
> > > ![EmpID] = Employee
> > > ![FormName] = strFormName
> > > ![ColumnName] = cName
> > > ![ColumnHidden] = cDisplay
> > > ![ColumnWidth] = cWidth
> > > ![ColumnOrder] = cOrder
> > > .Update
> > > End With
> > > Else
> > > End If
>
> > > Next c
> > > End With
> > > rst.Close
> > > Set rst = Nothing
>
> On Dec 5, 10:27 am, Technolust wrote:
>
>
>
> > On Dec 4, 2:58 pm, Kev wrote:
>
> > > Hi Does anybody know where I'm going wrong here in these lines? Access
> > > is asking for the parameters not getting them automatically.
>
> > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > Employee and FormName = strFormname;"
>
> > > DoCmd.RunSQL strSQLText
>
> > > This is the full code
>
> > > Private Sub UserPref_Click()
> > > 'UsersColumnPreferences
> > > Dim db As Database
> > > Dim rst As Recordset
> > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > Boolean, cWidth As Long
> > > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > > DoCmd.SetWarnings False
> > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > prior entries for this view and this person
> > > strFormName = "FireSafetySubform"
> > > Employee = Forms![switchboard].[SWEmpID]
> > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > DoCmd.SetWarnings False
> > > Dim strSQLText As String
> > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > Employee and FormName = strFormname;"
>
> > > DoCmd.RunSQL strSQLText
> > > DoCmd.SetWarnings True
> > > Set db = CurrentDb()
> > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > With Me.FireSafetySubform.Form
>
> > > For Each c In .Controls
> > > If c.Tag = "ch" Then
> > > cName = c.Name
> > > cDisplay = c.ColumnHidden
> > > cWidth = c.ColumnWidth
> > > cOrder = c.ColumnOrder
> > > With rst
> > > .AddNew
> > > ![EmpID] = Employee
> > > ![FormName] = strFormName
> > > ![ColumnName] = cName
> > > ![ColumnHidden] = cDisplay
> > > ![ColumnWidth] = cWidth
> > > ![ColumnOrder] = cOrder
> > > .Update
> > > End With
> > > Else
> > > End If
>
> > > Next c
> > > End With
> > > rst.Close
> > > Set rst = Nothing
> > > Set db = Nothing
>
> > > End Sub
>
> > > Thanks for your help
> > > Regards
> > > Kevin
>
> > I think you might have an 'operator error.' Try this, instead:
>
> > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> > Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> > textbox on a form? If it is a variable then you want to change that
> > part of the sql string to something similar to what I did above.
> > Otherwise, you have to refer to a control on a form as such: FORMS!
> > frmMainform!acontrol. Then, for example, that part of the sql string
> > becomes:
> > where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> > (Use the Access Help search function and search for "expressions used
> > in SQL")- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

I don't think you'll need .Update if you're using a dynamic SQL
statement. Show me the code UR using.

Re: ? Syntax problem in SQL Delete

On Dec 6, 3:36 am, Technolust wrote:
> On Dec 4, 5:17 pm, Kev wrote:
>
>
>
>
>
> > Thanks Technolust and Rich,
>
> > It works a treat now.
> > Employee is a Long variable
> > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
> > & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
> > ";"
>
> > One other question:
>
> > Is it more efficient to move the .Update to just before the rst.close
> > line (rst.update) so that the database only needs to update once?
>
> > Regards and thanks again
> > Kevin
>
> > With rst
>
> > > > .AddNew
> > > > ![EmpID] = Employee
> > > > ![FormName] = strFormName
> > > > ![ColumnName] = cName
> > > > ![ColumnHidden] = cDisplay
> > > > ![ColumnWidth] = cWidth
> > > > ![ColumnOrder] = cOrder
> > > > .Update
> > > > End With
> > > > Else
> > > > End If
>
> > > > Next c
> > > > End With
> > > > rst.Close
> > > > Set rst = Nothing
>
> > On Dec 5, 10:27 am, Technolust wrote:
>
> > > On Dec 4, 2:58 pm, Kev wrote:
>
> > > > Hi Does anybody know where I'm going wrong here in these lines? Access
> > > > is asking for the parameters not getting them automatically.
>
> > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > Employee and FormName = strFormname;"
>
> > > > DoCmd.RunSQL strSQLText
>
> > > > This is the full code
>
> > > > Private Sub UserPref_Click()
> > > > 'UsersColumnPreferences
> > > > Dim db As Database
> > > > Dim rst As Recordset
> > > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > > Boolean, cWidth As Long
> > > > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > > > DoCmd.SetWarnings False
> > > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > > prior entries for this view and this person
> > > > strFormName = "FireSafetySubform"
> > > > Employee = Forms![switchboard].[SWEmpID]
> > > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > > DoCmd.SetWarnings False
> > > > Dim strSQLText As String
> > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > Employee and FormName = strFormname;"
>
> > > > DoCmd.RunSQL strSQLText
> > > > DoCmd.SetWarnings True
> > > > Set db = CurrentDb()
> > > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > > With Me.FireSafetySubform.Form
>
> > > > For Each c In .Controls
> > > > If c.Tag = "ch" Then
> > > > cName = c.Name
> > > > cDisplay = c.ColumnHidden
> > > > cWidth = c.ColumnWidth
> > > > cOrder = c.ColumnOrder
> > > > With rst
> > > > .AddNew
> > > > ![EmpID] = Employee
> > > > ![FormName] = strFormName
> > > > ![ColumnName] = cName
> > > > ![ColumnHidden] = cDisplay
> > > > ![ColumnWidth] = cWidth
> > > > ![ColumnOrder] = cOrder
> > > > .Update
> > > > End With
> > > > Else
> > > > End If
>
> > > > Next c
> > > > End With
> > > > rst.Close
> > > > Set rst = Nothing
> > > > Set db = Nothing
>
> > > > End Sub
>
> > > > Thanks for your help
> > > > Regards
> > > > Kevin
>
> > > I think you might have an 'operator error.' Try this, instead:
>
> > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> > > Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> > > textbox on a form? If it is a variable then you want to change that
> > > part of the sql string to something similar to what I did above.
> > > Otherwise, you have to refer to a control on a form as such: FORMS!
> > > frmMainform!acontrol. Then, for example, that part of the sql string
> > > becomes:
> > > where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> > > (Use the Access Help search function and search for "expressions used
> > > in SQL")- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
>
> I don't think you'll need .Update if you're using a dynamic SQL
> statement. Show me the code UR using.- Hide quoted text -
>
> - Show quoted text -



At the moment I am using the code above in the click event of a
command button "Private Sub UserPref_Click()" and it is working well.
I am however trying to get it to work via a new module so that I can
use it in all my other forms without needing to replicate the code. I
am stuck on how to send the subform name (or as control or as form) to
the module as below

My reference in the properties sheet against the onclick event
is: =UserPref (FireSafety!FireSafetySubform) or
=UserPref (FireSafetySubform) or any number of other combinations

My module code is as below
I keep getting a type mismatch no matter what combination I try or a
"The object doesn't contain the automation object - FireSafety!
FireSafetySubform

Can you see where I am going wrong?

Thanks again for looking at this, It is such a help.
Regards
Kevin


Function UserPref(strFormName As Form)

'UsersColumnPreferences
Dim db As Database
Dim rst As Recordset
Dim c As Control, cName As String, cOrder As Long, cDisplay As
Boolean, cWidth As Long
Dim stDocName As String, intCurrentType As Integer
DoCmd.SetWarnings False
'stDocName = "Formatting - Empty for this Form" 'This deletes
prior entries for this view and this person
'strFormName = "FireSafetySubform"
Employee = Forms![Switchboard].[SWEmpID]
'DoCmd.OpenQuery stDocName, acNormal, , acEdit
DoCmd.SetWarnings False
Dim strSQLText As String

strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
" & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34)
& ";"



DoCmd.RunSQL strSQLText
DoCmd.SetWarnings True
Set db = CurrentDb()
Set rst = db.OpenRecordset("UsersColumnPreferences")

With Forms(strFormName).Form

For Each c In .Controls
If c.Tag = "ch" Then
cName = c.Name
cDisplay = c.ColumnHidden
cWidth = c.ColumnWidth
cOrder = c.ColumnOrder
With rst
.AddNew
![EmpID] = Employee
![FormName] = strFormName
![ColumnName] = cName
![ColumnHidden] = cDisplay
![ColumnWidth] = cWidth
![ColumnOrder] = cOrder
.Update
End With
Else
End If

Next c
End With
rst.Close
Set rst = Nothing
Set db = Nothing
End Function

Re: ? Syntax problem in SQL Delete

Hi Kev,

If you are not returning anything from the procedure then you should use
a Sub instead of a Function (just for consistency).

You can make a Sub (or Function) global (from a Standard Code module)
like this

Sub xyz()
...
End Sub

or

Public Sub xyz()
...
End Sub

You can call the sub like this from any form:

UserPref Employee, strFormName

Instead of determining the Employee number in the sub, just pass it in
as an argument to the sub

Employee is an argument and also has to be excluded from the sql string
and appended just like strFormName. But, since Employee is numeric (a
Long) you don't have to delimit Employee

call the global sub from a button like this:

Private Sub cmd1_Click()
Employee = something on your form
strFormName = me.Name

UserPref Employee, strFormName
...
End sub

And for your sql string you can simplify that a bit like this:


strSQLText = "DELETE * FROM [UsersColumnPreferences]
where EMPID = " & Employee & " And FormName = '" & strFormname & "'"

Instead of delimiting strFormName with chr(34) (which is the code for '
single quote) just use the single quote inside the sql string.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: ? Syntax problem in SQL Delete

On Dec 5, 2:56 pm, Kev wrote:
> On Dec 6, 3:36 am, Technolust wrote:
>
>
>
>
>
> > On Dec 4, 5:17 pm, Kev wrote:
>
> > > Thanks Technolust and Rich,
>
> > > It works a treat now.
> > > Employee is a Long variable
> > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
> > > & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
> > > ";"
>
> > > One other question:
>
> > > Is it more efficient to move the .Update to just before the rst.close
> > > line (rst.update) so that the database only needs to update once?
>
> > > Regards and thanks again
> > > Kevin
>
> > > With rst
>
> > > > > .AddNew
> > > > > ![EmpID] = Employee
> > > > > ![FormName] = strFormName
> > > > > ![ColumnName] = cName
> > > > > ![ColumnHidden] = cDisplay
> > > > > ![ColumnWidth] = cWidth
> > > > > ![ColumnOrder] = cOrder
> > > > > .Update
> > > > > End With
> > > > > Else
> > > > > End If
>
> > > > > Next c
> > > > > End With
> > > > > rst.Close
> > > > > Set rst = Nothing
>
> > > On Dec 5, 10:27 am, Technolust wrote:
>
> > > > On Dec 4, 2:58 pm, Kev wrote:
>
> > > > > Hi Does anybody know where I'm going wrong here in these lines? Access
> > > > > is asking for the parameters not getting them automatically.
>
> > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > Employee and FormName = strFormname;"
>
> > > > > DoCmd.RunSQL strSQLText
>
> > > > > This is the full code
>
> > > > > Private Sub UserPref_Click()
> > > > > 'UsersColumnPreferences
> > > > > Dim db As Database
> > > > > Dim rst As Recordset
> > > > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > > > Boolean, cWidth As Long
> > > > > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > > > > DoCmd.SetWarnings False
> > > > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > > > prior entries for this view and this person
> > > > > strFormName = "FireSafetySubform"
> > > > > Employee = Forms![switchboard].[SWEmpID]
> > > > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > > > DoCmd.SetWarnings False
> > > > > Dim strSQLText As String
> > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > Employee and FormName = strFormname;"
>
> > > > > DoCmd.RunSQL strSQLText
> > > > > DoCmd.SetWarnings True
> > > > > Set db = CurrentDb()
> > > > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > > > With Me.FireSafetySubform.Form
>
> > > > > For Each c In .Controls
> > > > > If c.Tag = "ch" Then
> > > > > cName = c.Name
> > > > > cDisplay = c.ColumnHidden
> > > > > cWidth = c.ColumnWidth
> > > > > cOrder = c.ColumnOrder
> > > > > With rst
> > > > > .AddNew
> > > > > ![EmpID] = Employee
> > > > > ![FormName] = strFormName
> > > > > ![ColumnName] = cName
> > > > > ![ColumnHidden] = cDisplay
> > > > > ![ColumnWidth] = cWidth
> > > > > ![ColumnOrder] = cOrder
> > > > > .Update
> > > > > End With
> > > > > Else
> > > > > End If
>
> > > > > Next c
> > > > > End With
> > > > > rst.Close
> > > > > Set rst = Nothing
> > > > > Set db = Nothing
>
> > > > > End Sub
>
> > > > > Thanks for your help
> > > > > Regards
> > > > > Kevin
>
> > > > I think you might have an 'operator error.' Try this, instead:
>
> > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> > > > Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> > > > textbox on a form? If it is a variable then you want to change that
> > > > part of the sql string to something similar to what I did above.
> > > > Otherwise, you have to refer to a control on a form as such: FORMS!
> > > > frmMainform!acontrol. Then, for example, that part of the sql string
> > > > becomes:
> > > > where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> > > > (Use the Access Help search function and search for "expressions used
> > > > in SQL")- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -
>
> > I don't think you'll need .Update if you're using a dynamic SQL
> > statement. Show me the code UR using.- Hide quoted text -
>
> > - Show quoted text -
>
> At the moment I am using the code above in the click event of a
> command button "Private Sub UserPref_Click()" and it is working well.
> I am however trying to get it to work via a new module so that I can
> use it in all my other forms without needing to replicate the code. I
> am stuck on how to send the subform name (or as control or as form) to
> the module as below
>
> My reference in the properties sheet against the onclick event
> is: =UserPref (FireSafety!FireSafetySubform) or
> =UserPref (FireSafetySubform) or any number of other combinations
>
> My module code is as below
> I keep getting a type mismatch no matter what combination I try or a
> "The object doesn't contain the automation object - FireSafety!
> FireSafetySubform
>
> Can you see where I am going wrong?
>
> Thanks again for looking at this, It is such a help.
> Regards
> Kevin
>
> Function UserPref(strFormName As Form)
>
> 'UsersColumnPreferences
> Dim db As Database
> Dim rst As Recordset
> Dim c As Control, cName As String, cOrder As Long, cDisplay As
> Boolean, cWidth As Long
> Dim stDocName As String, intCurrentType As Integer
> DoCmd.SetWarnings False
> 'stDocName = "Formatting - Empty for this Form" 'This deletes
> prior entries for this view and this person
> 'strFormName = "FireSafetySubform"
> Employee = Forms![Switchboard].[SWEmpID]
> 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> DoCmd.SetWarnings False
> Dim strSQLText As String
>
> strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> " & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34)
> & ";"
>
> DoCmd.RunSQL strSQLText
> DoCmd.SetWarnings True
> Set db = CurrentDb()
> Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> With Forms(strFormName).Form
>
> For Each c In .Controls
> If c.Tag = "ch" Then
> cName = c.Name
> cDisplay = c.ColumnHidden
> cWidth = c.ColumnWidth
> cOrder = c.ColumnOrder
> With rst
> .AddNew
> ![EmpID] = Employee
> ![FormName] = strFormName
> ![ColumnName] = cName
> ![ColumnHidden] = cDisplay
> ![ColumnWidth] = cWidth
> ![ColumnOrder] = cOrder
> .Update
> End With
> Else
> End If
>
> Next c
> End With
> rst.Close
> Set rst = Nothing
> Set db = Nothing
> End Function- Hide quoted text -
>
> - Show quoted text -

Try the code below. I think it's how you've defined your subform
parameter object as Form. Below I've defined it as a SubForm object
and I cleaned up your code a bit and added an error handler: *****
(save your current code for this subroutine to a Notepad file B4 copy
and paste code below) *****

Function UserPref(oSafetySubForm As Subform) As Boolean

'UsersColumnPreferences
Dim db As Database
Dim rst As Recordset
Dim c As Control, cName As String, cOrder As Long, cDisplay As
Boolean, cWidth As Long
Dim stDocName As String, intCurrentType As Integer
Dim strSQLText As String

On Error Goto Err_UserPref

'stDocName = "Formatting - Empty for this Form" 'This deletes
prior entries for this view and this person
'oSafetySubForm = "FireSafetySubform"
Employee = Forms![Switchboard].[SWEmpID]
'DoCmd.OpenQuery stDocName, acNormal, , acEdit

Set db = CurrentDb()

strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
" & Employee & " and FormName = " & Chr$(34) & oSafetySubForm.Name &
Chr$(34) & ";"
db.Execute strSQLText
Set rst = db.OpenRecordset("UsersColumnPreferences")


With oSafetySubForm.Form

For Each c In .Controls

If c.Tag = "ch" Then
cName = c.Name
cDisplay = c.ColumnHidden
cWidth = c.ColumnWidth
cOrder = c.ColumnOrder
With rst
.AddNew
![EmpID] = Employee
![FormName] = oSafetySubForm.Name
![ColumnName] = cName
![ColumnHidden] = cDisplay
![ColumnWidth] = cWidth
![ColumnOrder] = cOrder
.Update
End With
Else
End If

Next c
End With

UserPref = True '* Process succeeded!

Exit_UserPref:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Err_UserPref:
Dim iResp As VBA.VbMsgBoxResult

iResp = MsgBox("Error #" & Err.Number & vbCr & vbLf & _
Err.Description, vbRetryCancel+vbExclamation, Application.Name)
If iResp = vbRetry Then Resume
Resume Exit_UserPref
End Function

Re: ? Syntax problem in SQL Delete

On Dec 6, 10:26 am, Rich P wrote:
> Hi Kev,
>
> If you are not returning anything from the procedure then you should use
> a Sub instead of a Function (just for consistency).
>
> You can make a Sub (or Function) global (from a Standard Code module)
> like this
>
> Sub xyz()
> ..
> End Sub
>
> or
>
> Public Sub xyz()
> ..
> End Sub
>
> You can call the sub like this from any form:
>
> UserPref Employee, strFormName
>
> Instead of determining the Employee number in the sub, just pass it in
> as an argument to the sub
>
> Employee is an argument and also has to be excluded from the sql string
> and appended just like strFormName. But, since Employee is numeric (a
> Long) you don't have to delimit Employee
>
> call the global sub from a button like this:
>
> Private Sub cmd1_Click()
> Employee = something on your form
> strFormName = me.Name
>
> UserPref Employee, strFormName
> ..
> End sub
>
> And for your sql string you can simplify that a bit like this:
>
> strSQLText = "DELETE * FROM [UsersColumnPreferences]
> where EMPID = " & Employee & " And FormName = '" & strFormname & "'"
>
> Instead of delimiting strFormName with chr(34) (which is the code for '
> single quote) just use the single quote inside the sql string.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Thanks Rich,
I couldn't get it working until I figured that it's refering to the
main form. I configured a control on the main form for it's tag to be
"ch" and it works. However, I want it to work on the subform called
FireSafetySubform, so I thought it was a simple case of changing it's
name.
With strFormName = "FireSafety!FireSafetySubform" I get the error
message - "can't find the form"
With strFormName = "FireSafetySubform" I get the error
message - "can't find the form"

Do you know where I am going wrong?
Thank you
Kevin

Re: ? Syntax problem in SQL Delete

On Dec 6, 11:25 am, Technolust wrote:
> On Dec 5, 2:56 pm, Kev wrote:
>
> > On Dec 6, 3:36 am, Technolust wrote:
>
> > > On Dec 4, 5:17 pm, Kev wrote:
>
> > > > Thanks Technolust and Rich,
>
> > > > It works a treat now.
> > > > Employee is a Long variable
> > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
> > > > & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
> > > > ";"
>
> > > > One other question:
>
> > > > Is it more efficient to move the .Update to just before the rst.close
> > > > line (rst.update) so that the database only needs to update once?
>
> > > > Regards and thanks again
> > > > Kevin
>
> > > > With rst
>
> > > > > > .AddNew
> > > > > > ![EmpID] = Employee
> > > > > > ![FormName] = strFormName
> > > > > > ![ColumnName] = cName
> > > > > > ![ColumnHidden] = cDisplay
> > > > > > ![ColumnWidth] = cWidth
> > > > > > ![ColumnOrder] = cOrder
> > > > > > .Update
> > > > > > End With
> > > > > > Else
> > > > > > End If
>
> > > > > > Next c
> > > > > > End With
> > > > > > rst.Close
> > > > > > Set rst = Nothing
>
> > > > On Dec 5, 10:27 am, Technolust wrote:
>
> > > > > On Dec 4, 2:58 pm, Kev wrote:
>
> > > > > > Hi Does anybody know where I'm going wrong here in these lines? Access
> > > > > > is asking for the parameters not getting them automatically.
>
> > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > Employee and FormName = strFormname;"
>
> > > > > > DoCmd.RunSQL strSQLText
>
> > > > > > This is the full code
>
> > > > > > Private Sub UserPref_Click()
> > > > > > 'UsersColumnPreferences
> > > > > > Dim db As Database
> > > > > > Dim rst As Recordset
> > > > > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > > > > Boolean, cWidth As Long
> > > > > > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > > > > > DoCmd.SetWarnings False
> > > > > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > > > > prior entries for this view and this person
> > > > > > strFormName = "FireSafetySubform"
> > > > > > Employee = Forms![switchboard].[SWEmpID]
> > > > > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > > > > DoCmd.SetWarnings False
> > > > > > Dim strSQLText As String
> > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > Employee and FormName = strFormname;"
>
> > > > > > DoCmd.RunSQL strSQLText
> > > > > > DoCmd.SetWarnings True
> > > > > > Set db = CurrentDb()
> > > > > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > > > > With Me.FireSafetySubform.Form
>
> > > > > > For Each c In .Controls
> > > > > > If c.Tag = "ch" Then
> > > > > > cName = c.Name
> > > > > > cDisplay = c.ColumnHidden
> > > > > > cWidth = c.ColumnWidth
> > > > > > cOrder = c.ColumnOrder
> > > > > > With rst
> > > > > > .AddNew
> > > > > > ![EmpID] = Employee
> > > > > > ![FormName] = strFormName
> > > > > > ![ColumnName] = cName
> > > > > > ![ColumnHidden] = cDisplay
> > > > > > ![ColumnWidth] = cWidth
> > > > > > ![ColumnOrder] = cOrder
> > > > > > .Update
> > > > > > End With
> > > > > > Else
> > > > > > End If
>
> > > > > > Next c
> > > > > > End With
> > > > > > rst.Close
> > > > > > Set rst = Nothing
> > > > > > Set db = Nothing
>
> > > > > > End Sub
>
> > > > > > Thanks for your help
> > > > > > Regards
> > > > > > Kevin
>
> > > > > I think you might have an 'operator error.' Try this, instead:
>
> > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> > > > > Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> > > > > textbox on a form? If it is a variable then you want to change that
> > > > > part of the sql string to something similar to what I did above.
> > > > > Otherwise, you have to refer to a control on a form as such: FORMS!
> > > > > frmMainform!acontrol. Then, for example, that part of the sql string
> > > > > becomes:
> > > > > where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> > > > > (Use the Access Help search function and search for "expressions used
> > > > > in SQL")- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > I don't think you'll need .Update if you're using a dynamic SQL
> > > statement. Show me the code UR using.- Hide quoted text -
>
> > > - Show quoted text -
>
> > At the moment I am using the code above in the click event of a
> > command button "Private Sub UserPref_Click()" and it is working well.
> > I am however trying to get it to work via a new module so that I can
> > use it in all my other forms without needing to replicate the code. I
> > am stuck on how to send the subform name (or as control or as form) to
> > the module as below
>
> > My reference in the properties sheet against the onclick event
> > is: =UserPref (FireSafety!FireSafetySubform) or
> > =UserPref (FireSafetySubform) or any number of other combinations
>
> > My module code is as below
> > I keep getting a type mismatch no matter what combination I try or a
> > "The object doesn't contain the automation object - FireSafety!
> > FireSafetySubform
>
> > Can you see where I am going wrong?
>
> > Thanks again for looking at this, It is such a help.
> > Regards
> > Kevin
>
> > Function UserPref(strFormName As Form)
>
> > 'UsersColumnPreferences
> > Dim db As Database
> > Dim rst As Recordset
> > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > Boolean, cWidth As Long
> > Dim stDocName As String, intCurrentType As Integer
> > DoCmd.SetWarnings False
> > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > prior entries for this view and this person
> > 'strFormName = "FireSafetySubform"
> > Employee = Forms![Switchboard].[SWEmpID]
> > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > DoCmd.SetWarnings False
> > Dim strSQLText As String
>
> > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > " & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34)
> > & ";"
>
> > DoCmd.RunSQL strSQLText
> > DoCmd.SetWarnings True
> > Set db = CurrentDb()
> > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > With Forms(strFormName).Form
>
> > For Each c In .Controls
> > If c.Tag = "ch" Then
> > cName = c.Name
> > cDisplay = c.ColumnHidden
> > cWidth = c.ColumnWidth
> > cOrder = c.ColumnOrder
> > With rst
> > .AddNew
> > ![EmpID] = Employee
> > ![FormName] = strFormName
> > ![ColumnName] = cName
> > ![ColumnHidden] = cDisplay
> > ![ColumnWidth] = cWidth
> > ![ColumnOrder] = cOrder
> > .Update
> > End With
> > Else
> > End If
>
> > Next c
> > End With
> > rst.Close
> > Set rst = Nothing
> > Set db = Nothing
> > End Function- Hide quoted text -
>
> > - Show quoted text -
>
> Try the code below. I think it's how you've defined your subform
> parameter object as Form. Below I've defined it as a SubForm object
> and I cleaned up your code a bit and added an error handler: *****
> (save your current code for this subroutine to a Notepad file B4 copy
> and paste code below) *****
>
> Function UserPref(oSafetySubForm As Subform) As Boolean
>
> 'UsersColumnPreferences
> Dim db As Database
> Dim rst As Recordset
> Dim c As Control, cName As String, cOrder As Long, cDisplay As
> Boolean, cWidth As Long
> Dim stDocName As String, intCurrentType As Integer
> Dim strSQLText As String
>
> On Error Goto Err_UserPref
>
> 'stDocName = "Formatting - Empty for this Form" 'This deletes
> prior entries for this view and this person
> 'oSafetySubForm = "FireSafetySubform"
> Employee = Forms![Switchboard].[SWEmpID]
> 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
>
> Set db = CurrentDb()
>
> strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> " & Employee & " and FormName = " & Chr$(34) & oSafetySubForm.Name &
> Chr$(34) & ";"
> db.Execute strSQLText
> Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> With oSafetySubForm.Form
>
> For Each c In .Controls
>
> If c.Tag = "ch" Then
> cName = c.Name
> cDisplay = c.ColumnHidden
> cWidth = c.ColumnWidth
> cOrder = c.ColumnOrder
> With rst
> .AddNew
> ![EmpID] = Employee
> ![FormName] = oSafetySubForm.Name
> ![ColumnName] = cName
> ![ColumnHidden] = cDisplay
> ![ColumnWidth] = cWidth
> ![ColumnOrder] = cOrder
> .Update
> End With
> Else
> End If
>
> Next c
> End With
>
> UserPref = True '* Process succeeded!
>
> Exit_UserPref:
> rst.Close
> Set rst = Nothing
> Set db = Nothing
> Exit Function
>
> Err_UserPref:
> Dim iResp As VBA.VbMsgBoxResult
>
> iResp = MsgBox("Error #" & Err.Number & vbCr & vbLf & _
> Err.Description, vbRetryCancel+vbExclamation, Application.Name)
> If iResp = vbRetry Then Resume
> Resume Exit_UserPref
> End Function

Thanks Technolust,
However I can't seem to call the code from the main form's control
button. Any ideas?
Regards
Kevin

Re: ? Syntax problem in SQL Delete

On Dec 5, 6:45 pm, Kev wrote:
> On Dec 6, 11:25 am, Technolust wrote:
>
>
>
> > On Dec 5, 2:56 pm, Kev wrote:
>
> > > On Dec 6, 3:36 am, Technolust wrote:
>
> > > > On Dec 4, 5:17 pm, Kev wrote:
>
> > > > > Thanks Technolust and Rich,
>
> > > > > It works a treat now.
> > > > > Employee is a Long variable
> > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
> > > > > & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
> > > > > ";"
>
> > > > > One other question:
>
> > > > > Is it more efficient to move the .Update to just before the rst.close
> > > > > line (rst.update) so that the database only needs to update once?
>
> > > > > Regards and thanks again
> > > > > Kevin
>
> > > > > With rst
>
> > > > > > > .AddNew
> > > > > > > ![EmpID] = Employee
> > > > > > > ![FormName] = strFormName
> > > > > > > ![ColumnName] = cName
> > > > > > > ![ColumnHidden] = cDisplay
> > > > > > > ![ColumnWidth] = cWidth
> > > > > > > ![ColumnOrder] = cOrder
> > > > > > > .Update
> > > > > > > End With
> > > > > > > Else
> > > > > > > End If
>
> > > > > > > Next c
> > > > > > > End With
> > > > > > > rst.Close
> > > > > > > Set rst = Nothing
>
> > > > > On Dec 5, 10:27 am, Technolust wrote:
>
> > > > > > On Dec 4, 2:58 pm, Kev wrote:
>
> > > > > > > Hi Does anybody know where I'm going wrong here in these lines? Access
> > > > > > > is asking for the parameters not getting them automatically.
>
> > > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > > Employee and FormName = strFormname;"
>
> > > > > > > DoCmd.RunSQL strSQLText
>
> > > > > > > This is the full code
>
> > > > > > > Private Sub UserPref_Click()
> > > > > > > 'UsersColumnPreferences
> > > > > > > Dim db As Database
> > > > > > > Dim rst As Recordset
> > > > > > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > > > > > Boolean, cWidth As Long
> > > > > > > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > > > > > > DoCmd.SetWarnings False
> > > > > > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > > > > > prior entries for this view and this person
> > > > > > > strFormName = "FireSafetySubform"
> > > > > > > Employee = Forms![switchboard].[SWEmpID]
> > > > > > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > > > > > DoCmd.SetWarnings False
> > > > > > > Dim strSQLText As String
> > > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > > Employee and FormName = strFormname;"
>
> > > > > > > DoCmd.RunSQL strSQLText
> > > > > > > DoCmd.SetWarnings True
> > > > > > > Set db = CurrentDb()
> > > > > > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > > > > > With Me.FireSafetySubform.Form
>
> > > > > > > For Each c In .Controls
> > > > > > > If c.Tag = "ch" Then
> > > > > > > cName = c.Name
> > > > > > > cDisplay = c.ColumnHidden
> > > > > > > cWidth = c.ColumnWidth
> > > > > > > cOrder = c.ColumnOrder
> > > > > > > With rst
> > > > > > > .AddNew
> > > > > > > ![EmpID] = Employee
> > > > > > > ![FormName] = strFormName
> > > > > > > ![ColumnName] = cName
> > > > > > > ![ColumnHidden] = cDisplay
> > > > > > > ![ColumnWidth] = cWidth
> > > > > > > ![ColumnOrder] = cOrder
> > > > > > > .Update
> > > > > > > End With
> > > > > > > Else
> > > > > > > End If
>
> > > > > > > Next c
> > > > > > > End With
> > > > > > > rst.Close
> > > > > > > Set rst = Nothing
> > > > > > > Set db = Nothing
>
> > > > > > > End Sub
>
> > > > > > > Thanks for your help
> > > > > > > Regards
> > > > > > > Kevin
>
> > > > > > I think you might have an 'operator error.' Try this, instead:
>
> > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> > > > > > Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> > > > > > textbox on a form? If it is a variable then you want to change that
> > > > > > part of the sql string to something similar to what I did above.
> > > > > > Otherwise, you have to refer to a control on a form as such: FORMS!
> > > > > > frmMainform!acontrol. Then, for example, that part of the sql string
> > > > > > becomes:
> > > > > > where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> > > > > > (Use the Access Help search function and search for "expressions used
> > > > > > in SQL")- Hide quoted text -
>
> > > > > > - Show quoted text -- Hide quoted text -
>
> > > > > - Show quoted text -- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > I don't think you'll need .Update if you're using a dynamic SQL
> > > > statement. Show me the code UR using.- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > At the moment I am using the code above in the click event of a
> > > command button "Private Sub UserPref_Click()" and it is working well.
> > > I am however trying to get it to work via a new module so that I can
> > > use it in all my other forms without needing to replicate the code. I
> > > am stuck on how to send the subform name (or as control or as form) to
> > > the module as below
>
> > > My reference in the properties sheet against the onclick event
> > > is: =UserPref (FireSafety!FireSafetySubform) or
> > > =UserPref (FireSafetySubform) or any number of other combinations
>
> > > My module code is as below
> > > I keep getting a type mismatch no matter what combination I try or a
> > > "The object doesn't contain the automation object - FireSafety!
> > > FireSafetySubform
>
> > > Can you see where I am going wrong?
>
> > > Thanks again for looking at this, It is such a help.
> > > Regards
> > > Kevin
>
> > > Function UserPref(strFormName As Form)
>
> > > 'UsersColumnPreferences
> > > Dim db As Database
> > > Dim rst As Recordset
> > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > Boolean, cWidth As Long
> > > Dim stDocName As String, intCurrentType As Integer
> > > DoCmd.SetWarnings False
> > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > prior entries for this view and this person
> > > 'strFormName = "FireSafetySubform"
> > > Employee = Forms![Switchboard].[SWEmpID]
> > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > DoCmd.SetWarnings False
> > > Dim strSQLText As String
>
> > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > " & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34)
> > > & ";"
>
> > > DoCmd.RunSQL strSQLText
> > > DoCmd.SetWarnings True
> > > Set db = CurrentDb()
> > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > With Forms(strFormName).Form
>
> > > For Each c In .Controls
> > > If c.Tag = "ch" Then
> > > cName = c.Name
> > > cDisplay = c.ColumnHidden
> > > cWidth = c.ColumnWidth
> > > cOrder = c.ColumnOrder
> > > With rst
> > > .AddNew
> > > ![EmpID] = Employee
> > > ![FormName] = strFormName
> > > ![ColumnName] = cName
> > > ![ColumnHidden] = cDisplay
> > > ![ColumnWidth] = cWidth
> > > ![ColumnOrder] = cOrder
> > > .Update
> > > End With
> > > Else
> > > End If
>
> > > Next c
> > > End With
> > > rst.Close
> > > Set rst = Nothing
> > > Set db = Nothing
> > > End Function- Hide quoted text -
>
> > > - Show quoted text -
>
> > Try the code below. I think it's how you've defined your subform
> > parameter object as Form. Below I've defined it as a SubForm object
> > and I cleaned up your code a bit and added an error handler: *****
> > (save your current code for this subroutine to a Notepad file B4 copy
> > and paste code below) *****
>
> > Function UserPref(oSafetySubForm As Subform) As Boolean
>
> > 'UsersColumnPreferences
> > Dim db As Database
> > Dim rst As Recordset
> > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > Boolean, cWidth As Long
> > Dim stDocName As String, intCurrentType As Integer
> > Dim strSQLText As String
>
> > On Error Goto Err_UserPref
>
> > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > prior entries for this view and this person
> > 'oSafetySubForm = "FireSafetySubform"
> > Employee = Forms![Switchboard].[SWEmpID]
> > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
>
> > Set db = CurrentDb()
>
> > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > " & Employee & " and FormName = " & Chr$(34) & oSafetySubForm.Name &
> > Chr$(34) & ";"
> > db.Execute strSQLText
> > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > With oSafetySubForm.Form
>
> > For Each c In .Controls
>
> > If c.Tag = "ch" Then
> > cName = c.Name
> > cDisplay = c.ColumnHidden
> > cWidth = c.ColumnWidth
> > cOrder = c.ColumnOrder
> > With rst
> > .AddNew
> > ![EmpID] = Employee
> > ![FormName] = oSafetySubForm.Name
> > ![ColumnName] = cName
> > ![ColumnHidden] = cDisplay
> > ![ColumnWidth] = cWidth
> > ![ColumnOrder] = cOrder
>
> ...
>
> read more >>- Hide quoted text -
>
> - Show quoted text -

Sorry, just make sure that the function is defined as Public:

Public Function UserPref(oSafetySubform As Subform) As Boolean
....


..

Re: ? Syntax problem in SQL Delete

On Dec 7, 4:03 am, Technolust wrote:
> On Dec 5, 6:45 pm, Kev wrote:
>
>
>
>
>
> > On Dec 6, 11:25 am, Technolust wrote:
>
> > > On Dec 5, 2:56 pm, Kev wrote:
>
> > > > On Dec 6, 3:36 am, Technolust wrote:
>
> > > > > On Dec 4, 5:17 pm, Kev wrote:
>
> > > > > > Thanks Technolust and Rich,
>
> > > > > > It works a treat now.
> > > > > > Employee is a Long variable
> > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID = "
> > > > > > & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34) &
> > > > > > ";"
>
> > > > > > One other question:
>
> > > > > > Is it more efficient to move the .Update to just before the rst.close
> > > > > > line (rst.update) so that the database only needs to update once?
>
> > > > > > Regards and thanks again
> > > > > > Kevin
>
> > > > > > With rst
>
> > > > > > > > .AddNew
> > > > > > > > ![EmpID] = Employee
> > > > > > > > ![FormName] = strFormName
> > > > > > > > ![ColumnName] = cName
> > > > > > > > ![ColumnHidden] = cDisplay
> > > > > > > > ![ColumnWidth] = cWidth
> > > > > > > > ![ColumnOrder] = cOrder
> > > > > > > > .Update
> > > > > > > > End With
> > > > > > > > Else
> > > > > > > > End If
>
> > > > > > > > Next c
> > > > > > > > End With
> > > > > > > > rst.Close
> > > > > > > > Set rst = Nothing
>
> > > > > > On Dec 5, 10:27 am, Technolust wrote:
>
> > > > > > > On Dec 4, 2:58 pm, Kev wrote:
>
> > > > > > > > Hi Does anybody know where I'm going wrong here in these lines? Access
> > > > > > > > is asking for the parameters not getting them automatically.
>
> > > > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > > > Employee and FormName = strFormname;"
>
> > > > > > > > DoCmd.RunSQL strSQLText
>
> > > > > > > > This is the full code
>
> > > > > > > > Private Sub UserPref_Click()
> > > > > > > > 'UsersColumnPreferences
> > > > > > > > Dim db As Database
> > > > > > > > Dim rst As Recordset
> > > > > > > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > > > > > > Boolean, cWidth As Long
> > > > > > > > Dim stDocName As String, strFormName As String, Empl;oyee As Long
> > > > > > > > DoCmd.SetWarnings False
> > > > > > > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > > > > > > prior entries for this view and this person
> > > > > > > > strFormName = "FireSafetySubform"
> > > > > > > > Employee = Forms![switchboard].[SWEmpID]
> > > > > > > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > > > > > > DoCmd.SetWarnings False
> > > > > > > > Dim strSQLText As String
> > > > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > > > Employee and FormName = strFormname;"
>
> > > > > > > > DoCmd.RunSQL strSQLText
> > > > > > > > DoCmd.SetWarnings True
> > > > > > > > Set db = CurrentDb()
> > > > > > > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > > > > > > With Me.FireSafetySubform.Form
>
> > > > > > > > For Each c In .Controls
> > > > > > > > If c.Tag = "ch" Then
> > > > > > > > cName = c.Name
> > > > > > > > cDisplay = c.ColumnHidden
> > > > > > > > cWidth = c.ColumnWidth
> > > > > > > > cOrder = c.ColumnOrder
> > > > > > > > With rst
> > > > > > > > .AddNew
> > > > > > > > ![EmpID] = Employee
> > > > > > > > ![FormName] = strFormName
> > > > > > > > ![ColumnName] = cName
> > > > > > > > ![ColumnHidden] = cDisplay
> > > > > > > > ![ColumnWidth] = cWidth
> > > > > > > > ![ColumnOrder] = cOrder
> > > > > > > > .Update
> > > > > > > > End With
> > > > > > > > Else
> > > > > > > > End If
>
> > > > > > > > Next c
> > > > > > > > End With
> > > > > > > > rst.Close
> > > > > > > > Set rst = Nothing
> > > > > > > > Set db = Nothing
>
> > > > > > > > End Sub
>
> > > > > > > > Thanks for your help
> > > > > > > > Regards
> > > > > > > > Kevin
>
> > > > > > > I think you might have an 'operator error.' Try this, instead:
>
> > > > > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > > > > Employee and FormName = " & Chr$(34) & strFormname & Chr$(34) & ";"
>
> > > > > > > Also, does 'Employee' in "EMPID=Employee" refer to a variable or a
> > > > > > > textbox on a form? If it is a variable then you want to change that
> > > > > > > part of the sql string to something similar to what I did above.
> > > > > > > Otherwise, you have to refer to a control on a form as such: FORMS!
> > > > > > > frmMainform!acontrol. Then, for example, that part of the sql string
> > > > > > > becomes:
> > > > > > > where EMPID = " & FORMS!frmMainform!Employee & " and FormName.
> > > > > > > (Use the Access Help search function and search for "expressions used
> > > > > > > in SQL")- Hide quoted text -
>
> > > > > > > - Show quoted text -- Hide quoted text -
>
> > > > > > - Show quoted text -- Hide quoted text -
>
> > > > > > - Show quoted text -
>
> > > > > I don't think you'll need .Update if you're using a dynamic SQL
> > > > > statement. Show me the code UR using.- Hide quoted text -
>
> > > > > - Show quoted text -
>
> > > > At the moment I am using the code above in the click event of a
> > > > command button "Private Sub UserPref_Click()" and it is working well.
> > > > I am however trying to get it to work via a new module so that I can
> > > > use it in all my other forms without needing to replicate the code. I
> > > > am stuck on how to send the subform name (or as control or as form) to
> > > > the module as below
>
> > > > My reference in the properties sheet against the onclick event
> > > > is: =UserPref (FireSafety!FireSafetySubform) or
> > > > =UserPref (FireSafetySubform) or any number of other combinations
>
> > > > My module code is as below
> > > > I keep getting a type mismatch no matter what combination I try or a
> > > > "The object doesn't contain the automation object - FireSafety!
> > > > FireSafetySubform
>
> > > > Can you see where I am going wrong?
>
> > > > Thanks again for looking at this, It is such a help.
> > > > Regards
> > > > Kevin
>
> > > > Function UserPref(strFormName As Form)
>
> > > > 'UsersColumnPreferences
> > > > Dim db As Database
> > > > Dim rst As Recordset
> > > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > > Boolean, cWidth As Long
> > > > Dim stDocName As String, intCurrentType As Integer
> > > > DoCmd.SetWarnings False
> > > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > > prior entries for this view and this person
> > > > 'strFormName = "FireSafetySubform"
> > > > Employee = Forms![Switchboard].[SWEmpID]
> > > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
> > > > DoCmd.SetWarnings False
> > > > Dim strSQLText As String
>
> > > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > > " & Employee & " and FormName = " & Chr$(34) & strFormName & Chr$(34)
> > > > & ";"
>
> > > > DoCmd.RunSQL strSQLText
> > > > DoCmd.SetWarnings True
> > > > Set db = CurrentDb()
> > > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > > With Forms(strFormName).Form
>
> > > > For Each c In .Controls
> > > > If c.Tag = "ch" Then
> > > > cName = c.Name
> > > > cDisplay = c.ColumnHidden
> > > > cWidth = c.ColumnWidth
> > > > cOrder = c.ColumnOrder
> > > > With rst
> > > > .AddNew
> > > > ![EmpID] = Employee
> > > > ![FormName] = strFormName
> > > > ![ColumnName] = cName
> > > > ![ColumnHidden] = cDisplay
> > > > ![ColumnWidth] = cWidth
> > > > ![ColumnOrder] = cOrder
> > > > .Update
> > > > End With
> > > > Else
> > > > End If
>
> > > > Next c
> > > > End With
> > > > rst.Close
> > > > Set rst = Nothing
> > > > Set db = Nothing
> > > > End Function- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > Try the code below. I think it's how you've defined your subform
> > > parameter object as Form. Below I've defined it as a SubForm object
> > > and I cleaned up your code a bit and added an error handler: *****
> > > (save your current code for this subroutine to a Notepad file B4 copy
> > > and paste code below) *****
>
> > > Function UserPref(oSafetySubForm As Subform) As Boolean
>
> > > 'UsersColumnPreferences
> > > Dim db As Database
> > > Dim rst As Recordset
> > > Dim c As Control, cName As String, cOrder As Long, cDisplay As
> > > Boolean, cWidth As Long
> > > Dim stDocName As String, intCurrentType As Integer
> > > Dim strSQLText As String
>
> > > On Error Goto Err_UserPref
>
> > > 'stDocName = "Formatting - Empty for this Form" 'This deletes
> > > prior entries for this view and this person
> > > 'oSafetySubForm = "FireSafetySubform"
> > > Employee = Forms![Switchboard].[SWEmpID]
> > > 'DoCmd.OpenQuery stDocName, acNormal, , acEdit
>
> > > Set db = CurrentDb()
>
> > > strSQLText = "DELETE * FROM [UsersColumnPreferences] where EMPID =
> > > " & Employee & " and FormName = " & Chr$(34) & oSafetySubForm.Name &
> > > Chr$(34) & ";"
> > > db.Execute strSQLText
> > > Set rst = db.OpenRecordset("UsersColumnPreferences")
>
> > > With oSafetySubForm.Form
>
> > > For Each c In .Controls
>
> > > If c.Tag = "ch" Then
> > > cName = c.Name
> > > cDisplay = c.ColumnHidden
> > > cWidth = c.ColumnWidth
> > > cOrder = c.ColumnOrder
> > > With rst
> > > .AddNew
> > > ![EmpID] = Employee
> > > ![FormName] = oSafetySubForm.Name
> > > ![ColumnName] = cName
> > > ![ColumnHidden] = cDisplay
> > > ![ColumnWidth] = cWidth
> > > ![ColumnOrder] = cOrder
>
> > ...
>
> > read more >>- Hide quoted text -
>
> > - Show quoted text -
>
> Sorry, just make sure that the function is defined as Public:
>
> Public Function UserPref(oSafetySubform As Subform) As Boolean
> ...
>
> .- Hide quoted text -
>
> - Show quoted text -

Thanks, I did actually define it as Public after a few attempts.
I'm stuck on the syntax I need to use to call it from the main form.
Do you know what syntax I should use on the On_click section of the
property sheet or within the on_click code to call the public
function?

Do you know where I am going wrong?
Thank you
Kevin