UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Insert, Access 2016    
 
   
mike60smart
post Jan 15 2020, 04:53 PM
Post#1


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am using the following ONClick Event to add a new Record to a continuous subform

CODE
Private Sub cmdAdd_Click()

10        On Error GoTo cmdAdd_Click_Error
          Dim strSQL
          Dim lngFleetID As Long
          Dim dtInspectionDate As Date

20      If Me.Dirty Then Me.Dirty = False
30    lngFleetID = Me!FleetID
40    dtInspectionDate = Me!txtDate

50    strSQL = "INSERT INTO tblFleetInspections (FleetID, InspectionDate) " & vbCrLf _
                  & "Values (" & lngFleetID & ", " & Format(dtInspectionDate, "\#mm\/dd\/yyyy\#") & ");"
60        Debug.Print strSQL
70      CurrentDb.Execute strSQL, dbFailOnError
80    [Forms]![frmFleet].[frmNewFleetInspectionssubform].Form.Requery
85      Me.txtDate = Null
90        On Error GoTo 0
100       Exit Sub

cmdAdd_Click_Error:

110       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAdd_Click, line " & Erl & "."

End Sub


If there no previous records I get the following error:-
Attached File  error.PNG ( 2.75K )Number of downloads: 0


If I add a dummy record first and then carry out the OnClick Event it works just fine?

Any help appreciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
June7
post Jan 15 2020, 05:29 PM
Post#2



Posts: 1,153
Joined: 25-January 16
From: The Great Land


lngFleetID is declared as Long. Only Variant type can receive Null.

So the real question is Why is FleetID Null?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
mike60smart
post Jan 16 2020, 03:12 AM
Post#3


UtterAccess VIP
Posts: 13,585
Joined: 6-June 05
From: Dunbar,Scotland


Hi June7

You nailed it. Because the subform as yet did not have any records and the OnClick Event was on the Subform itself, hence a Null Value.

Fixed by obtaining the Current FleetID by using =[Parent]![FleetID]

Now solved

Many thanks yet again
kisses.gif pompom.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 09:27 PM