If/Then/Else determine form record source? 1

[Turb]

Hello all,
I was wondering if anyone could help me out on this.

Is it possible to have an If/Then/Else statement determine which query a form should use as a record source?

I know I can use the If/Then/Else statement to open a different form with a different query as record source, but I really don't want to have to make duplicate forms as well as queries if I can avoid it.

Thanks in advance!

 

[jordanking]

hello,

rs = an open recordset

You can set the form's recordset like the following:

Set frmFormName.Form.Recordset = rs

You can set a subform from the main form like:

Set Me.frmFormName.Form.Recordset = rs

.....
I'd rather be surfing

 

[Turb]

jordanking,
Thank you for answering.

Would something like this work under the on open event?

Dim strMsg As String
    strMsg = "[b][COLOR=red]MyCustomQuestion?[/color][/b]"
    If MsgBox(strMsg, vbQuestion + vbYesNo, "[b][COLOR=red]MyQuestionWindowTitle?[/color][/b]") = vbYes Then
    Set frm[b][COLOR=red]MyFormName[/color][/b].Form.Recordset = [b][COLOR=red]MyQuery1[/color][/b]

    Else
    Set frm[b][COLOR=red]MyFormName[/color][/b].Form.Recordset = [b][COLOR=red]MyQuery2[/color][/b]

    End If

 

[jordanking]

no,

Dim strMsg As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim myQuery1 As String
Dim myQuery2 As String

    myQuery1 = "SELECT * FROM tableName"
    myQuery2 = "SELECT * FROM tableName WHERE myField = 2"
    
    strMsg = "MyCustomQuestion?"
    If MsgBox(strMsg, vbQuestion + vbYesNo, "MyQuestionWindowTitle?") = vbYes Then
        rs.Open myQuery1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        Set frmMyFormName.Form.Recordset = rs
    Else
        rs.Open myQuery2, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        Set frmMyFormName.Form.Recordset = rs
    End If

you have to set the recordset value of the form to an open recordset, not a query string. This example will open an ubdateable recordset, you can change it to read only if you like.

alos, if you are donig this from within the same form inthe open event,

use: Set Me.Recordset = rs

.....
I'd rather be surfing

 

[Turb]

jordanking,

you have to set the recordset value of the form to an open recordset, not a query string.

If I cannot 'assign' the record source of the form to an already existing query in the Db, I'm not sure if this will work for me.
The 2 queries I reference (MyQuery1 & MyQuery2) are each based on 6 separate tables and each ask for user input 4 times to filter data.
When data entry by the user is complete, the form data is saved using a SQL query that writes the data to a separate (7th) table.

Is there no way to make this work?

 

[jordanking]

i see,

I assume that user is asked for input becuase the queries have built in parameters.

if the query names represent an actual object in your DB then it should work. Try it, you might need to play wiht the code to make sure the query is refrenced properly. I am used to setting the query value with a string in the open event of a recordset.

On another note, you could create another form that opens, captures input from the user, builds the string and then sets the recordset value. THis is preffered because you can perform your own data integrity/business rules validation against what the user supplies, and ensure that values are not given to the query that will cause it to "break".

.....
I'd rather be surfing

 

[Turb]

Yes, each query has built in parameters.

I'll play with it more using your suggestions.
If I get it to work, I'll update this post.

Thank you!

 

[TheAceMan1]

How are ya Turb . . .

Despite the posts already made, [blue]you havn't stated the criteria for determining which query to use![/blue] Once this is determined . . . your [blue]If Then Else[/blue] should be a breeze! . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Turb]

Hi'ya Ace!

Some of the data in one of the tables is specific for a single series of units that need different input.
Query 'MyQuery2' would use 1 different table than 'MyQuery1'.
I was hoping to have the code take the user input from the 'On Open' message (vbYesNo) to assign which query to utilize as record source for the form.
The form has half a dozen bound text boxes that populate from the underlying query (the field names of both queries are identical) and many unbound text boxes for user input.
Once the user completes his/her input, a command button runs a macro that (using the aformentioned SQL query) saves the form data to a separate table, prints the form and reopens it to start the process anew.

 

[TheAceMan1]

Turb . . .

I don't see what the problem is. You post your message, user selects yes/no or whatever and you set the forms [blue]RecordSource[/blue] to the proper SQL.

Are you having problems setting this up?

If so, post the two SQL statements and the question you wold ask the user . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Turb]

Um...
I must be, because I thought I had posted...

I'm sorry, I guess I don't understand. What two SQL statements?
Are you suggesting I should I convert my existing queries into SQL before attempting this?

 

[jordanking]

were you able to use the code i posted, if not, try what Ace man said,

which is to not open a recordset but set the "RowSource" not "Recordset" value to the query.

me.rowsource = MyQuery1

does that work?

.....
I'd rather be surfing

 

[randy700]

How about...

If MsgBox(strMsg, vbQuestion + vbYesNo, "MyQuestionWindowTitle?") = vbYes Then
    DoCmd.OpenForm "frmMyFormName"
    Forms!frmMyFormName.RecordSource = MyQuery1
Else
    DoCmd.OpenForm "frmMyFormName"
    Forms!frmMyFormName.RecordSource = MyQuery2
End If

Randy

 

[Turb]

I could never get either jordanking's or Aceman's suggestions to work.
I kept getting 'variable not defined' errors (amoung others); I truely believe I just don't know enough about the Access Vb code to make the correct changes to the samples given. I'm sorry.

I did however get randy's code to work for me.
~ I removed the underlying record source for the form in the form's properties sheet, but left the bound text boxes bound as they were.
~ I copied randy's code to the 'On Open' event for the form.
~ I changed the code to reflect my form and existing query names and ended up with this:

Dim strMsg As String
    strMsg = "[b][COLOR=red yellow]MyQuestion[/color][/b]"
If MsgBox(strMsg, vbQuestion + vbYesNo, "[b][COLOR=red yellow]MyQuestionWindowTitle[/color][/b]") = vbYes Then
    DoCmd.OpenForm "[b][COLOR=red yellow]MyFormName[/color][/b]"
    Forms![b][COLOR=red yellow]MyFormName[/color][/b].RecordSource = "[b][COLOR=red yellow]MyQueryName[/color][/b]"
Else
    DoCmd.OpenForm "[b][COLOR=red yellow]MyFormName[/color][/b]"
    Forms![b][COLOR=red yellow]MyFormName[/color][/b].RecordSource = "[b][COLOR=red yellow]MyOtherQueryName[/color][/b]"
End If

This works, even though running a DoCmd.OpenForm on a form's 'On Open' event seems a little strange to me.
But Access doesn't error to a stop and the form doesn't open twice so I guess I'm good to go.

Thank you randy!

 

[PHV]

In the Open event procedure of MyFormName:
If MsgBox("MyQuestion", vbQuestion + vbYesNo, "MyQuestionWindowTitle") = vbYes Then
Me.RecordSource = "MyQueryName"
Else
Me.RecordSource = "MyOtherQueryName"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886