I am trying to insert a SampleID(autonumber), ArticleNo(number), and SamplePoint(Text) into a table set up for the results. I need the Sample ID, ArticleNo and SamplePoint to tie the results table to the Samples Taken Table. I have searched the internet and tried several different samples that have not worked. Below is the latest approach I am trying. I am kinda new and am desparate for help.

Private Sub DateLogged_Click()
Dim SampleID As Integer
Dim ArticleNo As Interger
Dim SamplePoint As TextBox

dbs.Execute "INSERT INTO FGResultsTable(SampleID, ArtID, SPID)" & _
"SELECT (SampleID, ArticleNo, SamplePoint)" & _
"FROM FGSamplesTaken;"
dbs.Close
End Sub

well if you want both tables to be the same if its not a problem i would say turn off the autonumber in the column's table you trying to insert to. then turn it back on. in think i did something like that once.

Jose, thanks for replying so quickly. Although I do not have a autonumber set up for the table I am trying to insert into. The two tables will be tied together by the SampleID (autonumber) from the first table. I modified the code somewhat, though it is still not working. The code is listed below.

Private Sub Combo65_AfterUpdate()
Dim SampleID As Integer
Dim ArticleNo As Integer
Dim SamplePoint As String


RunSQL "INSERT INTO FGResultsTable (SampleID, ArtID & SPID)" & _
"SELECT FGSamplesTaken (SampleID, ArticleNo, SamplePoint);"

End Sub


Any help is greatly appreciated.

Ok do you need help trying to build the sql statement or you want to know whole process to insert data thru visual basic? I think I am misunderstanding what is it that you want to approach.

I am trying to set up a database to track samples in a lab. I have a table set up with SampleID(autonumber, Article No, Material Tested, Required Tests and acceptable limits. Now I want to set up a table for the lab personnel to enter their results and I need to tie it back to the Sample ID. Since our last correspondence, I have tried several other examples I have seen on the net. Below is my latest attempt.

Private Sub DateLogged_Click()
Dim SampleID As Integer
Dim ArticleNo As Integer
Dim SamplePoint As String
Dim SQL As String

Sqlstr = String "INSERT INTO FGResultsTable VALUES ('{0}','{1}','{2}') SampleID.Value , ArtID.Value, SPID.Text


End Sub

Ok in visual you will need a code like this.

Dim SampleID As Integer
Dim ArticleNo As Integer
Dim SamplePoint As String
'Initiate your variables with some values
SampleId = 1
ArticleNo = 2
SamplePoint = "Some strings"
Dim conn as New System.Data.SqlClient.SqlConnection("Initial Catalog=DbName;Data Source=ServerName;UID=UserName;PWD=Password;")
Dim sSQL as String
sSQL = "INSERT INTO FGResultsTable VALUES("+SampleID+","+ArtilcleNo+",'"+SamplePoint+"')"			
Dim cmd as New SqlCommand(sSQL, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.