Parameter Query help needed

[kbrown]

I have been struggling with understanding paramater queries for awhile now. I am currently working on the following code. There are three paramters for the query. The first two are pulled from an open form and the third is supplied based on the value of me.questionID. I have this code running on Form_Current.

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim qdf As dao.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs!qrysbfrmResponse
qdf.Parameters![ScreenDateParm] = [Forms]![frmEnterNS]![ScreenDateID]
qdf.Parameters![ClientIDParm] = [Forms]![frmEnterNS]![ClientID]
Select Case Me.QuestionID
    Case 31, 6
         Me.sbfrmResponse.Visible = True
         qdf.Parameters![Response] = 63
         Set rst = qdf.OpenRecordset
    Case Else
        Me.sbfrmResponse.Visible = False
End Select

What happens is that as soon as the form is opening (I can't even see it yet on my screen), I am prompted to enter all three variables. What I want to happen is that the variables are set by the code.

I can set up the code with individual queries and get it to work, but that seems like a lot of repetitive code. I would really like to get to the point where I understand parameter queries and how to use visual basic to pass the information to the query. Based on the posts in this forum, I think I am at least headed in the right direction with my code. Do you see any improvements I can make that might get this working properly?
Thank you!

 

[Adrianna]

qdf.Parameters![ScreenDateParm] = [Forms]![frmEnterNS]![ScreenDateID]

Does your Query actually say [ScreenDateParm] in the Criteria or WHERE statement? Meaning that even if your form is filled out, the query does not execute properly?

Your Query Criteria or WHERE statement should read [Forms]![frmEnterNS]![ScreenDateID] and therefore your declaration for the parameter should be qdf([Forms]![frmEnterNS]![ScreenDateID]) = [Forms]![frmEnterNS]![ScreenDateID]

P.S. Don't forget the " " if any of your parameters represent a sting.

Hope this helps!

 

[kbrown]

Thank you, that helped with those two variables. I now have the following code. I am still getting [Response] popping up. The code below is supposed to be supplying the value for [Response], but it isn't. Do you know how to change that part? Thanks

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs!qrysbfrmResponse
qdf.Parameters("[Forms]![frmEnterNS]![ClientID]") = [Forms]![frmEnterNS]![ClientID]
qdf.Parameters("[Forms]![frmEnterNS]![ScreenDateID]") = [Forms]![frmEnterNS]![ScreenDateID]

Select Case Me.QuestionID
    Case 31, 6
         Me.sbfrmResponse.Visible = True
         qdf.Parameters![Response] = 63
         Set rst = qdf.OpenRecordset
    Case Else
        Me.sbfrmResponse.Visible = False
        qdf.Parameters![Response] = 3
End Select

The query does run properly when I supply the Response value, however it is not passing the value from code and displaying the results on the form.
Thank you

I just rechecked the process and when I get to the correct question id (31 in this case) and enter 63 into the [Response] pop-up it does run the query and load the information into the subform correctly. So I guess I am just missing something that is causing the variable to not be passed to the query automatically which is making it pop-up each time you advance to a new question.

 

[Adrianna]

Are you saying that it is working only for the current record? Are you trying to loop through something because I don't see anything being looped through. Maybe what you need to do is declare the two parameters and then use a recordset to tests your CASE conditions for determining the "response" field in the recordset.

If I'm off, please explain your process.

 

[kbrown]

Thank you for your help with this. I will try to explain what I am trying to do and maybe that will help! I have a nutrition screen that is being filled out. There is one form that is used to enter the responses. On this form are two subforms that are visible all the time. One displays the question and the second is where you enter the response. They are both in single form view. Some of the questions can have multiple responses. The data entry person has requested a way to view the responses already entered. So what I have done is create a third subform that is visible when you get to those questions with multiple responses. The control source for this subform is a query that runs and is updated each time you add another response. The query captures all the responses selected.

The query has three variables. Two are pulled off a current form and these are working well, thanks to your help. The third is a variable, Response, that I am trying to assign a value using the code below. I do this on Form_Current, so each question is evaluated to see if it is the correct question and when it is, the subform is made visible and the parameter value is set.

Select Case Me.QuestionID
    Case 31, 6
         Me.sbfrmResponse.Visible = True
         qdf.Parameters![Response] = 63
         Set rst = qdf.OpenRecordset
    Case Else
        Me.sbfrmResponse.Visible = False
        qdf.Parameters![Response] = 63
End Select

What is actually happening is when the form is opening, a parameter box opens requesting you input a value for Response. Then with each new question it pops up again and again. When QuestionID = 31 and you enter the value of 63 in the parameter box, the subform opens and shows the list of responses when you add multiple responses.

I am not sure why the parameter isn't being passed to the query. I've tried to set up a query with a parameter like this before and been unsuccessful. I obviously have something wrong somewhere, but I am not sure where it is. I have been teaching myself visual basic over the past year from books and this forum, so I am sure there huge gaps in my knowledge. I really appreciate your time in looking this over. I hope my more detailed explanation of what I am trying to do makes sense!
Thanks

 

[kbrown]

I simplified the code to see if the Select Case was causing the problem and I am still not able to get the variable [Response] passed into the query. I also realized that the variable is a long integer, so I changed the declaration to match. Still no luck!

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Dim Response As Long

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrysbfrmResponse")
qdf.Parameters("[Forms]![frmEnterNS]![ClientID]") = [Forms]![frmEnterNS]![ClientID]
qdf.Parameters("[Forms]![frmEnterNS]![ScreenDateID]") = [Forms]![frmEnterNS]![ScreenDateID]
qdf.Parameters("[Response]") = 63
Set rst = qdf.OpenRecordset()

What I would like to be able to do is define [Response] based on which question is currently being answered, but I cannot seem to get the value to be passed to the query at all! I have tried other variations of defining the parameter

qdf("Response") = 63

or

qdf![Response]=63

I keep coming up with the same problem. When I open the form, a criteria pop-up appears for each question asking for input for [Response].

Thanks for any and all suggestions!

 

[Adrianna]

Stupid question from me... Is [Response] the Criteria in your Query for a specific field? Or are you trying to pass 63 back to the Response field?

If Response is a local variable in your Sub, then it's not going to pass anything into your query...it's going to hold the variable in the code until you tell it to feed back to a field in the query.
To pass a paramater back to the query, the parameter has to be requested.

If this seem strange, just place a "Response" field in your form and set the visible = False. Then run your if statement, update you query criteria to [Forms]![frmEnterNS]![Response] and then you can declare the parameters just like the other fields!

 

[kbrown]

Definitely not a stupid question! I am positive that I am missing something and it's probably something really obvious.

Is [Response] the Criteria in your Query for a specific field? Or are you trying to pass 63 back to the Response field?

[Response] is the criteria in my query for a specific field (ScreenQuestionID). I am trying to pass 63 to [Response]. From what you are saying, maybe I cannot do it the way I've been trying to do it. I am attaching a screen shot of my query design. Maybe that will help illuminate where I am going wrong.

Are you saying that I should have an unbound field on my form called Response and set the value of that field via VBA. Then I should use [Forms]![frmEnterNS]![Response] as the criteria in my query?

 

[kbrown]

Yeah, it works!!! I set up an unbound field on my form (frmScreenQuestions) and used the following code to set the unbound field's value. Then I used the reference to that field as the parameter for the query (like the other two parameters) and it worked! Now I will work on putting my select clause back in.

Thank you so much for your help!

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef


Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrysbfrmResponse")
Me.Response = 63

qdf.Parameters("[Forms]![frmEnterNS]![ClientID]") = [Forms]![frmEnterNS]![ClientID]
qdf.Parameters("[Forms]![frmEnterNS]![ScreenDateID]") = [Forms]![frmEnterNS]![ScreenDateID]
qdf.Parameters("[Forms]![frmScreenQuestions]![Response]") = [Forms]![frmScreenQuestions]![Response]
Set rst = qdf.OpenRecordset()