Write Form value into Criteria of Query 1

[JeffNolan1900]

I am trying to store the value of a form text box inside a query criteria, but I do not want to load the value of the textbox when the query opens, I want the value to be written in to the query criteria, thru vba, permanatly. As in stored as the value, so when the form is closed, the value is still stored inside the query criteria box.

What code would I use to send a value to the query criteria box?

Ex:
Form Text box: Jim
(user click submit button)
value 'Like "*" & Jim & "*"' is written to the query criteria for user name field.
the query is then opened using the vba cmd: DoCmd.OpenQuery

The piece in italics is what I am having trouble with

Any ideas?

 

[TheAceMan1]

how are ya JeffNolan1900 . . .

Have a look at [blue]QueryDefs[/blue] . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[GingerR]

You'd have to open the query in design mode, change the query, then save it. Or just create a new query and save it.

Look up QUERYDEF.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[JeffNolan1900]

Thanks guys.
Once again a key work from you and I am off to learning something new again...

http://www.databasedev.co.uk/query_using_listbox.html

Jeff

 

[JeffNolan1900]

Here is the code I have:

Private Sub CmbGenerate_Click()
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim Rsdb As DAO.Recordset
    Dim i As Integer
    Dim strWhere As String
    Dim strIN As String
    Dim varItem As Variant
    
    Set MyDB = CurrentDb()
        
    'Build the IN string by looping through the listbox
    For i = 0 To lstENTITYNAME.ListCount - 1
        If lstENTITYNAME.Selected(i) Then
            strIN = strIN & "'" & lstENTITYNAME.Column(0, i) & "',"
        End If
     Next i
     
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = "in (" & Left(strIN, Len(strIN) - 1) & ")"
    
    Set qdef = MyDB.QueryDefs![MainQuery]
    qdef.Parameters![ENTITY SETS]![ENTITY SET NAME] = strWhere
    
    DoCmd.OpenQuery "MainQuery", acViewNormal

    
End Sub

The problem I am having is with these 2 lines.
Set qdef = MyDB.QueryDefs![MainQuery]
qdef.Parameters![ENTITY SETS]![ENTITY SET NAME] = strWhere

I am trying to get the info from strWhere to be sent to a query I have called "MainQuery" in the criteria field ENTITY SET NAME, which is from the table ENTITY SETS

Any help would be great.
Thanks so much.

 

[JeffNolan1900]

Ok. This problem has been solved.

Here is the updated code:

Private Sub CmbGenerate_Click()
Dim qryT As DAO.QueryDef   'for this you have to include "Microsoft DAO 3.6 Object Library"
                             '(Tools, References, find, select and OK).
Dim duz
Dim SQLR As String

Set qryT = Nothing
DoCmd.Close acQuery, "GeoQuery", acSaveYes

On Error GoTo Err_H

DoCmd.SetWarnings False

DoCmd.CopyObject , "GeoQuery", acQuery, "GeoQueryTEMP"
DoCmd.SetWarnings True
Set qryT = CurrentDb.QueryDefs("GeoQuery")
duz = Len(qryT.SQL)

'qryT.SQL = "SELECT [ENTITY SETS].[ENTITY SET NAME] FROM [ENTITY SETS]"
qryT.SQL = Left$(qryT.SQL, (duz - 3))

SQLR = " WHERE ((([ENTITY SETS].[ENTITY SET NAME]) In("""
  If Me!lstENTITYNAME.ItemsSelected.Count = 0 Then GoTo X5
  For Each varItm In Me!lstENTITYNAME.ItemsSelected
    SQLR = SQLR & Forms!frmXMLcreator!lstENTITYNAME.Column(0, varItm) & """"
    SQLR = SQLR & ","""
  Next varItm
duz = Len(SQLR)
SQLR = Left$(SQLR, (duz - 2))
SQLR = SQLR & ")))"
SQLR = SQLR & " ORDER BY [ENTITY SETS].[ENTITY SET NAME];"
qryT.SQL = qryT.SQL & SQLR

X5:
   Set qryT = Nothing
 'If you want, you can run a "Query2" instead of "Report1"
 '********************************************************
   DoCmd.OpenQuery "BentleyGeoQuery", acViewNormal
   'DoCmd.OpenReport "Report1", acViewPreview
  ' DoCmd.OpenQuery "Query2"
   Exit Sub
Err_H:
   MsgBox Err.Description
   Exit Sub

End Sub

 

[TheAceMan1]

JeffNolan1900 . . .

Way ta go! How good it is for the soul when you solve it yourself . . . Bravo! and a Pinky!

See Ya! . . . . . .

Be sure to see thread181-473997