Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Autogenerate Drawing Number

    Dear Seniors,

    I am working in a Document Tracking database for my project use. Is it possible to autogenerate the Drawing Numbers and its title?

    (i.e.) I have a Table in Access Named MDR as follows, Where CPYDocumentNo is the Primary Key

    CPYDocumentNo; Title; Category
    A3031.001; Stength Analysis; AC

    Now I would like to Autogenerate the Additional Sheets in Access Table. For ex I want to create 3 Additional Sheets

    So My Input will be

    CPYDocumentNo; AdditionalSheets
    A3031.001; 3

    After I give this input, My MDR Table should be autoupdated as follows
    CPYDocumentNo; Title; Category
    A3031.001; Stength Analysis; AC
    A3031.002; Stength Analysis Sheet 2; AC
    A3031.003; Stength Analysis Sheet 3; AC
    A3031.004; Stength Analysis Sheet 4; AC

    One of my client is using their inbuilt software to do the above. I would like to understand whether it is possible in Access, If it is could you please provide your guidelines to do the same.
    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Have 1 field [id] be AUTONUMBER.
    Once the data comes in & autonumber populates, you can run a 2nd query to update [CPYDocumentNo] fld with the [prefix] & [id] to get the correct DocNo.
    It cant be primary key tho, but just index it instead. If it HAS to be primary key, transfer the whole thing to another tabl.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution that works ONLY IF the structure of the first 2 columns is as described in your post (the number and structure of the following columns does not matter):
    Code:
    Public Sub GenerateAdditionalSheets(ByVal TableName As String, ByVal DocNumber As String, ByVal Qty As Long)
    
    
        Dim rst As DAO.Recordset
        Dim varValues As Variant
        Dim strNames As String
        Dim strValues As String
        Dim strTemplate As String
        Dim strSQL As String
        Dim strCriteria As String
        Dim lngCount As Long
        Dim lngStart As Long
        Dim lngStop As Long
        Dim i As Long
        Dim j As Long
        
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        With rst
            strCriteria = .Fields(0).Name & " = '" & DocNumber & "'"
            .FindFirst strCriteria
            If .NoMatch = True Then
                MsgBox "Cannot find source row: " & DocNumber, vbInformation, "GenerateAdditionalSheets"
            Else
                lngCount = .Fields.Count - 1
                ReDim varValues(0 To lngCount)
                For i = 0 To lngCount
                    varValues(i) = .Fields(i).Value
                    If Len(strNames) > 0 Then strNames = strNames & ", "
                    strNames = strNames & .Fields(i).Name
                    If Len(strValues) > 0 Then strValues = strValues & ", "
                    Select Case .Fields(i).Type
                        Case dbText, dbMemo:    strValues = strValues & "'@" & i & "'"
                        Case Else:              strValues = strValues & "@" & i
                    End Select
                Next i
            End If
            .Close
        End With
        Set rst = Nothing
        Stop
        strTemplate = "INSERT INTO " & TableName & " ( " & strNames & " ) VALUES ( " & strValues & " );"
        lngStart = Val(Right(varValues(0), 3)) + 1
        lngStop = lngStart + Qty - 1
        For j = lngStart To lngStop
            Mid(varValues(0), Len(varValues(0)) - 2) = Format(j, "000")       
            For i = 0 To lngCount
                Select Case i
                    Case 0:     strSQL = Replace(strTemplate, "@" & i, varValues(0))
                    Case 1:     strSQL = Replace(strSQL, "@" & i, varValues(i) & " Sheet " & j)
                    Case Else:  strSQL = Replace(strSQL, "@" & i, varValues(i))
                End Select
    '            Debug.Print strSQL
            Next i
            CurrentDb.Execute strSQL, dbFailOnError
        Next j
        
    End Sub
    Quote Originally Posted by ranman256 View Post
    Have 1 field [id] be AUTONUMBER.
    Once the data comes in & autonumber populates, you can run a 2nd query to update [CPYDocumentNo] fld with the [prefix] & [id] to get the correct DocNo.
    It cant be primary key tho, but just index it instead. If it HAS to be primary key, transfer the whole thing to another tabl.
    This is bullshit for several reasons:
    1. You cannot garantee the synchronization between the table with the autonumber column and the target table.
    2. What happens when you want to add rows from say "A3031.003", while the next autonumber column in the table with the autonumber column is 115 because you already call the function 114 times for other rows? The three new rows would be: A3031.115, A3031.116 and A3031.117, not precisely what's expected.
    3. Indexed or not indexed is irrelevant here: you'd never get the correct result.
    4. What do you do with the second column that is supposed to change with the numeric part in the first one (i.e. "Stength Analysis" becomes "Stength Analysis Sheet 2")?
    Have a nice day!

  4. #4
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your code. But please clarify me the following point whether I had done the write thing.

    I have 2 tables in my database,

    1) MDR (CPYDocumentNo; Title; Category)

    2) AddSheets (CPYDocumentNo; AdditionalSheets)

    I had pasted your code in a Form module and replaced the Tablename in the code with "MDR" (Since it is my table name).

    Then I create the Command Button and renames as AddSheets. Then in the event procedure I put

    Code:
    Private Sub AddSheets_Click()
    GenerateAdditionalSheets
    End Sub
    When I click the button I am getting Complie Error. Forgive me If I had done the wrong thing.

    Could you please explain me how to execute this function, so that "MDR" table is populated with the results.
    Thanks and Regards
    R. Vadivelan

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. First try to compile the project. There is no sense in going on if the project cannot be compiled: you must identify and correct the errors first (if any).

    2. The declaration of the function is:
    Code:
    Public Sub GenerateAdditionalSheets(ByVal TableName As String, ByVal DocNumber As String, ByVal Qty As Long)
    This means that when you call it, it expects to receive the name of the table, the ID of the source document and the number of rows to be added. This is very elementary in any programming language.
    Quote Originally Posted by velu130486 View Post
    I have 2 tables in my database,

    1) MDR (CPYDocumentNo; Title; Category)

    2) AddSheets (CPYDocumentNo; AdditionalSheets)
    That's not what you explained in your previous post. If there are 2 tables involved in the process you shoud have mentioned them from the beginning. If you cannot provide accurate information in your questions, you'll probably not receive accurate answers.
    Have a nice day!

  6. #6
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Sorry for not explaining the things in better way.

    As I said in the previous post I have a Table named "MDR" with 3 columns as follows

    CPYDocumentNo; Title; Category
    A3031.001; Stength Analysis; AC

    I would like to add 3 additional columns to this table.

    Now I copied your code and Pasted it in a Form Module and renamed the Table name in the code by "MDR" remaining things I didn't change.

    Please explain me what I have to do further in order to get the result. I don't know how to proceed further?
    Thanks and Regards
    R. Vadivelan

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    I would like to add 3 additional rows to this table.
    Thanks and Regards
    R. Vadivelan

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Can you compile the project? If not what are the errors returned when you try?

    2. When the project can be compiled (i.e. no syntax errors remain in the code), you can call the procedure GenerateAdditionalSheets() like this:
    Code:
    GenerateAdditionalSheets TableName, DocNumber, Qty
    Where:

    - TableName is the name of the table in which new rows must be created ("MDR" if I follow your explanations).
    - DocNumber is the Id of the row from wich new rows must be created (e.g. "A3031.001"). Notice that in my example, the Id column must be the first column of the table (hence my warning when I posted the code).
    - Qty is the number of rows that must be added, based on the model of the row specified by DocNumber.

    Example:
    Code:
    GenerateAdditionalSheets "MDR", "A3031.001", 3
    Have a nice day!

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Yes I had compiled the Project and No Error.

    Then I call the Procedure as you said, and I am getting the error in the following line (Yellow highlighted)

    End With
    Set rst = Nothing
    Stop

    Then I click Step Over in debug menu and found that my table is updated. So I had deleted the "STOP" and tested the code and it works fine.

    Whether any problem will come in future because of this deletion?

    Is it possible to modify the code in order to get the values in Form text box.

    (i.e) I will put the following values from Form Text1 & Text2.

    When I call the code, Title column is updated as follows

    Stength Analysis Sheet 2 instead of "Stength Analysis Sheet 002"

    When I call the code twice, My table is updated twice. But my query is How can I update the table from the latest number?

    (i.e) When I call the code second time, then my table should be updated as
    A3031.005
    A3031.006
    A3031.007

    and so on...(i.e.) The table has to be populated but there should not be any duplicates.

    But very much thanks for you since with help of you my database as got good shape.
    Thanks and Regards
    R. Vadivelan

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    Then I call the Procedure as you said, and I am getting the error in the following line (Yellow highlighted)

    End With
    Set rst = Nothing
    Stop

    Then I click Step Over in debug menu and found that my table is updated. So I had deleted the "STOP" and tested the code and it works fine.

    Whether any problem will come in future because of this deletion?
    Nothing will happen. The Stop instruction was there for debuging purposes and I forgot to delete it, sorry about that.
    Quote Originally Posted by velu130486 View Post
    Is it possible to modify the code in order to get the values in Form text box.

    (i.e) I will put the following values from Form Text1 & Text2.
    Code:
    GenerateAdditionalSheets Me.Text_TableName.Value, Me.Text_DocNumber.Value, Me.Text_Qty.Value
    Where Text_TableName, Text_DocNumber and Text_Qty are the names of Textbox controls on the form.
    Quote Originally Posted by velu130486 View Post
    When I call the code, Title column is updated as follows

    Stength Analysis Sheet 2 instead of "Stength Analysis Sheet 002"
    Change the middle line in the second Select Case block to:
    Code:
                    Case 1:     strSQL = Replace(strSQL, "@" & i, varValues(i) & " Sheet " & Format(j, "000))
    Quote Originally Posted by velu130486 View Post
    When I call the code twice, My table is updated twice. But my query is How can I update the table from the latest number?

    (i.e) When I call the code second time, then my table should be updated as
    A3031.005
    A3031.006
    A3031.007

    and so on...(i.e.) The table has to be populated but there should not be any duplicates.

    But very much thanks for you since with help of you my database as got good shape.
    Not sure to understand. When calling the procedure you specify from which row the table must be updated and new rows added. So, you can call the procedure a second time using "A3031.004" in the DocNumber argument.

    If no duplicate values can exist in the doc Id column, that column shoud be indexed with a UNIQUE constraint.
    Have a nice day!

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Quote Originally Posted by Sinndho View Post
    Not sure to understand. When calling the procedure you specify from which row the table must be updated and new rows added. So, you can call the procedure a second time using "A3031.004" in the DocNumber argument.

    If no duplicate values can exist in the doc Id column, that column shoud be indexed with a UNIQUE constraint.
    I had called the procedure second time using "A3031.004" and it runs fine except the Title.

    My title should be updated as follows

    "Stength Analysis Sheet 005"

    However I am getting the result "Stength Analysis Sheet 004 Sheet 005"

    And when I put Unique Constraint in Doc ID Column,

    Then I am getting error in the following line

    "CurrentDb.Execute strSQL, dbFailOnError"

    Also for the Trail check I added a additional date column in my table and run the code, But instead of same date I am getting the time value not the date.
    Thanks and Regards
    R. Vadivelan

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution to solve the problem of column(1). The For... Next loop becomes:
    Code:
        For j = lngStart To lngStop
            Mid(varValues(0), Len(varValues(0)) - 2) = Format(j, "000")
            If InStr(varValues(1), " Sheet") > 0 Then varValues(1) = Left(varValues(1), InStr(varValues(1), " Sheet") - 1)
            For i = 0 To lngCount
                Select Case i
                    Case 0:     strSQL = Replace(strTemplate, "@" & i, varValues(0))
                    Case 1:     strSQL = Replace(strSQL, "@" & i, varValues(i) & " Sheet " & Format(j, "000"))
                    Case Else:  strSQL = Replace(strSQL, "@" & i, varValues(i))
                End Select
                Debug.Print strSQL
            Next i
            CurrentDb.Execute strSQL, dbFailOnError
        Next j
    You'll get an error when trying to execute the query if that query tries to create a row having a value that already exists in column(0) of the table. That's precisely the purpose of having a unique constraint on that column as 2 values cannot be the same in it. You should call the function GenerateAdditionalSheets() only after veryfying that the value passed in the second parameter (DocNumber) will not generate values that already exists in the table. The number of values that have to be verified depends on the value of the third parameter (Qty).
    Have a nice day!

  13. #13
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Now the Title Error is corrected and its works fine.

    For the duplicates I had tried to put the Index in Column 1 (Title) but still I am getting error. Is it possible to get the last value using Dlookup formula.
    (i.e) If I type "A3031.001" in one text box, then in the another text box it should show the value of "A3031.004"

    Now the problem is, I cann't add addtional columns in the table and run the code. As I said in the previous post, I would like to add Addtional columns like issueDate and Mhrs in the MDR Table for Trail Check. Whenever I generate the additional sheets these values shown be same for the addtional sheets also. However I am getting runtime error 3061 as per the attached file.
    Attached Thumbnails Attached Thumbnails Capture.JPG  
    Thanks and Regards
    R. Vadivelan

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by velu130486 View Post
    For the duplicates I had tried to put the Index in Column 1 (Title) but still I am getting error. Is it possible to get the last value using Dlookup formula.
    (i.e) If I type "A3031.001" in one text box, then in the another text box it should show the value of "A3031.004"
    You do whatever you want (DLookup, Recordset or whatever); the fact is that you cannot pass a DocNumber parameter that would generate a duplicate value in the indexed column.

    Quote Originally Posted by velu130486 View Post
    Now the problem is, I cann't add addtional columns in the table and run the code. As I said in the previous post, I would like to add Addtional columns like issueDate and Mhrs in the MDR Table for Trail Check. Whenever I generate the additional sheets these values shown be same for the addtional sheets also. However I am getting runtime error 3061 as per the attached file.
    Of course you can't, unless you modify the function so that the new columns are taken into account. You're not working logically here: you should first design the tables with all the required columns, then go on developing the other objects that will use the tables (queries, functions, forms, etc.). Every time you change the data structure (i.e. the tables design) you have to review and modify all the objects that use those tables. Not very practical!

    Note: I can't do nothing with a screen shot without seeing the code that triggered the reported error.
    Have a nice day!

  15. #15
    Join Date
    Oct 2013
    Posts
    163
    Hi Sinndho,

    Thanks for your reply. I consider this post has closed since it has solved all my problem.

    But what is strange for me is I had used the same code in Access 2013 for trail check and it allowed me to create additional columns, where us in 2010 it doesn't allow.

    (i.e) I inserted 3 columns like Dateof Issue, Title2 & Manhours. I got the value of Title2 where as for Dateofissue insted of same date I got values filled with time.

    I am getting error in the following line

    "CurrentDb.Execute strSQL, dbFailOnError"
    Thanks and Regards
    R. Vadivelan

Posting Permissions

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