No Clue what I'm doing

[m1sund3st00d]

I am new to VBA, as you will soon see... , and have limited experience in Access as well.

BG: I agreed to develop a database for my company for the purpose of tracking and logging marketing calls. I have created a flat table to track the individual potential customer data, and by law a second to validate the phone number is one that we can call (not on a DNC list).

This is where I am having problems. I need to verify the phone number from our marketing leads is not on any DNC Lists (which are imported in the second table).

I not sure if I need even use VBA or if a Macro will do the trick.

I have tried a few snippets from posts here but I can seem to get anything to run correctly.

Any help is Greatly! Appreciated!!

 

[pbaldy]

To check one number, open a recordset or use DCount to see if it's in the DNC table. To get a list of numbers you can call, use the unmatched query wizard to find numbers in your prospect table that aren't in the DNC table.

 

[m1sund3st00d]

Thanks for the direction. Unfortunately I maybe a complete dope. I picked up a book on Access VBA (the yellow one) and this is what I came up with based on your direction and examples. Doesnt seem to do anything though.

I think I'm going to need some handholding through this...

 

[m1sund3st00d]

sorry forgot to paste the code.....

(Let see if i do this right)

Dim db As Database
    Dim rsTable As Recordset
        Set db = CurrentDb
    Dim strNum As String
        strNum = "tblDNCList.PhoneNum"
Set rsTable = db.OpenRecordset("tblDNCList")
    
If strNum = Me.Phone Then
    MsgBox "Warning!: This number is in a DNC Database. You must leave this number Blank." = vbOKOnly
    
rsTable.Close
 
 End If

 

[pbaldy]

Perhaps this cut from an old db will help:

Private Sub cmdUpdateCust_Click()
  Dim strSQL           As String
  Dim rsCust           As DAO.Recordset

  If IsNull(Me.cmbCust_DD) Then
    MsgBox "No customer selected"
    Exit Sub
  Else

    strSQL = "SELECT * From tblCustomer " & _
             "WHERE phone_num = '" & Me.cmbCust_DD & "'"

    Set rsCust = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    If rsCust.EOF Then
      'What to do if they don't exist
    Else
      'What to do if they do
    End If
  End If

  Set rsCust = Nothing

End Sub

 

[m1sund3st00d]

THANKS!! Your code snippet worked great... I greatly appreciate the help.
I think I will be looking into a course or two due to the fact I clearly do not
know what I am doing. Thanks again! Here is what I ended up with for others reference.

Private Sub Phone_AfterUpdate()

  Dim strSQL           As String
  Dim rsPhone           As DAO.Recordset

  If IsNull(Me.Phone) Then
    MsgBox "No Number Entered."
    Exit Sub
  Else

    strSQL = "SELECT * From tblDNCList " & _
             "WHERE PhoneNum = '" & Me.Phone & "'"

    Set rsPhone = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    If Not rsPhone.EOF Then
      'What to do if they don't exist
      'MsgBox "End of File"
      
    'Else
      'What to do if they do
     MsgBox "This Phone Number is in a DNC Database. You must leave this number empty"
      
    End If
  End If

  Set rsPhone = Nothing

End Sub