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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Invalid Use Of Null, Access 2016    
 
   
mike60smart
post Sep 16 2019, 11:40 AM
Post#1


UtterAccess VIP
Posts: 13,470
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
 
theDBguy
post Sep 16 2019, 11:54 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,548
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,470
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
 
tina t
post Sep 16 2019, 02:10 PM
Post#4



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


hi Mike, have you tried setting a breakpoint and stepping through the code line by line?

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Sep 16 2019, 02:40 PM
Post#5


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


Hi tina

Did that and located a wrong spelling.

Now works as it should

Many thanks for the help

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
 
theDBguy
post Sep 16 2019, 03:01 PM
Post#6


Access Wiki and Forums Moderator
Posts: 76,548
Joined: 19-June 07
From: SunnySandyEggo


"wrong spelling?" Interesting that you got an invalid use of null rather than a parameter prompt or maybe error 3061. Just curious...

--------------------
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, 03:05 PM
Post#7


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


Hi theDBGuy

Yes Very strange.

I put the break points in as tina suggested and went through each control and is highlighted a Control which had the wrong spelling.

Corrected and worked just fine.


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



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


UtterAccess VIP
Posts: 13,470
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#10


UtterAccess VIP
Posts: 11,678
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#11


UtterAccess VIP
Posts: 13,470
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#12


UtterAccess VIP
Posts: 11,678
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:33 AM
Post#13


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


Hi David

When I paste the strSQL into my code it still highlights all in Red??


--------------------
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
 
mike60smart
post Sep 18 2019, 04:35 AM
Post#14


UtterAccess VIP
Posts: 13,470
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#15



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:46 AM
Post#16


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


Hi Phil

Thanks. Changed that and now get the following error:-

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


50 CurrentDb.Execute strSQL, dbFailOnError

??

--------------------
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
 
mike60smart
post Sep 18 2019, 05:52 AM
Post#17


UtterAccess VIP
Posts: 13,470
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#18



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:08 AM
Post#19


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


Hi Phil

This is the Immediate Window:-

CODE
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 CareReviewsID = 15;

--------------------
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
 
mike60smart
post Sep 18 2019, 06:36 AM
Post#20


UtterAccess VIP
Posts: 13,470
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
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 05:51 AM