Someone Respond please!!

[accessjpm]

I have a wonderful piece of SQL which does exactly what I want it to:

Dim sqlstr As String


sqlstr = "SELECT * FROM FromLegal LEFT JOIN FromCDG ON [FromLegal].[IDNumber]= [FromCDG].[IDNumber]" & _
" WHERE FromLegal.CounterpartyName Like """ & "*" & Me.CPNAME & "*" & """;"



DoCmd.RunSQL (sqlstr)


Except ... I run this from a button on a form. The idea is to pull back all of the records that meet the criteria in a textbox (Me.CPNAME). However, when I try to run this, I get an error asking me to do something with the result of this SQL, i.e. create a table, add it to a table etc. I want the data to appear just as a temporary and updatable set of records, because the user would use this code to lookup records by counterparty name and update them. That update would need to be saved back into the two tables queried (FromLegal, FromCDG). This is my first real adventure in Access, so be very detailed please.

Thanks ahead of time.

 

[WayneRyan]

jpm,

The DoCmd.RunSQL doesn't return anything. How is Access supposed
to know what to do with the records it finds? How would it know
to put them in a textbox, or even WHICH textbox?

Just make a new, popup, continuous form to display the data. Set
its recordsource to your query (change your SQL to a stored query).
The new query can use Forms![YourForm]![CPName] as the criteria for
the name field.

Wayne

 

[accessjpm]

Thank you, but a few questions-
How do I store the query?
How do I tell access which place on the form to put the data? Can I use the existing form I have which displays all of those fields?