Another requery question

[cardgunner]

In order to solve a subform filter problem I put a command button on the form that opens a query that is linked to the form's recordsource query.

The customer selects the button, fills in his criteria, saves the query and closes the query.

The form still shows all the records.

So I added a button for him to push to requery the form. I tried Me.requery, me.refresh, DoCmd.RunCommand acCmdSaveRecord, and a host of other acCmd's.

None of what I tried requiries the form with the new criteria.

The only way I have been able to do it is close the form and reopen it.

What am I not doing right or and what command should I be using?

 

[ajetrumpet]

Gunner,

I still have this file. You were not able to solve this initial problem? I'll try to go through the file if you'd like, if you tell me what I'm supposed to look for...

I have the file and the screen shot of the form with the two subforms...

 

[cardgunner]

Adam,

Thanks for the reply.

No I wasn't able to fix it like I wanted. I had to restructure the record sources of the form and the subforms.

Part of the fix was the the button I put on the form. It calls up a query. He will then add the criteria to the query. He saves it and closes ir. That brings him back to the form. However the I need to requery the form. I tried Me.Requery, me.refresh, DoCmd.RunCommand acCmdSaveRecord, and a host of other acCmd's. but none of them did what closing out ogf the form and going back in did. I put the code in a command button.

I'm not carzy about this fix cause I will have to teach him how to operate or use a query. Which is something I didn't want to do but in the long run will be better for him and me. It's not as user friendly as I'd like either. I'm worried about other users as well. right now it's just him. But he is going to give this db out and have someone lelse do the inputting. Also with the new query it makes the data non editable so I need to create a few more forms so he can add and edit.

So I guess in the long run, I found a poor workaround.

For this workaround I still need to know what command can I use that has the same effect as closing the form and reopening it?

As far as telling you what you are supposed to look for ... i will take a look at the post and try to explain it better.I do a bad job explaining things.

 

[gemma-the-husky]

its probably connected with the way the MAIN form and the SUBFORM are linked, and not necessarily to do with the way the query producing the subform is generated.

try parent.subdetails.requery
might be parent!subdetails.requery, see if that helps

where subdetails is the name of the subform on the main form

 

[cardgunner]

Gemma,

Thanks for the reply.

I'm not sure what you are telling me?

the command button is in the footer of the form. I want the onclick function to requery the form and all the subforms.

There are 4 subforms.
actionsubform
ordersubform
statussubform
instsubform

do I nned to requery each subform?

then what about the main form?

 

[ajetrumpet]

Gunner,

Do you have any relationships set up in your database? I don't see any....???

and I don't see any subdatasheets in your tables...

 

[cardgunner]

Sorry for not getting back to you, coloring Easter eggs.

I have not set up relationship in the db. I know that sounds pretty novice. I have no reason why I haven't.

However there is a relation between the tables

Customertbl.customerid=ordertbl.customerid
Customertbl.customerid=commenttble.customerid
Customertbl.customerid=statustbl.customerid
Customertbl.customerid=insttbl.customerid

Do you need the rest?

 

[cardgunner]

Subdatasheets in the tables, no.

 

[ajetrumpet]

Customertbl.customerid=ordertbl.customerid
Customertbl.customerid=commenttble.customerid
Customertbl.customerid=statustbl.customerid
Customertbl.customerid=insttbl.customerid

Do you need the rest?

No I don't, but the relationships would make solving your problem a whole lot easier, instead of sourcing your subforms from queries and such, which you have already done.

It makes it difficult to sift through your file too, and get the appropriate solution. I'll try to do it though...

 

[cardgunner]

Here is a screenshot.

Sorry for not being better prepared.

 

[cardgunner]

What command is needed in the code to do the same thing as closing the form and reopening it?

What else can I try. I do not know what gemma means.

I have tried refresh and requery what else can I try?

 

[gemma-the-husky]

cardgunner, is your dbs posted on another thread - if so, ill download and have alook as well - can you give me a link

 

[ajetrumpet]

I don't know what Gemma means either. But, he has done more work with this than I have.

I have a feeling this is really simple, but until I completely understand what you want, and how to navigate to the relevant objects in your DB, I can't really do much...

 

[cardgunner]

We are going in two different ways. I first had a problem/issue and could not solve it so I started working around the problem to find a different solution. This thread is an issue I got with a piece of code in the workaround. Well now I've got two issues with my new work around. However the second I'll post in a new thread.

However the first is, I have a button on my new work around form. This button opens a query.

DoCmd.OpenQuery "cstfrmjoinqry", acViewDesign, acEdit

This works fine.

I then fill in the criteria i want for the form. Save the query. Close the query by hitting the x in the far right top of the query.

this brings me back to the form. But it doesn't have the queries results yet.

I put a button on the form so I could click that to requery the form to show the results of the query thru the form. Everything I tried did not rerun or refresh or requery the form. I had to close out of the form and open it back up in order to get the results.

What code is needed in the onclick event of a command button that will do the same thing as closing the form and reopening it?

 

[ajetrumpet]

I put a button on the form so I could click that to requery the form to show the results of the query thru the form. Everything I tried did not rerun or refresh or requery the form. I had to close out of the form and open it back up in order to get the results.

There's your problem.

If you're seeing the requery action happen with a close and a subsequent open of the form, then the requery syntax (if it's even the right process to go through) behind the button is wrong. What is it?

Are you still dealing with the same form that's in the attached picture???

 

[cardgunner]

In have tried Me.requery, me.refresh, DoCmd.RunCommand acCmdSaveRecord, and a host of other acCmd's.

None of them re ran the form. I don't know how to call the action. It's what happens when you close the form and open it back up. The whole thing gets re-run.

I cannot see the results of the criteria of the query untill I re-open the report.

I really can't be having my client close the form and reopen it.

The form I;m doing it similar in looks to the screenshot but like I said I had to rework how the form gets it's information because I couldn't come up with a solution to the filter by form problem.

I posted the relationships for the old db at

http://www.access-programmers.co.uk/forums/showthread.php?t=145843

 

[cardgunner]

If I have to tomorrow I'll strip my new db and post that as well.

I didn't think this was going to be that complicated. But thank you for sticking with ime.

 

[ajetrumpet]

The new DB would be useful. The one I have right now has an actual SQL statement for the RS of one of the subs, and a query for the RS of the other sub. But, to tell you the truth, I have no idea why the data in these subs changes everytime you advance a record in the main form. You have no relationships, and the RS's of both subforms have no criteria clauses attached to them. Can you explain this to me???

 

[cardgunner]

They are linked (master and child ) by the customerid. It's in the subform's data property.

Getting ready for church, but will post db soon.

Have you any ideas on the code or command needed that will refresh or requery or rerun the form?

 

[cardgunner]

new db

I attached the new db.

The db opens to cstfrm. if you go to filter by query in the footer of form it opens a query where you can select the field or fields you want to filter by.

Lets type in the criteria of 1 in the actionid field

save the query and X out.

now you should be back to cstfrm.

hOwever you will see that all 4 customer are still there. The form has not refreshed.

What code do I need to put on the onclick event of the refresh button that is located in footer of the cstfrm that will refresh or requery the form? I have tried me.refrsh and me.requery but they don't seem to work.

If you close the form and get back into it you will see the 2 records that I want.

This is not how I wanted this process to go. I asked in an erlier thread for help on this problem.
http://www.access-programmers.co.uk/forums/showthread.php?t=14584
I have not been able to explain the issue well enough so I have had no suggestions on a better way to filter the information that is laid out in the cstfrm.


But for now all I need is the code or command that will refresh the form.