Command Button - to pull all

[Chantel33]

I would like to create a form to enter required data to produce a
questionnaire. In this form I would like for the user to be able to click or
select a button and that button pull all
Questions related to that particular condition. I have a joined table in a
query where the have relationship but I am only able to pull one at a time.
Is it possible to select the condition once by a click of a button and pull
all questions onto the report?

 

[Tom van Stiphout]

On Wed, 14 Apr 2010 11:16:01 -0700, Chantel33

I read your question several times but I am at a loss what you are
trying to do. Can you try again?
Speculating, I think you have a table with questions for several
conditions (maybe medical conditions). At minimum this would require:
tblConditions
ConditionID PK Autonumber
ConditionName text50 required UniqueIndex

tblQuestions
QuestionID PK Autonumber
ConditionID FK long integer required
QuestionText text255 required

and enforced referential integrity between these two tables.

I lost you on your "joined table...relationship..."
I also have trouble matching your first sentence which talks about the
desire to create a form, with the last one which talks about a report.

-Tom.
Microsoft Access MVP

 

[Chantel33]

Tom

I apologize i am still new and sometime find it hard to present my question
correctly on the board but here is a second try.

I do have the two tables mentioned:

tbl Questions
Question ID
Question Text
i have added the field Condition ID to the table

tbl Conditions
Condition name
condition ID

when i mentioned "joined table" i should have state referential integrity


I need a report – to produce the draft of the questionnaire

On my form I have a combo box with the conditions listed
Once I select the condition and it stores in the table, I would like to
produce
A Report -“Questionnaire†with all question associated with that one
condition.

Example: So if I select Asthma and there are 4 questions related to Asthma,
I want all 4 questions to populate on my Report - “Questionnaireâ€

 

[Tom van Stiphout]

On Thu, 15 Apr 2010 09:41:02 -0700, Chantel33

Thank you for the further explanation.
I understand you have a Criteria form where the user selects what
condition to report on. You can certainly store the condition to a
table but there is no need. In most applications we're not that
interested in what criteria the user selected, but your app could be
an exception.
Anyway, when the user then hits the Preview button, you run your
report. Let's say that initially you created this report to show all
questions, so the RecordSource is simply the name of the Questions
table. Indeed the report shows all records in that table.
Now you want to limit to just the selected condition. Looking at the
code behind your Preview button we see something like:
DoCmd.OpenReport "myReport", acViewPreview
Your job is to add another argument to this line:
dim strWhereCondition as string
strWhereCondition = "ConditionID=" & Me.myCombobox
DoCmd.OpenReport "myReport", acViewPreview, , strWhereCondition
(of course you need to replace myObjectNames with yours)

This code assumes the traditional 2-column combobox with hidden
ConditionID and visible ConditionName.

-Tom.
Microsoft Access MVP

 

[Chantel33]

Tom

I have done the following but it is still pulling all records from the table
what did i do wrong:

Private Sub preview_the_report_Click()
On Error GoTo Err_preview_the_report_Click

Dim stDocName As String
Dim strWhereCondition As String

stDocName = "Query1"
strWhereCondition = Me.Questionnaire

DoCmd.OpenReport stDocName, acViewPreview, , strWhereCondition

Exit_preview_the_report_Click:
Exit Sub

Err_preview_the_report_Click:
MsgBox Err.Description
Resume Exit_preview_the_report_Click

End Sub

 

[John W. Vinson]

Tom

I have done the following but it is still pulling all records from the table
what did i do wrong:

Private Sub preview_the_report_Click()
On Error GoTo Err_preview_the_report_Click

Dim stDocName As String
Dim strWhereCondition As String

stDocName = "Query1"
strWhereCondition = Me.Questionnaire

DoCmd.OpenReport stDocName, acViewPreview, , strWhereCondition

Exit_preview_the_report_Click:
Exit Sub

Err_preview_the_report_Click:
MsgBox Err.Description
Resume Exit_preview_the_report_Click

End Sub

You're assigning the value of the textbox or control named Questionnaire to
strWhereCondition.

That's not going to work: the WhereCondition parameter should be a text string
containing a valid SQL WHERE clause, without the word WHERE.

As written, this will open a Report named "Query1" (a very odd name for a
report). I don't know what the value of QUestionnaire is but it's probably not
a valid string, which would resemble something like

"[QuestionID] = 312"