The GPT can comment your code.

[Thales750]

But your code as shown keeps rereading the first record, and must get stuck in a forever loop. Am I missing something?

Here is the code with some comments. Following the code is an image of output.

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


'Add Object Type to all Objects based on  the Heading from the linked Excel File BillOfMaterials.xlsx

Dim strID As String
'Set the start value for the Varible that holds the Heading Value
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;"


'StrID can exist in four states
'1.  as "0"
'2.  as IsNull
'3. As the Current field value from Rs!rbID
'4. As a previous field value from Rs!rbID

'If the value of StrID going into a new record is "0" then it is given the vaule of the current field Rs!rbID
' If it is Null it will skip the first condition and move the the next one which is
' StrID gets the value from the current Rs!rbID
'in both cases Rs!rbDrawingObjecType gets its new value from StrID

 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
  
            Rs.MoveNext
        Loop
        Rs.Close
    Set Rs = Nothing
    End If


End Sub

Only the field Rs!rbDrawingObjectType is updated from Function

 

[Thales750]

But your code as shown keeps rereading the first record, and must get stuck in a forever loop. Am I missing something?

Did you get a chance to look at the last post?

 

[gemma-the-husky]

Did you get a chance to look at the last post?

Yes, I can see there's more code within the loop including a .movenext