Display Query on Form

[kirkm]

Hi.

I've based a Form on a query and can navigate through the records OK. A command button runs two queries, but these display on top of each other (and the Form).

Can I display the queries on the Form itself, and how ? Ideally so I can still change the data, if need be.

Many thanks, Kirk

 

[CBrighton]

Are the fields returned exactly the same?

You can change the record source of the form simply enough vian the command button's on click VBA event (e.g. me.record source = "qry2"), but if the controls on the form don't match the fields in the 2nd query you will have issues displaying the data.

 

[kirkm]

Actually they are the same fields - but the Form is not datasheet mode, like the queries, and has other controls.

It's desired to see all three outputs and be able to edit them.

I'm thinking of subforms (?) but getting out of my depth a bit.

I'll try creating 2 subforms and set their Record source to the queries....

Thanks - Kirk

 

[CBrighton]

You shouldn't need to.

If the fields are the same (and therefore the controls are bound to the correct field names) then all you have to do is change the forms recordsource property.

I gave an example above but click into the property and press F1 for help on it, I believe it includes examples of how to change it via VBA.

This should do what you need (replace bolded text with name of query):

Me.RecordSource = "[B]QueryName[/B]"

 

[vbaInet]

You will still have problems linking the controls to the query when you change the form's Recordsource. If you're good at coding then you can handle this well.

For your ease, just use subforms as you thought. So it doesn't pull records from both sources unecessarily, set the Filter to 0 on the hidden the subform.

 

[CBrighton]

If it's returning exactly the same fields then the controls should have no problems linking to the appropriate fields.

You can test if the form will work with each query by manually changing the recordsource in design mode and opening the form for each query. If they all work correctly then you know you can do it via VBA too.

If you go the subforms route then I'd advise a single subform object and changing this hform is displayed within it via VBA (instead of multiple subform objects, one for each query)

 

[vbaInet]

If you go the subforms route then I'd advise a single subform object and changing this hform is displayed within it via VBA (instead of multiple subform objects, one for each query)

It may seem like the best way to go but you're bouncing off the database more when you dynamically change the source object.

However, I would imagine you (kirkm) don't have huge amounts of data so one subform object would suffice.

 

[CBrighton]

It may seem like the best way to go but you're bouncing off the database more when you dynamically change the source object.

However, I would imagine you (kirkm) don't have huge amounts of data so one subform object would suffice.

Really?

We are comparing:

1. Open 3 subforms @ form load, each bound to a different query

2. Open 1 subform control, bound to either the first subform (& the associates query) or nothing @ form load

I wouldn't expect 1 to be more efficient unless the end user changes which query they want to view many times?

Or is it more a case that the overhead caused by initially connecting to the back end database, even if you have a connection open in other forms, means it's better to open them all at once?

 

[vbaInet]

Or is it more a case that the overhead caused by initially connecting to the back end database, even if you have a connection open in other forms, means it's better to open them all at once?

I gave an example ...

For your ease, just use subforms as you thought. So it doesn't pull records from both sources unecessarily, set the Filter to 0 on the hidden the subform.

Load once, filter countless times.

 

[CBrighton]

Surely it's still load once for each subform though, meaining 3 loads?

Even if the filter/criteria means no records are returned, all 3 recordsources would be checked upon load, right? Or does the 0 filter have a special meaning that stops the recordsource being checked?

 

[vbaInet]

Surely it's still load once for each subform though, meaining 3 loads?

Yep, but you load them once. Certain preparations are performed when a source is loaded onto a subform. These preparations will be performed once for each subform.

Even if the filter/criteria means no records are returned, all 3 recordsources would be checked upon load, right? Or does the 0 filter have a special meaning that stops the recordsource being checked?

No special meaning. It just means records are not pulled yet but it has passed the preparations stages.

Also note that there is the added advantage of having more control over looks and feel of the subform, sorting, filtering and all the other added advantages a form provides.

At this point, I will leave it for the owner of the thread to decide.

 

[CBrighton]

I agree your suggestion may be better if the user will always want to access all 3 queriy results, however I'm still unsure if the user will often access just 1 query.

However, I should make it clear that I'm not trying to argue here, just trying to see why one way is better than the other. I'm self trained and if your way is better then I'll keep that in mind next time I do a similar thing.

 

[kirkm]

It's very interesting to follow you both bouncing ideas. My abilities are limited in comparison - and too late here to try any coding or tests. Tomorrow awaits.

Changing the Forms recordsource would - I think - alter globally what's displayed and I'd like to see all 3 sets to visually compare (and edit).

The amount of data is quite large, 27000+ records and 93 fields. But speed isn't needed, and this is a one-off. Once the data is correct the job is done, although it will be useful to have the ability to rerun/check later.

I'm sure I'll more (basic) question once I get a little further into this, hope you can stay around .

Regards, Kirk

 

[CBrighton]

If you want all 3 at once then seperate subforms (or just seperate forms) would seem to be the answer then.

In which case you should be able to follow vbaInet's instructions except for hiding the other subforms & setting a filter to hide the results on the hidden forms.

 

[vbaInet]

Can you tell us what you mean by ...

A command button runs two queries,

?

It opens two queries or it performs certain operations on the queries?

 

[kirkm]

Yes, it opens 2 queries.

I'm failing on all fronts though ... after many hours I have the datsheet mode subform on the Form showing the right fields. I suspect it's not updating though so I'm attempting to set the Record Source for the subform in the main Forms Current event.
(after changing the query to the new records data)
But all I can achieve is -
Run-time error '2450':
Microsoft Access can't find the form 'Form1a' referred to in a macro expression or Visual Basic code.

 

[kirkm]

Aha! Success - but a snag. I'm not running any queries now and eventually have the two subforms working. But what a mission!

Option Compare Database
Option Explicit

Private Sub Form_Current()
Dim aa As Variant
aa = MyParse(kArtist, " ft ")
Label15.Caption = Trim(aa(1))
getIDv (aa(1))
Label16.Caption = Trim(aa(2))
getIDv2 (aa(2))

Child14.Requery
Child15.Requery
End Sub

Sub getIDv(ByVal xz)
Me.[Child14].Form.RecordSource = "SELECT ArtistRefA, Prefix, Artist  FROM tblbritburn WHERE Artist Like " & Chr$(34) & "*" & xz & "*" & Chr$(34) & ";"
End Sub

Sub getIDv2(ByVal xz)
Me.[Child15].Form.RecordSource = "SELECT ArtistRefA, Prefix, Artist  FROM tblbritburn WHERE Artist Like " & Chr$(34) & "*" & xz & "*" & Chr$(34) & ";"
End Sub

MyParse is like Split, and kArtist is a textbox from the query the Form is set to.

It's all working great except one hassle - I want to change data in the datasheet subforms. But if there's something there already I can't (easily) edit it as the whole thing becomes selected if I click at the start (or press Home on the keyboard).
Then it deletes, of course ! No problem adding to it, or entering something new.

Is this the Filter option you mentioned ?

 

[CBrighton]

I'm fairly sure that it's a standard feature of datasheet mode.

Keep in mind you can replicate the look of datasheet mode by carefully making a continuous form. Use the header to add lables for column headers and add a textbox control for each field. Move them so they have no space between them and appear in a grid when viewed.

However, that way the "cells" act as normal textboxes (because that's what they are!).

 

[kirkm]

I'm fairly sure that it's a standard feature of datasheet mode.

I'm suprised... I would have bet money that I use Datasheet mode almost exclusively and clicking a record becomes editable, Home-End, arrow through etc. Cursor behaves just right.
e.g. a query from Design mode is datasheet, yes ? Same as a table?

But I am reluctant to disagree... yet it seems so here.

I've never used a header/footer ! Your suggestions sounds useful. I find it hard to learn new trick though, not to mention remember ones I should know!

Cheers - Kirk

 

[CBrighton]

Sorry, normal is tabbing / cursoring into a field selecting all, but clicking in acting normally.

It's too early for my brain to work!