Opening new form for query output

texassynergy

Registered User.
Local time
Today, 06:05
Joined
Nov 1, 2013
Messages
11
I am relearning Access again and need some assistance. Using 2010.

Would like to open a new form as a popup to display the results of a query. Trying to avoid eliminating objects in my DB, so not wanting to create Query Objects, but just creating the SQL on the fly.

The purpose of opening the popup form is to display the results of the Query based on a Button selection that the user chooses. Not sure if I can pass the Query from Form to Form, or if the called Form should run the Query. The Query output is a single record which is a Memo field, which is why I want to open the new form to display the result. The Memo text can be a page long or more.

Please let me know if I am on the right track and any suggestions you may have. I am not getting any errors and can open the form. The problem is that I am not getting the Query results displayed on the new form. The code below is from my first form that has the buttons. The popup form is Results_frm.

Code:
Private Sub InScope_bt_Click()

Set dbs = CurrentDb

strSQL = "SELECT In_Scope FROM Project_Scope_Deliverables"
Debug.Print strSQL

ResultType = "I"

DoCmd.OpenForm "Results_frm"
Forms!Results_frm.RecordSource = strSQL
 
End Sub
Thanks for your assistance.
 
Are Pop Up and Modal set to Yes for the pop up form? If so, it's possible that code isn't continuing to run once it opens. You can put a message box after the OpenForm to test.
 
Paul, thanks for the suggestions. Popup and Modal were initially set to Yes. I set them to No and it still did not allow the results to display. I also created a message box after the opening of the form and I do get the form to open and the msgbox to display. I then reset Popup and Modal back to Yes and tried with the msgbox. Still get the msgbox. Please see code below. Let me know if you have any other suggestions.

Code:
Private Sub InScope_bt_Click()
Dim MResponse As Integer

Set dbs = CurrentDb

strSQL = "SELECT In_Scope FROM Project_Scope_Deliverables"
Debug.Print strSQL

ResultType = "I"

DoCmd.OpenForm "Results_frm"

MResponse = MsgBox("Is this opening after the form opens", vbYesNo, "Continue")
Forms!Results_frm.RecordSource = strSQL
 
End Sub
 
Paul,

Sorry. Should have mentioned. I have ran the query in the SQL query tool and it does return the row as requested.

As far as the db, I would have to delete all the records in most of my tables as it has client specific details regarding our project. If that will work for you, then I can do so.
 
We certainly don't want any private data posted. I just don't see the problem, as setting the record source property should populate the form.
 
Paul,

Attached is a scrubbed version of the DB. Most of the tables had to be truncated. However, it shouldn't affect the behavior of what I am trying to do.

If you will go to the Project_Scope_Deliverables form and open that form. Then click on the In Scope buttons, you should see the behavior. I did delete the memo that I had in there, however I created something similar and pasted it into the fields. Also, note that I am trying to pass a value from the Deliverables form to a Results form. This is to just to set up the title of the opened form and to be used in an If statement if need be. I was trying to think ahead and see if I could run the query inside the opened form instead of passing it from the calling form. However, I noticed that the IF statement is not being evaluated. Probably because of the value being passed.

It seems like it should all be basic stuff. But can't get it to work. Thanks for your assistance.
 

Attachments

The textbox is unbound.
 
Paul,

Ok. Great find. I appreciate that. So I can hardcode the binding in the properties of the textbox, but that doesn't allow me the flexibility to choose an option from my primary form. Btw, I did do that and the results displayed as you suggested.

So now I want to make this happen with the button code. I have added this line to it and now I get #Name? as my result in the textbox. I have tried several things like removing the hardcoded binding. Using quotes around the strSQL, etc. Any ideas why this doesn't work? No errors. Still get the opened form.

Code:
Forms!Results_frm.Form_Output.ControlSource = strSQL
 
One solution would be to bind the textbox to a fixed name, lets say "DataField" and then add an alias to your SQL:

strSQL = "SELECT MDM_Deliverables AS DataField FROM Project_Scope_Deliverables"

That way every SQL string returns the same field name.
 
Thanks Paul. That definitely worked. Cleaned things up a bit and now I have all three buttons working with the correct output. I appreciate you taking the time to help me understand.

I like that last trick. Using a constant and supplying that in an AS clause. That is real nifty.
 

Users who are viewing this thread

Back
Top Bottom