Need help with INSERT INTO

[FreshCoder]

Hi again!

I need to insert planks from planks table into planksOK table. They are only allowed to be 200cm, so if they are over they need to be cut.(300 cm = 200 + 100 and so on). Remaining length will be placed in own columns in planksOK.
I have managed to insert 200cm and below.


So if anyone can help me out it would be good
Sorry for bad english

Option Compare Database
Option Explicit

Private Sub comCut_Click()
    
    Dim strsql As String
    Dim stdset As DAO.Recordset
    
    Dim maxlength As Integer, LengthCM As Integer, LenghtOK As Integer, LengthRest1 As Integer, Lengthrest2 As Integer
                
    'empty result
    strsql = "DELETE * FROM PlanksOK"
    CurrentDb.Execute (strsql)
    
    strsql = "INSERT INTO PlanksOK(LengthOK) SELECT LengthCM FROM Planks WHERE LengthCM<=200"
    CurrentDb.Execute (strsql)
    

        strsql = "SELECT * FROM Planks"
        Set stdset = CurrentDb.OpenRecordset(strsql)
        With stdset
            Do Until LengthCM = 0
             LengthCM = !LengthCM
             maxlength = 200
                 If LengthCM > maxlength + 30 Then
                 strsql = "INSERT INTO PlanksOK(LengthRest1) SELECT LengthCM FROM Planks"
                 CurrentDb.Execute (strsql)
                 End If
            Exit Do
           .MoveNext
        .Close
        Loop
     End With
     End Sub

 

[Ranman256]

instead of using code, use queries.
The query has the criteria of the length.

no code needed.

 

[plog]

1. You are inserting all LengthCM<=200 records from Planks twice. Is that what you want? You do it initially, then they get put in again with your second section of code.

2. Where's the subtraction? You want a plank of 300 to be inserted twice, correct? Once as a 200 record and once as a 100 record. I see no subtraction to do that calculation. How's the code to know what to insert for the value?

3. LengthCM never gets updated inside your Do loop. The condition to exit the Do loop never gets hit once its inside the Do loop because LengthCM never gets updated to a new value.

4. LengthCM never equals anything (along with like 3 other variables). You never assign it a value, so I don't know what happens it when hits that Do loop for the first time. Probably blows the whole thing up. Why so many unused variables?

 

[FreshCoder]

I dont want it twice.
Yeah. Youre right. I have been trying so hard for days to find out how to insert into those columns that i probably forgot the most important.
I really dont know.
Are mye insert into lines atleast right?

Option Compare Database
Option Explicit

Private Sub comCut_Click()
    
    Dim strsql As String
    Dim stdset As DAO.Recordset
    
    Dim maxlength As Integer, LengthCM As Integer, LenghtOK As Integer, LengthRest1 As Integer
                
    'empty result
    strsql = "DELETE * FROM PlanksOK"
    CurrentDb.Execute (strsql)
    
    strsql = "INSERT INTO PlanksOK(LengthOK) SELECT LengthCM FROM Planks WHERE LengthCM<=200"
    CurrentDb.Execute (strsql)
    

        strsql = "SELECT * FROM Planks"
        Set stdset = CurrentDb.OpenRecordset(strsql)
        With stdset
            Do Until LengthCM = 0
             LengthCM = !LengthCM
             maxlength = 200
                 If LengthCM > maxlength + 30 Then
                 strsql = "INSERT INTO PlanksOK(LengthRest1) Values('" & LengthCM & "')"
                 LengthCM = LengthCM - maxlength
                    ElseIf (LengthCM > maxlength) And (LengthCM < maxlength + 30) Then
                    strsql = "INSERT INTO PlanksOK(LengthRest1) Values('" & 30 & "')"
                    CurrentDb.Execute (strsql)
                    LengthCM = LengthCM - 30
                    End If
           .MoveNext
            Loop
        .Close
       End With
     End Sub

 

[FreshCoder]

Here`s a picture of how it looks with the tables

 

[plog]

Ranman was right, this should be done solely by queries. I thought you wanted a new record for any overage of 200. You want that value to simply go into a new column. Don't know why you want it that way, but that isn't something for VBA, but SQL.

You simply use IIF values to cut up your value and place the appropriate amount into each column:

LengthRest1Value: Iif(LengthCM>400, 200, Iif(LengthCM>200, LengthCM -200, 0))

 

[FreshCoder]

Thanks.

I need to make it with code too!...


This part is not working :

strsql = "SELECT * FROM Lister"
    Set stdset = CurrentDb.OpenRecordset(strsql)
    With stdset
          'Do While Not .EOF
          Do Until lengde = 0
        lengde = !Lengde1
            maksLengde = 200
            minLengde = 30
            If lengde >= maksLengde + minLengde Then
                lengde = lengde - maksLengde
                strsql = "INSERT INTO ListerOK (lengde3) VALUES(" & maksLengde & ") "
                CurrentDb.Execute (strsql)
            ElseIf lengde > minLengde Then
                strsql = "INSERT INTO ListerOK (lengde4) VALUES(" & lengde & ") "
                CurrentDb.Execute (strsql)
                Exit Do
            End If
            'Loop
            .MoveNext
        Loop
        .Close
    End With

Is there any way to make do until and do while not .eof work together here?
its returning only one value and its the wrong one.

 

[arnelgp]

put in excel what are you trying to do.