DLookUp from unbound textbox or DLookUp from another unbound textbox

[DCV0204]

Ok, the code below works for my unbound textbox named SrchMed_ID; but have discovered that sometimes the Med_ID is not recognized in my table when it is present when performing a search, so I have decided to added an additional unbound textbox named srchnpi to my form. I am wanting to be able to enter a number (txt) into either SrchMed_ID or srchnpi click my button named btnsrch and it search my "Provider" table for the record and perform the docmd's in my code. I have tried changing my code several different ways I have indicated a few in red in my code below and can not figure it out. Should I be using one unbound textbox to accomplish this? Can anyone help me? Thanks.



Private Sub btnsrch_Click()
Dim MedID As String
' 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
Msg = "File Already Exist" & vbNewLine & vbNewLine & "Do not add to database." & vbNewLine & "Do not create another file folder." & vbNewLine & vbNewLine & "Would you like to update the database record. "
Title = "Information"
If IsNull(DLookup("[Medicaid_ID]", "Provider", "Medicaid_ID = '" & SrchMed_ID & "'" I tried adding an Or statement here)) Then
' NAME is Null because the Medicaid_ID doesn't exist.
DoCmd.OpenForm "AddNewFile", , , , acFormAdd
I have also tried to duplicate the dlookup statement above here change the focus to the "NPI_Number" (additional field in table to be searched)
Else
Msg = "File Already Exist" & vbNewLine & vbNewLine & "Do not add to database." & vbNewLine & "Do not create another file folder." & vbNewLine & vbNewLine & "Would you like to update the database record. "
Style = vbYesNo + vbInformation + vbDefaultButton2
Title = "Information"

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then 'User chose Yes.
MyString = "Yes"
DoCmd.OpenForm "UpdateCurrentFile", , , "Medicaid_ID = '" & SrchMed_ID & "'"

'Else
'DoCmd.OpenForm "UpdateCurrentFile", , , "NPI = '" & srchnpi & "'"
If Response = vbNo Then 'User chose No.
MyString = "No" 'Perform some action.

End If
End If
End If


End Sub

[pbaldy]

I don't see the point of the second textbox; I'd try to find out why it sometimes fails. Is there a pattern to when, or what value it misses?

[Missinglinq]

If IsNull(DLookup("[Medicaid_ID]", "Provider", "Medicaid_ID = '" & SrchMed_ID & "'" I tried adding an Or statement here)) Then

Asking to return the [Medicaid_ID] if the [Medicaid_ID] exists, which is what you're doing, doesn't really make a lot of sense to me. What you want to know is if a Record exists with the given [Medicaid_ID]. This is normally done using DCount, rather than DLookup, and the syntax would be something like this:

If DCount("*", "Provider", "Medicaid_ID = '" & SrchMed_ID & "'") = 0 Then

Linq ;0)>

[ssanfu]

I have to side with Linq; DCount() would be better than DLookup().

I did rework your code a little .... (this is untested)

Private Sub btnsrch_Click()
   Dim strMedID As String
   Dim varMed_ID As Variant  ' can hold a NULL
   Dim Msg, Style, Title

   ' Get Value from Medicaid_ID textbox on the Form:
   strMedID = Me.SrchMed_ID
   '   Me.btnsrch.SetFocus
   '   Me.SrchMed_ID.SetFocus
   '   MedID = Me.SrchMed_ID.Text

   '------------------
   ' for testing - comment or delete when debugging complete
   MsgBox strMedID & ""
   '------------------

   varMed_ID = DLookup("[Medicaid_ID]", "Provider", "Medicaid_ID = '" & strMedID & "'")

   If Len(varMed_ID & "") = 0 Then
      ' Medicaid_ID doesn't exist.
      DoCmd.OpenForm "AddNewFile", , , , acFormAdd

   Else
      ' Medicaid_ID exists.
      Msg = "Medicaid ID Already Exists" & vbNewLine & vbNewLine & "Do not add to database." & vbNewLine & "Do not create another file folder." & vbNewLine & vbNewLine & "Would you like to update the database record. "
      Style = vbYesNo + vbInformation + vbDefaultButton2
      Title = "Information"

      Response = MsgBox(Msg, Style, Title)
      If Response = vbYes Then   'User chose Yes.
         DoCmd.OpenForm "UpdateCurrentFile", , , "[Medicaid_ID] = '" & strMedID & "'"
      End If

   End If


End Sub

[pbaldy]

I would use DCount() myself, but I don't think that's a factor in why the DLookup() fails intermittently.