Create new record with same company information 1

[SimonCleaver]

Hi All,

Can anyone point me in the right direction?

I have a form called Contacts and I wish to create a command button to create a new where some of the current record information is copied to a new record.

Exactly the same as the "Create new contact from this Company" menu item in Outlook.

Is this possible?

My form (and table) is called Contacts, the Primary key is ContactID, the fields I want to copy are: CompanyName, Address, Phone, Fax.

Thnaks for any advice given.

 

[PaulBricker]

In the Click Event of your button, you would put something like this

Dim strContactID as String
Dim strCompanyName as String
Dim strAddress as String
Dim strPhone as String
Dim strFax as String

strContactID = Me.ContactID
strCompanyName = Me.CompanyName
strAddress = Me.Address
strPhone = Me.Phone
strFax = Me.Fax
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acActiveDataObject, "FormNameHere", acNewRec
Me.ContactID = strContactID
Me.CompanyName = strCompanyName
Me.Address = strAddress
Me.Phone = strPhone
Me.Fax = strFax
DoCmd.RunCommand acCmdSaveRecord

That should do it

Paul

 

[SimonCleaver]

Thanks Paul,

This works well apart from one thing - If a field is blank on the original record, I get an Error saying: Runtime Error 94 - Invalid Use of Null"

Do you know how I would get around this?

 

[KenReay]

Hi

use the NZ() function is one possibility ie

Me.Address = Nz(strAddress," &quot

Or you could bypass the setting so

If Not isNull(strAddress) Then
Me.Address = strAddress
End If

or

or you could change your table definition to accept nulls in the relevant field(s)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SimonCleaver]

Hi Ken,

Thanks for the advice, but it still gave me an error. Also the table is already set to accept nulls.
Below is the complete code that gives me the error if a field is blank - what do you think?


Private Sub cmdNewCompanyContact_Click()
Dim strContactID As String
Dim strCompanyName As String
Dim strAddress As String
Dim strCity As String
Dim strState As String
Dim strPostCode As String
Dim strCountry As String
Dim strPhone As String
Dim strPhone2 As String
Dim strFax As String
Dim WebPage As String

strContactID = Me.ContactID
strCompanyName = Me.CompanyName
strAddress = Me.Address
strCity = Me.City
strState = Me.State
strPostCode = Me.PostCode
strCountry = Me.Country
strPhone = Me.Phone
strPhone2 = Me.Phone2
strFax = Me.Fax
strWebPage = Me.WebPage

DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord acActiveDataObject, "Contacts", acNewRec
Me.CompanyName = strCompanyName
Me.Address = strAddress
Me.City = strCity
Me.State = strState
Me.PostCode = strPostCode
Me.Country = strCountry
Me.Phone = strPhone
Me.Phone2 = strPhone2
Me.Fax = strFax
Me.WebPage = strWebPage
DoCmd.RunCommand acCmdSaveRecord
End Sub

 

[KenReay]

Hi

Which line does it stop on

Show the code modified to include either the Nz(0 ir IF solution previous posted

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Which line does it stop on

Show the code modified to include either the Nz() or IF solution previous posted

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Which line does it stop on

Show the code modified to include either the Nz() or IF solution previously posted

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PaulBricker]

Try commenting out the DoCmd.RunCommand acCmdSaveRecord lines. I like them in there but Access automatically saves a record once you move to another one so it isn't entirely necessary and may be causing the trouble.


Paul

 

[SimonCleaver]

I tried both of the methods and both times it stopped on the first field that had a null value?

Private Sub cmdNewCompanyContact_Click()
Dim strCompanyName As String
Dim strAddress As String
............(and so on)

strCompanyName = Me.CompanyName
strAddress = Me.Address
............(and so on)

DoCmd.GoToRecord acActiveDataObject, "Contacts", acNewRec
Me.CompanyName = Nz(strCompanyName, " &quot
Me.Address = Nz(strAddress, " &quot
.........(and so on)

End Sub

Am I putting the Nz(....) in the correct place?



The other way:

Private Sub cmdNewCompanyContact_Click()
Dim strCompanyName As String
Dim strAddress As String
......... (And so on)

strCompanyName = Me.CompanyName
strAddress = Me.Address
........... (And So On)

DoCmd.GoToRecord acActiveDataObject, "Contacts", acNewRec
If Not IsNull(strCompanyName) Then
Me.CompanyName = strCompnayName
End If
If Not IsNull(strAddress) Then
Me.Address = strAddress
End If
.......(And so On)

End Sub


Again - Am i coding in the correct place?

 

[PaulBricker]

Move the Nz() function to the first part of the code like this.

strCompanyName = Nz(Me.CompanyName, " &quot
strAddress = Nz(Me.Address, " &quot

That should take care of the problem.

Paul

 

[SimonCleaver]

Brilliant

Thanks very much Paul - it now works like a dream!!!!