Update query- Access 2010 Win7

[aziska]

Hello,

I'm attempting to perform an update query to a table for the field "periodyear" and "period" using a text box and a list box that live on a form in front of the database. The text box's name is Year_Label and the list boxs name is Quarter_Label. I've had this working at one point where it would update with my entered information but now it brings up a window asking for the field's parameter. When I construct the query, i've used the expression builder and my code in SQL is this:

 UPDATE EQUI_H3_Export_Table SET EQUI_H3_Export_Table.periodyear = [Forms]![Opening Form]![Year_Label], EQUI_H3_Export_Table.period = [Forms]![Opening Form]![Quarter_Label];

What might I be missing to tell the query to take the already entered data rather than re-ask me for it? Thank you so much for your help. Its appreciated in advance.

 

[xenou]

The SQL looks right. How do you run it?

 

[aziska]

I've been trying to run it two different ways; i will either just run the queries seperately as saved queries, or I run them as parts of a macro where they come in as the last two steps. Neither of these ways seems to fix the window pop up.

 

[xenou]

The form is open when you run the code? You could check the value of the form contents with a line of code:

MsgBox "Year Label: " & [Forms]![Opening Form]![Year_Label] & vbNewLine & "Quarter Label: " & [Forms]![Opening Form]![Quarter_Label]
UPDATE EQUI_H3_Export_Table SET EQUI_H3_Export_Table.periodyear = [Forms]![Opening Form]![Year_Label], EQUI_H3_Export_Table.period = [Forms]![Opening Form]![Quarter_Label];

 

[aziska]

The form is open when you run the code? You could check the value of the form contents with a line of code:

MsgBox "Year Label: " & [Forms]![Opening Form]![Year_Label] & vbNewLine & "Quarter Label: " & [Forms]![Opening Form]![Quarter_Label]
UPDATE EQUI_H3_Export_Table SET EQUI_H3_Export_Table.periodyear = [Forms]![Opening Form]![Year_Label], EQUI_H3_Export_Table.period = [Forms]![Opening Form]![Quarter_Label];

Thanks for the help xenou, I appreciate your time with this.

After i put in this code its giving me a window message: "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', OR 'UPDATE'. The form is open when I run this query, if that helps any.

 

[xenou]

It's a puzzle. You should at least get a msgbox with the values from your form, before any messages about errors in the SQL.

Here's more fully fledged test code that I would use. First I would create the SQL statement and inspect it. I'd probably even copy it from out of the immediate window and paste it into a new query in SQL view to see if it works "by hand". Then I'd flip my routine out of test mode and see if it works directly from code:

Sub Foo()
Dim s As String
Const TestMode As String = True


	s = "UPDATE EQUI_H3_Export_Table"
	s = s & " SET EQUI_H3_Export_Table.periodyear = " & [Forms]![Opening Form]![Year_Label] & ","
	s = s & " EQUI_H3_Export_Table.period = " & [Forms]![Opening Form]![Quarter_Label]
	s = s  ";"
	
	'//The SQL that appears in the immediate window, if correct, should be possible to run as is
	Debug.Print s
	
	If TestMode Then
		On Error Resume Next
		CurrentDB.Execute s, dbFailOnError
		If Err Then
			Msgbox Err.Description
		End If
		On Error GoTo 0
	Else
		Msgbox "TEST: SQL Command is: " & s
	End If
	
End Sub

Using the above, what is the SQL text that you create, and does it run in a new query if you paste it in to SQL view? I'm assuming that the data types for year and quarter are numeric.

 

[aziska]

Something must be up with my access or my computer; i copy/pasted the code above into a brand new query in SQL view and it still returned the same error. The year is in the format of a general number but the quarter is from a list with the values of "01", "02", "03", and "04". I'm not sure if access will recognize them as numbers with the leading zeros or if it wants to say they are something else. Could the problem possibly be that i'm trying to update too much at once and I should split the actions up into different steps?

 

[xenou]

Leading zeros are ambiguous (it could be just how they are formatted). But it might mean they are stored as text.

Try this:

Sub Foo()
Dim s As String
Const TestMode As String = True


	s = "UPDATE EQUI_H3_Export_Table"
	s = s & " SET EQUI_H3_Export_Table.periodyear = " & [Forms]![Opening Form]![Year_Label] & ","
	[COLOR="#FF0000"]s = s & " EQUI_H3_Export_Table.period = " & Chr(39) & [Forms]![Opening Form]![Quarter_Label] & Chr(39)[/COLOR]
	s = s  ";"
	
	'//The SQL that appears in the immediate window, if correct, should be possible to run as is
	Debug.Print s
	
	If TestMode Then
		On Error Resume Next
		CurrentDB.Execute s, dbFailOnError
		If Err Then
			Msgbox Err.Description
		End If
		On Error GoTo 0
	Else
		Msgbox "TEST: SQL Command is: " & s
	End If
	
End Sub

That will put single quotes around the quarters, which is how they need to be if they are text values in the criteria.

 

[aziska]

Thanks for all the help xenou. I've tried running all of these code segments and continue to get errors of expected functions (update, delete, etc.) and i've checked into every trouble shooter website i can find as to why i cant update the values of the two boxes into my table, and nothing has solved the issue. So i think i'm going to chalk this one up to Access's win. Mayhap it will work some other day, but I'll just be sure to do more reading and learning to better prepare next time. Thanks again for all your efforts, they are very very much appreciated.

 

[accessHel]

Help

heyy
I am new to access .. I've already done several queries for a certain table (books). and i am able to create forms for them. but what about creating a form for an update query (set.. where..)
any suggestion how to do that? any clarified steps!!
thanks in advance