Incorporating a public variable in an Update Query that updates atable

  • Thread starter nouveauricheinvestments
  • Start date
N

nouveauricheinvestments

Hi,

I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.

Any help would be greatly appreciated.


Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select

End Sub
 
K

Ken Snell \(MVP\)

Concateneate the value of the public variable into the SQL string:

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
 
N

nouveauricheinvestments

Concateneate the value of the public variable into the SQL string:

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Hi,
Click to expand...
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Click to expand...
Any help would be greatly appreciated.
Click to expand...
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
Click to expand...
End Sub
Click to expand...
Click to expand...

When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.

Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
 
K

Klatuu

You have to have the variable outside the qoutes

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending Tickets].SelectTicket
= " &
_


Concateneate the value of the public variable into the SQL string:

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Hi,
Click to expand...
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Click to expand...
Any help would be greatly appreciated.
Click to expand...
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
Click to expand...
End Sub
Click to expand...
Click to expand...

When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.

Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
 
K

Ken Snell \(MVP\)

As Klatuu noted, you didn't build the SQL statement the way I showed in my
example. Look at my suggestion again.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Concateneate the value of the public variable into the SQL string:

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Hi,
Click to expand...
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Click to expand...
Any help would be greatly appreciated.
Click to expand...
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
Click to expand...
End Sub
Click to expand...
Click to expand...

When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.

Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
 
N

nouveauricheinvestments

You have to have the variable outside the qoutes

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending Tickets].SelectTicket
= " &
_


Concateneate the value of the public variable into the SQL string:
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hi,
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Any help would be greatly appreciated.
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
End Sub
Click to expand...
Click to expand...
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Click to expand...
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
Click to expand...

If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
 
N

nouveauricheinvestments

You have to have the variable outside the qoutes
Click to expand...
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending Tickets].SelectTicket
= " &
_
Click to expand...
news:e42adb7f-4fc3-43c7-a1fa-6392aeb6923b@c22g2000prc.googlegroups.com...
Click to expand...
On Nov 14, 9:46 am, "Ken Snell \(MVP\)"
Concateneate the value of the public variable into the SQL string:
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hi,
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Any help would be greatly appreciated.
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
End Sub
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
Click to expand...

If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
Click to expand...

This field I am trying to set to 'thisuser' was being queried by
another subform and the criteria was that the 'ResearchedBy' field 'Is
Null'. The problem now is that there is nothing in any of these
records for this field, but 3 of them are being filtered out as not
being null. The field is empty. I only have 6 records and 3 are
filtered out after I enter the 'Is Null' criteria, despite the fact
there isn't anything there. What would make that happen?
 
N

nouveauricheinvestments

You have to have the variable outside the qoutes
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending Tickets].SelectTicket
= " &
_

On Nov 14, 9:46 am, "Ken Snell \(MVP\)"
Concateneate the value of the public variable into the SQL string:
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hi,
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Any help would be greatly appreciated.
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
End Sub
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
Click to expand...
If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...
Click to expand...
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
Click to expand...

This field I am trying to set to 'thisuser' was being queried by
another subform and the criteria was that the 'ResearchedBy' field 'Is
Null'. The problem now is that there is nothing in any of these
records for this field, but 3 of them are being filtered out as not
being null. The field is empty. I only have 6 records and 3 are
filtered out after I enter the 'Is Null' criteria, despite the fact
there isn't anything there. What would make that happen?
Click to expand...

Nevermind, I just deleted it and added a new field...
 
N

nouveauricheinvestments

You have to have the variable outside the qoutes
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending Tickets].SelectTicket
= " &
_

On Nov 14, 9:46 am, "Ken Snell \(MVP\)"
Concateneate the value of the public variable into the SQL string:
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hi,
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Any help would be greatly appreciated.
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
End Sub
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
Click to expand...
If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...
Click to expand...
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
Click to expand...

This field I am trying to set to 'thisuser' was being queried by
another subform and the criteria was that the 'ResearchedBy' field 'Is
Null'. The problem now is that there is nothing in any of these
records for this field, but 3 of them are being filtered out as not
being null. The field is empty. I only have 6 records and 3 are
filtered out after I enter the 'Is Null' criteria, despite the fact
there isn't anything there. What would make that happen?
Click to expand...

I'm getting the following error message: Syntax error (missing
operator) in query expression 'Robin Tanner'.

Robin Tanner is the value of my public variable in this case. The
following is what I am using:

Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & ThisUser & ", [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
 
N

nouveauricheinvestments

On Nov 14, 10:51 am, (e-mail address removed) wrote:
You have to have the variable outside the qoutes
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending Tickets].SelectTicket
= " &
_

On Nov 14, 9:46 am, "Ken Snell \(MVP\)"
Concateneate the value of the public variable into the SQL string:
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hi,
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How should
I do this? I don't think I can incorporate a public variable into an
SQL statement. I used the value on another form because initially the
user signs into the application and this value on another form is set
to my public variable. My public variable is called 'ThisUser' and is
used throughout the application for restricting/granting permissions,
etc.
Any help would be greatly appreciated.
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
End Sub
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
Click to expand...
Click to expand...
This field I am trying to set to 'thisuser' was being queried by
another subform and the criteria was that the 'ResearchedBy' field 'Is
Null'. The problem now is that there is nothing in any of these
records for this field, but 3 of them are being filtered out as not
being null. The field is empty. I only have 6 records and 3 are
filtered out after I enter the 'Is Null' criteria, despite the fact
there isn't anything there. What would make that happen?
Click to expand...

I'm getting the following error message: Syntax error (missing
operator) in query expression 'Robin Tanner'.

Robin Tanner is the value of my public variable in this case. The
following is what I am using:

Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & ThisUser & ", [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...

I have tried running this with either 3 quotation marks on either side
of the public variable or with single commas inside of the quotations
as follows and it updates my records, but I don't know what it is
updating it to. After the sql statement is ran, I go back into the
table and the field LOOKS blank - but my query that searches for all
records where this field is null filters out the record. What am I
doing wrong?


With the triple quotes.....

Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & ThisUser & """, [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL


With the commas....


Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = '" & ThisUser & "', [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
 
K

Ken Snell \(MVP\)

Your result suggests that the thisuser variable is not "seen" by your code.
Did you DIM the thisuser variable as a Public variable in another module (if
it's a form module, that form must be open)?

If not, then your code has no idea that it's supposed to see the thisuser
variable from that other module. How/where are you Dim'g the thisuser
variable?
 
N

nouveauricheinvestments

Your result suggests that the thisuser variable is not "seen" by your code.
Did you DIM the thisuser variable as a Public variable in another module (if
it's a form module, that form must be open)?

If not, then your code has no idea that it's supposed to see the thisuser
variable from that other module. How/where are you Dim'g the thisuser
variable?

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/




I have tried running this with either 3 quotation marks on either side
of the public variable or with single commas inside of the quotations
as follows and it updates my records, but I don't know what it is
updating it to. After the sql statement is ran, I go back into the
table and the field LOOKS blank - but my query that searches for all
records where this field is null filters out the record. What am I
doing wrong?
Click to expand...
With the triple quotes.....
Click to expand...
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & ThisUser & """, [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Click to expand...
With the commas....
Click to expand...
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = '" & ThisUser & "', [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Click to expand...
Click to expand...

hmm...I have the following line in a non-form module.

Public ThisUser As String

I use the variable throughout the application to determine whether a
user has access to edit certain records, delete certain records, open
forms, and I haven't had any problems with it thus far.
 
K

Ken Snell \(MVP\)

Does the procedure in which you're running your code have an additional Dim
statement for the ThisUser variable?

Put a breakpoint on the code step that is building the SQL statement. When
the code stops on that line, hover with cursor over the ThisUser variable.
What value do you see in the popup text?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Your result suggests that the thisuser variable is not "seen" by your
code.
Did you DIM the thisuser variable as a Public variable in another module
(if
it's a form module, that form must be open)?

If not, then your code has no idea that it's supposed to see the thisuser
variable from that other module. How/where are you Dim'g the thisuser
variable?

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/




I have tried running this with either 3 quotation marks on either side
of the public variable or with single commas inside of the quotations
as follows and it updates my records, but I don't know what it is
updating it to. After the sql statement is ran, I go back into the
table and the field LOOKS blank - but my query that searches for all
records where this field is null filters out the record. What am I
doing wrong?
Click to expand...
With the triple quotes.....
Click to expand...
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & ThisUser & """, [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Click to expand...
With the commas....
Click to expand...
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = '" & ThisUser & "', [Pending
Tickets].SelectTicket = " & _
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Click to expand...
Click to expand...

hmm...I have the following line in a non-form module.

Public ThisUser As String

I use the variable throughout the application to determine whether a
user has access to edit certain records, delete certain records, open
forms, and I haven't had any problems with it thus far.
Click to expand...
 
K

Klatuu

I am assuing the field thisuser is a text field. Text fields have to be
enclosed in either single or double quotes. I always use double qoutes to
avoid problems with names like O'Connor which would cause a problem.

To put a double quote in a string, you use a pair of double quotes, so on
the left side, the first two quotes create a qoute and the third delimits
the string and on the right side, the first quote delimits the string and
the next two creates the other quote

You have to have the variable outside the qoutes

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending
Tickets].SelectTicket
= " &
_


On Nov 14, 9:46 am, "Ken Snell \(MVP\)"
Concateneate the value of the public variable into the SQL string:
Click to expand...
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Click to expand...

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
news:1004437b-0ba1-4a68-b4ef-b2cd8e72919f@v13g2000pro.googlegroups.com...
Click to expand...
Hi,
Click to expand...

I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How
should
I do this? I don't think I can incorporate a public variable into
an
SQL statement. I used the value on another form because initially
the
user signs into the application and this value on another form is
set
to my public variable. My public variable is called 'ThisUser' and
is
used throughout the application for restricting/granting
permissions,
etc.
Click to expand...
Any help would be greatly appreciated.
Click to expand...
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
Click to expand...
End Sub
Click to expand...
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Click to expand...
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
Click to expand...

If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...

MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
Click to expand...
 
N

nouveauricheinvestments

I am assuing the field thisuser is a text field. Text fields have to be
enclosed in either single or double quotes. I always use double qoutes to
avoid problems with names like O'Connor which would cause a problem.

To put a double quote in a string, you use a pair of double quotes, so on
the left side, the first two quotes create a qoute and the third delimits
the string and on the right side, the first quote delimits the string and
the next two creates the other quote


You have to have the variable outside the qoutes
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = """ & thisuser & """, [Pending
Tickets].SelectTicket
= " &
_

On Nov 14, 9:46 am, "Ken Snell \(MVP\)"
Concateneate the value of the public variable into the SQL string:
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation='" & _
NameOfYourPublicVariable & "';"
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

Hi,
I am using the following event code in my after update code for a
combo box on my form. As you can see, I am setting [Pending
Tickets].ResearchedBy = a value on another form. I would like to
actually set this ResearchedBy field = a public variable. How
should
I do this? I don't think I can incorporate a public variable into
an
SQL statement. I used the value on another form because initially
the
user signs into the application and this value on another form is
set
to my public variable. My public variable is called 'ThisUser' and
is
used throughout the application for restricting/granting
permissions,
etc.
Any help would be greatly appreciated.
Private Sub Research_AfterUpdate()
Dim MySQL As String
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = [Forms]![frmSplash]![User] WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
End Sub
When I try that, I get a pop up that asks me for the value of thisuser
(my public variable). This is the code I have.
Private Sub Research_AfterUpdate()
Dim MySQL As String
DoCmd.SetWarnings False
Select Case Me!Research.Text
Case Is = "Yes"
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = thisuser, [Pending Tickets].SelectTicket = " &
_
"False WHERE " & _
"[Pending Tickets].Explanation=[Forms]!
[ViewSelectedOpenTicket]![Explanation];"
DoCmd.RunSQL MySQL
Case Is = "No"
MsgBox "This ticket will remain in the Open Tickets view and
trade specialists will continue to see " & _
"it. If you plan to research and resolve this ticket, it
is important you mark this question " & _
"Yes.", vbCritical, "Alert"
End Select
DoCmd.SetWarnings True
End Sub
Click to expand...
Click to expand...
If you don't mind me asking, why are you using three quotes? Why
would it not be the following? How is vb interpreting this that
requires three quotes...
Click to expand...
MySQL = "UPDATE [Pending Tickets] SET [Pending
Tickets].ResearchedBy = " & thisuser & ", [Pending
Tickets].SelectTicket
Click to expand...
Click to expand...

I'm sorry I won't be able to get to this until next week. I will
however let you know how it goes Monday morning when I get back to it.

Thanks for your help guys. Sorry, busy day.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a variable in Recordset.Find To lookup an ID in a table 2
Using a calculated Variable from a function in another function 7
how do i set an Access table field equal to a VB variable? 5
Passing a Date Variable from Code to Update Query in a Loop 7
Can I update an option group in a form based on query? 2
Using global variable in update query 3
Can I reference a form field to get a variable and use it in a query? 1
Change textbox/combobox colors with a procedure 9

Top