Edit Existing Record

[nathan sanders]

I am having no luck writing some code to find and edit an existing record in
a table.

Can someone please provide me with (or point me to) an example of how to
achieve this.

Cheers
Nathan

 

[Brendan Reynolds]

This example finds the record in the table 'tblSource' that contains the
value entered in the text box 'txtOne' in the 'SourceID' field (the primary
key) and updates the value of the 'SourceText' field with the value entered
in the text box 'txtTwo' ...

Private Sub cmdTest_Click()

CurrentDb.Execute "UPDATE tblSource SET SourceText = '" & _
Me.txtTwo & "' WHERE SourceID = " & Me.txtOne, dbFailOnError

End Sub

 

[nathan sanders]

Brendan,

Thanks for looking at this. I have inserted the code you gave me into a
procedure and I am not getting any errors when I run it, however I am also
not getting the change made to the field. The code I have is below.

Case "DC AMERICAN EXPRESS "

strTrnval = Right(TXTDATA!field3, 6)
strfil1 = "amex"
MsgBox (strfil1)
CurrentDb.Execute "update tblSummary set fldAmexpaid = '" &
strTrnval & "' where fldDate = " & FILDATE

Case else
..
..
..
..
..

What's wrong here?

Nathan

 

[Brendan Reynolds]

If you're certain that the "DC AMERICAN EXPRESS" condition is being met,
then the most likely scenario is that there aren't any records that match
the criteria (where fldDate = " & FILDATE). If, as the name implies, this is
a Date/Time field, you need '#' delimiters, and the date needs to be in US
mm/dd/yyyy format. Try something like ...

Const UsDateFormat As String = "mm/dd/yyyy"
....
"' where fldDate = #" & Format$(FILDATE, UsDateFormat) & "#"

 

[Baz]

Brendan,

Thanks for looking at this. I have inserted the code you gave me into a
procedure and I am not getting any errors when I run it, however I am also
not getting the change made to the field. The code I have is below.

Case "DC AMERICAN EXPRESS "

strTrnval = Right(TXTDATA!field3, 6)
strfil1 = "amex"
MsgBox (strfil1)
CurrentDb.Execute "update tblSummary set fldAmexpaid = '" &
strTrnval & "' where fldDate = " & FILDATE

Case else
.
.
.
.
.

What's wrong here?

Nathan

Go back to Brendan's example, note the "dbFailOnError" option, and add it to
your code. You will then discover that there is an error in your SQL.

At a glance, it appears that you are handling a date field incorrectly.
Dates in Access SQL need to be delimited by "#" characters, thus:

CurrentDb.Execute "update tblSummary set fldAmexpaid = '" & strTrnval & "'
where fldDate = #" & FILDATE & "#"

Furthermore, dates need to be in American format i.e. mm/dd/yyyy. It looks
like you are a Kiwi and so, I believe, you are with the entire civilised
world in preferring dd/mm/yyyy. So, you are gonna have to reformat the
date, thus:

CurrentDb.Execute "update tblSummary set fldAmexpaid = '" & strTrnval & "'
where fldDate = #" & Format(FILDATE,"mm/dd/yyyy") & "#"

 

[nathan sanders]

Brendan, Baz

Thank you both for your help. This is now working perfectly and I have
learnt something.

Thanks again...
Nathan (a reasonably civilized kiwi!)