Trying to make a program, but in one of my forms...

I am writing to a table, one of the things in this is an auto number...

SpecSales Table , working fine, gets updated... and allocates an auto number.

I'm reading from Customer Table & Eye test Table (using inner join)

When the SpecSales Table is updated, I need information from Customer Table, Eye Test Table & SpecSales Table to be inserted into a text file, I currently have the information from the Customer Table & Eye Test Table working fine, but I'm lost on how to get the SpecSales Table information into it, all I need is the auto number from it (SpecSales ID)

Code is here to get an idea of what im doing...

SpecSalesTable Code:
  1. Public Class SpectaclesSelection

  2.     Private Sub SpectaclesSelection_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

  3.         DisplayList(5) 'LOADS DISPLAYLIST METHOD

  4.     End Sub

  5.  
  6.     Private Sub DisplayList(ByVal CusRef As Integer)

  7.         Dim ConnectionString As String

  8.         Dim SQLString As String

  9.         Dim TitleString As String

  10.         Dim conn As System.Data.OleDb.OleDbConnection

  11.         Dim dr As System.Data.OleDb.OleDbDataReader

  12.         Dim cmd As System.Data.OleDb.OleDbCommand

  13.         ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data "

  14.         ConnectionString += "Source=" & "Opticians.accdb "

  15.         conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

  16.         SQLString = "SELECT * FROM CustomerTable" 'SELECTS ALL INFORMATION

  17.         Try                                     '    FROM DATABASE

  18.             conn.Open()

  19.             If ConnectionState.Open Then

  20.                 cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)

  21.                 dr = cmd.ExecuteReader()

  22.                 If dr.HasRows Then

  23.                     ListBoxOutput.Items.Clear()

  24.                     While dr.Read

  25.                         If Not IsDBNull(dr.Item("Surname")) Then  'INSERTS INFORMATION INTO

  26.                             TitleString = dr.Item("Surname")    '       LISTBOX

  27.                             ListBoxOutput.Items.Add(TitleString)

  28.                         End If

  29.                     End While

  30.                 End If

  31.                 dr.Close()

  32.             End If

  33.         Catch

  34.             MessageBox.Show("Error accessing database")     'IF ERROR ENCOUNTERED SHOWS MESSAGE

  35.         End Try

  36.         conn.Close()

  37.     End Sub

  38.  
  39.     Private Sub ListBoxOutput_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBoxOutput.SelectedIndexChanged

  40.         Dim ConnectionString As String

  41.         Dim SQLString As String

  42.         Dim cmd As System.Data.OleDb.OleDbCommand

  43.         Dim conn As System.Data.OleDb.OleDbConnection

  44.         Dim dr As System.Data.OleDb.OleDbDataReader

  45.         ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "

  46.         ConnectionString += "Source=" & "Opticians.accdb "

  47.         conn = New System.Data.OleDb.OleDbConnection(ConnectionString)

  48.         AddressTextBox.Text = ListBoxOutput.Text

  49.         SQLString = "SELECT * FROM CustomerTable "

  50.         SQLString += "INNER JOIN EyeTestTable ON CustomerTable.CustomerID = EyeTestTable.CustomerID "

  51.         SQLString += "Where '" & AddressTextBox.Text & "'"

  52.         SQLString += "= Surname"

  53.         Try

  54.             conn.Open()

  55.             If ConnectionState.Open.ToString = "Open" Then

  56.                 cmd = New System.Data.OleDb.OleDbCommand(SQLString, conn)

  57.                 dr = cmd.ExecuteReader()

  58.                 If dr.HasRows Then

  59.                     dr.Read()

  60.                     If Not IsDBNull(dr.Item("Street")) Then

  61.                         AddressTextBox.Text = dr.Item("Street").ToString

  62.                     End If

  63.                     If Not IsDBNull(dr.Item("Town")) Then

  64.                         Address2TextBox.Text = dr.Item("Town").ToString

  65.                     End If

  66.                     If Not IsDBNull(dr.Item("County")) Then

  67.                         Address3TextBox.Text = dr.Item("County").ToString

  68.                     End If

  69.                     If Not IsDBNull(dr.Item("RightEyeMeasurement")) Then

  70.                         RightEyeTextBox.Text = dr.Item("RightEyeMeasurement").ToString

  71.                     End If

  72.                     If Not IsDBNull(dr.Item("LeftEyeMeasurement")) Then

  73.                         LeftEyeTextBox.Text = dr.Item("LeftEyeMeasurement").ToString

  74.                     End If

  75.                     If Not IsDBNull(dr.Item("DateOfTest")) Then

  76.                         DateTextBox.Text = dr.Item("DateOfTest").ToString

  77.                     End If

  78.                 End If

  79.             End If

  80.         Catch ex As Exception

  81.         End Try

  82.  
  83.  
  84.     End Sub

  85.  
  86.     Private Sub ListBoxOutput_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBoxOutput.Click

  87.  
  88.         Button2.Enabled = True

  89.         GroupBox3.Enabled = False

  90.  
  91.  
  92.     End Sub

  93.  
  94.     Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

  95.         Me.Close()

  96.     End Sub

  97.  
  98.     Private Sub ExitToolStripMenuItem_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click

  99.         Me.Close()

  100.  
  101.     End Sub

  102.  
  103.     Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

  104.  
  105.     End Sub

  106.  
  107.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  108.         Me.Close()

  109.  
  110.     End Sub

  111.  
  112.     Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

  113.  
  114.     End Sub

  115.  
  116.     Private Sub GroupBox2_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox2.Enter

  117.  
  118.     End Sub

  119.     Private Sub TextBox2_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Address2TextBox.TextChanged

  120.  
  121.     End Sub

  122.  
  123.     Private Sub OrderButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OrderButton.Click

  124.         Dim myValue As Integer

  125.         Dim myValue1 As Integer

  126.         Dim myValue2 As Integer

  127.         Dim myValue3 As Integer

  128.         Dim totalCost As Integer

  129.         Dim deposit As Integer

  130.  
  131.         myValue = 50

  132.  
  133.         Select Case GPTextBox.Text

  134.             Case "Glass"

  135.                 myValue1 = 30

  136.             Case "Plastic"

  137.                 myValue1 = 15

  138.         End Select

  139.  
  140.         Select Case ScratchTextBox.Text

  141.             Case "Yes"

  142.                 myValue2 = 10

  143.             Case "No"

  144.                 myValue2 = 0

  145.         End Select

  146.  
  147.         Select Case UVTextBox.Text

  148.             Case "Yes"

  149.                 myValue3 = 15

  150.             Case "No"

  151.                 myValue3 = 0

  152.         End Select

  153.  
  154.  
  155.  
  156.         totalCost = myValue + myValue1 + myValue2 + myValue3

  157.         deposit = totalCost * 0.2

  158.  
  159.         TextBox1.Text = totalCost

  160.         TextBox2.Text = deposit

  161.         TextBox3.Text = totalCost - deposit

  162.  
  163.         Button3.Enabled = True

  164.         Button4.Enabled = True

  165.         myAddNew(8) 'CALLS ADDNEW METHOD WHEN ADD BUTTON IS CLICKED

  166.  
  167.  
  168.  
  169.     End Sub

  170.     Private Sub myAddNew(ByVal CusRef As Integer)

  171.         Dim ConnectionString As String

  172.         Dim SQLString As String

  173.         Dim whichButtonDialogResult As DialogResult

  174.         Dim dbCommand As System.Data.OleDb.OleDbCommand

  175.         Dim Connection As System.Data.OleDb.OleDbConnection

  176.         ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "

  177.         ConnectionString += "Source=" & "Opticians.accdb "

  178.         Connection = New System.Data.OleDb.OleDbConnection(ConnectionString)

  179.         SQLString = "INSERT INTO SpecSalesTable (FrameID, DateOfSale, GlassOrPlastic, ScratchCoating, UVFilter, TotalCost, DepositPaid) "

  180.         SQLString += "Values ('" & StockTextBox.Text & "',#" & Date.Today & "#,'" & GPTextBox.Text & "','" & ScratchTextBox.Text & "','" & UVTextBox.Text & "','" & TextBox1.Text & "','" & TextBox2.Text & "')"

  181.         whichButtonDialogResult = MessageBox.Show("Are You Sure You Want To Place This Order?", "Add Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

  182.         If whichButtonDialogResult = DialogResult.Yes Then

  183.             Connection.Open()

  184.             If CBool(ConnectionState.Open) Then

  185.                 dbCommand = New System.Data.OleDb.OleDbCommand(SQLString, Connection)

  186.                 Try

  187.                     dbCommand.ExecuteNonQuery()

  188.                     MessageBox.Show("Order placed, SpecSalesTable Updated! ")

  189.                 Catch ex As Exception

  190.                     MessageBox.Show(" Error placing order... ")    'IF ERROR DISPLAYS MESSAGE

  191.                 End Try

  192.  
  193.             End If

  194.  
  195.         End If

  196.  
  197.         Connection.Close()

  198.     End Sub

  199.  
  200.     Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

  201.  
  202.     End Sub

  203.  
  204.     Private Sub IDTextBox_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

  205.  
  206.     End Sub

  207.  
  208.     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

  209.  
  210.         GroupBox3.Enabled = True

  211.  
  212.  
  213.     End Sub

  214.  
  215.     Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

  216.         Dim txtFile As String = IO.File.ReadAllText("template.txt")

  217.         txtFile = txtFile.Replace("[date]", "07/03/2010")

  218.         txtFile = txtFile.Replace("[left eye]", LeftEyeTextBox.Text)

  219.         txtFile = txtFile.Replace("[right eye]", RightEyeTextBox.Text)

  220.         txtFile = txtFile.Replace("[material]", GPTextBox.Text)

  221.         txtFile = txtFile.Replace("[UVF]", UVTextBox.Text)

  222.         txtFile = txtFile.Replace("[Scratch]", ScratchTextBox.Text)

  223.         

  224.         IO.File.WriteAllText("NewOrder.txt", txtFile)

  225.         Process.Start("NewOrder.txt")

  226. End Class
*had to leave some out