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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Invalid Use Of Null, Access 2016    
 
   
mike60smart
post Sep 16 2019, 11:40 AM
Post#1


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


Hi Everyone

I have the following Code which is used to Duplicate a Record.

I am using a Main Form / Subform with Both the Main and Subform set as Single Form View.

On a Command Button on the Subform I have the following On Click Event which when I run it gives me the error "Invalid Use of Null" ?

Any help appreciated.

CODE
Private Sub cmdD_Click()
10    On Error GoTo cmdD_Click_Err

20    If Me.Dirty Then Me.Dirty = False
      Dim strSQL As String

      Dim lngResident_FK As Long
      Dim dteReviewDate As Date
      Dim strNursingCareNeedsNotes As String
      Dim strTherapyCareNeedsNotes As String
      Dim strDischargeLocation As String
      Dim strDischargePlan As String
      Dim strPriorSetting As String
      Dim strPrimaryComorbidities As String
      Dim lngNursingCaseMixCategory_FK As Long
      Dim strSkilledOT As String
      Dim strOTProjectedSched As String
      Dim strSkilledPT As String
      Dim strPTProjectedSched As String
      Dim strSkilledLPT As String
      Dim strSLPProjectedSched As String
      Dim strSkilledNsg As String
      Dim strAreasofFocusConcern As String
      Dim strCareConferences As String
      Dim strEstimatedTimeFrame As String
      Dim dte100thDayDateActual As Date
      Dim dteLastCoveredDay As Date

30    lngResident_FK = Me.Resident_FK
40    dteReviewDate = Date
50    strNursingCareNeedsNotes = Me.NursingCareNeedsNotes
60    strTherapyCareNeedsNotes = Me.TherapyCareNeedsNotes
70    strDischargeLocation = Me.DischargeLocation
80    strDischargePlan = Me.DischargePlan
90    strPriorSetting = Me.PriorSetting
100   strPrimaryComorbidities = Me.PrimaryComorbidities
110   lngNursingCaseMixCategory_FK = Me.NursingCaseMixCategory_FK
120   strSkilledOT = Me.SkilledOT
130   strOTProjectedSched = Me.OTProjectedSched
140   strSkilledPT = Me.SkilledPT
150   strPTProjectedSched = Me.PTProjectedSched
160   strSkilledLPT = Me.SkilledSLP
170   strSLPProjectedSched = Me.SLPProjectedSched
180   strSkilledNsg = Me.SkilledNsg
190   strAreasofFocusConcern = Me.AreasofFocusConcern
200   strCareConferences = Me.CareConferences
210   strEstimatedTimeFrame = Me.EstimatedTimeFrame
220   dte100thDayDateActual = Me![100thDayDateActual]
230   dteLastCoveredDay = Me.LastCoveredDay


240   strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, NursingCareNeedsNotes, DischargeLocation, DischargePlan, PriorSetting ," _
              & " PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledOT, OTProjectedSched ," _
              & " SkilledPT, PTProjectedSchedule, SkilledLPT, SLPProjectedSchedule, SkilledNsg, AreasofFocusConcern, DischargePlan, CareConferences, EstimatedTimeFrame, 100thDayDateActual, LastCoveredDay ) " _
              & "VALUES (" & lngResident_FK & ", " & Format(dteReviewDate, "\#dd\-mmm\-yyyy\#") & ", '" & strNursingCareNeedsNotes & "', '" & strTherapyCareNeedsNotes _
              & "', '" & strDischargeLocation & "', '" & strDischargePlan & "', '" & strPriorSetting & "', '" & strPrimaryComorbidities _
              & "', " & lngNursingCaseMixCategory_FK & ", '" & strSkilledOT & "', '" & strOTProjectedSched & "', '" & strSkilledPT & "','" & strPTProjectedSched _
              & "', '" & strSkilledLPT & "', '" & strSLPProjectedSched & "', '" & strSkilledNsg & "', '" & strAreasofFocusConcern & "', '" & strCareConferences _
              & "', '" & strEstimatedTimeFrame & "', " & Format(dte100thDayDateActual, "\#dd\-mmm\-yyyy\#") & ", " & Format(dteLastCoveredDay, "\#dd\-mmm\-yyyy\#") & ");"
250       Debug.Print strSQL
260       CurrentDb.Execute strSQL, dbFailOnError

270   MsgBox "Remember to update the Review date as necessary!!", vbCritical

280   Forms![frmResidentCareReviews].[frmCareReviewsSubform].Form.Requery
290   DoCmd.GoToRecord , , acLast
300   Me.ReviewDate.SetFocus


cmdD_Click_Exit:
310       Exit Sub

cmdD_Click_Err:
320       MsgBox Error$
330       Resume cmdD_Click_Exit


End Sub

--------------------
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
 
Start new topic
Replies
theDBguy
post Sep 16 2019, 11:54 AM
Post#2


UA Moderator
Posts: 77,506
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. I didn't read the entire code. Which line is complaining about the use null?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mike60smart
post Sep 16 2019, 01:40 PM
Post#3


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


Hi theDBGuy

That is the strange thing it does not indicate a specific line it just pops up the message box with "Invalid Use of Null"

--------------------
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
 
Phil_cattivocara...
post Sep 16 2019, 11:24 PM
Post#4



Posts: 368
Joined: 2-April 18



QUOTE (mike60smart)
That is the strange thing it does not indicate a specific line it just pops up the message box with "Invalid Use of Null"
This is due to your error handler.
CODE
cmdD_Click_Err:
  320       MsgBox Error$
  330       Resume cmdD_Click_Exit
Or you disable this error handler and you let the error interrupt the execution, or you should add some information in the msgbox, as you do in the code you usually post. See the last one before this: https://www.UtterAccess.com/forum/index.php...2055069&hl=
CODE
cmdSearchRecord_Click_Error:

190       MsgBox "Error " &   Err.Number & " (" & Err.Description & ") in procedure   cmdSearchRecord_Click, line " & Erl & "."

This post has been edited by Phil_cattivocarattere: Sep 16 2019, 11:25 PM

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mike60smart
post Sep 17 2019, 07:48 AM
Post#5


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


Hi Phil

Many thanks for pointing that out.

I thought the code was working OK but as usual I spoke too soon.

If a control does not contain any data then I get the following Error:-

Attached File  Error.PNG ( 3.42K )Number of downloads: 0


How can I modify the code so that if any Control does not contain any data it would copy the Record anyway?

Any help appreciated.

My code at the moment is this:-

CODE
Private Sub cmdD_Click()

10        On Error GoTo cmdD_Click_Error
20    If Me.Dirty Then Me.Dirty = False
      Dim strSQL As String

      Dim lngResident_FK As Long
      Dim dteReviewDate As Date
      Dim strNursingCareNeedsNotes As String
      Dim strTherapyCareNeedsNotes As String
      Dim strDischargeLocation As String
      Dim strDischargePlan As String
      Dim strPriorSetting As String
      Dim strPrimaryComorbidities As String
      Dim lngNursingCaseMixCategory_FK As Long
      Dim strSkilledOT As String
      Dim strOTProjectedSched As String
      Dim strSkilledPT As String
      Dim strPTProjectedSched As String
      Dim strSkilledSLP As String
      Dim strSLPProjectedSched As String
      Dim strSkilledNsg As String
      Dim strAreasofFocusConcern As String
      Dim strCareConferences As String
      Dim strEstimatedTimeFrame As String
      Dim dte100thDayDateActual As Date
      Dim dteLastCoveredDay As Date

30    lngResident_FK = Me.Resident_FK
40    dteReviewDate = Date
50    strNursingCareNeedsNotes = Me.NursingCareNeedsNotes
60    strTherapyCareNeedsNotes = Me.TherapyCareNeedsNotes
70    strDischargeLocation = Me.DischargeLocation
80    strDischargePlan = Me.DischargePlan
90    strPriorSetting = Me.PriorSetting
100   strPrimaryComorbidities = Me.PrimaryComorbidities
110   lngNursingCaseMixCategory_FK = Me.NursingCaseMixCategory_FK
120   strSkilledOT = Me.SkilledOT
130   strOTProjectedSched = Me.OTProjectedSched
140   strSkilledPT = Me.SkilledPT
150   strPTProjectedSched = Me.PTProjectedSched
160   strSkilledSLP = Me.SkilledSLP
170   strSLPProjectedSched = Me.SLPProjectedSched
180   strSkilledNsg = Me.SkilledNsg
190   strAreasofFocusConcern = Me.AreasofFocusConcern
200   strCareConferences = Me.CareConferences
210   strEstimatedTimeFrame = Me.EstimatedTimeFrame
220   dte100thDayDateActual = Me![100thDayDateActual]
230   dteLastCoveredDay = Me.LastCoveredDay


240   strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, NursingCareNeedsNotes, TherapyCareNeedsNotes, DischargeLocation, DischargePlan, PriorSetting ," _
              & " PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledOT, OTProjectedSched ," _
              & " SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, SkilledNsg, AreasofFocusConcern, CareConferences, EstimatedTimeFrame, 100thDayDateActual, LastCoveredDay ) " _
              & "VALUES (" & lngResident_FK & ", " & Format(dteReviewDate, "\#dd\-mmm\-yyyy\#") & ", '" & strNursingCareNeedsNotes & "', '" & strTherapyCareNeedsNotes _
              & "', '" & strDischargeLocation & "', '" & strDischargePlan & "', '" & strPriorSetting & "', '" & strPrimaryComorbidities _
              & "', " & lngNursingCaseMixCategory_FK & ", '" & strSkilledOT & "', '" & strOTProjectedSched & "', '" & strSkilledPT & "','" & strPTProjectedSched _
              & "', '" & strSkilledSLP & "', '" & strSLPProjectedSched & "', '" & strSkilledNsg & "', '" & strAreasofFocusConcern & "', '" & strCareConferences _
              & "', '" & strEstimatedTimeFrame & "', " & Format(dte100thDayDateActual, "\#dd\-mmm\-yyyy\#") & ", " & Format(dteLastCoveredDay, "\#dd\-mmm\-yyyy\#") & ");"
250       Debug.Print strSQL
260       CurrentDb.Execute strSQL, dbFailOnError

270   MsgBox "Remember to update the Review date as necessary!!", vbCritical

280   Forms![frmResidentCareReviews].[frmCareReviewsSubform].Form.Requery
290   DoCmd.GoToRecord , , acLast
300   Me.ReviewDate.SetFocus




          
310       On Error GoTo 0
320       Exit Sub

cmdD_Click_Error:

330       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdD_Click, line " & Erl & "."

End Sub

--------------------
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
 
cheekybuddha
post Sep 17 2019, 03:08 PM
Post#6


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

You can probably make all this a lot easier.

First, I'm assuming that your table (tblCareReviews) is able to accept duplicate data in all the fields that you are entering (ie none has a unique index or similar).

Then, do you have a control on the form containing the ID/Primary key of the current record? If not, add one - you can make it hidden if you don't want it displayed to the user.

Then,
CODE
Private Sub cmdD_Click()
10    On Error GoTo cmdD_Click_Error

      Dim strSQL As String

20    If Me.Dirty Then Me.Dirty = False
30    strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, NursingCareNeedsNotes, TherapyCareNeedsNotes, DischargeLocation, DischargePlan, PriorSetting ," _
             & " PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledOT, OTProjectedSched ," _
             & " SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, SkilledNsg, AreasofFocusConcern, CareConferences, EstimatedTimeFrame, 100thDayDateActual, LastCoveredDay ) " _
            & " SELECT cr.Resident_FK, cr.ReviewDate, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.DischargeLocation, cr.DischargePlan, cr.PriorSetting ," _
             & " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledOT, cr.OTProjectedSched ," _
             & " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.SkilledNsg, cr.AreasofFocusConcern, cr.CareConferences, cr.EstimatedTimeFrame, cr.100thDayDateActual, cr.LastCoveredDay " _
             & " FROM tblCareReviews cr " & _
             & " WHERE CareReviewID = " & Me.txtCareReviewID & ";"
40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError

60    MsgBox "Remember to update the Review date as necessary!!", vbCritical

70    Forms![frmResidentCareReviews].[frmCareReviewsSubform].Form.Requery
80    DoCmd.GoToRecord , , acLast
90    Me.ReviewDate.SetFocus
100   Exit Sub

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

End Sub


You must pay attention to the last line of the strSQL (the line previous to line 40). Change the field name (CareReviewID) to the name of the Primary Key field of tblCareReviews and change the name of the control (Me.txtCareReviewID) to the name of the textbox on the form that is bound to that field.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Sep 17 2019, 04:05 PM
Post#7


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


Hi David

These lines all highlight in Red ??

CODE
30    strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, NursingCareNeedsNotes, TherapyCareNeedsNotes, DischargeLocation, DischargePlan, PriorSetting ," _
             & " PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledOT, OTProjectedSched ," _
             & " SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, SkilledNsg, AreasofFocusConcern, CareConferences, EstimatedTimeFrame, 100thDayDateActual, LastCoveredDay ) " _
             & " SELECT cr.Resident_FK, cr.ReviewDate, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.DischargeLocation, cr.DischargePlan, cr.PriorSetting , _
             & " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledOT, cr.OTProjectedSched , _
             & " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.SkilledNsg, cr.AreasofFocusConcern, cr.CareConferences, cr.EstimatedTimeFrame, _
             & " cr.100thDayDateActual, cr.LastCoveredDay " _
             & " FROM tblCareReviews cr " & _
             & " WHERE CareReviewID = " & Me.CareReviewID & ";"


What does the cr. stand for in all the Controls ie cr.Resident_FK

--------------------
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
 
cheekybuddha
post Sep 18 2019, 03:21 AM
Post#8


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


Good morning, Mike.

It appears that you have lost a few closing double quotes where you have reformatted the long lines.

Have a go with this:
CODE
30    strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, NursingCareNeedsNotes, TherapyCareNeedsNotes, DischargeLocation, DischargePlan, PriorSetting ," _
             & " PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledOT, OTProjectedSched ," _
             & " SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, SkilledNsg, AreasofFocusConcern, CareConferences, EstimatedTimeFrame, 100thDayDateActual, LastCoveredDay ) " _
             & " SELECT cr.Resident_FK, cr.ReviewDate, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.DischargeLocation, cr.DischargePlan, cr.PriorSetting , " _
             & " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledOT, cr.OTProjectedSched , " _
             & " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.SkilledNsg, cr.AreasofFocusConcern, cr.CareConferences, cr.EstimatedTimeFrame, " _
             & " cr.100thDayDateActual, cr.LastCoveredDay " _
             & " FROM tblCareReviews cr " & _
             & " WHERE CareReviewID = " & Me.CareReviewID & ";"


cr is an alias assigned to the second instance of tblCareReviews.

I am suggesting an alternative syntax to your original INSERT statement.

Before you used:
INSERT INTO YourTable (Field1, Field2, etc) VALUES (ValueFromForm1, ValueFromForm2, etc)

This version uses:
INSERT INTO YourTable (Field1, Field2, etc) SELECT Field1, Field2 FROM YourTable WHERE ID = IDFromForm

Since you are duplicating an existing record, you can select the required values from the table directly in a sub-query. You won't have to worry about string/date delimiters or whether any fields are NULL or not.

I used the alias to help Access understand that the values selected are part of the sub-query because you are inserting values from/into the same table.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Sep 18 2019, 04:35 AM
Post#9


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


Hi David

This is what the code is now:-

CODE
Private Sub cmdD_Click()
10    On Error GoTo cmdD_Click_Error

      Dim strSQL As String

20    If Me.Dirty Then Me.Dirty = False


30    strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, NursingCareNeedsNotes, TherapyCareNeedsNotes, DischargeLocation, DischargePlan, PriorSetting ," _
             & " PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledOT, OTProjectedSched ," _
             & " SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, SkilledNsg, AreasofFocusConcern, CareConferences, EstimatedTimeFrame, 100thDayDateActual, LastCoveredDay ) " _
             & " SELECT cr.Resident_FK, cr.ReviewDate, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.DischargeLocation, cr.DischargePlan, cr.PriorSetting , " _
             & " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledOT, cr.OTProjectedSched , " _
             & " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.SkilledNsg, cr.AreasofFocusConcern, cr.CareConferences, cr.EstimatedTimeFrame, " _
             & " cr.100thDayDateActual, cr.LastCoveredDay " _
             & " FROM tblCareReviews cr " & _
             & " WHERE CareReviewID = " & Me.CareReviewID & ";"
            

40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError

60    MsgBox "Remember to update the Review date as necessary!!", vbCritical

70    Forms![frmResidentCareReviews].[frmCareReviewsSubform].Form.Requery
80    DoCmd.GoToRecord , , acLast
90    Me.ReviewDate.SetFocus
100   Exit Sub

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

End Sub

--------------------
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
 
Phil_cattivocara...
post Sep 18 2019, 04:56 AM
Post#10



Posts: 368
Joined: 2-April 18



In this line
CODE
& " FROM tblCareReviews cr " & _
last & has to be deleted.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mike60smart
post Sep 18 2019, 05:52 AM
Post#11


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


Hi Phil

Is it because these two fields are DateTime Data Types??

cr.100thDayDateActual, cr.LastCoveredDay

--------------------
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
 
Phil_cattivocara...
post Sep 18 2019, 06:01 AM
Post#12



Posts: 368
Joined: 2-April 18



QUOTE (mike60smart)
Is it because these two fields are DateTime Data Types??
It could be.Now it's the right time to see what you have in the immediate windows with Debug.Print strSQL.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
mike60smart
post Sep 18 2019, 06:36 AM
Post#13


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


Hi Phil

I rearranged the fields so that they now match the order they are in the underlying tblCareReviews.

I now get this error:-


Attached File  Error.PNG ( 3.73K )Number of downloads: 0


Code is now:-

CODE
Private Sub cmdD_Click()
10    On Error GoTo cmdD_Click_Error

      Dim strSQL As String

20    If Me.Dirty Then Me.Dirty = False


30    strSQL "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledNsg, NursingCareNeedsNotes, " _
        & " TherapyCareNeedsNotes, SkilledOT, OTProjectedSched , SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, PriorSetting , EstimatedTimeFrame, " _
        & " 100thDayDateActual, LastCoveredDay, DischargePlan, DischargeLocation, AreasofFocusConcern, CareConferences) " _
        & " SELECT cr.Resident_FK, cr.ReviewDate, " _
        & " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledNsg, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.SkilledOT, cr.OTProjectedSched , " _
        & " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.PriorSetting, cr.EstimatedTimeFrame, cr.100thDayDateActual, cr.LastCoveredDay, " _
        & " cr.DischargePlan, cr.DischargeLocation, cr.AreasofFocusConcern, cr.CareConferences " _
        & " FROM tblCareReviews cr " _
        & " WHERE CareReviewsID = " & Me.CareReviewsID & ";"
            

40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError
60    MsgBox "Remember to update the Review date as necessary!!", vbCritical

70    Forms![frmResidentCareReviews].[frmCareReviewsSubform].Form.Requery
80    DoCmd.GoToRecord , , acLast
90    Me.ReviewDate.SetFocus
100   Exit Sub

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

End Sub

--------------------
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
 
cheekybuddha
post Sep 18 2019, 12:41 PM
Post#14


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


Look at the beginning of line 30:

You have:
CODE
30    strSQL "INSERT INTO tblCareReviews ...


It should read:
CODE
30    strSQL = "INSERT INTO tblCareReviews ...


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Sep 18 2019, 12:47 PM
Post#15


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


Hi David

No the code is this:-

CODE
Private Sub cmdD_Click()
10    On Error GoTo cmdD_Click_Error

      Dim strSQL As String

20    If Me.Dirty Then Me.Dirty = False


30    strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledNsg, NursingCareNeedsNotes, " _
        & " TherapyCareNeedsNotes, SkilledOT, OTProjectedSched , SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, PriorSetting , EstimatedTimeFrame, " _
        & " 100thDayDateActual, LastCoveredDay, DischargePlan, DischargeLocation, AreasofFocusConcern, CareConferences) " _
        & " SELECT cr.Resident_FK, cr.ReviewDate, " _
        & " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledNsg, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.SkilledOT, cr.OTProjectedSched , " _
        & " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.PriorSetting, cr.EstimatedTimeFrame, cr.100thDayDateActual, cr.LastCoveredDay, " _
        & " cr.DischargePlan, cr.DischargeLocation, cr.AreasofFocusConcern, cr.CareConferences " _
        & " FROM tblCareReviews cr " _
        & " WHERE CareReviewsID = " & Me.CareReviewsID & ";"
            

40    Debug.Print strSQL
50    CurrentDb.Execute strSQL, dbFailOnError
60    MsgBox "Remember to update the Review date as necessary!!", vbCritical

70    Forms![frmResidentCareReviews].[frmCareReviewsSubform].Form.Requery
80    DoCmd.GoToRecord , , acLast
90    Me.ReviewDate.SetFocus
100   Exit Sub

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

End Sub

--------------------
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
 
cheekybuddha
post Sep 18 2019, 12:53 PM
Post#16


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


Not in Post#20, it wasn't!

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Sep 18 2019, 12:55 PM
Post#17


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


With the code in Post#27 I can not duplicate a compile error.

There must be a syntax error elsewhere in your code.

Otherwise, perhaps save everything and do a compact and repair.

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Sep 18 2019, 01:48 PM
Post#18


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


Hi David

Did a Compact & Repair and still no joy

--------------------
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
 
cheekybuddha
post Sep 18 2019, 02:05 PM
Post#19


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


What happens when you do Debug->Compile ?

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Sep 18 2019, 02:16 PM
Post#20


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


Hi David

I commented out all code and then did a Compile.

No errors found.

Run the On Click Event and same result


--------------------
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
 
cheekybuddha
post Sep 18 2019, 02:18 PM
Post#21


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


Even with the code commented out?

Do you know how to decompile? If so, perhaps worth a shot, then C&R then compile again.

If not, perhaps it's time to start importing objects into a new database.

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Sep 18 2019, 02:31 PM
Post#22


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


Hi David

Here with a stripped down example

Attached File  Test.zip ( 52.26K )Number of downloads: 4

--------------------
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
 
tina t
post Sep 19 2019, 02:28 PM
Post#23



Posts: 6,361
Joined: 11-November 10
From: SoCal, USA


hi Mike, i downloaded your test db, and ran it in A2016 64-bit on Win7Pro. i put a break in the code immediately after the Debug.Print line, then copied the SQL statement from the Immediate window, pasted it into the SQL pane of a new query, and changed to DatasheetView. i got the following error msg, as

Attached File  Capture.PNG ( 30K )Number of downloads: 0


see the red dots where Access doesn't like the fieldname, probably because it begins with a number instead of an alpha character. so i put both instances of the fieldname in brackets, in the SQL statement in VBA, as

QUOTE
30 strSQL = "INSERT INTO tblCareReviews (Resident_FK, ReviewDate, PrimaryComorbidities, NursingCaseMixCategory_FK, SkilledNsg, NursingCareNeedsNotes, " _
& " TherapyCareNeedsNotes, SkilledOT, OTProjectedSched , SkilledPT, PTProjectedSched, SkilledSLP, SLPProjectedSched, PriorSetting , EstimatedTimeFrame, " _
& " [100thDayDateActual], LastCoveredDay, DischargePlan, DischargeLocation, AreasofFocusConcern, CareConferences) " _
& " SELECT cr.Resident_FK, cr.ReviewDate, " _
& " cr.PrimaryComorbidities, cr.NursingCaseMixCategory_FK, cr.SkilledNsg, cr.NursingCareNeedsNotes, cr.TherapyCareNeedsNotes, cr.SkilledOT, cr.OTProjectedSched , " _
& " cr.SkilledPT, cr.PTProjectedSched, cr.SkilledSLP, cr.SLPProjectedSched, cr.PriorSetting, cr.EstimatedTimeFrame, cr.[100thDayDateActual], cr.LastCoveredDay, " _
& " cr.DischargePlan, cr.DischargeLocation, cr.AreasofFocusConcern, cr.CareConferences " _
& " FROM tblCareReviews cr " _
& " WHERE CareReviewsID = " & Me.CareReviewsID & ";"

then i removed the break from the code and ran it again by just clicking the button on the form. it worked without error.

i know you used an alternative method to move forward, hon; just thought you might be interested in the result of my test. :)

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Sep 19 2019, 02:37 PM
Post#24


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


Hi Tina

Many thanks just works a treat.

Thanks again to everyone.

kisses.gif pompom.gif
cheers.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
 
tina t
post Sep 19 2019, 03:10 PM
Post#25



Posts: 6,361
Joined: 11-November 10
From: SoCal, USA


you're welcome, hon, it's nice to finally be able to help someone using A2016! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cheekybuddha
post Sep 20 2019, 04:18 AM
Post#26


UtterAccess Moderator
Posts: 12,285
Joined: 6-December 03
From: Telegraph Hill


yayhandclap.gif yayhandclap.gif Double-Yay! yayhandclap.gif yayhandclap.gif

uarulez2.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
PatHartman
post Sep 21 2019, 10:18 AM
Post#27



Posts: 71
Joined: 20-February 03
From: Stratford,Ct USA


Just so you know, your original problem was caused because you were copying form fields to variables and then inserting the variables in the SQL string. This was an unnecessary step. You could have just referenced the form fields in the insert statement. The problem was caused because at least one of the string fields was occasionally null and string variables CANNOT be Null. Another way to resolve the problem would have been to define the variables as Variant rather than string. But simply omitting the "copy to a variable" step would have eliminated the problem.
Go to the top of the page
 

Posts in this topic
- mike60smart   Invalid Use Of Null   Sep 16 2019, 11:40 AM
- - theDBguy   Hi Mike. I didn't read the entire code. Which ...   Sep 16 2019, 11:54 AM
- - mike60smart   Hi theDBGuy That is the strange thing it does not...   Sep 16 2019, 01:40 PM
- - tina t   hi Mike, have you tried setting a breakpoint and s...   Sep 16 2019, 02:10 PM
|- - mike60smart   Hi tina Did that and located a wrong spelling. N...   Sep 16 2019, 02:40 PM
|- - theDBguy   "wrong spelling?" Interesting that you g...   Sep 16 2019, 03:01 PM
|- - mike60smart   Hi theDBGuy Yes Very strange. I put the break po...   Sep 16 2019, 03:05 PM
- - Phil_cattivocarattere   QUOTE (mike60smart)That is the strange thing it do...   Sep 16 2019, 11:24 PM
- - mike60smart   Hi Phil Many thanks for pointing that out. I tho...   Sep 17 2019, 07:48 AM
- - cheekybuddha   Hi Mike, You can probably make all this a lot eas...   Sep 17 2019, 03:08 PM
- - mike60smart   Hi David These lines all highlight in Red ?? COD...   Sep 17 2019, 04:05 PM
- - cheekybuddha   Good morning, Mike. It appears that you have lost...   Sep 18 2019, 03:21 AM
- - mike60smart   Hi David When I paste the strSQL into my code it ...   Sep 18 2019, 04:33 AM
- - mike60smart   Hi David This is what the code is now:- CODEPriv...   Sep 18 2019, 04:35 AM
- - Phil_cattivocarattere   In this lineCODE& " FROM tblCareReviews cr...   Sep 18 2019, 04:56 AM
- - mike60smart   Hi Phil Thanks. Changed that and now get the foll...   Sep 18 2019, 05:46 AM
|- - cheekybuddha   Hi Mike, Can you post the SQL output in the immed...   Sep 18 2019, 10:11 AM
|- - cheekybuddha   That's a weird one, Mike. What is highlighted...   Sep 18 2019, 11:35 AM
|- - mike60smart   Hi David The error Pops up and it does not highli...   Sep 18 2019, 12:03 PM
- - mike60smart   Hi Phil Is it because these two fields are DateTi...   Sep 18 2019, 05:52 AM
- - Phil_cattivocarattere   QUOTE (mike60smart)Is it because these two fields ...   Sep 18 2019, 06:01 AM
- - mike60smart   Hi Phil This is the Immediate Window:- CODEINSER...   Sep 18 2019, 06:08 AM
- - mike60smart   Hi Phil I rearranged the fields so that they now ...   Sep 18 2019, 06:36 AM
- - Phil_cattivocarattere   CODEstrSQL "INSERT INTO tblCareReviews...you m...   Sep 18 2019, 07:20 AM
|- - mike60smart   Hi Phil Ok now it reverts to the same Error 3075 ...   Sep 18 2019, 08:13 AM
- - cheekybuddha   Look at the beginning of line 30: You have: CODE3...   Sep 18 2019, 12:41 PM
- - mike60smart   Hi David No the code is this:- CODEPrivate Sub c...   Sep 18 2019, 12:47 PM
- - cheekybuddha   Not in Post#20, it wasn't!   Sep 18 2019, 12:53 PM
- - cheekybuddha   With the code in Post#27 I can not duplicate a com...   Sep 18 2019, 12:55 PM
- - mike60smart   Hi David Did a Compact & Repair and still no ...   Sep 18 2019, 01:48 PM
- - cheekybuddha   What happens when you do Debug->Compile ?   Sep 18 2019, 02:05 PM
- - mike60smart   Hi David I commented out all code and then did a ...   Sep 18 2019, 02:16 PM
- - cheekybuddha   Even with the code commented out? Do you know how...   Sep 18 2019, 02:18 PM
- - mike60smart   Hi David Here with a stripped down example   Sep 18 2019, 02:31 PM
- - cheekybuddha   Hi Mike, It looks like you are using a newer vers...   Sep 18 2019, 04:36 PM
|- - Phil_cattivocarattere   QUOTE (cheekybuddha)@Phil, are you still checking ...   Sep 19 2019, 03:07 AM
||- - cheekybuddha   @Phil, Great! Just wondered if you were able...   Sep 19 2019, 03:29 AM
|- - mike60smart   Hi David No there are no special fields like MVF ...   Sep 19 2019, 04:47 AM
|- - Phil_cattivocarattere   QUOTE (cheekybuddha)Just wondered if you were able...   Sep 19 2019, 07:13 AM
|- - mike60smart   Hi Phil & David I have managed to solve my pr...   Sep 19 2019, 11:36 AM
|- - cheekybuddha   Well, certainly an alternative way of going about ...   Sep 19 2019, 01:19 PM
- - tina t   hi Mike, i downloaded your test db, and ran it in ...   Sep 19 2019, 02:28 PM
- - mike60smart   Hi Tina Many thanks for the update. I will test ...   Sep 19 2019, 02:31 PM
|- - Phil_cattivocarattere   Tina_t got the same than me. Your test must pass.   Sep 19 2019, 02:33 PM
- - mike60smart   Hi Tina Many thanks just works a treat. Thanks a...   Sep 19 2019, 02:37 PM
- - tina t   you're welcome, hon, it's nice to finally ...   Sep 19 2019, 03:10 PM
- - cheekybuddha   Double-Yay!   Sep 20 2019, 04:18 AM
- - PatHartman   Just so you know, your original problem was caused...   Sep 21 2019, 10:18 AM



Custom Search


RSSSearch   Top   Lo-Fi    20th February 2020 - 10:00 PM