Need help with VBA syntax for table lookup

[hunterw]

I'm using a table imported from an external spreadsheet for some of my form data.

The first thing to do is look up an ID in the first column of the table. This should store the row that the ID is stored in. After this, I need to look through its row, and if a field is not NULL, it needs to return both the field's value and the column name.

Help me oh masters of Access VBA!

 

[hunterw]

I'm trying to fill data boxes in a form by referring to this table, but the way data is organized in the table is not intuitive. The first column of the table contains an ID which will be searched for. I need the row number since the ID is not simply 1,2,3,4 etc. With row number in hand, I need to look through the rest of the columns in that row for data. Most of these fields will be blank, which I don't need. When a field does contain data, however, I need to fill a couple of form data boxes - one with the name of that specific column, and the second with the data in that field.

This could happen up to about 10 times, so I have 20 boxes in the form that might be filled up.

Let me try and clarify things with some pseudocode!

Private Sub Mouse_UID_Exit(Cancel As Integer)
    Dim SearchCriteria As String
    Dim DataTable As Dynaset
    Dim RowNumber As Long
    Dim i As Integer
    Dim x As Integer
    
    Set SearchCriteria = [Mouse UID]
    Set DataTable = [GenotypeTable]
    Set RowNumber = SearchTableAndReturnRowNumber(DataTable, SearchCriteria)
    
    For i = 1 To DataTable.NumberOfColumns
        If IsNull(DataTable.i.RowNumber) Then
            Next
        Else
   	    ["DataBoxA" & x] = DataTable.i.RowNumber
	    ["DataBoxB" & x] = Datatable.i.Fields()
	    x++
        Next
End Sub