Updateing records in Access 1

[nospace52]

Using VB6 - I'm trying to update records - There is a data1 and MSFlexGrid on the Form.
When the form loads the code:
Private Sub Form_Load()
Dim D$, SQL$
D$ = "OUT"
SQL$ = "SELECT [Out].[Name], [Out].[Remarks] FROM Out WHERE ((([Out].[Remarks]) Is Not Null)) ORDER BY [Out].[Name];"
Data1.RecordSource = SQL$
Data1.Refresh
Text1.Text = SQL$
End Sub
Runs fine.

I added a command button for a user to press to update
the Remarks field in certain conditions:
The code on the command button is:
Private Sub Command1_Click()
Dim SQL2$, a1$, b1$
a1$ = "AM"
b1$ = "APPT/AM"
SQL2$ = "UPDATE OUT SET OUT.Remarks = b1$ WHERE ((([OUT].[Remarks])= a1$));"
Do Until Data1.Recordset.EOF
Data1.RecordSource = SQL2$
Data1.UpdateRecord
Data1.Recordset.MoveNext
Loop
text1.text = SQL2$
End Sub
The Text1 shows the SQL2 statement but the Remarks field
is not updated.

Am I missing something or do I need to add a statement
to the command button to get the Remarks field to update ?

Thanks

< I want to work in Theory - everything and everybody works in Theory !!>

 

[AkutaSame]

I don't see a refresh there. Perhaps that is what is preventing the &quot;Remarks&quot; field from updating on your window? You are indeed changing the data recordset, but you are not refreshing the query, so it's not going to update the data displayed.

 

[nospace52]

AkutaSame,

I tried to do a data1.refresh in the Command code but
keep getting a Invalid Operation error.

I have placed the refresh in several spots.
Thanks

< I want to work in Theory - everything and everybody works in Theory !!>

 

[AndyWatt]

[i]> SQL2$ = "UPDATE OUT SET OUT.Remarks = b1$ WHERE ((([OUT].[Remarks])= a1$));" [/i]

Should read

SQL2$ = "UPDATE OUT SET OUT.Remarks = '" & b1$ & "' WHERE ((([OUT].[Remarks])= '" & a1$ & "'));"

Andy
&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux
&quot;Why does my program keep showing error messages every time something goes wrong?&quot;

 

[AkutaSame]

Ahhh, yes. Andy picked up that you are missing your single quotes for your query. Of course, submitting this directly to SQL will cause issues (without the quotes)... and is typically overlooked when someone is used to using a query analyzer.

 

[AndyWatt]

nospace52

So is it fixed?

Andy
&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux
&quot;Why does my program keep showing error messages every time something goes wrong?&quot;

 

[nospace52]

Andy - I replaced the SQL2$ statement with what you gave me and the records are not being updated.

Not sure if it something to do with a Refresh. When I put a
Data1.Refresh in the code that is when I get the Invalid Operation error.

I have ran the code one line at a time and the loop is working but no update.

Thanks

< I want to work in Theory - everything and everybody works in Theory !!>

 

[AndyWatt]

nospace52

D'Oh, sorry I'm being a bit stupid here - I fixed one problem but failed to notice the other...

I'm guessing that you want to set all records that contain &quot;AM&quot; in them to read &quot;APPT/AM&quot;.

Replace

Do Until Data1.Recordset.EOF
  Data1.RecordSource = SQL2$
  Data1.UpdateRecord
  Data1.Recordset.MoveNext
Loop

With

Data1.Database.Execute SQL2$

And that should update every record that meets the criteria (i.e. Where Remarks = &quot;AM&quot; replace with &quot;APPT/AM&quot.

I apologise for being so singularly unobservant.

Andy
&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux
&quot;Why does my program keep showing error messages every time something goes wrong?&quot;

 

[nospace52]

Andy,

That worked like a champ - have a great weekend and
thanks again.

< I want to work in Theory - everything and everybody works in Theory !!>

 

[AndyWatt]

Pleased to be able to help.

Andy
&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux
&quot;Why does my program keep showing error messages every time something goes wrong?&quot;