Assistance on writing a macro

  • Thread starter Thread starter Bob Witt via AccessMonster.com
  • Start date Start date
B

Bob Witt via AccessMonster.com

Help, I am new at using Access and was hoping I can get some asssistance
writing what appears to be a simpla macro. Here is my situation: I have a
table that has essentially two columns, a RegID column and a Response
column (the table is used to record test scores). Each test response has
200 hundred rows, so the table looks like this: first column is the RegID
(a six digit number) and the second column is the Response (A,B,C or D).
Since there are 200 questions on the exam, the same RegID is repeated 200
times, followed by the appropriate response for that question. I need to
take this data and send it to a text file that is formatted like this:
RegID ABCDABCDABCD etc: with a hard return at the end of the line. A new
line is then the next unique RegID followied by ABCDABCDABCD, etc. How can
I do this with a macro?
 
Bob,

Doing this with a macro is possible, but very difficult and not appropriate.

There are some concatenation functions which you could use. For
example, see
http://www.rogersaccesslibrary­.com/OtherLibraries.asp#Hookom­,Duane

However, in this case, you could make a user-defined function specific
to the purpose. In a standard module, put something along the lines of
this...

Public Function TestScores(Reg As Long) As String
Dim rst As DAO.Recordset
Dim strScores As String
Set rst = CurrentDb.OpenRecordset("SELECT Response FROM YourTable
WHERE RegID=" & Reg)
With rst
Do Until .EOF
strScores = strScores & !Response
.MoveNext
Loop
.Close
End With
Set rst = Nothing
TestScores = strScores
End Function

Then, make a query that has available a list of unique RegIDs, and a
second column which is a calculated field like this...
TestScores([RegID])
Use this query as the source of your text export.
 
Back
Top