Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: copy current record then append next alphabet revision charactor

    Hi there,

    quite a few months ago a generous forum member wrote this code for me at the bottom of this message. It creates a new order entry by first checking the highest record number, adding 1 to it and appending 'a'. This works great.

    Now I would like a button that copies the current record and appends the next letter of the alphabet. So if i start with record "1000/a" a new revision would be 1000/b etc

    At the moment i have to manually copy record 1000/a and change it to 1000/b

    Any ideas/ help would be much appriciated
    thanks
    marcus


    ' This code creates a new record and appends /a
    Private Sub New_Entry_Click()
    If MsgBox("Create a New Entry ? ", vbYesNo) = vbYes Then

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim NewNum As Integer
    Dim NewCompNum As String

    strSQL = "SELECT DISTINCT Left(Ref_ID,Len(Ref_ID) - 2) AS CompNum " _
    & "FROM tblOrders " _
    & "ORDER BY Left(Ref_ID, Len(Ref_ID) - 2);"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    If Not rs.BOF And Not rs.EOF Then
    rs.MoveLast
    NewNum = CInt(rs!CompNum)
    End If

    rs.Close
    db.Close

    Set rs = Nothing
    Set db = Nothing

    NewNum = NewNum + 1

    NewCompNum = NewNum & "/a"
    MsgBox (NewCompNum)

    DoCmd****nSQL "INSERT INTO tblOrders (Ref_ID) " & _
    " VALUES (" & _
    "'" & NewCompNum & "'" & _
    ")"

    Forms.main.Main_sub1.Requery
    Forms.main.frm_Main_sub_system.Requery


    Else
    'MsgBox ("ok")
    End If
    End Sub

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use something like this:
    Code:
    Private Sub Command_New_Revision_Click()
    
        Const c_strSQL As String = "INSERT INTO Tbl_Orders ( Ref_ID ) VALUES ( '@ID' );"
        
        Dim strNewRevisionID As String
        
        strNewRevisionID = Left(Me.Text_Ref_ID, InStr(Me.Text_Ref_ID, "/")) & Chr(Asc(Right(Me.Text_Ref_ID, 1)) + 1)
        CurrentDb.Execute Replace(c_strSQL, "@ID", strNewRevisionID), dbFailOnError
        
    End Sub
    I probably would introduce a couple of verifications:

    1. What happens if a row with the same value in the column Ref_ID as the one computed in strNewRevisionID already exists ?

    2. What happens if the current Ref_ID value is '1000/z' ?
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    thanks for replying to my thread.

    I had to modify the code slightly taking out the Text bit before the code worked (i don't know why that made a difference??) . Changed it to Me.Ref_ID

    The code works great creating a new entry with the next alphabetical letter appended. However i would like to copy all the fields in current record and copy them to a new record (in the same table) then update the revision of Ref_ID to the next revision. Is there an easy way to do this as I'm not that familiar with some of the code you have used.

    btw what does @ID do?


    Const c_strSQL As String = "INSERT INTO Tbl_Orders ( Ref_ID ) VALUES ( '@ID' );"

    strNewRevisionID = Left(Me.Ref_ID, InStr(Me.Ref_ID, "/")) & Chr(Asc(Right(Me.Ref_ID, 1)) + 1)
    CurrentDb.Execute Replace(c_strSQL, "@ID", strNewRevisionID), dbFailOnError


    in anwser to your questions.

    I will never get beyond rev /e so if I do reach /z i'm doing something wrong with my quotes.

    If a record already exists with the same revision I'll pop up a message informing the user.

    thanks again
    marus

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For duplicating the current record into a new one, here's one possibility (there are several others):
    Code:
    Private Sub Command_Duplicate_Click()
    
        Dim rst As DAO.Recordset
        Dim varFields As Variant
        Dim i As Integer
        
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        varFields = rst.GetRows(1)
        rst.Close
        Set rst = Nothing
        DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
        Me.Recordset.AddNew
        For i = 0 To UBound(varFields)
            Me.Recordset.Fields(i) = varFields(i, 0)
        Next i
        Me.Recordset.Update
        Me.Refresh
        
    End Sub
    The problem is that you'll have to find a way to exclude the primary key (if any) from being copied. If you know its ordinal position into the fields set, you can easily use:
    Code:
    If i <> PKIndex Then Me.Recordset.Fields(i) = varFields(i, 0)
    with PKIndex being the ordinal position of the primary key.


    @ID is a place holder. It is replaced by the actual value of strNewRevisionID by the Replace() function. I find that way of coding more readable than:
    Code:
    CurrentDb.Execute "INSERT INTO Tbl_Orders ( Ref_ID ) VALUES ( '" & strNewRevisionID & "' );", dbFailOnError
    But it is purely a matter of preferences and habits.
    Have a nice day!

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    You are way way way more advanced than i. I'm struggling trying under stand the code you posted. It looks very impressive i just wish i new what it was doing :-) just bought Access 2007 VBA for dummies so hopefully i'll be able to catch up in a few years!!

    When i first run the code i get a Run-time error 7951 'You entered an expression that has an invalid reference to the RecordsetClone property.

    Which i guessing this error has come about because the table a am trying to copy is in a subform called Main_sub1.

    So i tried to move the code inside the sub form where the table is.

    I then got an error message ' Runtime 2489 saying The object 'Main_sub1' isn't open.


    I tried to enter a DoCmd.openform but it got a bit messy after that.

    You said there are other ways to copy the record and give it a new number?

    In my mind this is what i need to do.

    Select the record currently selected.
    Copy all the fields except the first one as it is a primary key
    Insert new records into table and give the primary key field a new/unique number (1000/b) for example.

    Sorry for my lack of experience.

    I'm sure i'll kick my self in the head one day for asking such stupid questions

    thanks
    marcus

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by marcusmacman View Post
    When i first run the code i get a Run-time error 7951 'You entered an expression that has an invalid reference to the RecordsetClone property.

    Which i guessing this error has come about because the table a am trying to copy is in a subform called Main_sub1.
    As you correctly asserted (it's better than "guessed", more professional ) You can only reference the RecordsetClone property of a form if this form is bound to a data source (i.e. if it has a valid RecordSource or, if you prefer, if it has an open RecordSet object: when a form is unbound, its RecordSet member object is Nothing).
    Quote Originally Posted by marcusmacman View Post
    So i tried to move the code inside the sub form where the table is.

    I then got an error message ' Runtime 2489 saying The object 'Main_sub1' isn't open.
    This because of the line:
    Code:
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    Where I'm sure the error occurs. Strictly speaking a subform is not "open", i.e. it is not part of the Forms collection, and is then inaccessible to most of the "macro-like" DoCmd commands. You can easily correct this by changing the code and use another method to move to the new record:
    Code:
    Private Sub Command_Duplicate_Click()
    '
    ' This procedure is in the class module of the subform.
    '
        Dim rst As DAO.Recordset
        Dim varFields As Variant
        Dim i As Integer
        
        Set rst = Me.RecordsetClone
        rst.Bookmark = Me.Bookmark
        varFields = rst.GetRows(1)
        rst.Close
        Me.Recordset.AddNew
        For i = 0 To UBound(varFields)
            Me.Recordset.Fields(i) = varFields(i, 0)
        Next i
        Me.Recordset.Update
        Me.Refresh
        Set rst = Me.RecordsetClone
        rst.MoveLast
        Me.Bookmark = rst.Bookmark
        rst.Close
        Set rst = Nothing
        
    End Sub
    The only (easily solvable) problem that you have now is that you need to find a way to call the procedure from the parent form. For instance, you may change the declaration of the procedure from Private to Public, which makes it callable from outside its class module:
    Code:
    Public Sub Command_Duplicate_Click()
    '
    ' This procedure is in the class module of the subform.
    '
    From the parent form (where the button Command_Duplicate is) you now use:
    Code:
    Private Sub Command_Duplicate_Click()
    '
    ' This procedure is in the class module of the parent form.
    '
        Me.Main_sub1.Form.Command_Duplicate_Click
    
    End Sub
    You can as well let the procedure stay where it is (in the class module of the parent form) and change it in a way that allows to use the RecordSet of the subform. If the control of type subform/subreport is named 'Main_sub1' (be careful here: the name of the control that displays a subform into the parent form can be different from the name of the subform itself), you can let the procedure reside in the parent form and change it to (the key points are in red):
    Code:
    Private Sub Command_Duplicate_Click()
    '
    ' This procedure is in the class module of the parent form.
    '
        Dim frm As Form
        Dim rst As DAO.Recordset
        Dim varFields As Variant
        Dim i As Integer
        
        Set frm = Me.Main_sub1.Form    ' Create a reference to the subform.
        Set rst = frm.RecordsetClone
        rst.Bookmark = frm.Bookmark
        varFields = rst.GetRows(1)
        rst.Close
        frm.Recordset.AddNew
        For i = 0 To UBound(varFields)
            frm.Recordset.Fields(i) = varFields(i, 0)
        Next i
        frm.Recordset.Update
        frm.Refresh
        Set rst = frm.RecordsetClone
        rst.MoveLast
        frm.Bookmark = rst.Bookmark
        rst.Close
        Set rst = Nothing
        Set frm = Nothing
        
    End Sub
    In any case pay a close attention to the distinction between the name of the control of type subform and the name of the subform itself. The general syntax for referencing something (control, property or public procedure) in the subform from its parent form is:
    Code:
    Me.Control_Of_Type_Subform.Form.Control_Or_Procedure
    See also: Forms: Refer to Form and Subform properties and controls.
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    that's really useful information. I've spent most of the day going through Access for Dummies. I've just read your reply and what you are saying is slowly making sense. I'll update the thread when i've had a chance to digest all the info you have passed on. thanks again
    Marus

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •