Re: update table from form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi
all my fields are text / strings and am trying to get form to update table
as when get it to run query only updating first record.
query sql is
UPDATE dbo_DataLots SET dbo_DataLots.StatusFlag = " "
WHERE (((dbo_DataLots.LotNumber)=[forms]![serial]![LotNumber]));

command in form is
Private Sub update_Click()
On Error GoTo Err_update_Click

Dim stDocName As String

stDocName = "SERIAL UPDATE"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_update_Click:
Exit Sub

Err_update_Click:
MsgBox Err.Description
Resume Exit_update_Click

End Sub
AS this was only updating selected record i tried to run using
strSQL = "UPDATE dbo_DataLots " & _
"SET StatusFlag = ' ' & " _
"WHERE [LotNumber] = '" & Replace(Me.LotNumber, "'", "''") & "'"
as suggested this only updates selected record aswell how can I updat eall
records shown in form
Thanks
Tina


She posted another message yesterday, in which I gave her the syntax to
update a field based on a Like statement in here WHERE clause. But it didn't
occur to me until this morning that Access will not allow you to store a
space in a text field. It will allow leading spaces, followed by text, but
it will not allow a single space, or trailing spaces (it is as if it uses
RTRIM to trim all trailing spaces before it saves a text field).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Stefan Hoffmann" wrote:

hi Dale,

Dale Fye wrote:
What is the DataType of your StatusFlag field? Is it text (if so, how many
characters)? After trying to answer your previous question, I realized that
if the field is a text field, Access will not let you actually store blanks,
so you are going to have to use some other character in that field.
This shouldn't raise a syntax error.


mfG
--> stefan <--

.

Relevant Pages

  • Re: execute append query
    ... copied sql from query into strings in vba in form, ... Dim qdfAppend as querydef ... > yes i did declare the parameter in the query and yes there was a suitable ...
    (microsoft.public.access.modulesdaovba)
  • Re: execute append query
    ... It may be that adding dbFailOnError to your original Execute statement would ... > copied sql from query into strings in vba in form, ... rather than allenbrowne at mvps dot org. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Working with tables in Excel 2007.
    ... The only way I've found to handle data tables is using the old ADODB ... only put strings in, and I can't read the index of the string selected, I ... colorful data table displayer in Excel '07, it works in Microsoft's Query ... can't make it work in the ADODB connector, where I can give it parameters ...
    (microsoft.public.excel.programming)
  • Access 2003 treats embedded functions in nested queries as a reference rather than a value - Acc
    ... Microsoft Access 2003 allows one to embed "expressions" in the query ... I also make extensive use of queries that are built from queries, ... Typically the IIf and Switch functions I use return strings. ... Has this behavior changed in Microsoft Access 2007? ...
    (microsoft.public.access.queries)