Form based on query

[kirkm]

Hi,

I'm using the Form Wizard at design time to make a Datasheet Form based on a query from sql.

Is it possible to use that same Form with different queries? I'm finding if the query changes even though the Form is still based on it, the Form keeps the specs from the original query. Can I update it anyhow - other than by making a new Form?

Thanks - kirk

 

[rapsr59]

If I understand your correctly, you want to change the Forms Record Source to a new different query. It this is correct you can change it at run time by changing the Record Source to Another Query.

Me.RecordSource = qryDifferent

Richard

 

[kirkm]

Hi Richard, Is that how's it's supposed to work?

I get no update of the field names (to those in the new query)
and everything filled with #Name?.

This seems to be fixed only by designing another Form (based on *that* query). In other words, one Form per query.


Kirk

 

[SOS]

Richard's advice is spot on, but if your fields in the other query are different than the others you will also need to programmatically set each of the controls' control sources. You can't just chang ethe query and expect it to work. Your controls are bound to certain fields and those fields need to exist in each query or else you need to change the control source of the field.

So, for example:

Me.YourTextBox1.ControlSource = "FieldNameHere"

 

[kirkm]

Thanks SOS, I'm slowly getting there.
I now open the Form with

DoCmd.OpenForm "Form1", acFormDS, , , , , "query2"

query2 is the query to run. The Form has 3 text boxes, set at design time - named txt0, txt1, txt3.

Then I have:-

--
Option Compare Database
Option Explicit
Private Sub Form_Load()
Me.RecordSource = OpenArgs
SetupForm
End Sub
Sub SetupForm()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset(Me.RecordSource)
Dim I
For I = 0 To rst.Fields.Count - 1
Forms!Form1("txt" & CStr(I)).ControlSource = rst(I).Name
Rem Forms!Form1("txt" & CStr(I)).Name = rst(I).Name
Next
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub

--

The remmed out line is, I think, the only problem remaining.
It brings up this error -

Run-time error '2136':
To set this property, open the form or report in Design view.

Is there a way around this ?

Thanks - Kirk

 

[rapsr59]

SOS is also spot on! Your fields must have the same name in both queries.

If the field names are different in query 2 there is a way to change the name of the fields in the second query to match the names in the first query during the design of the second query. You can use “Aliases” for the field names during the design stage of the second query.

Example:

Let’s assume that the names of the three fields in query 1 are: Field1, Field2 and Field3

Let’s also assume that the names of the three fields in query 2 are: Field100, Field200 and Field300

In query design set your fields in the query to:
Field1: Field100
Field2: Field200
Field3: Field300
And the query will return Fields Field100, Field200 and Field300 as Field1, Field2 and Field3 respectively.

When you look at the query in sql view it will look like this:
SELECT Table2.Field100 AS Field1, Table2.Field200 AS Field2, Table2.Field300 AS Field3
FROM Table2;

When you want to open the subject form, and want to use the second query, enter the below code when opening the form. (Perhaps in a command button click event)

[COLOR=black][FONT=Verdana] Dim stDocName As String[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] Dim strQueryName As String[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana] stDocName = "frmSubject"[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] strqryName = "qryTwo"[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana] DoCmd.OpenForm stDocName, , , , , , strQueryName 'Open Args [/FONT][/COLOR]

In the open event of the subject form that has the dual query capabilities enter the following code to change the Record Source

[COLOR=black][FONT=Verdana]Private Sub Form_Open(Cancel As Integer)[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  If Len(Me.OpenArgs) > 0 Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]     Me.RecordSource = Me.OpenArgs[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  End If[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]

I hope the above information will help you with your programming needs.

Richard

 

[kirkm]

Thanks Richard. Yes it all helps. I have it working nicely now.
Cheers - Kirk

 

[rapsr59]

KIRK,

The above technique also works for reports. I have a report that uses 5 different queries as it's record source and it is programmed using the same technique.

Have fun,

Richard