Using VBA to generate a form

[agent]

Hi everyone,

Just to start off with, let me say that I am a beginner to Access. I have used MySQL in the past, so I have some idea of the table structure and SQL syntax. I've also done a little bit of VBA for Excel.

Right now, I'm trying to do a database to hold customer surveys. Basically, the customer fills out a piece of paper, and then this information gets entered into the database. This lets other people in the company generate reports and analyze the data.

Now, the questions for the surveys are split into three categories (Sales, Construction, and Service). I have a table holding these categories like so:

SurveyQuestions_Categories

ID	CategoryName
1	Sales
2	Construction
3	Service

Survey questions also have their own table and form; I wanted it to be easy to add/edit/delete questions without having to go into the guts of the database itself. There are also two types of answers possible; a numerical scale from 1-10 (AnswerType of 1), and a text field (AnswerType of 2). Let's say I have something like this:

SurveyQuestions

ID      CategoryID	Question	AnswerType
1       1	        blah blah       1
2       2	        question2       1
3       3	        Comments        2
4       1               Some question   1

Answers are stored in a third table with three fields (SurveyID, QuestionID, Answer). Now how would I go about generating a dynamic form taking into account all of the above? The form should look something like this:

Customer Survey

Sales
  1. blah blah
     <combo box with 1-10>

  2. Some question
     <combo box with 1-10>

Construction
  1. question2
     <combo box with 1-10>

Service
  1. Comments
     <text box>

The only way I can think of doing it is using VBA to dynamically generate the form. Here's what I have so far to loop through the tables to pull out the categories and questions:

Sub GenerateSurveyForm()

    Dim db As Database
    
    Set db = CurrentDb()
    Set results = db.OpenRecordset("SELECT id, CategoryName FROM SurveyQuestions_Categories ORDER BY id ASC")
    While Not results.EOF
        Set results2 = db.OpenRecordset("SELECT id, CategoryID, Question, AnswerType FROM SurveyQuestions WHERE CategoryID = " & results![id] & " ORDER BY id ASC")
        While Not results2.EOF
            <form code here>
            
            results2.MoveNext
        Wend
        results.MoveNext
    Wend

End Sub

However, I can't figure out how to use VBA to generate the actual form fields. If I were doing this with PHP, MySQL, and HTML, I'd know how to handle this. However, VBA and Access forms seem kind of restricting to me. Am I on the right track? Is there a better (or more efficient) way to do this?

Thanks!

 

[ajetrumpet]

Probably a more efficient way, but this code might be of use to you:

Set frm = CreateForm(, Me.listtables)
frm.DefaultView = 2

  strRS = rs.Name
    strFORM = frm.Name
      frm.RecordSource = "temp"

    For i = 0 To rs.Fields.Count - 1
      Set c = CreateControl(strFORM, acTextBox, acDetail, , rs.Fields(i).Name)
        c.Name = rs.Fields(i).Name
    Next i

I took it from one of my examples. It creates a dynamic form, then populates it with controls that are bound to each field of the table (represented in VB by the open recordset). That's probably the line you're going to want to walk here...

Just be aware that creating forms in Basic are not going to pretty, and if you try and make them so, you're going to be doing a lot more work than is necessary. If you want a "pretty looking" form, I would suggest using the interface, wizards, toolbox, etc. You'll save yourself a lot of grief.

 

[agent]

Thanks for the quick response, ajetrumpet! That gives me a starting point for solving this problem.