Allen,
Sorry to bother you again but here is the code that l am running. The sql
query is failing to run because of a syntax error , but for the life of me l
cant see the error.
cpcodeext1 is a number and is declared as an integer
[OFA_BLB_CODE] is a text field in the table
Please can you help !
Option Compare Database
Option Explicit
Dim cpcodeext1 As Integer
Private Sub Form_AfterInsert()
'DoCmd.OpenTable "GROUPBLBS"
'[NEXT_OFA_CP_CODE] = cpcodeext1
'DoCmd.Save
'DoCmd.Close
Dim db As Database, sql As String
Set db = CurrentDb
sql = "UPDATE [GROUPBLBS] SET [NEXT_OFA_CP_CODE]=" & cpcodeext1 & "WHERE
[OFA_BLB_CODE]=""" & Me.[OFA_BLB_CODE] & """" 'item 1
db.Execute sql, dbFailOnError
Set db = Nothing
'sql = "UPDATE [GROUPBLBS] SET [NEXT_OFA_CP_CODE]=""" & cpcodeext1 & """"
'item 2
'Use item 1- if cpcodeext1 is a numeric item, item 2- if it is text
'Item 1 updates the field ofa_blb_code for the current recordset only
'Item 2 updates the field ofa_blb_code for ALL records in the table.
End Sub
Many thanks for all your help so far,
Regards
Allen Browne said:
Add a WHERE clause to your UPDATE query statement.
For example, if the form's table has a primary key field named ID, :
sql = "UPDATE [GROUPBLBS] SET [NEXT_OFA_CP_CODE] = " & cpcodeext1 & " WHERE
ID = " & Me.ID & ";"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Michael Beckinsale said:
Allen,
Many thanks, the code now runs as it should with the declared
variables
etc.
After posting to the group l looked @ references but did not have a clue
which one it should be.
However the problem is not totally solved as ALL the records are being
updated on that field. I only want to update the one referred to on the
form.
Pls do you have any idea's.
I have spent 2 days trying to resolve this problem and although close the
final bit is causing much "head scratching"
Regards
You have a problem with references.
1. Open a code window, and choose References on the Tools menu.
2. Make sure you have checked the box beside:
Microsoft DAO 3.51
and that it does not say "MISSING".
More info on references:
http://allenbrowne.com/ser-38.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Reposted this as l dont know how contributors track feedback.
Hope somebody can throw some light on this please.
I'm a newbie to Access VBA but experienced in Excel VBA and this
has
me
stumped !
TC
Many thanks for your reply.
Tried the code you supplied but had the following problems,
1. Access 97 does not appear to recognise As database as valid variable
type.
2. Access 97 does not appear to recognise dbfailonerror as a valid
command.
Whilst trying to resolve the problem we removed the variable type
declaration 'As database' and the command dbfailonerror and the code
ran.
However it updated ALL the records in the table for that field whereas
we
only want to update the subject recordset field.
Any further help greatfully appreciated.
Regards
Your Form_AfterInsert code looks wrong, to me:
DoCmd.OpenTable "GROUPBLBS"
[NEXT_OFA_CP_CODE] = cpcodeext1
You realize that >will not< edit field [NEXT_OFA_CP_CODE] of table
GROUPBLBS? It will open that table as a datasheet, then edit field
[NEXT_OFA_CP_CODE] of the recordset on which the form is based - two
entitely different things.
If you wanted to edit field [NEXT_OFA_CP_CODE] of table
GROUPBLBS
in
the
AfterInsert event of the form, you'd need something like this:
(untested)
dim db as database, sql as string
set db = currentdb()
1- sql = "UPDATE [GROUPBLBS] SET [NEXT_OFA_CP_CODE]=" & cpcodeext1
2- sql = "UPDATE [GROUPBLBS] SET [NEXT_OFA_CP_CODE]=""" &
cpcodeext1
&
""""
db.execute sql, dbfailonerror
set db = nothing
Use 1- if cpcodeext1 is a numeric item, 2- if it is text.
HTH,
TC
message
Hi All,
Below is the sample code l have used to trigger events on a
form
and
then
update a counter in another table. The code works as expected except
that
when the form is returned for the next new record to be
entered
it
does
not
behave correctly in that a new record can be created , however if
you
want
to close the form it thinks that a null value is present and an
error
is
returned.
Please can anybody tell me what is wrong as l am going
craaaaaaaaazy.
Sorry about the MsgBoxes but trying to de-bug.
Windows98 & Access97
Option Compare Database
Option Explicit
Dim cpcodeext1
Private Sub Form_AfterInsert()
DoCmd.OpenTable "GROUPBLBS"
MsgBox ("message 1")
[NEXT_OFA_CP_CODE] = cpcodeext1
MsgBox ("message 2")
DoCmd.Save
MsgBox ("message 3")
DoCmd.Close
MsgBox ("message 4")
End Sub
Private Sub OFA_BLB_CODE_LostFocus()
Dim intnewrec As Integer
Dim cpcode
Dim cpcodelen
Dim cpcodeext
MsgBox ("message 5")
intnewrec = Me.NewRecord
MsgBox ("message 6")
If intnewrec = True Then
MsgBox ("message 6")
cpcode = Format(Me![NEXT_OFA_CP_CODE], "##0")
MsgBox ("message 7")
cpcodelen = Len(cpcode)
MsgBox ("message 8")
If cpcodelen = 1 Then
MsgBox ("message 9")
cpcode = "00" & cpcode
MsgBox ("message 10")
End If
MsgBox ("message 11")
If cpcodelen = 2 Then
MsgBox ("message 12")
cpcode = "0" & cpcode
MsgBox ("message 13")
End If
MsgBox ("message 14")
If cpcodelen = 3 Then
MsgBox ("message 15")
cpcode = cpcode
MsgBox ("message 16")
End If
MsgBox ("message 17")
Me![OFA_CP_CODE] = Left(Me![OFA_BLB_CODE], 4) & "D" & cpcode
MsgBox ("message 18")
cpcodeext = (Me![NEXT_OFA_CP_CODE]) + 1
MsgBox ("message 19")
cpcodeext1 = cpcodeext
MsgBox ("message 20")
End If
End Sub
Private Sub OFA_CP_CODE_BeforeUpdate(Cancel As Integer)
End Sub
Private Sub tbOFA_LOD2_CODE_GotFocus()
Dim intnewrec As Integer
intnewrec = Me.NewRecord
If intnewrec = True Then
Me![tbOFA_LOD2_CODE] = Left(Me![OFA_LOD1_CODE], 6) +
Left(Me![OFA_BLB_CODE], 4)
End If
End Sub
Regards
Michael Beckinsale
