Ok
I have a table whihc has column name title and some survey question.
Now i want to create a form so that when i select Name ( I guess this will
be combo box) I dont know how to do that even where i can select name ? but
when i select name the person title auto populates and so does the relevant
answers to the questions
for example i choose John and the title should auto populate and question 1
should populate also and so on but if i pick adam then everything related to
adam populates.
Your help will be really appreciated
Your combo is probably "bound" to the Name field, in which case all you're doing when you select a new name is
overwriting the data in the current record with your selection.
In your case, you should make the combo "unbound" by deleting the value in the .ControlSource property of that combo.
Next, write code in the AfterUpdate event of the combo to go to the selected record:
Sub MyCombo_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "Name='" & Me.MyCombo.Column(0) & "'"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
MsgBox "No Match Found"
End If
Set rst = Nothing
End Sub
This would take you to the record selected, and would populate your form with the data from that record.
Additionally: I'd urge you to reconsider your table design, as well as your column names. Anytime you see columns named
xxxx1, xxxx2 etc then you're almost certainly working with a bad design. Also, what happens when John needs to take a
new survey, but you need to save the results of the old survey?
I'd think you would want a separate table for Users/People, on for SurveyDetails, another for SurveyQuestions, and one
for SurveyAnswers. Users would contain info on the people (Name, Phone, etc), SurveyDetails would contain "header" info
on the survey (NameOfSurvey, DateBegun, BuiltBy, etc), SurveyQuestions would contain the actual questions for a specific
survey (and would be related to SurveyHeader), and SurveyAnswers would contain User answers for that survey
As to column names: Name is a Reserved Word in Access/Jet, and while it may not cause troubles now, as your database
grows and your coding needs become more complex you'll eventually find problems with using Reserved words. Most
professional developers adopt a Nameing Convention (search on that term for more info than you bargained for) to get
around this; for example, Name is obviously a Text field, so you could change it to "sName" or "txtName" or "strName"
.... this not only avoids using reserved words, but it also gives you a clue as to what datatype you're dealing with.
While that is pretty obvious with a column like "Name", in many cases it's not so clear ...
The table look like this
Name Title Question1 Question 2 Question 3
John Manager 1 2 3
Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com