DataSheet Form Based on Query

[Itaiz]

Hi All,
I built a datasheet form based on query. This form is a sub form in another form and should present resaults for searching. for make it dynamic I change the SQL in the Qeury it based on. The query changed, but when requery the subform there is no change on it.
The following code is the one I'm using:

Set mydb = CurrentDb
Set MyQuery = mydb.QueryDefs("qryOrdersList")
MyQuery.SQL = mySTR
MyQuery.Close

Form_SubFrmOrdersList.Requery

(of course I put into mySTR SQl sentence before.)
the query working fine after the change, and return the right results.

Please advise
Thanks

 

[jal]

I've never used a subform, but I have something in my notes which says:


subform1.SourceObject = "Query.fillGrid"

So I am wondering if you forgot to include that line of code.

 

[jal]

Oh, woops, maybe I didn't read your example close enough. I'll take another look.

 

[jal]

Well, I tried to reproduce the problem but was unable. This code worked for me:

subform1.SourceObject = "Query.qryCust"
subform1.Requery
Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("qryCust")
qry.SQL = "Select top 5 * from customers"
subform1.Requery

 

[Itaiz]

i dont have such an attribute "Source object". I put it in record source. (did it in design)
Itai

 

[Itaiz]

Oh, Sorry I know what you did, it's not a datasheet. good idea, I will check it.

 

[Itaiz]

Greate it works, thanks

 

[Itaiz]

Not Good!!!!
I must use a datasheet form in subform and not a query, because I use dblClick on Datasheet. it cant work with query.
Please advice.

 

[jal]

I assume you mean to doubleClick in a particular column, right? (As opposed to some other part of the subform). If so, read on, maybe I can help.

Like I said, I've never used a subform, but today, after looking at this thread, I decided to give it a try. Here's a way to create a subform in Tabular format (full columns) and still have access to regular Textbox events such as On_Click:

- In addition to your regualar form, create a second form (to serve as a subform), using the wizard, and make it Tabular (Continuous), with its record source set to the Customers table. Cut and paste the labels from the Detail section into the Header section. In the Detail section, rearrange the textboxes into a tabular format. If any textboxes need to be read-only, set them to Locked. This second form will actually be your sufborm.
- In this second form, choose View > Field List, if any textboxes are missing, and then drag the missing columns (missing textboxes) onto the form.
- Now go to your main form, insert a subform object and, using the Wizard, make it "base this subform on an existing form", and then choose your second form as that existing form. The result is that your second form is now called a "subform" and is embedded into the main form.
- As for events, realize that each TextBox existing in the Detail section has regular Textbox events. So far I tested:
TextBox_changed (TextChanged event)
TextBox_Enter

Is it possible that this approach would work?

 

[Itaiz]

This is very similar to what I did in the beginning.
But instead of cut and paste labels, you can create this form as Datasheet form (use wizard) bound to your customer table/query (then you dont have to cut and paste labels).
In my little project it is bound to query. Like you did its in a subform which refer to this form, lets call it form B, and to the main form (which contain the subform) we'll called form A.
Form A is a search form and the results appear in form B. Then I can dblClick on a Record and see more details in another tab.
Now everytime I'm doing a search I build different 'Where' clause in code. and push it into a query which function as record source for Form B. but when I requery Form B the results dont show, even that the query content has been changed.
I tried to do it also in other ways with no succeed.
Thanks
Itai

 

[jal]

Thanks for the tip about DataSheet view. No more cutting and pasting for me.

I think I sort of reproduced the problem. I dropped two buttons on the main form. For one button, I used the queryDef object (as you did above) to set the sql to "Select * from Customers"

In the other button I set it to "Select Top 5 from customers"

Here's what I experienced: No matter which button i use, I have to click it twice before the refresh actually occurs. If I can figure out how to get it to work on the first click, I'll let you know.

 

[jal]

Ok, now it's working on the first click. I had to set the SourceObject value in my code right before calling the Requery. Here's the code for the On_Click of the two buttons (identical except using different sql strings)

Private Sub Command2_Click()
Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("qryCust")
qry.SQL = "Select * from customers"
Me.subformCustomers.SourceObject = "Query.qryCust"
Me.subformCustomers.Requery
End Sub



Private Sub Command3_Click()
Me.subformCustomers.Requery
Dim qry As QueryDef
Set qry = CurrentDb.QueryDefs("qryCust")
qry.SQL = "Select top 5 * from customers"
Me.subformCustomers.SourceObject = "Query.qryCust"
Me.subformCustomers.Requery
End Sub

 

[jal]

And yes, my subform is a DataSheet form this time. So, like me, you should have a SourceObject property available.

 

[Itaiz]

I dont have SourceObject for a form, only for subform control, The name "subformCustomers" is refer to the control on the main form or to the form itself. I think you change sourceobject of the control from form to query.

Thanks,
Itai