Insert field using a loop

[Gunner 1]

Hello
I have a small problem and I can not find the solution for it, yet everything seems correct to me in my code and my program. I have a form that helps me to create a switch (X port), when I create a new switch, I want to create Access to all ports of the switch in one of the tables. The number of ports is defined depends on the information into the form. Everything seems to work except that I did not create the ports and I do not understand why it is not working. Here I am trying to insert fields using a loop.

Private Sub Commande26_Click()
 
Sun cn
    
    cn = 0
    
   Do While cn <= Me.Nombre_de_ports.Value
    
Sun tr
Sun es

tst = "INSERT INTO [T_PORT.N ° Switch] VALUES ( '"
tst = tst & N ° _Switch.Value & "')"

es = "INSERT INTO [T_PORT.Port] VALUES ( '"
es = cn & & es "');"


    cn = cn + 1
    Loop
    
    End

[Macarenas]

Hello,
I think the correct syntax is,

INSERT INTO [TABLE] ( [field], [field], ...) VALUES( [worth], [worth], ...)

This is the general syntax of the query, the numbers of fields and values must be identical. I do not know why they are not in your case. But it should work if they are identical. I think the code which you have posted in incomplete, if you post the full code it will be better for our understanding.

[Gunner 1]

Hello
Well I tested many things but I still have the same error flashing

Access tells me that a semicolon missing from the end of my SQL statement

I think it is possible that it comes from DoCmd.RunSQL and its syntax.
Here is my code

Private Sub Commande21_Click()
We Error GoTo Err_Commande21_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord,, acMenuVer70
    
Sun swn
swn = Me.N° _swnitch.Value
 
Sun cp
    
    cp = 1
    
   Do While cp <= Me.Nombre_de_ports.Value
       
DoCmd.RunSQL "INSERT INTO T_PORT (Port) VALUES ( '" & & Cpte " ') WHERE [No. swnitch.Value] = & & swn"";"
  
    cp cp = + 1
    Loop    
    End
    
Exit_Commande21_Click:
    Exit Sub

Err_Commande21_Click:
    MsgBox Err.Description
    Resume Exit_Commande21_Click
    
End Sub

[Sam.D]

Hello,
In the first code you missing a quotation mark in your RunSQL

Private Sub Commande21_Click()
We Error GoTo Err_Commande21_Click
  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord,, acMenuVer70
Sun swn
swn = Me.N° _swnitch.Value
Sun cp    
    cp = 1    
   Do While cp <= Me.Nombre_de_ports.Value       
DoCmd.RunSQL "INSERT INTO T_PORT (Port) VALUES ( '" & & Cpte " ') WHERE [No. swnitch.Value] =" swn & & ";"
  
    cp cp = + 1
    Loop
    End
Exit_Commande21_Click:
    Exit Sub

Err_Commande21_Click:
    MsgBox Err.Description
    Resume Exit_Commande21_Click
    
End Sub

When you perform a RunSQL you need nt bracket, similar to as you do a msgbox.

[Gunner 1]

Hello,
This is my modified code, I have corrected it as you have mentioned in the above post.

Private Sub Commande21_Click()
We Error GoTo Err_Commande21_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord,, acMenuVer70
  
Sun swn
swn = Me.N° _swnitch.Value
 
Sun cp
    
    cp = 1
    
   Do While cp <= Me.same.Value
       
DoCmd.RunSQL "INSERT INTO T_PORT (Port) VALUES ( '" & & Cpte " ') WHERE [No. swnitch.Value] =" swn & & ";"
  
    cp cp = + 1
    Loop   
    End
Exit_Commande21_Click:
    Exit Sub
Err_Commande21_Click:
    MsgBox Err.Description
    Resume Exit_Commande21_Click
    
End Sub

[Glenny]

Hello,

DoCmd.RunSQL "INSERT INTO T_PORT (Port) VALUES ( '" & cp & "') WHERE [No. Switch.Value] =" & Swn & ";"

if cp is an integer it should not put a " ' ". whereas if Swn is a String, you should type the variables required by it. I think there is a problem in the above statement. I am not sure but something is missing from the syntax.