Someone Respond please!! (1 Viewer)

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.

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

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?

How do I store the query?

Click to expand...

- Click the new button on the queries tab of the database window. Choose the tables and/or queries you want to select data from from the combo. If you have selected more than one table/query you will need to draw the appropriate join lines if Access hasn't automatically drawn them for you. Select the columns you want for the query. Add any criteria. Save the query.

How do I tell access which place on the form to put the data?

Click to expand...

Click the new button on the forms tab of the database window. Choose the form wizard and then select the query that you just saved from the combo. Make your choices for the form and the wizard will build it. When the wizard is done, open the form in design view and make any modifications that you want. I normally don't like the wizard's layout so I rearrange the controls to my liking. Don't forget to alter the tab order if you resequence the fields.

If you want to make the form completely from scratch, choose design view rather than form wizard but still select your query from the combo. You now have a completly blank form that is bound to a RecordSource. You can open the field list and drag fields from the field list onto the form or you can select controls from the toolbox and add objects that way. If you add controls that you want to display data from the recordSource, you'll need to bind them. Open the properties dialog for a control and on the Data tab, select the field you want from the dropdown in the RecordSource property.

Can I use the existing form I have which displays all of those fields?

Click to expand...

If you want to change the recordsource of a form on the fly, you can do so by setting the form's RecordSource equal to a query name or SQL string:
Me.RecordSource = SomeQueryName