The GPT can comment your code. (1 Viewer)

[Thales750]

Here's the code:

Private Sub cmdImportRaw_Click()

    If IsNull(pubOrdersID) Then
        MsgBox ("Select Order")
        Exit Sub
    End If

strSQL = "DELETE tblBomImportRaw.RawBomID, tblBomImportRaw.rbOrdersID " & vbCrLf & _
"FROM tblBomImportRaw " & vbCrLf & _
"WHERE (((tblBomImportRaw.rbOrdersID)=getOrdersID()));"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

strSQL = "INSERT INTO tblBomImportRaw ( rbID, rbLogDate, rbOrdersID, rbSubCategory, rbFloor, rbManufacturer, rbCode, rbSize, rbDescription, rbCount, rbExtra, rbUnit, rbComment, rbLabel, rbUserID ) " & vbCrLf & _
"SELECT xlsBillOfMaterialsBom.ID, Now() AS Expr2, getOrdersID() AS Expr1, xlsBillOfMaterialsBom.[Sub Category], xlsBillOfMaterialsBom.Floor, xlsBillOfMaterialsBom.Manufacturer, xlsBillOfMaterialsBom.Code, xlsBillOfMaterialsBom.Size, xlsBillOfMaterialsBom.Description, xlsBillOfMaterialsBom.Count, xlsBillOfMaterialsBom.Extra, xlsBillOfMaterialsBom.Unit, xlsBillOfMaterialsBom.Comment, xlsBillOfMaterialsBom.Label, getUserID() AS Expr3 " & vbCrLf & _
"FROM xlsBillOfMaterialsBom;"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

Dim strID As String
strID = "0"

strSQL = "SELECT tblBomImportRaw.RawBomID, tblBomImportRaw.rbID, tblBomImportRaw.rbOrdersID, tblBomImportRaw.rbDrawingObjectType " & vbCrLf & _
"FROM tblBomImportRaw " & vbCrLf & _
"WHERE (((tblBomImportRaw.rbOrdersID)=getOrdersID())) " & vbCrLf & _
"ORDER BY tblBomImportRaw.RawBomID;"

Dim Rs As DAO.Recordset

    Set Rs = CurrentDb.OpenRecordset(strSQL)
    Rs.Edit
   
       
    If Rs.RecordCount > 0 Then
       
        Rs.MoveFirst
        Do Until Rs.EOF
       
            If IsNull(Rs!rbID) Then
                strID = "0"
            Else
                If strID = "0" Then
                    strID = Rs!rbID
                    Rs.Edit
                    Rs!rbDrawingObjectType = strID
                    Rs.Update
         
                Else
                Rs.Edit
                    Rs!rbDrawingObjectType = strID
                    Rs.Update
                End If
            End If

     Debug.Print strID
   
            Rs.MoveNext
        Loop
        Rs.Close
    Set Rs = Nothing
    End If


End Sub

Here are the comments, What is missing is the intent.

ChatGPT:
"
This code is written in VBA and is a subroutine that runs when the cmdImportRaw button is clicked. The first thing it does is check if the variable "pubOrdersID" is null, and if it is, it displays a message box saying "Select Order" and exits the subroutine.

Next, it runs two SQL statements: the first is a DELETE statement that deletes records from the "tblBomImportRaw" table where the "rbOrdersID" field matches the value of the "getOrdersID()" function. The second is an INSERT INTO statement that inserts records into the "tblBomImportRaw" table from the "xlsBillOfMaterialsBom" table.

Then it declares a variable "strID" and sets it to "0", and runs a SELECT statement that selects records from the "tblBomImportRaw" table where the "rbOrdersID" field matches the value of the "getOrdersID()" function, and orders the records by the "RawBomID" field. The results of this query are used to populate a DAO recordset object, which is then used to iterate through the records, updating the "rbDrawingObjectType" field with the value of the "strID" variable. Finally, it closes the recordset and sets it to nothing."

 

[Pat Hartman]

The fact that the AI can do this is impressive but this is the type of documentation that is useless except to rank novices and that is only because they have trouble reading the VBA at all, so I can't really get excited about it. Explaining in simple English what lines of code are doing can be very helpful to someone who is not comfortable with VBA. But in the real world, people who write documentation like this rarely update it if they modify the code. And in the end, it doesn't help at all with understanding what the code is actually there for.

It is the intent of the code that developers need to know. Especially if the code itself looks like it might not be optimal for example, why would you use a VBA loop here instead of an action query which would be more efficient?

 

[Thales750]

It is the intent of the code that developers need to know. Especially if the code itself looks like it might not be optimal for example, why would you use a VBA loop here instead of an action query which would be more efficient?

I'm fully aware that the intent is the whole purpose of comments. This is like asking a 2 year old, but it won't stay 2 years old forever.

As to the second question, I guess the answer is, if all you have is a hammer, everything looks like a nail. I have no idea how to make a query that copies a value to the next row based on a null field. as you can see from this loop, that is what this does.
As to the efficiency, do you mean it is efficient to use or to write. This loop runs on several thousand record in an instant. So not sure how much more efficient it need to be.

 

[Pat Hartman]

Sorry, the question was rhetorical. My intention was not criticism but just emphasising what should be explained as comments.

 

[561414]

Anyone not having fun with ChatGPT is missing out. It can do much more than just commenting it, it detects bugs, refactors, translates, etc. On its own, it will give you generic answers first, but if you know your stuff, you can correct it and make it spit out better info.

 

[Thales750]

Anyone not having fun with ChatGPT is missing out. It can do much more than just commenting it, it detects bugs, refactors, translates, etc. On its own, it will give you generic answers first, but if you know your stuff, you can correct it and make it spit out better info.

I had it write a function in Python.

 

[561414]

I had it write a function in Python.

I encourage you to do stuff in languages you want to toy with. I had it write a custom intent classifier from scratch using BERT in python. It threw errors but it also solved them, so that's AI doing AI, not bad. It's also great to help you learn design patterns and architectures. Needless to say it knows about things not related to programming as well.

 

[Thales750]

Sorry, the question was rhetorical. My intention was not criticism but just emphasising what should be explained as comments.

I did not take it as criticism. In fact, you got me to wondering.
I'm going to start a new thread in the Query Section exploring the concept of off current row conditions affecting the current row.

 

[MsAccessNL]

Here's the code:

Private Sub cmdImportRaw_Click()

    If IsNull(pubOrdersID) Then
        MsgBox ("Select Order")
        Exit Sub
    End If
   


strSQL = "DELETE tblBomImportRaw.RawBomID, tblBomImportRaw.rbOrdersID " & vbCrLf & _
"FROM tblBomImportRaw " & vbCrLf & _
"WHERE (((tblBomImportRaw.rbOrdersID)=getOrdersID()));"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

strSQL = "INSERT INTO tblBomImportRaw ( rbID, rbLogDate, rbOrdersID, rbSubCategory, rbFloor, rbManufacturer, rbCode, rbSize, rbDescription, rbCount, rbExtra, rbUnit, rbComment, rbLabel, rbUserID ) " & vbCrLf & _
"SELECT xlsBillOfMaterialsBom.ID, Now() AS Expr2, getOrdersID() AS Expr1, xlsBillOfMaterialsBom.[Sub Category], xlsBillOfMaterialsBom.Floor, xlsBillOfMaterialsBom.Manufacturer, xlsBillOfMaterialsBom.Code, xlsBillOfMaterialsBom.Size, xlsBillOfMaterialsBom.Description, xlsBillOfMaterialsBom.Count, xlsBillOfMaterialsBom.Extra, xlsBillOfMaterialsBom.Unit, xlsBillOfMaterialsBom.Comment, xlsBillOfMaterialsBom.Label, getUserID() AS Expr3 " & vbCrLf & _
"FROM xlsBillOfMaterialsBom;"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges



Dim strID As String
strID = "0"

strSQL = "SELECT tblBomImportRaw.RawBomID, tblBomImportRaw.rbID, tblBomImportRaw.rbOrdersID, tblBomImportRaw.rbDrawingObjectType " & vbCrLf & _
"FROM tblBomImportRaw " & vbCrLf & _
"WHERE (((tblBomImportRaw.rbOrdersID)=getOrdersID())) " & vbCrLf & _
"ORDER BY tblBomImportRaw.RawBomID;"


Dim Rs As DAO.Recordset

    Set Rs = CurrentDb.OpenRecordset(strSQL)
    Rs.Edit
   
       
    If Rs.RecordCount > 0 Then
       
        Rs.MoveFirst
        Do Until Rs.EOF
       
            If IsNull(Rs!rbID) Then
                strID = "0"
            Else
                If strID = "0" Then
                    strID = Rs!rbID
                    Rs.Edit
                    Rs!rbDrawingObjectType = strID
                    Rs.Update
         
                Else
                Rs.Edit
                    Rs!rbDrawingObjectType = strID
                    Rs.Update
                End If
            End If
           
               
           
       

       

     Debug.Print strID
   
            Rs.MoveNext
        Loop
        Rs.Close
    Set Rs = Nothing
    End If


End Sub

Here are the comments, What is missing is the intent.

ChatGPT:
"
This code is written in VBA and is a subroutine that runs when the cmdImportRaw button is clicked. The first thing it does is check if the variable "pubOrdersID" is null, and if it is, it displays a message box saying "Select Order" and exits the subroutine.

Next, it runs two SQL statements: the first is a DELETE statement that deletes records from the "tblBomImportRaw" table where the "rbOrdersID" field matches the value of the "getOrdersID()" function. The second is an INSERT INTO statement that inserts records into the "tblBomImportRaw" table from the "xlsBillOfMaterialsBom" table.

Then it declares a variable "strID" and sets it to "0", and runs a SELECT statement that selects records from the "tblBomImportRaw" table where the "rbOrdersID" field matches the value of the "getOrdersID()" function, and orders the records by the "RawBomID" field. The results of this query are used to populate a DAO recordset object, which is then used to iterate through the records, updating the "rbDrawingObjectType" field with the value of the "strID" variable. Finally, it closes the recordset and sets it to nothing."

I am currently working on a serialized bom inventory system. What kind of products is your inventory system based on?

 

[gemma-the-husky]

@Thales750

Out of interest, what happens if your delete or insert statements fail, as the error handling is incomplete.
What does getordersid() and getuserid() do? What if they do not return the correct values?

 

[Thales750]

I am currently working on a serialized bom inventory system. What kind of products is your inventory system based on?

Residential and Light Commercial Construction

 

[Thales750]

@Thales750

Out of interest, what happens if your delete or insert statements fail, as the error handling is incomplete.
What does getordersid() and getuserid() do? What if they do not return the correct values?

getUserID() Returns the logged in User
getOrdersID() Returns the current tblOrders Primary Key.

This is the only error code I have ever needed for running queries in VBA.

    If Rs.RecordCount > 0 Then       
                     Rs.MoveFirst
                    Do Until Rs.EOF

              End If
                   Rs.Close
                   Set Rs = Nothing

Queries of this simplicity will run for infinity without fail. Once you debug them. Bear in mind this is a manual operation for importing new data into the system. It will be performed by admin folks and there is a Subform so a user can see the results.

I'm going to start a new thread on this. The latest import was 29,256 records with 8 levels of dependencies, That one will be better to go over since it is designed to creates recursive records with as many levels as needed.

 

[Thales750]

@Thales750

Out of interest, what happens if your delete or insert statements fail, as the error handling is incomplete.
What does getordersid() and getuserid() do? What if they do not return the correct values?

Another thing concerning public variable and Public functions based on them in Queries, never set them anywhere in the data base except one place. If you need a similar value make a new one. From what I've seen here on this forum, my systems are at least as reliable (probably more) as most. Also every system I build now is not running on a split db. They are all running on SQL, you could say that makes them extremely reliable. The ERP we made for the chemical plant has dozens of users on site and that many more on VPNs from home. So that make at least 50,000 hours in the last couple of years. Not a single issue.

 

[gemma-the-husky]

getUserID() Returns the logged in User
getOrdersID() Returns the current tblOrders Primary Key.

This is the only error code I have ever needed for running queries in VBA.

    If Rs.RecordCount > 0 Then      
                     Rs.MoveFirst
                    Do Until Rs.EOF

              End If
                   Rs.Close
                   Set Rs = Nothing

Queries of this simplicity will run for infinity without fail. Once you debug them. Bear in mind this is a manual operation for importing new data into the system. It will be performed by admin folks and there is a Subform so a user can see the results.

I'm going to start a new thread on this. The latest import was 29,256 records with 8 levels of dependencies, That one will be better to go over since it is designed to creates recursive records with as many levels as needed.

Is that really your error code?
How can that work?

 

[Uncle Gizmo]

I just saw a tweet:-

Someone asked GTP if children could be transgendered. GTP made an obvious political statement:- "that they should be able to" and they should be supported!

 

[Thales750]

I just saw a tweet:-

Someone asked GTP if children could be transgendered. GTP made an obvious political statement:- "that they should be able to" and they should be supported!

I decided within the first 10 minutes of use that it is not up to having social conversations. I was playing around with a lady friend, so I asked it what do you say to a beautiful women. I lectured me on the evils of objectifying women. I tried asking it with variations, like what if it is my girl friend.
Same lecture from the GPT. The bottom line is it is a program. Real philosophy alludes it.

I've known a lot of women in my life, if you sincerely tell one of them that she is beautiful without it having a motive. They will like it. And, sometimes even if you do have a motive.

 

[Pat Hartman]

Real philosophy alludes it.

It has been programmed to be a liberal. You can tell by its responses.

 

[Thales750]

Is that really your error code?
How can that work?

It works because once a query works, it will run a million times, or a billion times. It's the code that follows the error that is vulnerable, especially to inadvertent nulls. If the SQL Statement fails, it will normally not produce records, As I said, thousands of queries, ran millions of times, no errors.
Another reliability enhancer in our systems. No deletions, none ,nada, nowhere. Once a record is in a database, it should never be deleted.

 

[Thales750]

It has been programmed to be a liberal. You can tell by its responses.

I don't think the absence of thinking skills are limited to liberals, lol. It is going to be interesting how the mantles will be picked up by AI moving forward. Obviously more of the same divisive rhetoric everywhere. Only this time around, the concentration of wealth at the top is going to accelerated even faster.

 

[Pat Hartman]

The AI has show great ability to "think" but only when dealing with technical topics. When dealing with personal topics, it consistently takes a liberal view. Someone programmed that. Just the way that someone programmed Bing to "feature" negative articles on Trump. The evil orange man is living rent free in the heads of the Congress and the media and Bing/MSN is doing its best to make sure I see all the negative articles about Trump even though I never click on them. I can tell by the headlines how biased they will be even if there is some grain of truth to their critique. There is almost never a day where when I open a new window, a Trump article isn't presented front and center. How important is he? Apparently much more important than the real news of the day. Today's lead is "Trump used records requests to get look behind scenes of talks over his taxes" I guess this must be wrong for some reason. It doesn't even seem to be news to me.