We help IT Professionals succeed at work.

getting a string (VBA)

Ernest Grogg
Ernest Grogg asked
on
105 Views
Last Modified: 2017-03-22
hello,

I want to get the return values for an unbound box (like a search results box):

the strWhere is where I want a query or if I can use a SQL code?

strWhere = "PersonID","qry","EntryPoint= & Form_myForm.txtGate"

(all values in the strwhere to be listed in the unbound box) BUT THE PIC DOESN'T SHOW IF I TRY TO USE A SQL:

 Capture.JPG
but using a query that calls for certain information:
something like:  
strWhere = "SELECT BackgroundCheck.PersonID, BackgroundCheck.EntryPoint, FROM BackgroundCheck, WHERE BackgroundCheck.Background Is Null AND BackgroundCheck.Entry= Wisconsin Gate"

Open in new window


Capture.JPG

Dim strWhere As String                  'The criteria string.
    
strWhere= 'don't know what to put here

strSQL = "SELECT MGNameAddressPhone.PersonID, MGNameAddressPhone.FName AS [First Name], MGNameAddressPhone.LName AS [Last Name], MGNameAddressPhone.LicenseNumber AS [Licence# (W/State)], FROM MGNameAddressPhone"

    strSQL = strSQL & " WHERE " & strWhere
    Me.SearchResults.RowSource = strSQL
    Me.SearchResults = Me.SearchResults.ItemData(1)
   

Open in new window


then when a user clicks the box the result they clicked on to open a new box:

A popup opens to a form that is looking at the unbound box
Private Sub SearchResults_Click()
DoCmd.OpenForm "frmmypopup"
End Sub

Open in new window

Comment
Watch Question

Ernest GroggSecurity Management InfoSec

Author

Commented:
Maybe Get rid of the

strWhere and just use one

strSQL

???

SELECT MGNameAddressPhone.PersonID, MGNameAddressPhone.FName AS [First Name], MGNameAddressPhone.LName AS [Last Name], MGNameAddressPhone.LicenseNumber AS [Licence# (W/State)], FROM MGNameAddressPhone

but have an

INNERJOIN

but don't know how I would write this:

SELECT MGNameAddressPhone.PersonID, MGNameAddressPhone.FName AS [First Name], MGNameAddressPhone.LName AS [Last Name], MGNameAddressPhone.LicenseNumber AS [Licence# (W/State)], BackgroundCheck.Background, BackgroundCheck.EntryPoint
FROM BackgroundCheck INNER JOIN MGNameAddressPhone ON BackgroundCheck.PersonID = MGNameAddressPhone.PersonID
WHERE (((BackgroundCheck.Background) Is Null) AND ((BackgroundCheck.EntryPoint)=[forms]![frmSearchAllNameAddressPhone].[Form]![txtGate]));

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
If I understand what you are trying do do, text box needs to be a subform.  You can recreate the Where clause if the criteria is variable.  Then put it in the RecordSource of the subform.

Me.MySubformcontrol.Form.RecordSource = strSQL
If the subform doesn't automatically refresh, add:
Me.MySubformcontrol.Form.Requery

Then in the click or double click event of one of the subform controls, you can use DoCmd.OpenForm to pass in a where argument and open the form to the selected record.
Ernest GroggSecurity Management InfoSec

Author

Commented:
Trying to do a queue system, but the field is unbound.  Trying to reuse a search results box that I have.  currently it uses fields for the where

strSQL = strSQL & " WHERE " & strWhere


but want to change it to this:

strSQL= SELECT MGNameAddressPhone.PersonID, MGNameAddressPhone.FName AS [First Name], MGNameAddressPhone.LName AS [Last Name], MGNameAddressPhone.LicenseNumber AS [Licence# (W/State)], BackgroundCheck.Background, BackgroundCheck.EntryPoint
FROM BackgroundCheck INNER JOIN MGNameAddressPhone ON BackgroundCheck.PersonID = MGNameAddressPhone.PersonID
WHERE (((BackgroundCheck.Background) Is Null) AND ((BackgroundCheck.EntryPoint)=[forms]![frmSearchAllNameAddressPhone].[Form]![txtGate]));

Open in new window



so thinking here...how to write that sql statement I took from the Query, and write it for VBA?
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Ernest GroggSecurity Management InfoSec

Author

Commented:
yeah, so what i will do is create that as a subform and use a list box to change the data.