Copying infor

[Aurora]

I am using Access 2000
I currently have a form that draws basic customer
information from one Accounts Db to a Tele Call Rec form
when a telephone number is entered and is already in the
Dbase.

If the information is not there we have to enter all of
the basic information on the form. Then we have to re-
enter the same information into the Accounts Db in order
to print the Tele Call Rec form.

Does anyone know if I can create a macro that will copy
information from a form to the Db? If so can you give me
an idea of how this would be done?

I would certainly appreciate any help you can give me.

Aurora

 

[Treebeard]

I am using Access 2000
I currently have a form that draws basic customer
information from one Accounts Db to a Tele Call Rec form
when a telephone number is entered and is already in the
Dbase.

If the information is not there we have to enter all of
the basic information on the form. Then we have to re-
enter the same information into the Accounts Db in order
to print the Tele Call Rec form.

Does anyone know if I can create a macro that will copy
information from a form to the Db? If so can you give me
an idea of how this would be done?

I would certainly appreciate any help you can give me.

Aurora

Aurora,

There's several ways to do this, here's one.

Put this function in the form. Of course modify it to conform to your table
and field names. Make sure you have ADO(Microsost ActiveX Data Objects
Library) checked in your code references(Tools/References) , not DAO. You
might have to change this because the default setting is DAO for Access
2000, I think. ADO is the newer version of DAO.

Public Function AddCustomerInfoToAccountDB()
Dim dbs As ADODB.Connection, rst As ADODB.Recordset
Dim strPhone As String, strSQL As String

' see if there is a valid string in the phone number field
If Not IsNull(Me.PHONE) Then
strPhone = Trim(Me.PHONE) ' use trim incase the user entered spaces
Set dbs = CurrentProject.Connection ' open the database
Set rst = New Recordset ' open a recordset
strSQL = "SELECT * FROM [AccountTable] WHERE [PHONE] = " & strPhone

rst.Open strSQL, dbs, adOpenStatic, adLockOptimistic
' if the record count is not 0 then the account is already there
If Not (rst.RecordCount > 0) Then
' you must add it
rst.Close
rst.Open "AccountTable", dbs, adOpenDynamic, adLockOptimistic
rst.AddNew
rst!PHONE = strPhone
If Not IsNull(Me.Address1) Then rst!Address1 = Me.Address1
If Not IsNull(Me.Address2) Then rst!Address2 = Me.Address2
If Not IsNull(Me.City) Then rst!City = Me.City
' etc, etc
' do this for as many fields on the form
' which are also in the table
rst.Update ' save the record
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End If
End Function

Call this function when the form closes or put it in the print button, if
you have one.
..