Copy record and update one field

[Gegsynz]

Greetings from New Zealand

I have just followed GHudson method of copying a record by adding his code to the Onclick event of a command button on a data entry form and it works beautifully. As a newcomer to access this feature has had me stumped for a couple of weeks now. Thanks a lot GHUDSON

Code is:

Dim sFirstName As String
Dim sLastName As String
Dim sMailingLocation As String
Dim sPhoneNumber As String

sFirstName = FirstName 'FirstName is the name of the table field
sLastName = LastName
sMailingLocation = MailingLocation
sPhoneNumber = PhoneNumber

DoCmd.GoToRecord , , acNewRec

FirstName = sFirstName
LastName = sLastName
MailingLocation = sMailingLocation
PhoneNumber = sPhoneNumber

sFirstName = ""
sLastName = ""
sMailingLocation = ""
sPhoneNumber = ""

I am using this in a document database where some records may have many copies each of which must have it's own record but with a different copy number.

What I would really like to do now is to have an additional feature which when a copy of the record is made it will automatically increment the copy number field, (which I have set to default at 1,) dependent on the number of copies. That is add 1 to the current copy number field for each successive document record that is copied.

 

[chergh]

You want something like this in your record code

rs.edit

rs!countfield = rs!countfield +1

rs.update

 

[Gegsynz]

Thanks for the very prompt reply, please forgive my ignorance but would this suggested code tag on the end of the code I have to duplicate the record ?

Gerry

 

[chergh]

Actually ignore my previous stuff you should just be able to add a line after you have declared your variables with

countfield = countfield + 1

 

[Gegsynz]

Doing a quick form here at home and your last suggestion works perfectly.
Can't wait to get to work to try it out on the real thing. Can't thank you enough.

Kind regards
Gerry

 

[Gegsynz]

Made more progress in the last couple of hours than for the last couple of weeks! However I have struck a problem.. Everything is OK when the user is at the last copy number of the record and clicks my add copy button to add another copy but if they happen to go back a couple of records say from copy no 12 to copy no 10 and press the button they will get another copy of the record but with copy field showing 11 which has already been allocated. Is there anyway I can get round this problem ?
Code is attached
Private Sub Command4_Click()
Dim sFirstName As String
Dim sLastName As String
Dim sMailingLocation As String
Dim sPhoneNumber As String
Dim sCopyNumber As String

sFirstName = FirstName 'FirstName is the name of the table field
sLastName = LastName
sMailingLocation = MailingLocation
sPhoneNumber = PhoneNumber
sCopyNumber = CopyNumber

DoCmd.GoToRecord , , acNewRec

FirstName = sFirstName
LastName = sLastName
MailingLocation = sMailingLocation
PhoneNumber = sPhoneNumber
CopyNumber = sCopyNumber
sFirstName = ""
sLastName = ""
sMailingLocation = ""
sPhoneNumber = ""
sCopyNumber = ""
CopyNumber = CopyNumber + 1
CopyNumber.SetFocus
Label6.Visible = True 'Shows label to watch Copy number incrementing

Thanks
Gerry

 

[Alan R]

Try using DMax. The Access Help function will give you details on how to implement.

Alan

 

[chergh]

Try working this into your code:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim x As Integer

Set db = CurrentDb

strSQL = "SELECT max(tbl_name.num) as Max_Num FROM tbl_name"

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

x = rs!Max_num

copynumber = x +1

 

[Gegsynz]

Chergh, thanks for your help and patience. I have added suggested code into my trial form and it works very well. However it finds the highest copy number in the copy number field of the table where I actually want the highest copy number for the group of records that relate to a particular document. e.g Document number 123 may have 10 copies, Document number 170 43 copies, if I try to add a second copy to a new entry it gives me copy 44. Am I trying to be too ambitious and run before I can walk or is there some way to take a "snapshot" of the current form and see what is the highest number of in the CopyNumber field for records that are an exact match of all the fields?

code so far:

Private Sub Command4_Click()
Dim sFirstName As String
Dim sLastName As String
Dim sMailingLocation As String
Dim sPhoneNumber As String
Dim sCopyNumber As String

sFirstName = FirstName 'FirstName is the name of the table field
sLastName = LastName
sMailingLocation = MailingLocation
sPhoneNumber = PhoneNumber
sCopyNumber = CopyNumber

DoCmd.GoToRecord , , acNewRec

FirstName = sFirstName
LastName = sLastName
MailingLocation = sMailingLocation
PhoneNumber = sPhoneNumber
CopyNumber = sCopyNumber
sFirstName = ""
sLastName = ""
sMailingLocation = ""
sPhoneNumber = ""
sCopyNumber = ""

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim x As Integer

Set db = CurrentDb

strSQL = "SELECT max(Table1.CopyNumber) as Max_Num FROM Table1"

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

x = rs!Max_num

CopyNumber = x + 1
'CopyNumber = CopyNumber + 1

CopyNumber.SetFocus
Label6.Visible = True 'Shows label to watch Copy number incrementing

 

[liddlem]

Hi Gegynz
I may be totally wrong here, but I would create a query that searched for all records belonging to the Document. Then point the the query to the MAX of the QUERY. This should return the highest number within the document group.

Having said this - I must confess that I have no idea how I would write this in code - Sorry! (I shall wait for the gurus to show me how)

 

[WayneRyan]

Gegsynz,

Remove the CopyNumber from your duplication code.

It's not really being "duplicated" anyway.

Instead, use the Form's BeforeInsert event to perform the following
line of code:

CopyNumber = DMax("[CopyNumber]", "YourTable", "[DocumentName] = '" & Me.DocumentName & "'"), 0) + 1 <-- Add 1 for
               ^                                       ^                                      ^          next number
               |                                       |                                      |
               The DMax function will find the         This clause restricts the search to    Value if no existing
               maximum value for a field based        only a specific document
               on some criteria -> Me.DocumentName

That will assign the appropriate number based on the maximum existing CopyNumber
for some specific document.

Wayne

 

[liddlem]

Thanks Wayne. I have learned something here too.
Just one question....
Should there be an "iif(" somewhere in your code? You indicate that somehow the code will assign a value of 0 (Zero) if no record is found.
Or will this default to zero by virtue that an error is returned?

 

[WayneRyan]

liddlem,


Oops, I forgot to put in the NZ function.
It's the part that provides a 0 when the criteria (DocumentName) has no copies.

Kind of a tough part to leave out.

CopyNumber = Nz(DMax("[CopyNumber]", "YourTable", "[DocumentName] = '" & Me.DocumentName & "'"), 0) + 1 <-- Add 1 for
                  ^                                       ^                                      ^          next number
                  |                                       |                                      |
                  The DMax function will find the         This clause restricts the search to    Nz function Value 
                  maximum value for a field based         only a specific document               if no existing Copies
                  on some criteria -> Me.DocumentName

Wayne

 

[Gegsynz]

I'm thrilled with the responses I am getting to my problem but some of the code is getting getting the better of me. Chergh has set me off in the right direction and maybe now may be a good time to explain a bit about my document db. Basically to keep track of technical manuals. Fields include a system number (0900= Aircon, 1000 = Generator, etc then a unique access number and copy number. So an aircon manual maybe labeled 0900-1102/2 indicating copy 2 of document 1102. Can I use that unique access number in the current record to identify the largest copy number associated with it and then add 1 when the add copy button is clicked ?

 

[chergh]

With the last code you posted change the sql statement to;

strSQL = "SELECT max(Table1.CopyNumber) as Max_Num FROM Table1 " & _
"WHERE system_number = forms!frm_name!system_number AND " & _
"unique_access_number = forms!frm_name!unique_access_number"

 

[Gegsynz]

Cut and pasted the code but keep getting an error on line:

Set rs = db.OpenRecordset(strSQL)

Code now reads:

Private Sub Command4_Click()
Dim sSystemNumber As String
Dim sAccessNumber As String
Dim sFirstName As String
Dim sLastName As String
Dim sMailingLocation As String
Dim sPhoneNumber As String
Dim sCopyNumber As String

sSystemNumber = SystemNumber 'SystemNumber is the name of the table field
sAccessNumber = AccessNumber
sFirstName = FirstName
sLastName = LastName
sMailingLocation = MailingLocation
sPhoneNumber = PhoneNumber
sCopyNumber = CopyNumber

DoCmd.GoToRecord , , acNewRec

FirstName = sFirstName
LastName = sLastName
MailingLocation = sMailingLocation
PhoneNumber = sPhoneNumber
CopyNumber = sCopyNumber
sFirstName = ""
sLastName = ""
sMailingLocation = ""
sPhoneNumber = ""
sCopyNumber = ""

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim x As Integer

Set db = CurrentDb

strSQL = "SELECT max(Table1.CopyNumber) as Max_Num FROM Table1 " & _
"WHERE system_number = forms!frm_name!system_number AND " & _
"unique_access_number = forms!frm_name!unique_access_number"

'strSQL = "SELECT max(Table1.CopyNumber) as Max_Num FROM Table1"

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

x = rs!Max_num

CopyNumber = x + 1
'CopyNumber = CopyNumber + 1

CopyNumber.SetFocus
Label6.Visible = True 'Shows label to watch Copy number incrementing






End Sub

 

[chergh]

where you have:

forms!frm_name!system_number

replace "frm_name" with the name of your form and "system number" with the name of the form field which contains that info and you will need to do the same with unique access number as well.

 

[Gegsynz]

Chergh thanks again for the prompt response and appreciate your help. I had done your suggested changes but posted wrong code.

Still get same result with this code:
on line: Set rs = db.OpenRecordset(strSQL)


Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim x As Integer

Set db = CurrentDb

strSQL = "SELECT max(Table1.CopyNumber) as Max_Num FROM Table1 " & _
"WHERE SystemNumber = forms!Form1!SystemNumber AND " & _
"AccessNumber = forms!Form1!Accessnumber"

'strSQL = "SELECT max(Table1.CopyNumber) as Max_Num FROM Table1"

Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

x = rs!Max_num

CopyNumber = x + 1
'CopyNumber = CopyNumber + 1

CopyNumber.SetFocus
Label6.Visible = True 'Shows label to watch Copy number incrementing






End Sub

 

[chergh]

What error message are you getting?

 

[Gegsynz]

Run-time error '3601' :
Too few parameters, Expected 2
Set rs = db.OpenRecordset(strSQL) highlighted in VB