setting a forms recordsource

[philfer]

Hello,

Is there a way to set the RecordSource of a form and the ControlSource of a control on it before opening it.

I have tried using the Forms collection but the form must be open first.

I tried opening the form and then setting the two sources and refreshing it which worked but then the code kept continuing before anythign else could be done.

I want to set the RecordSource of a form and the ControlSource of a control on it, then open it and allow the user to read what is on it and press OK before the code moves on.

Help

Cheers
Phil

[Imdabaum]

How many recordsources are you going to loop through?

My initial suggestion would be to query MSysObjects and get a recordset of available recordsources, or create a table with the recordsources you might use

Here is an example of how to get all tables and queries stored in your database.
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*" And (MSysObjects.Name)
Not Like "~*") AND ((MSysObjects.Type)=1)) OR (((MSysObjects.Type)
4)) OR (((MSysObjects.Type)=5)) OR (((MSysObjects.Type)=6));

Or make a table with these recordsources and your possible SQL statements.
RecorSourceTable
ID
RecordName
RecordSource as Memo


Private Sub Form_Load()
Me.Recorsource = <source>
End Sub

Private Sub Form_Current()
Me.ctl.RecordSource = <source>
End Sub

Private Sub Button1_Click()
Me.RecordSource = <nextRecordSource>
End Sub

You might have to play around with this. I've never actually done it, but I would propose something like this until I get a chance to actually test it.

[Imdabaum]

An additional field to add if you choose to make a table might be fieldname that you want the control to attach to.

I might be completely wrong though.

[Imdabaum]

Of course then if I had re-read your post over again, I wouldn't have overcomplicated things so much.

It sounds like you're looping through the recordsource without waiting for the user to push the okay button.

How do you assign the form.recordsource and ctrl.recordsource the first time?

Maybe you could use a modular-level counter then in the OK_Click command check what counter you're at: Select Case counter
Case 1
form.recorsource = source
form.ctl.recordsource = source2 etc...

It doesn't offer you a whole lot of flexibility... but maybe it'll give you a start.

Sorry if this seems completely useless.
Cheers