UPDATE - missing operator in expression

[AccessAddict]

Hello All,

Hoping someone can help me, I am in the learning stages of SQL and VBA.
I've been using Access for quite some time now (since 1998) and have limited
knowledge of SQL/VBA. Thus, HELP.

I created an unbound form, full of text boxes (named txtPO1_1/2/2005,
txtPO1_1/30/2005, etc) across the form and the boxes going down which
increment according to the row (Example for first column/first row =
txtPO1_1/2/2005, first column/second row = txtPO2_1/2/2005, first
column/third row = txtPO3_1/2/2005, etc.). The first column increments its
name (down the rows) accordingly - txtITEM_1, txtITEM_2, etc. A combo box
allows the user to choose a group and the form fills with information from
the "many-side" table for each of the items in the group specified. Then a
button clears the form for the next group.

I now want to update this information (which I want the user to be able to
change) to the "many-side" table.

I am running into a problem with either the SQL or VBA syntax or my
knowledge of it. I'm attaching my code, perhaps someone can help me figure
this out?

The error states "syntax error (missing operator) in query expression
'Forms!Group Test Update Matrix 1.Me"txtPO1_1/2/2005" > 0'."

I am trying to get the update to find the textbox named txtPO1_1/2/2005 and
update (according to the textbox it finds - in this case, #1/2/2005#) to the
field [Post Off Start Date] in the table called [N POST OFF TABLE test] and
to update the textbox contents (value) the field [Post Off Price] in the same
table.

The problem is in the "DoCmd.RunSQL" line, nested in the middle of the
innermost "Do Until" line.

Thank you in advance (for even reading all of this - I know its long).

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

Dim intITEM As Integer
Dim hldITEM As String

Dim intGroup As Integer
Dim hldGroup As String


Dim hldPOST As String
Dim intPO As Integer

Dim cboGroup As String
cboGroup = Me.cboSUPSUBFL.Value

strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Item Description],[Post
Off Price],[N POST OFF TABLE test].[Item #] FROM [N ITEM PRICING TABLE
test]INNER JOIN[N Post Off Table test] ON [N item Pricing Table test].[Item
#]=[N POST OFF TABLE test].[Item #]where [N ITEM PRICING TABLE
test].[SUPSUBFL] = '" & cboGroup & "' ORDER BY [SUPSUBFL],[N Post Off Table
test].[Item #],[Post Off Start Date]"


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst


With rs

intGroup = 0
intITEM = 0
intPO = 0

hldGroup = cboGroup
'hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until hldGroup <> .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

intITEM = intITEM + 1


Do Until .EOF Or hldGroup <> .Fields("SUPSUBFL")


hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM & " / " &
..Fields("[item description]")
intPO = intPO + 1

Do Until hldITEM <> .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") < #6/5/2005#)
Then

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intPO & "_" & hldPOST) = .Fields("[Post
Off Price]")

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

End If

.MoveNext

Loop
intITEM = intITEM + 1
Loop

Loop

.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub

 

[OfficeDev18 via AccessMonster.com]

Hello All,

Hoping someone can help me, I am in the learning stages of SQL and VBA.
I've been using Access for quite some time now (since 1998) and have limited
knowledge of SQL/VBA. Thus, HELP.

I created an unbound form, full of text boxes (named txtPO1_1/2/2005,
txtPO1_1/30/2005, etc) across the form and the boxes going down which
increment according to the row (Example for first column/first row =
txtPO1_1/2/2005, first column/second row = txtPO2_1/2/2005, first
column/third row = txtPO3_1/2/2005, etc.). The first column increments its
name (down the rows) accordingly - txtITEM_1, txtITEM_2, etc. A combo box
allows the user to choose a group and the form fills with information from
the "many-side" table for each of the items in the group specified. Then a
button clears the form for the next group.

I now want to update this information (which I want the user to be able to
change) to the "many-side" table.

I am running into a problem with either the SQL or VBA syntax or my
knowledge of it. I'm attaching my code, perhaps someone can help me figure
this out?

The error states "syntax error (missing operator) in query expression
'Forms!Group Test Update Matrix 1.Me"txtPO1_1/2/2005" > 0'."

I am trying to get the update to find the textbox named txtPO1_1/2/2005 and
update (according to the textbox it finds - in this case, #1/2/2005#) to the
field [Post Off Start Date] in the table called [N POST OFF TABLE test] and
to update the textbox contents (value) the field [Post Off Price] in the same
table.

The problem is in the "DoCmd.RunSQL" line, nested in the middle of the
innermost "Do Until" line.

Thank you in advance (for even reading all of this - I know its long).

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

Dim intITEM As Integer
Dim hldITEM As String

Dim intGroup As Integer
Dim hldGroup As String

Dim hldPOST As String
Dim intPO As Integer

Dim cboGroup As String
cboGroup = Me.cboSUPSUBFL.Value

strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Item Description],[Post
Off Price],[N POST OFF TABLE test].[Item #] FROM [N ITEM PRICING TABLE
test]INNER JOIN[N Post Off Table test] ON [N item Pricing Table test].[Item
#]=[N POST OFF TABLE test].[Item #]where [N ITEM PRICING TABLE
test].[SUPSUBFL] = '" & cboGroup & "' ORDER BY [SUPSUBFL],[N Post Off Table
test].[Item #],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

With rs

intGroup = 0
intITEM = 0
intPO = 0

hldGroup = cboGroup
'hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until hldGroup <> .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

intITEM = intITEM + 1


Do Until .EOF Or hldGroup <> .Fields("SUPSUBFL")


hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM & " / " &
.Fields("[item description]")
intPO = intPO + 1

Do Until hldITEM <> .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") < #6/5/2005#)
Then

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intPO & "_" & hldPOST) = .Fields("[Post
Off Price]")

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

End If

.MoveNext

Loop
intITEM = intITEM + 1
Loop

Loop

.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub

I think I've found the problem. Try changing the SQL statement from:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

to:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms![Group Test Update Matrix 1]!txtPO1_1.Value = #1/2/2005#;")

All I did was put brackets around the form name (it contains blank spaces; it
needs brackets), remove the extraneous "Me." designation (you're already
identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

 

[AccessAddict]

Thank you Sam for your quick reply.

I had the brackets around the form name at one time and it didn't help but I
still had the "me". Let me try it again. I'll post back to let you know
what happens.

Hello All,

Hoping someone can help me, I am in the learning stages of SQL and VBA.
I've been using Access for quite some time now (since 1998) and have limited
knowledge of SQL/VBA. Thus, HELP.

I created an unbound form, full of text boxes (named txtPO1_1/2/2005,
txtPO1_1/30/2005, etc) across the form and the boxes going down which
increment according to the row (Example for first column/first row =
txtPO1_1/2/2005, first column/second row = txtPO2_1/2/2005, first
column/third row = txtPO3_1/2/2005, etc.). The first column increments its
name (down the rows) accordingly - txtITEM_1, txtITEM_2, etc. A combo box
allows the user to choose a group and the form fills with information from
the "many-side" table for each of the items in the group specified. Then a
button clears the form for the next group.

I now want to update this information (which I want the user to be able to
change) to the "many-side" table.

I am running into a problem with either the SQL or VBA syntax or my
knowledge of it. I'm attaching my code, perhaps someone can help me figure
this out?

The error states "syntax error (missing operator) in query expression
'Forms!Group Test Update Matrix 1.Me"txtPO1_1/2/2005" > 0'."

I am trying to get the update to find the textbox named txtPO1_1/2/2005 and
update (according to the textbox it finds - in this case, #1/2/2005#) to the
field [Post Off Start Date] in the table called [N POST OFF TABLE test] and
to update the textbox contents (value) the field [Post Off Price] in the same
table.

The problem is in the "DoCmd.RunSQL" line, nested in the middle of the
innermost "Do Until" line.

Thank you in advance (for even reading all of this - I know its long).

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

Dim intITEM As Integer
Dim hldITEM As String

Dim intGroup As Integer
Dim hldGroup As String

Dim hldPOST As String
Dim intPO As Integer

Dim cboGroup As String
cboGroup = Me.cboSUPSUBFL.Value

strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Item Description],[Post
Off Price],[N POST OFF TABLE test].[Item #] FROM [N ITEM PRICING TABLE
test]INNER JOIN[N Post Off Table test] ON [N item Pricing Table test].[Item
#]=[N POST OFF TABLE test].[Item #]where [N ITEM PRICING TABLE
test].[SUPSUBFL] = '" & cboGroup & "' ORDER BY [SUPSUBFL],[N Post Off Table
test].[Item #],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

With rs

intGroup = 0
intITEM = 0
intPO = 0

hldGroup = cboGroup
'hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until hldGroup <> .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

intITEM = intITEM + 1


Do Until .EOF Or hldGroup <> .Fields("SUPSUBFL")


hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM & " / " &
.Fields("[item description]")
intPO = intPO + 1

Do Until hldITEM <> .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") < #6/5/2005#)
Then

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intPO & "_" & hldPOST) = .Fields("[Post
Off Price]")

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

End If

.MoveNext

Loop
intITEM = intITEM + 1
Loop

Loop

.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub

I think I've found the problem. Try changing the SQL statement from:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

to:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms![Group Test Update Matrix 1]!txtPO1_1.Value = #1/2/2005#;")

All I did was put brackets around the form name (it contains blank spaces; it
needs brackets), remove the extraneous "Me." designation (you're already
identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

 

[AccessAddict]

Sorry Sam...

I just noticed (whilst typing) that (where you cleaned up the end of my SQL
statement) "txtPO1_1.Value = #1/2/2005#;")" there are a couple misconceptions:

txtPO1_ is okay but the "1.Value = #1/2/2005#" is not. I do not want this
date to go into that text box-in fact, it is a number that goes into that box
- not a date. Each box is named by the date that I want the actual field [N
POST OFF TABLE].[Post Off Start Date] in the table to contain.

Thus, the reference to the text box "txtPO1_1/2/2005" will update to the [N
Post Off Table].[Post Off Start Date] the date value of #1/2/2005# (which I
am hardcoding into the SQL string AND the VALUE of "txtPO1_1/2/2005" (which
is a number not a date) will update to the field [Post Off Price].

Does this make any sense? The "N POST OFF TABLE" has a combined key field
of "ITEM #" and "Post Off Start Date" together they must be unique.

Hello All,

Hoping someone can help me, I am in the learning stages of SQL and VBA.
I've been using Access for quite some time now (since 1998) and have limited
knowledge of SQL/VBA. Thus, HELP.

I created an unbound form, full of text boxes (named txtPO1_1/2/2005,
txtPO1_1/30/2005, etc) across the form and the boxes going down which
increment according to the row (Example for first column/first row =
txtPO1_1/2/2005, first column/second row = txtPO2_1/2/2005, first
column/third row = txtPO3_1/2/2005, etc.). The first column increments its
name (down the rows) accordingly - txtITEM_1, txtITEM_2, etc. A combo box
allows the user to choose a group and the form fills with information from
the "many-side" table for each of the items in the group specified. Then a
button clears the form for the next group.

I now want to update this information (which I want the user to be able to
change) to the "many-side" table.

I am running into a problem with either the SQL or VBA syntax or my
knowledge of it. I'm attaching my code, perhaps someone can help me figure
this out?

The error states "syntax error (missing operator) in query expression
'Forms!Group Test Update Matrix 1.Me"txtPO1_1/2/2005" > 0'."

I am trying to get the update to find the textbox named txtPO1_1/2/2005 and
update (according to the textbox it finds - in this case, #1/2/2005#) to the
field [Post Off Start Date] in the table called [N POST OFF TABLE test] and
to update the textbox contents (value) the field [Post Off Price] in the same
table.

The problem is in the "DoCmd.RunSQL" line, nested in the middle of the
innermost "Do Until" line.

Thank you in advance (for even reading all of this - I know its long).

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

Dim intITEM As Integer
Dim hldITEM As String

Dim intGroup As Integer
Dim hldGroup As String

Dim hldPOST As String
Dim intPO As Integer

Dim cboGroup As String
cboGroup = Me.cboSUPSUBFL.Value

strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Item Description],[Post
Off Price],[N POST OFF TABLE test].[Item #] FROM [N ITEM PRICING TABLE
test]INNER JOIN[N Post Off Table test] ON [N item Pricing Table test].[Item
#]=[N POST OFF TABLE test].[Item #]where [N ITEM PRICING TABLE
test].[SUPSUBFL] = '" & cboGroup & "' ORDER BY [SUPSUBFL],[N Post Off Table
test].[Item #],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

With rs

intGroup = 0
intITEM = 0
intPO = 0

hldGroup = cboGroup
'hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until hldGroup <> .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

intITEM = intITEM + 1


Do Until .EOF Or hldGroup <> .Fields("SUPSUBFL")


hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM & " / " &
.Fields("[item description]")
intPO = intPO + 1

Do Until hldITEM <> .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") < #6/5/2005#)
Then

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intPO & "_" & hldPOST) = .Fields("[Post
Off Price]")

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

End If

.MoveNext

Loop
intITEM = intITEM + 1
Loop

Loop

.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub

I think I've found the problem. Try changing the SQL statement from:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

to:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms![Group Test Update Matrix 1]!txtPO1_1.Value = #1/2/2005#;")

All I did was put brackets around the form name (it contains blank spaces; it
needs brackets), remove the extraneous "Me." designation (you're already
identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

 

[AccessAddict]

One more thing...I typed a version of our SQL string that let the code
through - I then realized that I am updating in the wrong place (I should
have known that! sheesh)
They have to see what's there before they change it. I put this code in a
button for the saving a record option:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 ")

I want the form to fill, the user to review, the user to change as needed
and then press the button to update. It does not update to the table.

Sorry Sam...

I just noticed (whilst typing) that (where you cleaned up the end of my SQL
statement) "txtPO1_1.Value = #1/2/2005#;")" there are a couple misconceptions:

txtPO1_ is okay but the "1.Value = #1/2/2005#" is not. I do not want this
date to go into that text box-in fact, it is a number that goes into that box
- not a date. Each box is named by the date that I want the actual field [N
POST OFF TABLE].[Post Off Start Date] in the table to contain.

Thus, the reference to the text box "txtPO1_1/2/2005" will update to the [N
Post Off Table].[Post Off Start Date] the date value of #1/2/2005# (which I
am hardcoding into the SQL string AND the VALUE of "txtPO1_1/2/2005" (which
is a number not a date) will update to the field [Post Off Price].

Does this make any sense? The "N POST OFF TABLE" has a combined key field
of "ITEM #" and "Post Off Start Date" together they must be unique.

Hello All,

Hoping someone can help me, I am in the learning stages of SQL and VBA.
I've been using Access for quite some time now (since 1998) and have limited
knowledge of SQL/VBA. Thus, HELP.

I created an unbound form, full of text boxes (named txtPO1_1/2/2005,
txtPO1_1/30/2005, etc) across the form and the boxes going down which
increment according to the row (Example for first column/first row =
txtPO1_1/2/2005, first column/second row = txtPO2_1/2/2005, first
column/third row = txtPO3_1/2/2005, etc.). The first column increments its
name (down the rows) accordingly - txtITEM_1, txtITEM_2, etc. A combo box
allows the user to choose a group and the form fills with information from
the "many-side" table for each of the items in the group specified. Then a
button clears the form for the next group.

I now want to update this information (which I want the user to be able to
change) to the "many-side" table.

I am running into a problem with either the SQL or VBA syntax or my
knowledge of it. I'm attaching my code, perhaps someone can help me figure
this out?

The error states "syntax error (missing operator) in query expression
'Forms!Group Test Update Matrix 1.Me"txtPO1_1/2/2005" > 0'."

I am trying to get the update to find the textbox named txtPO1_1/2/2005 and
update (according to the textbox it finds - in this case, #1/2/2005#) to the
field [Post Off Start Date] in the table called [N POST OFF TABLE test] and
to update the textbox contents (value) the field [Post Off Price] in the same
table.

The problem is in the "DoCmd.RunSQL" line, nested in the middle of the
innermost "Do Until" line.

Thank you in advance (for even reading all of this - I know its long).

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

Dim intITEM As Integer
Dim hldITEM As String

Dim intGroup As Integer
Dim hldGroup As String

Dim hldPOST As String
Dim intPO As Integer

Dim cboGroup As String
cboGroup = Me.cboSUPSUBFL.Value

strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Item Description],[Post
Off Price],[N POST OFF TABLE test].[Item #] FROM [N ITEM PRICING TABLE
test]INNER JOIN[N Post Off Table test] ON [N item Pricing Table test].[Item
#]=[N POST OFF TABLE test].[Item #]where [N ITEM PRICING TABLE
test].[SUPSUBFL] = '" & cboGroup & "' ORDER BY [SUPSUBFL],[N Post Off Table
test].[Item #],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

With rs

intGroup = 0
intITEM = 0
intPO = 0

hldGroup = cboGroup
'hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until hldGroup <> .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

intITEM = intITEM + 1


Do Until .EOF Or hldGroup <> .Fields("SUPSUBFL")


hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM & " / " &
.Fields("[item description]")
intPO = intPO + 1

Do Until hldITEM <> .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") < #6/5/2005#)
Then

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intPO & "_" & hldPOST) = .Fields("[Post
Off Price]")

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

End If

.MoveNext

Loop
intITEM = intITEM + 1
Loop

Loop

.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub

I think I've found the problem. Try changing the SQL statement from:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

to:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms![Group Test Update Matrix 1]!txtPO1_1.Value = #1/2/2005#;")

All I did was put brackets around the form name (it contains blank spaces; it
needs brackets), remove the extraneous "Me." designation (you're already
identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

 

[OfficeDev18 via AccessMonster.com]

One more thing...I typed a version of our SQL string that let the code
through - I then realized that I am updating in the wrong place (I should
have known that! sheesh)
They have to see what's there before they change it. I put this code in a
button for the saving a record option:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]![txtPO1_1/2/2005] > 0 ")

I want the form to fill, the user to review, the user to change as needed
and then press the button to update. It does not update to the table.

Sorry Sam...

[quoted text clipped - 168 lines]

Sorry for the misunderstanding, AA. However, I can't figure out why your
fixed-up SQL statement doesn't work. It looks correct from here.

I have two suggestions. 1 - try adding brackets around the text box name
txtPO1_1/2/2005 (shown above). 2 - is the text box [Post Off Start Date] part
of your form's record source? That might affect it. You may have to either
close the form or go to a different record before the display updates
correctly.

 

[AccessAddict]

I added the last part of the statement and now get an error. This is the
code behind the button (it doesn't work and gives me the error -

"Sytax error (missing operator) in query expression 'Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value'."

DoCmd.RunSQL "UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value;"

One more thing...I typed a version of our SQL string that let the code
through - I then realized that I am updating in the wrong place (I should
have known that! sheesh)
They have to see what's there before they change it. I put this code in a
button for the saving a record option:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 ")

I want the form to fill, the user to review, the user to change as needed
and then press the button to update. It does not update to the table.

Sorry Sam...

I just noticed (whilst typing) that (where you cleaned up the end of my SQL
statement) "txtPO1_1.Value = #1/2/2005#;")" there are a couple misconceptions:

txtPO1_ is okay but the "1.Value = #1/2/2005#" is not. I do not want this
date to go into that text box-in fact, it is a number that goes into that box
- not a date. Each box is named by the date that I want the actual field [N
POST OFF TABLE].[Post Off Start Date] in the table to contain.

Thus, the reference to the text box "txtPO1_1/2/2005" will update to the [N
Post Off Table].[Post Off Start Date] the date value of #1/2/2005# (which I
am hardcoding into the SQL string AND the VALUE of "txtPO1_1/2/2005" (which
is a number not a date) will update to the field [Post Off Price].

Does this make any sense? The "N POST OFF TABLE" has a combined key field
of "ITEM #" and "Post Off Start Date" together they must be unique.

AccessAddict wrote:
Hello All,

Hoping someone can help me, I am in the learning stages of SQL and VBA.
I've been using Access for quite some time now (since 1998) and have limited
knowledge of SQL/VBA. Thus, HELP.

I created an unbound form, full of text boxes (named txtPO1_1/2/2005,
txtPO1_1/30/2005, etc) across the form and the boxes going down which
increment according to the row (Example for first column/first row =
txtPO1_1/2/2005, first column/second row = txtPO2_1/2/2005, first
column/third row = txtPO3_1/2/2005, etc.). The first column increments its
name (down the rows) accordingly - txtITEM_1, txtITEM_2, etc. A combo box
allows the user to choose a group and the form fills with information from
the "many-side" table for each of the items in the group specified. Then a
button clears the form for the next group.

I now want to update this information (which I want the user to be able to
change) to the "many-side" table.

I am running into a problem with either the SQL or VBA syntax or my
knowledge of it. I'm attaching my code, perhaps someone can help me figure
this out?

The error states "syntax error (missing operator) in query expression
'Forms!Group Test Update Matrix 1.Me"txtPO1_1/2/2005" > 0'."

I am trying to get the update to find the textbox named txtPO1_1/2/2005 and
update (according to the textbox it finds - in this case, #1/2/2005#) to the
field [Post Off Start Date] in the table called [N POST OFF TABLE test] and
to update the textbox contents (value) the field [Post Off Price] in the same
table.

The problem is in the "DoCmd.RunSQL" line, nested in the middle of the
innermost "Do Until" line.

Thank you in advance (for even reading all of this - I know its long).

Private Sub UpdateGroup_Click()
On Error GoTo Err_UpdateGroup_Click

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String

Dim intITEM As Integer
Dim hldITEM As String

Dim intGroup As Integer
Dim hldGroup As String

Dim hldPOST As String
Dim intPO As Integer

Dim cboGroup As String
cboGroup = Me.cboSUPSUBFL.Value

strSQL = "SELECT [SUPSUBFL],[Post Off Start Date],[Item Description],[Post
Off Price],[N POST OFF TABLE test].[Item #] FROM [N ITEM PRICING TABLE
test]INNER JOIN[N Post Off Table test] ON [N item Pricing Table test].[Item
#]=[N POST OFF TABLE test].[Item #]where [N ITEM PRICING TABLE
test].[SUPSUBFL] = '" & cboGroup & "' ORDER BY [SUPSUBFL],[N Post Off Table
test].[Item #],[Post Off Start Date]"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst

With rs

intGroup = 0
intITEM = 0
intPO = 0

hldGroup = cboGroup
'hldGroup = .Fields("SUPSUBFL")
hldITEM = .Fields("[Item #]")


Do Until hldGroup <> .Fields("SUPSUBFL") Or .EOF

Me("txtGroup") = hldGroup

intGroup = intGroup + 1

intITEM = intITEM + 1


Do Until .EOF Or hldGroup <> .Fields("SUPSUBFL")


hldITEM = .Fields("[Item #]")

Me("txtITEM" & intITEM) = hldITEM & " / " &
.Fields("[item description]")
intPO = intPO + 1

Do Until hldITEM <> .Fields("Item #") Or .EOF

If (.Fields("[Post Off Start Date]") < #6/5/2005#)
Then

hldPOST = Str$(.Fields("[Post Off Start Date]"))

Me("txtPO" & intPO & "_" & hldPOST) = .Fields("[Post
Off Price]")

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

End If

.MoveNext

Loop
intITEM = intITEM + 1
Loop

Loop

.Close
End With
Set rs = Nothing
Set db = Nothing

Exit_UpdateGroup_Click:
Exit Sub

Err_UpdateGroup_Click:
MsgBox Err.Description
Resume Exit_UpdateGroup_Click
End Sub
I think I've found the problem. Try changing the SQL statement from:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms!Group Test Update Matrix 1.Me""txtPO1_1" & Chr(47) & "2" & Chr(47) &
"2005"" > 0 ")

to:

DoCmd.RunSQL ("UPDATE [N Post Off Table test] INNER JOIN [N
ITEM PRICING TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST
OFF TABLE test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE
Forms![Group Test Update Matrix 1]!txtPO1_1.Value = #1/2/2005#;")

All I did was put brackets around the form name (it contains blank spaces; it
needs brackets), remove the extraneous "Me." designation (you're already
identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

 

[OfficeDev18 via AccessMonster.com]

I added the last part of the statement and now get an error. This is the
code behind the button (it doesn't work and gives me the error -

"Sytax error (missing operator) in query expression 'Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value'."

DoCmd.RunSQL "UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value;"

One more thing...I typed a version of our SQL string that let the code
through - I then realized that I am updating in the wrong place (I should

[quoted text clipped - 182 lines]

I think the problem is that you have a SET after starting the WHERE clause. I
don't think that's legal. I think what yuo want is:

SET [Post Off Start Date] = #1/2/2005#, [Post Off Price] = Forms![Group Test
Update Matrix 1]! [txtPO1_1/2/2005].value WHERE Forms![Group Test Update
Matrix 1]![txtPO1_1/2/2005] > 0

By the way, FYI, when I have to create dicey queries in my VBA, what I
usually do is create them in Access' built-in QBE (query by example) grid.
Only after I test the hell out of it do I copy the SQL and paste it in a
DoCmd.RunSQL statement. Can you do that without too much damage? It just
might prove beneficial.

 

[AccessAddict]

Sam,

I didn't realize that you had responded I must have forgotten to check the
box to "notify me..." and when I didn't get a message...just assumed...you
hadn't..."I know, never assume!"
Newest command behind the button (that doesn't work)...

DoCmd.RunSQL "UPDATE from INNER JOIN Forms![Group Test Update Matrix 1] on
Forms![Group Test Update Matrix 1]![txtITEM1].Value = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# and [Post Off
Price]=Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value WHERE
Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value <> 0 ;"

I get through the VBA screen with no error, but when I try to run it I get a
syntax error...SQL I'm assuming...whoops, there I go again...

Also, I have been wondering about the QBE grid. I don't know where to find
it in Access.
It was mentioned in something I read, as well. Can you direct me how to get
there? I have looked and don't know where to find it.

Thanks for all your help.

....Donna

I added the last part of the statement and now get an error. This is the
code behind the button (it doesn't work and gives me the error -

"Sytax error (missing operator) in query expression 'Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value'."

DoCmd.RunSQL "UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value;"

One more thing...I typed a version of our SQL string that let the code
through - I then realized that I am updating in the wrong place (I should

[quoted text clipped - 182 lines]

identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

I think the problem is that you have a SET after starting the WHERE clause. I
don't think that's legal. I think what yuo want is:

SET [Post Off Start Date] = #1/2/2005#, [Post Off Price] = Forms![Group Test
Update Matrix 1]! [txtPO1_1/2/2005].value WHERE Forms![Group Test Update
Matrix 1]![txtPO1_1/2/2005] > 0

By the way, FYI, when I have to create dicey queries in my VBA, what I
usually do is create them in Access' built-in QBE (query by example) grid.
Only after I test the hell out of it do I copy the SQL and paste it in a
DoCmd.RunSQL statement. Can you do that without too much damage? It just
might prove beneficial.

 

[OfficeDev18 via AccessMonster.com]

Sam,

I didn't realize that you had responded I must have forgotten to check the
box to "notify me..." and when I didn't get a message...just assumed...you
hadn't..."I know, never assume!"
Newest command behind the button (that doesn't work)...

DoCmd.RunSQL "UPDATE from INNER JOIN Forms![Group Test Update Matrix 1] on
Forms![Group Test Update Matrix 1]![txtITEM1].Value = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# and [Post Off
Price]=Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value WHERE
Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value <> 0 ;"

I get through the VBA screen with no error, but when I try to run it I get a
syntax error...SQL I'm assuming...whoops, there I go again...

Also, I have been wondering about the QBE grid. I don't know where to find
it in Access.
It was mentioned in something I read, as well. Can you direct me how to get
there? I have looked and don't know where to find it.

Thanks for all your help.

...Donna
[quoted text clipped - 26 lines]

DoCmd.RunSQL statement. Can you do that without too much damage? It just
might prove beneficial.

The QBE grid? That's simple. Just click on the Query tab and click on <New>
to open up a blank grid. A dialog box will open for you to choose a table.
'X' out of it. In the upper left-corner, you'll see the letters "SQL." Click
on it to open a blank SQL window. Copy and paste your SQL statement into the
window. Remove all double-quotes, and change all single quotes (if there are
any) to double quotes (Query SQL diacritical markings are slightly different
from VBA-based SQL markings). When you're finished formatting the SQL window
properly, click on the "display" button ( where the word "SQL" showed up
before) and select Design. Voila - there's your QBE grid, all populated with
your objects, fields, and code. To test your query, click 'Display' again,
and select 'Datasheet View.' You can edit your query right there in the grid;
no need to return to the SQL window.

Alternatively, if you want to start your query over again, simply select the
tables N Post Off Table test and N ITEM PRICING TABLE test and double-click
them to place them in the window. Build the Join the way you want, change the
query to an UPDATE query... You'll have to experiment to get all this, it
won't happen in five minutes. Believe me, though, it's worth the time in
diamonds.

Moving along... get through the VBA screen with no error, but when I try to
run it I get a syntax error... Donna, don't let VBA fool you. When you're
building SQL in VBA, the editor doesn't do any checking whatsoever. It's all
text at that point. You could type "XYZ" and "123" and it couldn't care less.
Even in the SQL window I mentioned before it won't check syntax until you
click 'Display' to leave the SQL display. Then... it checks everything, and
it benevolently shows you where you syntax errors are. (Unfortunately,
NOTHING shows where logic errors are.)

Now for your SQL statement: 1) UPDATE from INNER JOIN ..... Wha? UPDATE from .
.... what table????... INNER JOIN...
2) You can't use a form as a table/query in a SQL statement. Only a table or
query can be used.

Good luck!

 

[AccessAddict]

My assumption was, again, wrong. The error is actually in a Microsoft Office
Access box, if that helps.

Sam,

I didn't realize that you had responded I must have forgotten to check the
box to "notify me..." and when I didn't get a message...just assumed...you
hadn't..."I know, never assume!"
Newest command behind the button (that doesn't work)...

DoCmd.RunSQL "UPDATE from INNER JOIN Forms![Group Test Update Matrix 1] on
Forms![Group Test Update Matrix 1]![txtITEM1].Value = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# and [Post Off
Price]=Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value WHERE
Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value <> 0 ;"

I get through the VBA screen with no error, but when I try to run it I get a
syntax error...SQL I'm assuming...whoops, there I go again...

Also, I have been wondering about the QBE grid. I don't know where to find
it in Access.
It was mentioned in something I read, as well. Can you direct me how to get
there? I have looked and don't know where to find it.

Thanks for all your help.

...Donna

I added the last part of the statement and now get an error. This is the
code behind the button (it doesn't work and gives me the error -

"Sytax error (missing operator) in query expression 'Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value'."

DoCmd.RunSQL "UPDATE [N Post Off Table test] INNER JOIN [N ITEM PRICING
TABLE test] on [N ITEM PRICING TABLE test].[Item #] = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# WHERE Forms![Group Test
Update Matrix 1]!txtPO1_1/2/2005 > 0 and SET [Post Off Price] = Forms![Group
Test Update Matrix 1]!txtPO1_1/2/2005.value;"

One more thing...I typed a version of our SQL string that let the code
through - I then realized that I am updating in the wrong place (I should
[quoted text clipped - 182 lines]
identifying the form), and 'cleaned up' the end of the SQL statement. I also
added a semi-colon at the end for good measure.

I think the problem is that you have a SET after starting the WHERE clause. I
don't think that's legal. I think what yuo want is:

SET [Post Off Start Date] = #1/2/2005#, [Post Off Price] = Forms![Group Test
Update Matrix 1]! [txtPO1_1/2/2005].value WHERE Forms![Group Test Update
Matrix 1]![txtPO1_1/2/2005] > 0

By the way, FYI, when I have to create dicey queries in my VBA, what I
usually do is create them in Access' built-in QBE (query by example) grid.
Only after I test the hell out of it do I copy the SQL and paste it in a
DoCmd.RunSQL statement. Can you do that without too much damage? It just
might prove beneficial.

 

[AccessAddict]

Sam...
Thank you so much for all of your instruction on the QBE...I will definately
try it.

I was afraid that updating from a form would be my problem....I have tried
unsuccessfully, to update from a crosstab, a pivot table, and now an unbound
form using SQL/VBA (which has been like doing it manually after creating all
the text boxes - and that was just for the first part of the year - I planned
on duplicating it with a > #6/5/2005 condition for the second half) plus, I
knew I would have to write individual code each individual the update from
the text boxes (alot of work, but worth it if it worked) - it all seems
impossible at this point.

I am not sure if you know what I am trying to do (you can look up the other
threads from me - they all have to do with my struggle through this issue).

The user has requested that I allow changes in the data of the Post Off
Table for specified groups (since they all have similar data structures).
They want me to take a table with fields, item #, and Post Off Start Date and
SHOW IT as a crosstab or pivot table view but let them change and add data to
the view and update these changes to the table. I have gone in
circles...because after exploring all of these methods...I have still come to
the same conclusion...that you can't update from a form....

Unless you have another idea...?

Early on, I did get the crosstab to divide out the dates into a crosstab and
send it out to EXCEL and bring it back as a make a table then update to this
table and send it out again and so on and so forth. It worked if you change
an amount that is already there...but it wouldn't let you update to a POST
OFF START DATE that had no data in it currently.

I'm afraid I am probably confusing you, as I am also...do you have any other
suggested ways of accomplishing this? I am frustrated because I really
thought that with programming, you could do anything and have been working
under this premise.
I have put more time into this task then any others since beginning this
project in January. Too much time...only to get nowhere.

Thanks for letting me vent....

Donna

Sam,

I didn't realize that you had responded I must have forgotten to check the
box to "notify me..." and when I didn't get a message...just assumed...you
hadn't..."I know, never assume!"
Newest command behind the button (that doesn't work)...

DoCmd.RunSQL "UPDATE from INNER JOIN Forms![Group Test Update Matrix 1] on
Forms![Group Test Update Matrix 1]![txtITEM1].Value = [N POST OFF TABLE
test].[Item #] SET [Post Off Start Date] = #1/2/2005# and [Post Off
Price]=Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value WHERE
Forms![Group Test Update Matrix 1]![txtPO1_1/2/2005].value <> 0 ;"

I get through the VBA screen with no error, but when I try to run it I get a
syntax error...SQL I'm assuming...whoops, there I go again...

Also, I have been wondering about the QBE grid. I don't know where to find
it in Access.
It was mentioned in something I read, as well. Can you direct me how to get
there? I have looked and don't know where to find it.

Thanks for all your help.

...Donna

I added the last part of the statement and now get an error. This is the
code behind the button (it doesn't work and gives me the error -

[quoted text clipped - 26 lines]

DoCmd.RunSQL statement. Can you do that without too much damage? It just
might prove beneficial.

The QBE grid? That's simple. Just click on the Query tab and click on <New>
to open up a blank grid. A dialog box will open for you to choose a table.
'X' out of it. In the upper left-corner, you'll see the letters "SQL." Click
on it to open a blank SQL window. Copy and paste your SQL statement into the
window. Remove all double-quotes, and change all single quotes (if there are
any) to double quotes (Query SQL diacritical markings are slightly different
from VBA-based SQL markings). When you're finished formatting the SQL window
properly, click on the "display" button ( where the word "SQL" showed up
before) and select Design. Voila - there's your QBE grid, all populated with
your objects, fields, and code. To test your query, click 'Display' again,
and select 'Datasheet View.' You can edit your query right there in the grid;
no need to return to the SQL window.

Alternatively, if you want to start your query over again, simply select the
tables N Post Off Table test and N ITEM PRICING TABLE test and double-click
them to place them in the window. Build the Join the way you want, change the
query to an UPDATE query... You'll have to experiment to get all this, it
won't happen in five minutes. Believe me, though, it's worth the time in
diamonds.

Moving along... get through the VBA screen with no error, but when I try to
run it I get a syntax error... Donna, don't let VBA fool you. When you're
building SQL in VBA, the editor doesn't do any checking whatsoever. It's all
text at that point. You could type "XYZ" and "123" and it couldn't care less.
Even in the SQL window I mentioned before it won't check syntax until you
click 'Display' to leave the SQL display. Then... it checks everything, and
it benevolently shows you where you syntax errors are. (Unfortunately,
NOTHING shows where logic errors are.)

Now for your SQL statement: 1) UPDATE from INNER JOIN ..... Wha? UPDATE from .
.... what table????... INNER JOIN...
2) You can't use a form as a table/query in a SQL statement. Only a table or
query can be used.

Good luck!

 

[OfficeDev18 via AccessMonster.com]

Sam...
Thank you so much for all of your instruction on the QBE...I will definately
try it.

I was afraid that updating from a form would be my problem....I have tried
unsuccessfully, to update from a crosstab, a pivot table, and now an unbound
form using SQL/VBA (which has been like doing it manually after creating all
the text boxes - and that was just for the first part of the year - I planned
on duplicating it with a > #6/5/2005 condition for the second half) plus, I
knew I would have to write individual code each individual the update from
the text boxes (alot of work, but worth it if it worked) - it all seems
impossible at this point.

I am not sure if you know what I am trying to do (you can look up the other
threads from me - they all have to do with my struggle through this issue).

The user has requested that I allow changes in the data of the Post Off
Table for specified groups (since they all have similar data structures).
They want me to take a table with fields, item #, and Post Off Start Date and
SHOW IT as a crosstab or pivot table view but let them change and add data to
the view and update these changes to the table. I have gone in
circles...because after exploring all of these methods...I have still come to
the same conclusion...that you can't update from a form....

Unless you have another idea...?

Early on, I did get the crosstab to divide out the dates into a crosstab and
send it out to EXCEL and bring it back as a make a table then update to this
table and send it out again and so on and so forth. It worked if you change
an amount that is already there...but it wouldn't let you update to a POST
OFF START DATE that had no data in it currently.

I'm afraid I am probably confusing you, as I am also...do you have any other
suggested ways of accomplishing this? I am frustrated because I really
thought that with programming, you could do anything and have been working
under this premise.
I have put more time into this task then any others since beginning this
project in January. Too much time...only to get nowhere.

Thanks for letting me vent....

Donna
[quoted text clipped - 61 lines]

Good luck!

When I said you can't use a form as a recordset, that only meant the form
itself. You can, however, use the form's RecordSource, whether it be a query
or a single table. That's what you really want, anyway; frankly, that's what
you had all along.

By the way, and please don't be offended by this, while I understand your
frustration, I really don't understand your project. To put this thread to
rest, but to attempt to finish "offline", how about zipping up your form,
together with all pertinent tables and queries, and email it to me (see below)
.. I think I'll be more able to help you that way.

 

[AccessAddict]

Thank you. I agree.

I will send it to you first thing tomorrow. I have backtracked my thoughts,
back to Access and the query. I am now re-inventing the wheel, yet one more
time.

I am back to the basics, query with the two tables you suggested (which I
originally began). See how this goes for the rest of this day...I'll let you
know my progress in the email with the tables and queries tomorrow.

Have a good "rest" of the day!

Donna

Sam...
Thank you so much for all of your instruction on the QBE...I will definately
try it.

I was afraid that updating from a form would be my problem....I have tried
unsuccessfully, to update from a crosstab, a pivot table, and now an unbound
form using SQL/VBA (which has been like doing it manually after creating all
the text boxes - and that was just for the first part of the year - I planned
on duplicating it with a > #6/5/2005 condition for the second half) plus, I
knew I would have to write individual code each individual the update from
the text boxes (alot of work, but worth it if it worked) - it all seems
impossible at this point.

I am not sure if you know what I am trying to do (you can look up the other
threads from me - they all have to do with my struggle through this issue).

The user has requested that I allow changes in the data of the Post Off
Table for specified groups (since they all have similar data structures).
They want me to take a table with fields, item #, and Post Off Start Date and
SHOW IT as a crosstab or pivot table view but let them change and add data to
the view and update these changes to the table. I have gone in
circles...because after exploring all of these methods...I have still come to
the same conclusion...that you can't update from a form....

Unless you have another idea...?

Early on, I did get the crosstab to divide out the dates into a crosstab and
send it out to EXCEL and bring it back as a make a table then update to this
table and send it out again and so on and so forth. It worked if you change
an amount that is already there...but it wouldn't let you update to a POST
OFF START DATE that had no data in it currently.

I'm afraid I am probably confusing you, as I am also...do you have any other
suggested ways of accomplishing this? I am frustrated because I really
thought that with programming, you could do anything and have been working
under this premise.
I have put more time into this task then any others since beginning this
project in January. Too much time...only to get nowhere.

Thanks for letting me vent....

Donna

Sam,

[quoted text clipped - 61 lines]

Good luck!

When I said you can't use a form as a recordset, that only meant the form
itself. You can, however, use the form's RecordSource, whether it be a query
or a single table. That's what you really want, anyway; frankly, that's what
you had all along.

By the way, and please don't be offended by this, while I understand your
frustration, I really don't understand your project. To put this thread to
rest, but to attempt to finish "offline", how about zipping up your form,
together with all pertinent tables and queries, and email it to me (see below)
.. I think I'll be more able to help you that way.

 

[OfficeDev18 via AccessMonster.com]

Thank you. I agree.

I will send it to you first thing tomorrow. I have backtracked my thoughts,
back to Access and the query. I am now re-inventing the wheel, yet one more
time.

I am back to the basics, query with the two tables you suggested (which I
originally began). See how this goes for the rest of this day...I'll let you
know my progress in the email with the tables and queries tomorrow.

Have a good "rest" of the day!

Donna
[quoted text clipped - 54 lines]

together with all pertinent tables and queries, and email it to me (see below)
.. I think I'll be more able to help you that way.

Thanks. You too...

 

[AccessAddict]

Sam...

I looked closer this time and noticed that your email address is not
included in this thread (am I missing it?).
....or is it (e-mail address removed)?

Thank you. I agree.

I will send it to you first thing tomorrow. I have backtracked my thoughts,
back to Access and the query. I am now re-inventing the wheel, yet one more
time.

I am back to the basics, query with the two tables you suggested (which I
originally began). See how this goes for the rest of this day...I'll let you
know my progress in the email with the tables and queries tomorrow.

Have a good "rest" of the day!

Donna

Sam...
Thank you so much for all of your instruction on the QBE...I will definately

[quoted text clipped - 54 lines]

together with all pertinent tables and queries, and email it to me (see below)
.. I think I'll be more able to help you that way.

Thanks. You too...

 

[OfficeDev18 via AccessMonster.com]

Sam...

I looked closer this time and noticed that your email address is not
included in this thread (am I missing it?).
...or is it (e-mail address removed)?

Thank you. I agree.
[quoted text clipped - 16 lines]
.. I think I'll be more able to help you that way.

Thanks. You too...

Click on the '+' sign (signature) of the posting where I made the offer. The
email address is in text format.

 

[SteveS]

PMFJI,

Hi Donna,

I know the code is "in development" but I did notice in "strSQL" you need to
add a space before and after "Inner Join" and before "Where".

It looks like you are using dates as field names???? Or is that only for the
controls on the form?

And in the code, it looks like you are storing multiple peices of data in
one field??


If you don't mind, would you also send the db to me?

limbim53 at yahoo dot com

 

[OfficeDev18 via AccessMonster.com]

My assumption was, again, wrong. The error is actually in a Microsoft Office
Access box, if that helps.

Sam,

[quoted text clipped - 51 lines]

Donna, you didn't send it to me. If you didn't find my email address it's sam
at dynarex dot com. Of course, you have to format the address correctly.