Validation assistance (SOLVED)

[Michael Abrams]

I am trying to validate if a record exists in the Table tblEDI_LOG
Form Name = frm_EDI_LOG) – form is Unbound

Here are the field names and associated control names:

TABLE NAME FIELD NAME
HEALTHPLAN (Text) cboHP
CLIENT (Text) CLIENT_LIST
FILE_MONTH (Text) cboFile_Month
FILE_WEEK (Text) cboFile_Week
FILE_DATE (Short Date) txtFile_Date

Here is the code I have behind the Save Records button.
(All of the other code works when I remove the Validation Code)

I need to use all 5 fields as validation (although Report Week and/or Report Date can be null)
I tried to do this just with 2 fields as a test, but I would guess some of my single/double quotes are messing me up.

I need help with the DCount

Code: Select all

Private Sub SAVE_RECS_Click()

    Dim varItem As Variant
    Dim strSQL As String
    Dim db As Database
    Dim rec As Recordset
    
    For Each varItem In Me.CLIENT_LIST.ItemsSelected
    
       If DCount("'" & " And " & "[HEALTHPLAN] = " & "'" & Me.cbo_HP & "*", "tblEDI_LOG", "[CLIENT] = " & "'" & Me.CLIENT_LIST.ItemData(varItem)) > 0 Then
       MsgBox "A record already exists for one or more of the records you are trying to add on the requested date, the records will not be saved."
      DoCmd.CancelEvent
    Exit Sub
       Else: End If 
        
      
                Set db = CurrentDb
                Set rec = db.OpenRecordset("Select * from [tblEDI_LOG]")
            
                rec.AddNew
                
                rec("CLIENT") = Me.CLIENT_LIST.ItemData(varItem)
                rec("Healthplan") = Me.cbo_HP
                rec("FILE_month") = Me.cboFile_Month
                rec("FILE_week") = Me.cboFile_Week
                rec("FILE_date") = Me.txtFile_Date
                rec("TIME_STAMP") = Now
                rec.Update
                
            Set rec = Nothing
            Set db = Nothing
    Next varItem
    
End Sub

Thank you for helping me !

Michael

[HansV]

I'll assume that you meant cbo_HP instead of cboHP.
Try replacing

Code: Select all

       If DCount("'" & " And " & "[HEALTHPLAN] = " & "'" & Me.cbo_HP & "*", "tblEDI_LOG", "[CLIENT] = " & "'" & Me.CLIENT_LIST.ItemData(varItem)) > 0 Then

with

Code: Select all

    Dim strWhere As String
    strWhere = "HEALTHPLAN='" & Me.cbo_HP & "' AND CLIENT='" & Me.CLIENT_LIST.ItemData(varItem) & _
        "' AND FILE_MONTH='" & Me.cboFile_Month & "'"
    If Not IsNull(Me.cboFile_Week) Then
        strWhere = strWhere & " AND FILE_WEEK='" & Me.cboFile_Week & "'"
    End If
    If Not IsNull(Me.cboFile_Date) Then
        strWhere = strWhere & " AND FILE_DATE='" & Me.cboFile_Date & "'"
    End If
    If DCount("*", "tblEDI_LOG", strWhere) > 0 Then

[Michael Abrams]

Thank you Hans - I will work on this today and let you know how it works out.

(My guess is that it's perfect !)

Thank you !

MIchael

[Michael Abrams]

OK - message says "Next without For" There is a "For Each" - so I do not understand.

Code: Select all

Private Sub SAVE_RECS_Click()

    Dim varItem As Variant
    Dim strSQL As String
    Dim db As Database
    Dim rec As Recordset   
   
    
    For Each varItem In Me.CLIENT_LIST.ItemsSelected
    
     Dim strWhere As String
    strWhere = "HEALTHPLAN='" & Me.cboHP & "' AND CLIENT='" & Me.CLIENT_LIST.ItemData(varItem) & _
        "' AND FILE_MONTH='" & Me.cboFile_Month & "'"
    If Not IsNull(Me.cboFile_Week) Then
        strWhere = strWhere & " AND FILE_WEEK='" & Me.cboFile_Week & "'"
    End If
    If Not IsNull(Me.txtFile_Date) Then
        strWhere = strWhere & " AND FILE_DATE='" & Me.txtFile_Date & "'"
    End If
    If DCount("*", "tblEDI_LOG", strWhere) > 0 Then
      
    
   
        
                Set db = CurrentDb
                Set rec = db.OpenRecordset("Select * from [tblEDI_LOG]")
            
                rec.AddNew
                
                rec("CLIENT") = Me.CLIENT_LIST.ItemData(varItem)
                rec("Healthplan") = Me.cboHP
                rec("FILE_month") = Me.cboFile_Month
                rec("FILE_week") = Me.cboFile_Week
                rec("FILE_date") = Me.txtFile_Date
                rec("TIME_STAMP") = Now
                rec.Update
                
            Set rec = Nothing
            Set db = Nothing
    Next varItem
    

    
End Sub

Can you take a look please ? Did I place the validation code in the wrong spot?

(Assume the field names and control names are now correct)

Again - thank you so much !

MIchael

[HansV]

You omitted the part below the If DCount(...) > 0 Then line.

If you want to add a new record for each selected item in the client list box that doesn't have a match, use this:

Code: Select all

Private Sub SAVE_RECS_Click()
    Dim varItem As Variant
    Dim strSQL As String
    Dim db As Database
    Dim rec As Recordset
    Dim strWhere As String

    Set db = CurrentDb
    Set rec = db.OpenRecordset("tblEDI_LOG", dbOpenDynaset)

    For Each varItem In Me.CLIENT_LIST.ItemsSelected
        strWhere = "HEALTHPLAN='" & Me.cboHP & "' AND CLIENT='" & Me.CLIENT_LIST.ItemData(varItem) & _
            "' AND FILE_MONTH='" & Me.cboFile_Month & "'"
        If Not IsNull(Me.cboFile_Week) Then
            strWhere = strWhere & " AND FILE_WEEK='" & Me.cboFile_Week & "'"
        End If
        If Not IsNull(Me.txtFile_Date) Then
            strWhere = strWhere & " AND FILE_DATE='" & Me.txtFile_Date & "'"
        End If
        If DCount("*", "tblEDI_LOG", strWhere) = 0 Then
            rec.AddNew
            rec("CLIENT") = Me.CLIENT_LIST.ItemData(varItem)
            rec("Healthplan") = Me.cboHP
            rec("FILE_month") = Me.cboFile_Month
            rec("FILE_week") = Me.cboFile_Week
            rec("FILE_date") = Me.txtFile_Date
            rec("TIME_STAMP") = Now
            rec.Update
        End If
    Next varItem

    rec.Close
    Set rec = Nothing
    Set db = Nothing
End Sub

[Michael Abrams]

Thanks Hans - beautiful....

Thank you so much.

MIchael

[Michael Abrams]

Another head shaker.

I am now receiving
RunTime error 3464
Data type mismatch in criteria expressionat this line:

If DCount("*", "tblEDI_LOG", strWhere) = 0 Then

when I enter a date in the txtFile_Date field.

The code works fine when any or all of the other fields are entered. It only occurs after entering txtFile_Date.

In addition, I still cannot figure out why I cannot Add a New record.

I have attached a stripped down version for you to view.

Thank you for helping me out with this.

MIchael

[Michael Abrams]

OK - I may be getting closer - txtFile_Date is a date field - I believe I should be using #. Just trying different ways to use the #.

If I am successful, I will post back ASAP.

Michael

[HansV]

Try this:

Code: Select all

        If Not IsNull(Me.txtFile_Date) Then
            strWhere = strWhere & " AND FILE_DATE=#" & Format(Me.txtFile_Date, "yyyy/mm/dd") & "#"
        End If

[Michael Abrams]

Of course it does work now. Thank you !

Were you able to figure out why it won't add a new record?

Much, much appreciated HansV.

Michael

[HansV]

I'll take a look at that now.

[Michael Abrams]

Thank you !

Michael

[HansV]

Your form is unbound - its Record Source is blank. You cannot go to a new record on an unbound form...

[Michael Abrams]

Oy.
Looks like I have some work ahead of me.

I will attempt to have the form bound to the table and see how much I will need to tweak the code.

Thanks HansV.


Michael

[HansV]

You currently save the data from the form explicitly using the Save Record button. To start with a "fresh" record, you only need to clear the controls on the form:

Code: Select all

Private Sub cmdAddNewRec_Click()
    Dim i As Long
    If MsgBox("Did you click the SAVE BUTTON?", _
        vbQuestion + vbYesNo, "CONTINUE?") = vbNo Then
    Else
        Me.cboHP = Null
        Me.cboFile_Month = Null
        Me.cboFile_Week = Null
        Me.txtFile_Date = Null
        Me.txtRecvdHP = Null
        Me.chkNoFile = False
        For i = 0 To Me.CLIENT_LIST.ListCount - 1
            Me.CLIENT_LIST.Selected(i) = False
        Next i
        Me.cboHP.SetFocus
    End If
End Sub

You can remove the button CLEAR_FORM - it does more or less the same.

[Michael Abrams]

Using the above attached, when I open the form and go to record 2, the client is not highlighted.

I have removed the Clear Button.

[HansV]

Your code sets focus to cboHP (I didn't change that). So what is the problem?

[Michael Abrams]

My co-worker just nudged me and explained that I should keep the form unbound, and on the "Add New Record Button" (which actually should just save the record and then clear the form) add code after saving the record, to clear all fields - which 'appears' to the user as adding a new record.
I need to remove the docmd add new record of course.

I will let you know how this works out. As always, thank you for your patience.

Michael

[HansV]

It seems that a new reply to this thread disappeared. For what it's worth:

FILE_WEEK is a Date/Time field, but you treat it as a text field. Change the line

Code: Select all

            strWhere = strWhere & " AND FILE_WEEK='" & Me.cboFile_Week & "'"

to

Code: Select all

            strWhere = strWhere & " AND FILE_WEEK=#" & Format(Me.cboFile_Week, "yyyy/mm/dd") & "#"

[Michael Abrams]

I apologize for that HansV. A minute or two after clicking Submit, I realized that it was a matter of switching to the #.
So I deleted the question.

Next time I will leave the question and just edit it to say I solved it.

Thank you - Michael