Adding information to Access DB

Hi - I've writting the following code to add information into an MS Access db but I'm having problems.

Problem:    
    The intMaxRows number is based on categories and the changes with each category.  Sometimes is will only be 8 and others as many as 90.
    Therefore, I cannot put in a set number of ".Fields("GR1a") = strQA(t)", etc...
    It will add a new record but not the field results.
    The code that I've written will debug.print the correct results.  However, the values are not added to the DB.

Any suggestions?

'------------------------------------------------------------------------------------------------------------------
Function Import2Access()
On Error Resume Next

    Dim rsResults As Recordset
    Dim sqlSurvey As String
    Dim strRowA As String, strRowB As String, strRowC As String, strRowT As String
    Dim strQuotes As String
    Dim v As Integer
   
    strQuotes = """"
    Debug.Print "TableName: " & strTableName
   
    If (Not OpenDataBase(strTableName)) Then
        MsgBox "Database could not be Openend !"
        End
    End If
   
    sqlSurvey = "Select * "
    sqlSurvey = sqlSurvey & "From " & strTableName & " " & strQuotes

    Set rsResults = dbSurvey.OpenRecordset(sqlSurvey)
   
        With rsResults
            .AddNew
           
            For t= 1 To intMaxRows                                   '<---------------------------------------------- HERE'S WHERE THE PROBLEM STARTS
                strRowA = strFormNameA & t & "a"    
                strRowB = strFormNameA & t & "b"    
                strRowC = strFormNameA & t & "c"          
                strRowT = strFormNameA & t& "t"                                                                            
                                                                   
                .Fields(strRowA) = strQA(t)
                .Fields(strRowB) = strQB(t)
                .Fields(strRowC) = strQC(t
                .Fields(strRowT) = strQT(t)
                Debug.Print v & " " & strQA(v) & " " & strQB(v) & " " & strQC(v) & " " & strQT(v)
            Next
            .Update
            .MoveLast
        End With

    dbSurvey.Close
   
End Function
'------------------------------------------------------------------------------------------------------------------
eciabattariAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RogueSolutionsCommented:
First thing I can see is that your .AddNew and .Update are outside the loop so the fields just keep getting updated with the db being told to store them.

May be more to it than that though?
0
eciabattariAuthor Commented:
I placed the .AddNew and .Update outside the loop due the fact that 1 record may have 8 fields, 11 fields or 60 fields worth of data.  It really depends on the category

DB Configuration
Table "tblGeneral"
field 1: ID, autonumber, primary key
field 2: GR1a
field 3: GR2a
field 18: GR16a

Table tblContracting
field 1: ID, autonumber, primary key
field 2: CT1a
field 3: CT2a
field 25: GR24a

I only wanted to add 1 record for each category & survey response.
0
RogueSolutionsCommented:
Right OK, think I see what you have better now.

One further thing, your debug line uses v when the loop was t ?  Maybe that's just a typo though?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

eciabattariAuthor Commented:
Figured it out....

Changed  .Fields(strRowT) = strQT(t) to

If strQT(t) <> "" Then
         .Fields(strRowT) = strQT(t)
End If
                   
0
RogueSolutionsCommented:
OK.  You Access fields won't allow empty strings.  Surprised you didn't get an error for trying to set them as such
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eciabattariAuthor Commented:
Thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.