Return query info to a form

[melaniews]

Here is my code so far. I would like this query info to return to the form instead of its own query window. This is an event in a class object.


Private Sub cmdFindStore_Click()

On Error GoTo Err_cmdFindStore_Click

Dim stDocName As String

stDocName = "qryStoreSearch"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdFindStore_Click:
Exit Sub

Err_cmdFindStore_Click:
MsgBox Err.Description
Resume Exit_cmdFindStore_Click

End Sub


Thanks in advance,
Melanie

 

[scking]

My understanding is that you want to use the qryStoreSearch as the source for the data display on a form. Paste the SQL from qryStoreSearch into the form RowSource property or, using the builder on the side of the property display, recreate the query, or embed the SQL in the code-behind-the-form and assign parameters from the controls on the form. Then you would assign the sql result to the RowSource property and perform a form requery (Me.Requery)



-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[melaniews]

I think I have the general idea of what you are saying in your post and I think that is the way that I had it first. It works that way -- using requery. However, I'm now expanding the form to include other searches.

The original query was a parameter query. I want to add another parameter query. So I took out the parameter criteria and just ran the query in the form. Ideally what I would like to do is open the form, then be able to click on either button to run the associated parameter query.

If I base the form on one of these queries, I can easily use the requery. Adding the second ruins the whole thing. Perhaps I am approaching this incorrectly??? Any guidance would be helpful. Also recommendations for any books, etc. to learn more VBA. I'm really having a hard time understanding it using help screens.

Melanie

 

[scking]

I occasionally use an option form with multiple checkboxes. Then, after the user selects a checkbox, I assign the value of the sql to the RowSource. Try something like this:

------------------------------------
optControl_AfterUpdate

Dim strSQL
Select Case optControl
Case 1
strSQL = "SELECT * FROM " & Me.Combo1 & ";"
Case 2
strSQL - "SELECT * FROM " & Me.Combo2 & ";"
Case 3
strSQL = "SELECT * FROM " & Me.Combo3 & ";"
End Select
Me.RowSource = strSQL
Me.Requery
----------------------------------

This is just an example to get you going.

-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[melaniews]

It will take me a little while to get this code worked out. I've got the concept and I think I can make it work. Thanks so much.

Any book recommendations for VBA? I see lots of stuff on VB, but I only use VBA and would like to learn it to the point of some competence in writing code (if that is possible)

Also a basic SQL book would be very helpful. I just copy stuff out of the SQL window in Access and understand a little bit more each time through trial and error (lots of error).

 

[melaniews]

I have one last issue on this and I think I've got it. What do I put for Me.Combo1
What does this refer to? I thought it was the name of the option items in the control but that didn't seem to work.

 

[scking]

It can be any control that has a value. I just did that to demonstrate. Since we are concatenating a string together to get a SQL string we are just using the value that is in the combo boxes. Actually the code should probably be triggered by the combo boxes themselves. So in the AfterUpdate they would call a subroutine (of the code above) that would set the RowSource for the form. Both of these would be Code-Behind-The-Form.

Sub Combo1_AfterUpdate
Call SetFormSQL
End Sub

Sub SetFormSQL

Dim strSQL

Select Case optControl
Case 1
strSQL = "SELECT * FROM " & Me.Combo1 & ";"
Case 2
strSQL - "SELECT * FROM " & Me.Combo2 & ";"
Case 3
strSQL = "SELECT * FROM " & Me.Combo3 & ";"
End Select
Me.RowSource = strSQL
Me.Requery

End Sub
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[melaniews]

I've been off for a while and I'm back and still not getting it.

The combo thing is confusing me. I'm not using combo boxes. I have a frame with three Toggle buttons which are the options in the frame. The toggle buttons have "got focus" and "lost focus" events that I think I need to address, but no after update.

Which part goes with the frames "after update" event and which part goes with the toggle buttons "got focus" event. And I don't know what goes in the "lost focus". Something to cancel the code I think.

TIA,
Melanie

 

[scking]

If you use the GotFocus and LostFocus events for the option buttons then you need to program every button you have. However, if you use the Frame_AfterUpdate event you do it once. The Frame has a 'Value' after update which is the value of the button selected.

optButton1.Value = 1
optButton2.Value = 2
optButton3.Value = 3 <==Selected

Frame.Value is 3 and the AfterUpdate event is triggered.

Why is the Combo box confusing you. If could come from the value of a TextBox, ComboBox, ListBox, or you name it. The only thing we get is a variable string value that is concatenated to the strSQL valiable to produce valid SQL.

WalkThrough

strSQL = &quot;SELECT * FROM &quot; & Me.Combo1 & &quot;;&quot;

Let's say the value selected in the Combo1 is a table named 'tblPerson'. Then the strSQL after you select it would be 'SELECT * FROM tblPerson;' which would correctly select all field values from tblPerson. Then, when you assign it as a RecordSource or ControlSource for a form or control and requery it will provide this. I do 'embedded sql' in the code very often. It is used to provide filtered data for forms, drive changes to other controls as a result of a selection from Combo1, and, the same concept is used to create filter strings.






-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[melaniews]

Here is my actual code that I have written based on the info I have understood so far. The reason I am confused is that I keep getting an error when I try to compile this code. The error is in the place where you put &quot;Combo1&quot; in your example. As I said, I am using a toggle button, so I used the name for each toggle button and set them equal to 1, 2, or 3. I get an error when it gets to the name of the button. It says &quot;sub or function not defined&quot; and highlights the name of the first toggle button (where you had combo1 in your example).


Sub SetFormSQL()

Dim strSQL

Select Case optControl
Case 1
strSQL = &quot;SELECT tblLocation.strLocationName1, tblLocation.strRetailLocatorAddress1, tblLocation.strRetailLocatorAddress2, tblLocation.strRetailLocatorCity, tblLocation.strRetailLocatorState, tblLocation.strRetailLocatorZip5, tblLocation.strRetailLocatorZip4, tblIndividual.strPhone, tblLocation.hypLocationURL, tblIndividual.strEmail, tblLocation.strMailingAddress1, tblLocation.strMailingAddress2, tblLocation.strMailingCity, tblLocation.strMailingState, tblLocation.strMailingzip5, tblLocation.strMailingZip4, tblLocation.lngLocationID, tblIndividual.strFax, tblIndividual.strFirstName, tblIndividual.strLastName, tblIndividual.strIndividualType, tblPurchase.FIREARMS, tblPurchase.AMMO, tblPurchase.ACCESSORIES, tblPurchase.FISHLINE, tblPurchase.TARGETS, tblPurchase.[PARTS REPAIR] FROM (tblLocation INNER JOIN tblIndividual ON tblLocation.lngLocationID = tblIndividual.lngLocationID) INNER JOIN tblPurchase ON tblLocation.lngLocationID = tblPurchase.lngLocationID&quot;
WHERE (((tblLocation.strRetailLocatorZip5) = [Enter Zip Code])) & Me.FindByZipCode = 1 & &quot;;&quot;
Case 2
strSQL -&quot;SELECT tblLocation.strLocationName1, tblLocation.strRetailLocatorAddress1, tblLocation.strRetailLocatorAddress2, tblLocation.strRetailLocatorCity, tblLocation.strRetailLocatorState, tblLocation.strRetailLocatorZip5, tblLocation.strRetailLocatorZip4, tblIndividual.strPhone, tblLocation.hypLocationURL, tblIndividual.strEmail, tblLocation.strMailingAddress1, tblLocation.strMailingAddress2, tblLocation.strMailingCity, tblLocation.strMailingState, tblLocation.strMailingzip5, tblLocation.strMailingZip4, tblLocation.lngLocationID, tblIndividual.strFax, tblIndividual.strFirstName, tblIndividual.strLastName, tblIndividual.strIndividualType, tblPurchase.FIREARMS, tblPurchase.AMMO, tblPurchase.ACCESSORIES, tblPurchase.FISHLINE, tblPurchase.TARGETS, tblPurchase.[PARTS REPAIR] FROM (tblLocation INNER JOIN tblIndividual ON tblLocation.lngLocationID = tblIndividual.lngLocationID) INNER JOIN tblPurchase ON tblLocation.lngLocationID = tblPurchase.lngLocationID&quot;
WHERE (((tblLocation.lngLocationID) = [Enter Location ID])) & Me.FindByLocationId = 2 & &quot;;&quot;
Case 3
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70 & Me.FindByBusinessName & &quot;;&quot;
End Select
Me.RowSource = strSQL
Me.Requery

End Sub


Also, you will note what I have in Case 3. I don't really think this is going to work but I haven't gotten that far in debugging this code to see what I need to do there. I'm just trying to call the find function off of the menu.

I really appreciate all your help.
Melanie