Problem passing query results to a form

[AdrianK]

Hi All,


I'm a newcomer to VBA, and i'm having a problem, so any help would be greatly appreciated.


I have a query:


SELECT *
FROM Data
WHERE (Data.ClientRef=Forms!MainForm!Ref.Value);


Which runs fine, returning the single set of records matching the reference entered in the text box, however I want to use this information to populate a form I have created (Update) which has source of the "Data" table.

I currently have the following code:



Private Sub RefUpdate_Click()

'Check for value in Ref field, if none start popup box
If IsNull(Ref.Value) Then
MsgBox "Please Input Reference.", vbExclamation
Exit Sub
End If

' Open Form
DoCmd.OpenForm "UpdateFrm", acNormal, "", "", , acNormal

' Go to Searched value
DoCmd.GoToRecord acDataQuery, "UpdateReference", acGoTo, 1

' Close Form
DoCmd.Close acForm, "MainForm"

End Sub



However, this is causing an error:


Run-time error '2489'

The object 'UpdateReference' isn't open.


when I try to run it. I have tried openning the query before the GoToRecord command using DoCmd.OpenQuery. This gets rid of the error, but the query itself opens and the form is not at the correct record.

I wish to populate the form with the results of various equality queries (Eg. where client=x, or Status=y, or Contact=z where x,y or z are all user submitted values in the Forms!MainForm!Ref field), these queries will all return all details from 'Data', so exactly the same fields from the same table each time. Only one Query will be active within the form at any one time.

In summary, the queries are all different but return the same fields in the same order from the same table, but I wish to then display the results in a single form, although only one query will be in use at a time, and to re-populate the form the user will return to the search form to pick a new search.

If anyone could help that would be greatly appreciated.


Many thanks,


Adrian

[Oorang]

I'm confused, are you trying to pass the SQL, the recordset, or a string value to the new form?

[AdrianK]

The user will click the button relevant to the query they wish to run, so Ideally I would like to change the form recordset each time they make a new selection.

Unless of course there is a better way to populate the form with the results.

Cheers.

[OBP]

Adrian, see this post.
http://vbaexpress.com/forum/showthread.php?t=19418