MsgBox Reference Field from Table on unbound form

**DCV0204** · 02-13-2015, 07:00 AM

Hi, I have been trying to get this to work with no luck.

I have an unbound form with an unbound text box named SrchMedID with serveral buttons. Within my code I have a msgbox to ask a question; within the msgbox I would like to show the date populated in my field named "SuppressDate" if there is a date entered (not all records have a date). Here is the latest code I have come up with (the code in red below is where I am having the issue):

Private Sub btnsrch_Click()
Dim MedID As String
Dim SuppressDate As Date
' Get Value from Medicaid_ID textbox on the Form:
Me.btnsrch.SetFocus
Me.SrchMed_ID.SetFocus
MedID = Me.SrchMed_ID.Text

Dim Msg, Style, Title, MyString
Dim Response As Integer

If IsNull(DLookup("[Medicaid_ID]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'")) Then

DoCmd.OpenForm "RA_Tracking", , , , acFormAdd
Me.SrchMed_ID = ""

Else

If (DCount("[Medicaid_ID]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'" & _
"And Not Nz([SuppressDate])")) Then

DoCmd.OpenForm "RA_Tracking", , , "Returned_Mail.Medicaid_ID = '" & SrchMed_ID & "'"
Me.SrchMed_ID = ""

Else

Response = MsgBox("Is the RA a Refund with a $0.00 balance with an RA Date prior to " & Format([SuppressDate], "mm/dd/yyyy") & " (date will be set-up tp auto populate later, click 'No' and compare RA Date to Suppress Date)" & vbNewLine & vbNewLine & "If RA Date is prior to the Suppress Date = Recycle; otherwise log RA and give to Lisa", vbYesNo) 'added 12/11/2014

If Response = vbYes Then 'added 12/112014

MsgBox ("Do not log RA's for this Medicaid ID:" & (Chr(13)) & (Chr(13)) & " " & [SrchMed_ID] & (Chr(13)) & (Chr(13)) & "Recycle this providers RA's")
Me.SrchMed_ID = ""

ElseIf Response = vbNo Then

DoCmd.OpenForm "RA_Tracking", , , "Returned_Mail.Medicaid_ID = '" & SrchMed_ID & "'"
Me.SrchMed_ID = ""

End If
End If
End If
End Sub

The message I receive with this code is:

Is the RA a Refund with a $0.00 balance with an RA Date prior to 12/30/1899 (date will be set-up tp auto populate later, click 'No' and compare RA Date to Suppress Date)

If RA Date is prior to the Suppress Date = Recycle, otherwise log RA and give to Lisa

12/30/1899 is not the date in the field on my table for the record. I have also tried:

Format("SuppressDate", "mm/dd/yyyy")
[SuppressDate]
(SuppressDate)

any suggestions to get the msgbox to display the date field named "SuppressDate" from my table named "Returned_Mail" on an unbound form?
Thanks

**NTC** · 02-13-2015, 07:21 AM

first sanity check things by not doing the Format the first time around...

also instead of: & [SuppressDate] &

try: # [SuppressDate] #

**DCV0204** · 02-13-2015, 07:48 AM

Actually the Format was the last thing I tried...

Your suggestion: #[SuppressDate]# gives me a the following message:

Is the RA a Refund with a $0.00 balance with an RA Date prior to #[SuppressDate]# (date will be set-up tp auto populate later, click 'No' and compare RA Date to Suppress Date)

If RA Date is prior to the Suppress Date = Recycle, otherwise log RA and give to Lisa

Since my form is unbound and the "SuppressDate" field is not a part of the form, do I need to reference the table from where the field "SuppressDate" is maintained?
Thanks.

**orange** · 02-13-2015, 09:15 AM

You need to tell Access where or what #[SuppressDate]# is.

I find your code difficult to read.

I have a free routine called SmartIndenter and I ran your code though it to get this reformatting

Code:

Private Sub btnsrch_Click()
    Dim MedID As String
    Dim SuppressDate As Date
    ' Get Value from Medicaid_ID textbox on the Form:
    Me.btnsrch.SetFocus
    Me.SrchMed_ID.SetFocus
    MedID = Me.SrchMed_ID.text

    Dim Msg, Style, Title, MyString   '<-----these are variants by default
    Dim Response As Integer

    If IsNull(DLookup("[Medicaid_ID]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'")) Then
        DoCmd.OpenForm "RA_Tracking", , , , acFormAdd
        Me.SrchMed_ID = ""

    Else

        If (DCount("[Medicaid_ID]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'" & _
                                                     "And Not Nz([SuppressDate])")) Then 'what do you expect from NZ
                                                                                                          'maybe you meant IsNull()

            DoCmd.OpenForm "RA_Tracking", , , "Returned_Mail.Medicaid_ID = '" & SrchMed_ID & "'"
            Me.SrchMed_ID = ""

        Else

            Response = MsgBox("Is the RA a Refund with a $0.00 balance with an RA Date prior to " & Format([SuppressDate], "mm/dd/yyyy") & " (date will be set-up tp auto populate later, click 'No' and compare RA Date to Suppress Date)" & vbNewLine & vbNewLine & "If RA Date is prior to the Suppress Date = Recycle; otherwise log RA and give to Lisa", vbYesNo)    'added 12/11/2014

            If Response = vbYes Then    'added 12/112014

                MsgBox ("Do not log RA's for this Medicaid ID:" & (Chr(13)) & (Chr(13)) & " " & [SrchMed_ID] & (Chr(13)) & (Chr(13)) & "Recycle this providers RA's")
                Me.SrchMed_ID = ""



            ElseIf Response = vbNo Then

                DoCmd.OpenForm "RA_Tracking", , , "Returned_Mail.Medicaid_ID = '" & SrchMed_ID & "'"
                Me.SrchMed_ID = ""


            End If
        End If
    End If
End Sub

Where is SuppressDate??

Do you have a clear concise description of what this code is trying to accomplish?
I recommend you test incrementally to get piece to work, and then progressively add more details/complexity.
Good luck with your project.

**DCV0204** · 02-13-2015, 09:27 AM

Ok, I made some changes to my code and it seems to be working now. Here is the new code:

Dim MedID As String
Dim SuppressDate As Date
' Get Value from Medicaid_ID textbox on the Form:
Me.btnsrch.SetFocus
Me.SrchMed_ID.SetFocus
MedID = Me.SrchMed_ID.Text

If IsNull(DLookup("[Medicaid_ID]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'")) Then
DoCmd.OpenForm "RA_Tracking", , , , acFormAdd
Me.SrchMed_ID = ""

Else
If (DCount("[Medicaid_ID]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'" & _
"And Not Nz([SuppressDate])")) Then

DoCmd.OpenForm "RA_Tracking", , , "Returned_Mail.Medicaid_ID = '" & SrchMed_ID & "'"
Me.SrchMed_ID = ""

Else

Dim Msg, Style, Title, MyString
Dim Response As Integer
Msg = (Chr(10)) & " Does the RA contain a 'Refunds from Provider' with a 'Refund Total' of $0.00 for 'Suspense Remaining' with an RA Date prior to" & vbNewLine & vbNewLine & " " & (DLookup("[SuppressDate]", "Returned_Mail", "Medicaid_ID = '" & SrchMed_ID & "'")) & "? " & vbNewLine & vbNewLine & "Select:" & vbNewLine & "Yes = Recycle RA" & vbNewLine & "No = Input RA" & vbNewLine & "Cancel - Cancel Inquiry"
Style = vbYesNoCancel + vbInformation + vbDefaultButton0
Title = "Answer Question"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then

MsgBox ("Do not log RA's for this Medicaid ID:" & (Chr(13)) & (Chr(13)) & " " & [SrchMed_ID] & (Chr(13)) & (Chr(13)) & "Recycle this providers RA's")
Me.SrchMed_ID = ""
Else
If Response = vbNo Then
DoCmd.OpenForm "RA_Tracking", , , "Returned_Mail.Medicaid_ID = '" & SrchMed_ID & "'"
Me.SrchMed_ID = ""
If Response = vbCancel Then
MyString = "Cancel"
DoCmd.CancelEvent
Me.SrchMed_ID = ""
Else
Response = Cancel
Me.SrchMed_ID = ""
Close

End If
End If
End If
End If
End If
End Sub

**orange** · 02-13-2015, 07:24 PM

I still recommend you get the free SmartIndenter, and also MZTools for VBA also free.
They are excellent, free utilities.

MsgBox Reference Field from Table on unbound form

Thread Tools

MsgBox Reference Field from Table on unbound form

Similar Threads

Saving unbound Attachment form field to table attachment field

Filling field on underlying table of subform from unbound combobox on main form

How to get value in a combo box into the table field, I only get the reference number

Include a table field within the message of msgbox for confirmation

With my unbound object frame control (reference to Excel spreadsheet)

Posting Permissions