Place your code in a query, (as the query SQL) and then call that query Something like this:
Bare in mind that the command button will generate the following code for you!
Code:
Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Dim stDocName As String
stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command1_Click:
Exit Sub
Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click
End Sub
Private Sub Command0_Click()
Dim db As DAO.Database 'declare variable to hold db/workspace name
Dim qdf As DAO.QueryDef 'declare variable to hold query info
Dim rs As DAO.Recordset 'declare variable to reference query result table
Dim strSQL As String 'declare sql string variable
Dim queryName As String 'declare query name variable
Dim table As String 'declare table name variable
Set db = CurrentDb() 'Set name of database
queryName = "tempQuery" 'Set name of temporary query
table = "[Table Name]" 'Set name of the table
strSQL = "SELECT * FROM " & table & " WHERE [field name]='" & Me.input.Value & "';"
'If Not QueryExists(queryName) Then
'Set qdf = db.CreateQueryDef(queryName)
'Else
Set qdf = db.QueryDefs(queryName)
'End If
qdf.SQL = strSQL 'Match the SQL to the query name
Set rs = db.OpenRecordset(queryName)
[B]Me.[Where I want to set value on form] = rs![Table Field Name][/B]
End Sub
Must include references to DAO.
And put this in a module:
Code:
Public Function QueryExists(queryName As String) As Boolean
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
QueryExists = False
For Each qdf In db.QueryDefs
If qdf.Name = queryName Then
QueryExists = True
Exit Function
End If
Next qdf
End Function