Saving data

[Quixxx]

Hello All,

I have been loosing my mind trying to figure this out. I have been trying to save a record from a form but I keep getting this Runtime error 3265 - Item not found in this data collection.

I know that the field names are correct. as a matter of fact, I have changed them thinking that maybe I had used them somewhere else.

Here is the code.

Private Sub cmdSave_Click()
Dim rstSet1 As New ADODB.Recordset
Dim groupNAMEtext As String
Dim statusRVA As String
Dim recIND As Integer
Dim strSQL As String

groupNAMEtext = Nz(Me.cboGroupLIST.Value, "")
statusRVA = Nz(Me.cboStatus.Value, "")

Set rstSet1 = New ADODB.Recordset

rstSet1.Open "tbldata", CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rstSet1
.AddNew
newRVAnumberCHECK = .Fields("rvaTrackingNUM")
If IsNull(statusRVA) Or statusRVA = "" Then
MsgBox "Please select the RVA status"
cboStatus.SetFocus
Exit Sub
Else
If statusRVA = "COMPLETED" Or statusRVA = "Sent" Then
If MsgBox("Do you wish to closed this RVA request?", vbYesNo, "Confirm") = vbYes Then
.Fields("status") = statusRVA
Else
Exit Sub
End If
Else
.Fields("status") = statusRVA
End If
End If

If chkGROUPDOC = True Then
If IsNull(txtDocNAME) Or txtDocNAME = "" Then
MsgBox "Please enter the Physician Name for this RVA"
Exit Sub
Else
.Fields("grpname") = Me.txtDocNAME.Value
End If
Else
If IsNull(groupNAMEtext) Or groupNAMEtext = "" Then
MsgBox "Please enter the Group Name for this RVA"
Exit Sub
Else
.Fields("grpName") = groupNAMEtext
End If
End If

If IsNull(txtSiteCO) Or txtSiteCO = "" Then
MsgBox "Please enter the Site Co-ordinators name"
txtSiteCO.SetFocus
Exit Sub
Else
.Fields("siteCoo") = Me.txtSiteCO.Value
End If

If IsNull(txtGroupTYPE) Or txtGroupTYPE = "" Then
MsgBox "Please enter the Group Type"
txtGroupTYPE.SetFocus
Exit Sub
Else
.Fields("currModel") = Me.txtGroupTYPE.Value
End If

If IsNull(txtLHIN) Or txtLHIN = "" Then
MsgBox "Please enter the LHIN number"
txtLHIN.SetFocus
Exit Sub
Else
.Fields("LHIN") = Me.txtLHIN.Value
End If

If IsNull(txtNewGROUP) Or txtNewGROUP = "" Then
MsgBox "Please enter the Proposed Group Type"
txtNewGROUP.SetFocus
Exit Sub
Else
.Fields("newModel") = Me.txtNewGROUP.Value
End If

.Fields("numDOCS") = Me.txtNumDOCS.Value

If IsNull(txtTargetDT) Or txtTargetDT = "" Then
Else
.Fields("targetDT") = CDate(Me.txtTargetDT.Value)
End If

If IsNull(txtAllCNSREC) Or txtAllCNSREC = "" Then
Else
.Fields("allCnsREC") = CDate(Me.txtAllCNSREC.Value)
End If

.Fields("numCNSREC") = Me.txtNumCNSREC.Value

If IsNull(txtDateSC) Or txtDateSC = "" Then
Else
.Fields("reviewDT_SC") = CDate(Me.txtDateSC.Value)
End If

If IsNull(txtDateAPPROVESC) Or txtDateAPPROVESC = "" Then
Else
.Fields("approveDTSC") = CDate(Me.txtDateAPPROVESC.Value)
End If

If IsNull(txtDataCOMPLETE) Or txtDataCOMPLETE = "" Then
Else
.Fields("dateDataPULL") = CDate(Me.txtDataCOMPLETE.Value)
End If

If IsNull(txtDataANALYZED) Or txtDataANALYZED = "" Then
Else
.Fields("dateAnalyzed") = CDate(Me.txtDataANALYZED.Value)
End If

If IsNull(txtDateSCreview) Or txtDateSCreview = "" Then
Else
.Fields("rvaReviewDT") = CDate(Me.txtDateSCreview.Value)
End If

If IsNull(txtDateSCapproved) Or txtDateSCapproved = "" Then
Else
.Fields("rvaApproveDT") = CDate(Me.txtDateSCapproved.Value)
End If

If IsNull(txtDateRVAcour) Or txtDateRVAcour = "" Then
Else
.Fields("puroDT") = CDate(Me.txtDateRVAcour.Value)
End If

If IsNull(txtGainLOSS) Or txtGainLOSS = "" Then
Else
.Fields(groupGainLOSS) = Me.txtGainLOSS.Value
End If

If IsNull(txtGroupPATIENTS) Or txtGroupPATIENTS = "" Then
Else
.Fields(grpRosterNUM) = Me.txtGroupPATIENTS.Value
End If

If IsNull(txtDataPULLcomplete) Or txtDataPULLcomplete = "" Then
Else
.Fields(dateDataPULLcomplete) = CDate(Me.txtDataPULLcomplete.Value)
End If

If IsNull(txtAnalysisPERIOD) Or txtAnalysisPERIOD = "" Then
Else
.Fields(analysisPeriod) = Me.txtAnalysisPERIOD.Value
End If

If IsNull(txtVersionNUM) Or txtVersionNUM = "" Then
MsgBox "Please enter the version number for this RVA"
txtVersionNUM.SetFocus
Exit Sub
Else
.Fields("versionNumber") = Me.txtVersionNUM.Value
End If

.Fields("Notes") = Nz(Me.txtNotes.Value, "")
.Fields("flag") = Me.chkflag.Value

End With

rstSet1.Update
rstSet1.Close
Set rstSet1 = Nothing
MsgBox "Data has been saved"
Forms!frmmain!lstStart.Requery
cmdSave.Enabled = False

End Sub


The problem is when the code gets to :

If IsNull(txtGroupPATIENTS) Or txtGroupPATIENTS = "" Then
Else
.Fields(grpRosterNUM) = Me.txtGroupPATIENTS.Value
End If

When I comment the code the next field is highlighted during debug.

Any thoughts?

Thanks a million!!

 

[djtech2k]

Is it the null check or the action that causes it to break? Just as a guess, maybe try taking out the "isnull".

Maybe something like:

If txtGroupPATIENTS <> "" Then
.Fields(grpRosterNUM) = Me.txtGroupPATIENTS.Value
End If

Just a guess, but again you need to know if its the null check or the action that bombs out.

 

[Quixxx]

Thanks for your answer... I figured it out already. I had added extra fields to the table and when I modified my code I forgot to put the quotes.

If IsNull(txtGroupPATIENTS) Or txtGroupPATIENTS = "" Then
Else
.Fields(grpRosterNUM) = Me.txtGroupPATIENTS.Value
End If

should have been

If IsNull(txtGroupPATIENTS) Or txtGroupPATIENTS = "" Then
Else
.Fields("grpRosterNUM") = Me.txtGroupPATIENTS.Value
End If

Thanks again!

 

[mrmovie]

i dont like all your If statements. you shoudl be able to code this a little better so that it is dynamic.

For Each aControl In myForm.Controls
If Left(aControl.Name) = "txt" Then
.... or some other dynamic manip of controls

Next

'think about dynamically creating controls (not just populating controls) on your form based on data.

 

[Quixxx]

I thought about doing something like that but in this case... the users using the form are just typing it in without any kind of verification. I wanted to make sure that the correct data was being entered without an input mask on all of the form fields.

I guess I could have started all the date fields with DTE rather then TXT and run through the loop that way.

Thanks for the tip!