Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2008
    Posts
    22

    Unanswered: Need help with VBA syntax for table lookup

    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!

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm not sure if you gave enough information to completely help you or not. However, I can give you some pointers.

    Depending on whether you're using and indexed table or not, and whether you're using DAO or ADO, you want to use the .Seek, .Find, or .FindFirst method. Also, you need the .Fields() property to find out the column name. I suggest you read the Help on the above before you attempt to do any programming.

    Hope this is helpful,

    Sam

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why do you need to do this exactly? What do you intend to do with the NULLs?
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    22
    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.
    Last edited by hunterw; 06-24-08 at 15:23.

  5. #5
    Join Date
    Jun 2008
    Posts
    22
    Here's my code so far:

    Code:
        Dim dbsNecropsy As DAO.Database
        Dim rstGenotypes As DAO.Recordset
        Dim fld As DAO.Field
        Dim FieldName As String
        Dim Blah As Integer
        
          
        Set dbsNecropsy = CurrentDb
        Set rstGenotypes = dbsNecropsy.OpenRecordset("FacilityTyping", dbOpenTable)
        
        rstGenotypes.Index = "Animal UID"
        rstGenotypes.Seek "=", [Mouse UID]
        
        If rstGenotypes.NoMatch Then
            MsgBox "Genotypes for UID could not be found."
        Else
            [Gender] = rstGenotypes!Sex
            For Blah = 11 To rstGenotypes.Fields.Count - 1
                Set fld = rstGenotypes(Blah)
                Set FieldName = fld.Name
                If IsNull(rstGenotypes!FieldName) Then
                    Next
                Else
                    ["Gene" & Blah] = rstGenotypes!FieldName
                    ["Allele" & Blah] = FieldName
                End If
            Next
        End If
    I have no idea if it works, as it throws an error at me for this:

    Set FieldName = fld.Name

    I have no idea why.

    Any and all help, as always, is greatly appreciated!

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    FieldName is not an object (and it should not be an object given the way you are using it), it is just a simple string variable.
    get rid of the Set

    this will move the error to the next line unless you have the misfortune to have a field in your recordset having the literal name FieldName.
    to get rid of this new error try
    rstGenotypes(FieldName)

    the error now moves to the GeneBlah line. same fix:
    rstGenotypes(FieldName)

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •