I have an access 2007 db and I am trying to retrieve data. I have a table "Projects" with ProjectNumber key one to many ProjectNumber in the "General" Table. ReportNumber is the key in my General Table which links 1 to 1 in my other tables. I selected the General Table after the Projects table when setting up my datasource too, so it's all connected nicely and filters perfectly in a series of comboboxes or datagridview, etc. My data rows look like this in my "General" table...


etc... so they will eventually be stored as they are entered, all out of order, mixing many project numbers and report numbers.

I have a combobox bound to my "Projects" table displaying all project numbers. What I need is when the user selects a project number from the combobox it will automatically add 0.001 to the highest exisitng report number and display it in a label.

What I have so far...

Private Sub NewReportNumber()

        If FormMain.ComboBoxReportNumber.Text = "" Then
            Me.ReportLabel.Text = Me.ComboBoxProjectNumber.Text & "0.0001"
            Dim oldNumber As String
            Dim trimDecimal As String
            Dim AddOne As String
            Dim trimAgain As String
            Dim newNumber As String
            oldNumber = Me.NewReportDataSet.Tables("General").Rows(0)(1).ToString
            trimDecimal = Microsoft.VisualBasic.Right(oldNumber, 5)
            AddOne = trimDecimal + 0.0001
            trimAgain = Microsoft.VisualBasic.Right(AddOne, 5)
            newNumber = Me.ComboBoxProjectNumber.Text & trimAgain
            Me.ReportLabel.Text = newNumber
        End If
    End Sub

    Private Sub ComboBoxProjectNumber_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBoxProjectNumber.SelectedIndexChanged
    End Sub
I have my query set to descending so the largest project number is listed first hoping it would pull it from the row.

I can display a datagridview or combobox or listbox and it only shows the revelant report numbers associated with the project number chosen. But when I try to pick it from the datatable it doesn't filter it at all and goes back to picking the first row of data in my database.

Anyone know how to get it to pick the last report number sorted by the relevant project number and add 0.0001 to it and display that number please let me know!!!