0

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

2
Contributors
5
Replies
6
Views
7 Years
Discussion Span
Last Post by jbisono
0

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.

0

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.

0

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.

Edited by jbisono: n/a

0

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

0

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()

Edited by jbisono: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.