Completing fields on a form with matching data from previous records...

[the-m0th]

Me Again! Are you sick of me yet?

anyway, i'm trying to make it a little further with my new call answering database at work.

Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.

i've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.

i'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.

i've tried searching online so i'm not just torturing you lot but i must be phrasing it wrong as i'm not coming up with anything. i even had a look through your code repository (it blew my mind with what you can do in access) but i couldn't find anything that looks like it could help me.

the code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows

Switchboard:-

Option Compare Database
Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI

Private Sub cmd_onnet_Click()
    DoCmd.OpenForm FormName:="On_Net_Communications"
End Sub

Private Sub Form_Load()
    Set MaxxCom = CreateObject("MaxxDTC.CTI")
End Sub

Private Sub MaxxCom_Established(ByVal Call_ID As String, ByVal Answering_Internal_Ext As String, ByVal Answering_Outside_Number As String, ByVal Answering_Device_Type As Metro_MaxxCom_CTI_COM_API.DeviceCategories, ByVal Internal_Calling_Ext As String, ByVal Outside_Caller_Number As String, ByVal Trunk_Outside_Number As String, ByVal Calling_Device_Type As Metro_MaxxCom_CTI_COM_API.DeviceCategories, ByVal Originally_Called_Dev As String, ByVal Last_Redirection_Ext As String, ByVal Local_Connection_State As Metro_MaxxCom_CTI_COM_API.LocalConnectionStates, ByVal Event_Cause As Metro_MaxxCom_CTI_COM_API.EventCauses)
    txt_incoming.Value = Outside_Caller_Number
End Sub

The call answering screen:-

Option Compare Database

Private Sub cmd_recordcomplete_Click()
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
    txt_dateandtime.Value = Now
    Me.txt_callertelephone.Value = Forms!Switchboard!txt_incoming.Value
    Forms!Switchboard!txt_incoming.Value = ""
End Sub

this is the code from the script file that pops the screens (i didn't write this, i just played about with it)

Option Strict On

Imports Metro.MaxxCom.CTI.Scripting.CTI
Module Script

#Region "Initialization Code - Do not remove"

    'This code is required for the script to function correctly - do not edit or remove!

    Private WithEvents CTI As Metro.MaxxCom.CTI.Scripting.CTI

    Public Sub Initialize(ByVal objCTI As Metro.MaxxCom.CTI.Scripting.CTI)
        CTI = objCTI
    End Sub


#End Region

    Private Sub CTI_Established(ByVal sender As Object, ByVal e As Metro.MaxxCom.CTI.Client.Monitor.EstablishedEventArgs) Handles CTI.Established
        If e.Trunk_Outside_Number <> "" Then
            If CTI.AppActivate("Call Answering Switchboard", AppActivateCriteria.Contains) Then
                CTI.SendKeys("%TMM")    'Run Macro
                System.Threading.Thread.Sleep(100)
                CTI.SendKeys(e.Trunk_Outside_Number & "{Enter}")
            End If
        End If
    End Sub

End Module

any help or links to information where i could at least get a toe in will be very much appreciated.

Wayne

 

[the-m0th]

little update, i've found a few posts on other sites that i'm pretty sure are trying to do the same thing as what i'm doing and i've progressed (can i call it progression when the code doesn't appear to do anything?) but the code doesn't appear to do anything. just wondering if one of you guys could have a quick look and tell me where i've gone wrong?

here's the updated code for the call answering screen, i've commented the code to show what i think it does...

Option Compare Database

Private Sub cmd_recordcomplete_Click()
    DoCmd.RunCommand acCmdSaveRecord 'saves the current record
    DoCmd.Close acForm, Me.Name 'closes the form
End Sub

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec 'goes to new record
    txt_dateandtime.Value = Now 'enters the current date and time into the correct box
    Me.txt_callertelephone.Value = Forms!Switchboard!txt_incoming.Value 'copies the outside number from the text box on the main form into the telephone number box on the call answer form
    Forms!Switchboard!txt_incoming.Value = "" 'resets the caller i.d. text box on the main form ready for the next incoming call.
    If txt_callertelephone.Value <> "" Then 'if the value of txt_callertelephone is not blank then carry out this procedure
        Set db = CurrentDb
        Set rs = db.OpenRecordset("On_Net_Communications", dbOpenDynaset) '"On_Net_Communications" is the name of the table containing the records i want to search
    
        rs.FindLast "Tel No" = txt_callertelephone.Value 'Find the last value that matches the value of the text box containing the telephone number in the feild named "Tel No"

    
        If rs.NoMatch Then 'what to do if there is no match
             txt_callername.Value = "" 'leave the callername value blank
             txt_email.Value = "" 'leave the email value blank
    
        Else 'what to do if the telephone number is found in a previous record
             txt_callername.Value = rs!CallerName 'update the text box containing the callers name to the value of the field "CallerName" in the record that matches the telephone number
             txt_email.Value = rs!Email 'update the text box containing the callers email address to the value of the field "Email" in the record that matches the telephone number
        End If
    
        rs.Close 'closes the recordset
        Set rs = Nothing 'unloads the recordset
        Set db = Nothing 'unloads the current db
    End If
End Sub

Thanks
Wayne

 

[pbaldy]

This link doesnt sound like it's relevant, but the recordset code in it would be a solution to your problem.

http://www.baldyweb.com/ImmediateWindow.htm

If you field is text, you'd need delimiters around the form value.

 

[the-m0th]

Hi,

Thanks for the advice and the link. Would you mind giving me an example of a value with delimiters? i'm trying to decipher the code in the link and it would probably be helpful to see one before i try and create one.

Thanks Again
Wayne

 

[pbaldy]

It would look like

strSQL = "SELECT...WHERE FieldName = '" & Me.ControlName & "'"

 

[the-m0th]

ok, i've tried having a go with the code from the link you sent me and my current form load subroutine looks like this.....

Private Sub Form_Load()
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    
    DoCmd.GoToRecord , , acNewRec 'goes to new record
    txt_dateandtime.Value = Now 'enters the current date and time into the correct box
    Me.txt_callertelephone.Value = Forms!Switchboard!txt_incoming.Value 'copies the outside number from the text box on the main form into the telephone number box on the call answer form
    Forms!Switchboard!txt_incoming.Value = "" 'resets the caller i.d. text box on the main form ready for the next incoming call.
    
    Set db = CurrentDb
    strSQL = "SELECT * FROM On_Net_Communications WHERE TelNo = " & Me.txt_callertelephone
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If Not rs.EOF Then
        Me.txt_callername = rs!CallerName
        Me.txt_email = rs!Email
    End If

ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
End Sub

but it's currently hanging on the line "Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)" and giving the error.


runtime error 3075 syntax error (missing operator) in query expression 'TelNo ='.

any ideas what i'm doing wrong?

Thanks
Wayne

 

[pbaldy]

That would imply it's not getting the value from the other form; is it in the textbox? You can try:

strSQL = "SELECT * FROM On_Net_Communications WHERE TelNo = " & Forms!Switchboard!txt_incoming.Value

or if it's text:

strSQL = "SELECT * FROM On_Net_Communications WHERE TelNo = '" & Forms!Switchboard!txt_incoming.Value & "'"

 

[the-m0th]

cheers mate that worked a treat, i still can't get my head around where all the quote marks and & symbols come from but i'm learning. it's very much appreciated.

thanks again
Wayne

 

[pbaldy]

Happy to help Wayne! Don't worry, at dome point it will all fall into place.