Form_AfterInsert - Frustrated !!!!

  • Thread starter Thread starter Michael Beckinsale
  • Start date Start date
M

Michael Beckinsale

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
 
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


Michael Beckinsale said:
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
Click to expand...
 
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
TC said:
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


Michael Beckinsale said:
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
Click to expand...
Click to expand...
 
Michael Beckinsale said:
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
Click to expand...
command.

Those suggest a reference problem. Those two syntaxes are definitely valid
in Access 97. For example, if you go to the debug window (^g) and type:

? dbfailonerror

it should reply with:

128.


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.
Click to expand...

Oops, I should maybe have forseen that & mentioned it. Sorry if that caused
you any problems!

To restrict the update to a single record, you'd add a WHERE clause to the
SQL statement. For example:

UPDATE [GROUPBLBS] SET [NEXT_OFA_CP_CODE]=" & cpcodeext1 & _
" WHERE [SomeOtherField]=" & 99

- to update just the record(s) where [SomeOtherField] was 99.

HTH,
TC

Any further help greatfully appreciated.

Regards
TC said:
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


Michael Beckinsale said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top