Add New Record If Doesn't Exist

[Survane]

I have a form where I enter callers information. I may have duplicate callers but that's ok. What I want to do is if a caller is a duplicate caller, then after the phone number is entered, it will check that table and automatically enter that caller's information in the table. I have each contact linked to a CallID so a contact can have more than one calls but a call can have only one contact. How can I do this, potentially using ADO or by another method?

Thanks.

 

[BanditWk]

On the AfterUpdate of the Phone field:

Dim Dbs as Database 'if not already dimensioned
Dim Rst as Recordset 'if not already dimensioned

Set Dbs = CurrentDb 'if not already set
Set Rst = Dbs.OpenRecordset("NameOfCallTable",dbOpenDynaset)

Rst.FindFirst "[SearchFieldName] = " & Forms!NameOfCallForm!NameOfFieldToSearch 'if numeric value, or

Rst.FindFirst "[SearchFieldName] = '" & Forms!NameOfCallForm!NameOfFieldToSearch & "'" 'if text value

If Rst.NoMatch Then ' Meaning not found
Rst.AddNew
Else
Rst.Edit
end if
Rst!Name = Me!Name
Rst!Address = Me!Address
Rst!Phone = Me!Phone
Rst.Update
Rst.Close
Dbs.Close

HTH Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

"No need to send gifts, just send a smile."

 

[Survane]

Thanks Bandit WK. I will try this a post feedback.

 

[Clarkie001]

Bandit,

I am trying to use this coding on a similar user form, but I am getting User-defined type not defined. Can you shed any light?

Any help is appreciated.

Clarkie

 

[nat1967]

Hi Clarkie,

What version of Access are you using? It sounds like you are running A2k or later. If so, you need to check to see if you have a reference to DAO set. By default, A2k and later uses ADO.

To check references: in the VB window, GoTo: Tools>References. Check for the latest DAO object. if not checked, check it.

If you have DAO in your references, then modify the above code to say:

Dim Dbs as DAO.Database 'if not already dimensioned
Dim Rst as DAO.Recordset 'if not already dimensioned

I believe that should help. If not, post your code and show exactly where the code errors (turns yellow).

Have A Great Day!!!,

Nathan
Senior Test Lead

 

[Clarkie001]

Nathan,

Perfect mate, thats all it was a simple check box in the references.

Cheers

Clarke