refer to a form which is not open

[exaccess]

Hello to All,
Is it possible to assign a query to the recordsource of a form dynamically? I assume the answer is yes. Can this action be done when the form is not open? If yes what is the correct syntax to use when the code is in a normal module as a public sub? I tried many possibilities and could not get it to work.

Dim ForNm as string
Dim Qry as string
ForNm = "PersonalFm"
Forms(ForNm).Recordsource = Qry

Forms![PersonalFm].Recordsource = Qry

[Forms]![PersonalFm].Form.Recordsource = Qry

Please advise. Many thanks.

 

[Beetle]

Can this action be done when the form is not open?

No.

If the record source of the form is a saved query, then you could use querydefs to update the SQL of the query. Not sure whether or not that would be a solution for whatever it is you're trying to do.

 

[exaccess]

No.

If the record source of the form is a saved query, then you could use querydefs to update the SQL of the query. Not sure whether or not that would be a solution for whatever it is you're trying to do.

Thanks for the reply. I am trying to synchronize the rows of a continuous form with a single form. The continuous form displays the results of a query. Each row corresponds to a record. User clicks on a row and a single form is opened showing the details of the record. The two forms must run on the same recordsource. The single form is used by many other continuous forms to display details of results. Thus the single form is general purpose and the recordsource has to be dynamic. The problem is if I assign the recordsource after the form is opened then the query is executed twice and this is very confusing to the user.

 

[JHB]

The problem is if I assign the recordsource after the form is opened then the query is executed twice and this is very confusing to the user.

How and where do you assign the recordsource, (show you code)?
Couldn't you open the form hidden, assign the recordsource and then make the form visible?
Code like below:

  DoCmd.OpenForm "YourForm", , , , , acHidden
  'Do something here
  Forms![YourForm].Visible = True

 

[exaccess]

Here is the code. It is executed in a Standard module.

DoCmd.OpenForm Forname, , , , , acHidden
        Forms(Forname).Visible = False
        Set Frsc = Forms(Forname).RecordsetClone
        'Set Prsc = Forms(Parname).RecordsetClone
        Forms(Forname).RecordSource = Cqry.SQL
        Forms(Forname).Visible = True
        DoCmd.OpenForm Forname

Making it hidden and invisible is a good idea, but it does not help because the query is interaktive and displays a question which can not be suppressed.

 

[missinglinq]

...I am trying to synchronize the rows of a continuous form with a single form. The continuous form displays the results of a query. Each row corresponds to a record. User clicks on a row and a single form is opened showing the details of the record. The two forms must run on the same recordsource.

This description sounds like a Datasheet View Form rather than a Continuous View Form!

If you're running Access 2007 or later, you could go to Properties - Format and under Default View select Split Form, and I believe that it would give you what you need!

Linq ;0)>

 

[exaccess]

This description sounds like a Datasheet View Form rather than a Continuous View Form!

Well it is similar to datasheet view but in reality it is a continuous form.

If you're running Access 2007 or later, you could go to Properties - Format and under Default View select Split Form, and I believe that it would give you what you need!
Linq ;0)>

Unfortunately Iam using Access 2000. This is because of compatibility issues. I am converting databases written using very old software and most of our macines are running 2000.

 

[Pat Hartman]

Having one form open a second to a specific record is simple and can be done directly with the DoCmd.OpenForm method. Just use the Where argument to sync the forms.

An alternative method is to have the query RecordSource of the second form use criteria from the first form to select a record:

Select ....
From ...
Where SomeField = Forms!yourfirstform!thesyncfield;

 

[JHB]

Making it hidden and invisible is a good idea, but it does not help because the query is interaktive and displays a question which can not be suppressed.

Can you elaborate that, which question is displays and can not be suppressed?
Show the Cqry.SQL, (SQL-string).
I just set up some sample code, it work as expected, (open a form called "frmADDCUSTOMER" as hidden, set the RecordSource to the "tblCUSTOMERS", and view the form).

  DoCmd.OpenForm "frmADDCUSTOMER", , , , , acHidden
  Forms!frmADDCUSTOMER.RecordSource = "tblCUSTOMERS"
  Forms![frmADDCUSTOMER].Visible = True

 

[exaccess]

Hartman and JHB thanks a lot for the examples. I'll try them and get back to you ASAP.

 

[exaccess]

Hello Beetle
I have tested the proposal. Yes it is possible to change the recordsource without requerying, but this change only applies to the name and selected fields of the query and not to the parameters of the where clause. Result is the executed query has 31 records and the second one 1040. I need to copy the recordsource query after execution. This is what I have been saying all along.

Anyway as far as I can see there is no way of grabbing the SQL of the query with its input values. I'll have to look for another solution unless anyone has other ideas. Thanks to all who contributed.

 

[exaccess]

Having one form open a second to a specific record is simple and can be done directly with the DoCmd.OpenForm method. Just use the Where argument to sync the forms.

An alternative method is to have the query RecordSource of the second form use criteria from the first form to select a record:

Select ....
From ...
Where SomeField = Forms!yourfirstform!thesyncfield;

I have just seen your post. In fact I am working on something similar. Do you have any examples?

 

[Beetle]

Yes it is possible to change the recordsource without requerying

That's not what I was suggesting, and that's not what the second command button on the example db does.It does not change the record source of the form. It rewrites the SQL of the existing record source before opening the form.

but this change only applies to the name and selected fields of the query and not to the parameters of the where clause.

Wrong. You're rewritng the SQL so you can include any criteria necessary, even user prompts, which you stated previously you need for the query -

the query is interaktive and displays a question which can not be suppressed.

Anything that your current query (which is SQL) does can be rewritten in code with different form references, different prompts, whatever. However, like I said before (in a post which is now gone due to the forum problems today) this will require that your Single form (the one you want to open from multiple other Continuous forms) have its own record source query.

What Pat is suggesting is the way this type of thing is normally handled. However, you said previously that you seem to have an unusual situation where you want this one instance of your single form to be opened from multiple other Continuous forms. Therefore, it seems to me that you will need to dynamically rewrite form references and/or criteria prompts on the fly. That was why I recommended querydefs.

I suppose it's possible I have completely misunderstood what you're trying to do. At any rate, good luck with your project.

 

[Pat Hartman]

When you need to open forms or reports from multiple forms, the best solution is to use the Where argument if that is possible. On the occassions where it isn't, I create a hidden form where I store the parameters. Then the form/report's RecordSource query can get its criteria from a common hidden form. Or, with newer versions of Access, you can use TempVars.

 

[exaccess]

Well I developed another solution. Instead of having the query prompt for a field value I placed a form between the menu and form1. This one captures the user input, builds up the sql, creates the query as recordsource for form1 and form2. This solves the case. I have at the moment two glitches. One is with single form2 when I reach the last record I get a blank form. This needs to be stopped. Second is I want to stop moving to next record with mouse wheel. Any help will be appreciated.