Loop excel column and match with records in database

[NickWren]

Sorry if my code is all over the place and doesnt make to much sense;

I am trying to write a code that can look through an excel worksheet and use the sample names to correlate to ones in the tables/queries, if the name exists then import the excel data, if not move to the next sample name on the excel spreadsheet.

    Dim fd As FileDialog
    Dim NormalizedFileName As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Add "Comma Delimited Files", "*.csv", 1
        .InitialFileName = "//Wrenlab02/research files/MULTIPLE MYELOMA/PCR DATA/MM Screen PCR/" & Year(Date) & "/"
        .InitialView = msoFileDialogViewDetails
        .AllowMultiSelect = False
        .Title = "Select CT Values to be imported for your Multiple-Myeloma Sample"
        If .Show Then
            selectFile = .SelectedItems(1)
             Dim xl              As Object
        Dim xlWrkBk         As Object
        Dim xlSht           As Object
        Set xl = CreateObject("Excel.Application")
        xl.Visible = False 'Control whether or not Excel should be visible to
                                'the user or not.
        Set xlWrkBk = xl.Workbooks.Open(selectFile)
        Set xlSht = xlWrkBk.Worksheets(1)
        
        Dim i As Integer
        i = 2
        LastRow = xlSht.UsedRange.Rows.Count
        Do While (i < LastRow)
        SampleName = xlSht.Cells(i, 1)

Dim rs As ADODB.Recordset
Dim sSQL As String
Dim sValue As String

sSQL = "SELECT * FROM tblPatient"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

DoCmd.SetWarnings False

            If SampleName = [subformtblFacilityPatients].Form![WrenID] Then
            Me.Text74 = [subformtblFacilityPatients].Form![WrenID]
            
            End If
                    Loop
        xlWrkBk.Close False 'Close the Workbook without saving any changes
        xl.Quit 'Close Excel
        Else
            End
        End If
    End With
    Set xlSht = Nothing
    Set xlWrkBk = Nothing
    Set xl = Nothing
    Set fd = Nothing

 

[static]

I'm not sure what your question is. I started rewriting your code and came up with these comments.

They may be useful or not...

You use DoCmd.SetWarnings - this is not a good thing. You should not need to turn off UI updates/messages for your code to run properly.
Don't create variables for values that are only used once.
variable i is set to 2 and never changes
...getting late.

Dim NormalizedFileName As String
Dim xl              As Object
Dim xlWrkBk         As Object
Dim xlSht           As Object
Dim i As Integer
Dim rs As dao.Recordset
Dim sValue As String

With Application.FileDialog(msoFileDialogFilePicker)

	.Filters.Add "Comma Delimited Files", "*.csv", 1
	.InitialFileName = "//Wrenlab02/research files/MULTIPLE MYELOMA/PCR DATA/MM Screen PCR/" & Year(Date) & "/"
	.InitialView = msoFileDialogViewDetails
	.AllowMultiSelect = False
	.Title = "Select CT Values to be imported for your Multiple-Myeloma Sample"
	
	If .Show Then
		Set xl = CreateObject("Excel.Application")
		xl.Visible = False 
		'Control whether or not Excel should be visible to the user or not.

		Set xlSht = xl.Workbooks.Open(.SelectedItems(1)).Worksheets(1)

		LastRow = xlSht.UsedRange.Rows.Count
		Do While (2 < LastRow)
			SampleName = xlSht.Cells(2, 1)
			Set rs = currentdb.openrecordset("SELECT * FROM tblPatient")
			If SampleName = [subformtblFacilityPatients].Form![WrenID] Then
				Me.Text74 = [subformtblFacilityPatients].Form![WrenID]
			End If
		Loop
		xl.Quit 
	Else
		End
	End If
End With

 

[Cronk]

Static, from a quick look, it seems to me that the OP was wanting i to increment. With your code, the loop
Do While (2 < LastRow)
will either never run, or will be endless

 

[static]

I know.

variable i is set to 2 and never changes

I hit that and went to bed. If OP shows more interest at some point maybe I will too.

 

[NickWren]

Sorry this was just a snippet of my code so I forgot the line i = i +1.
The problem I am having is having a look through a query for a given field (Sample ID) and then see if it matches any of the other Sample IDs on the excel spreadsheet; after it finds the matching Sample ID or doesnt the code should go down to the next row to check that Sample ID.

 

[NickWren]

This should be the full code; but I still am having trouble looking through the query "qryCOCandLabels" or the subform called "subformtblFacilityPatients" (they are essentially the same thing).

All I want to do is use an excel spreadsheets first column (has our "WrenID's" in it) to search through each one to see if there is a corresponding WrenID inside the query/subform. After that I will import data to that record so I might need to know how to call upon it so I can add more data to that specific record.

Private Sub Command73_Click()
    Dim fd As FileDialog
    Dim NormalizedFileName As String
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .Filters.Add "Comma Delimited Files", "*.csv", 1
        .InitialFileName = "//Wrenlab02/research files/MULTIPLE MYELOMA/PCR DATA/MM Screen PCR/" & Year(Date) & "/"
        .InitialView = msoFileDialogViewDetails
        .AllowMultiSelect = False
        .Title = "Select CT Values to be imported for your Multiple-Myeloma Sample"
        If .Show Then
            selectFile = .SelectedItems(1)
             Dim xl              As Object
        Dim xlWrkBk         As Object
        Dim xlSht           As Object
        Set xl = CreateObject("Excel.Application")
        xl.Visible = False 'Control whether or not Excel should be visible to
                                'the user or not.
        Set xlWrkBk = xl.Workbooks.Open(selectFile)
        Set xlSht = xlWrkBk.Worksheets(1)
        
        Dim i As Integer
        Dim n As Integer
        n = 1
        i = 2
        LastRow = xlSht.UsedRange.Rows.Count
        Do While (i < LastRow)
        SampleName = xlSht.Cells(i, 1)
        Do While (n < [subformtblFacilityPatients].Form![Total])

            If SampleName = [subformtblFacilityPatients].Form![WrenID] Then
            Me.Text74 = [subformtblFacilityPatients].Form![WrenID]
            End If
            
            n = n + 1
            Loop
                        
            i = i + 1
                    Loop
        xlWrkBk.Close False 'Close the Workbook without saving any changes
        xl.Quit 'Close Excel
        Else
            End
        End If
    End With
    Set xlSht = Nothing
    Set xlWrkBk = Nothing
    Set xl = Nothing
    Set fd = Nothing
End Sub

 

[Cronk]

So just as the doctor replied to the patient who said "I'm still having health troubles", where is the problem and what isn't working properly?