Running query in Access VBA to populate field

[dridhas]

Hello Fellas,

i have an issue that i cant seem to get the answer to it, hopefully you will be able to help me out.

i have an Access Form that when entering data and clicking on a button it populates a Word Document.

there is one particular field that i cant populate and cant find a way to do it so far.

That field in particular needs to be filled out using a query to a table in the same DataBase.



in theory and in my mind, i need to run the query in VBA and then show the result of the query on the Word Document.


this is what i have and cant pass from there

Dim strSQL As String

strSQL = "SELECT table.field FROM table WHERE table.field = " & Me!User



Can you please help me out on showing me how to acomplish this?


Thanks in advance!!!

[ranman256]

If me.user is a string then you need quotes....

...Where tbl.field = '" & me.user & "'"

[dridhas]

me!user is populated on the form, i type that.

now, after changing that, how do i run the query and show the results in a variable string for then populate into the Word Document?

Regards!!!

[dridhas]

So, i was able to run the query and capture the result on a variable.

i used this code:
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("SELECT Nombre, Ubicacion, ApellidoPaterno, ApellidoMaterno FROM Usuario WHERE Usuario = '" & Me!Usuario & "'")


If Not RS.EOF Then
Name = RS.Fields("Nombre")
Location = RS.Fields("Ubicacion")
Last = RS.Fields("Apellido")
Else
Name = ""
Location = ""
Last = ""
End If

Nombre = Name
Ubicacion = Location
Apellido = Last

RS.Close

Set RS = Nothing
Set DB = Nothing


Now, ApellidoPaterno or ApellidoMaterno dont show.

i tried using just ApellidoPaterno or ApellidoMaterno and it does not show anything.

if i run the SQL it works fine and gets all the data

any advice?

[Missinglinq]

You can't do this, directly, using a Query! Populating a Control on a Form from a Table/Query that is not part of the Recordsource of the Form, requires using the DLookup() function, using the Where Clause (Criteria) that you would normally use in the SQL Statement of a Query.

From Access Help:

How to: Assign a Control a Value From a Table

You can use the DLookup function to display the value of a field that is not in the record source for your form or report. For example, suppose you have a form based on an Order Details table. The form displays the OrderID, ProductID, UnitPrice, Quantity, and Discount fields. However, the UnitPrice field is in another table: Products. You could use the DLookup function in a calculated control to display the UnitPrice on the same form when the user selects a product.

The following example populates the UnitPrice text box with the price of the product currently selected in the ProductID combo box.

Private Sub ProductID_AfterUpdate()

    ' Evaluate filter before it is passed to DLookup function.
     strFilter = "ProductID = " & Me!ProductID

    ' Look up product's unit price and assign it to the UnitPrice control.
     Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

End Sub

The DLookup function has three arguments. The first specifies the field you are looking up (UnitPrice); the second specifies the table (Products); and the third specifies which value to find (the value for the record where the ProductID is the same as the ProductID on the current record in the Orders subform).

Linq ;0)>