code to overwrite query

[cardgunner]

I want to create a reset key where if the user messes up the structure of a query he can go to a form and hit a reset button.

So this button when pressed would create a copy of BackupQuery1 and past it as Query1. This would then overwrite the mistakes he made and reset the query.

What code I need to use for this?

Any better ideas?

 

[cardgunner]

I think it's right in front of me

DoCmd.CopyObject?

 

[cardgunner]

While I'm searching what is wrong this

DoCmd.CopyObject "([Cardgunner2],[Query3],acQuery = acdefault,[BackupQuery3])"

Maybe someone can take a look.

I got a run time error 3024

Could not find C:\ my computer path\"([Cardgunner2],[Query3],acQuery = acdefault,[BackupQuery3])".mdb

 

[namliam]

Try hitting F1 and reading up...

 

[cardgunner]

Well I have and it brought me to

http://msdn2.microsoft.com/en-us/library/aa210012.aspx

I'm having a hard time with the source object type. I don't understand what I;m reading.

I did get rid of the bad path

code is now

DoCmd.CopyObject ("",[Query3],acQuery = "BackupQuery3" ,[BackupQuery3])

 

[cardgunner]

Well thru trial and error I this works

DoCmd.CopyObject "", "Query3", acQuery, "BackupQuery3"

I'm hoping it will hold up in real time.

Please, let me know if this is a good way the user can reset a query in case he cannot undo his mistakes.

 

[namliam]

Sure it copies the backupquery back to the query3... thats what it does... "restore"

But why do you let users fiddle around with your queries anyway?

 

[Rabbie]

Sure it copies the backupquery back to the query3... thats what it does... "restore"

But why do you let users fiddle around with your queries anyway?

As Namliam says you shouldn't let your users access your tables and queries directly. They should just see the forms and reports. This will reduce the risk of corruption in your DB.

 

[cardgunner]

For lack of a better way to filter a form with subforms.

I have a form with subforms. He wants to set criteria for a field in the subforms and filter the records of the subform AND the mainform.

We have Asubform, Bsubform, and Cmainform.

In Asubform field AID he wants to see all 1 values.
In Bsubform field BID he wants to see all 6 values.
in Cmainform field CID he wants to see all 4 values.

All forms are linked by customerID. Cmainform is the parent.

His desired results would be all show me all records where 6 and 1 and 4 are present.


If a record has one and not the other it would not show.

He wanted to do this by using the Filter By Form in the Access Toolbar.

However that would show the all records where 4 is in Cmainform. All records where 6 was in Bsubform, all the records where 1 was in Asubform.

There are more fields to filter then those 3. A total of 32 at least.

I asked for guidance in an earlier post and because I had trouble explaining what I was looking for I did not receive much.
http://www.access-programmers.co.uk/forums/showthread.php?t=145843
So with time ticking I came up with this brilliant idea.

I agree I would not like him in there as well, but I couldn't come up with a easier or better way.

What do you suggest?

 

[Rabbie]

I am a little puzzled by your request because Normally the Mainform will show the Parent side of a 1 to Many relationship and the subform will display the Child records that are linked to that particular Parent.

What are the relationships between your forms? it would be helpful if you can post a copy of your DB so we can see what is what.

 

[namliam]

I read in your other post you cannot post your DB due to confidentiality... but you can probably make a mock up DB pretty quickly which shows what you want to do without breaking the confidentiality...
If you can make such a mock up, it is really the best/fastest way we can help you...

Well for a possible solution you can offer something like "cascading combo's" and do the altering in code...
or offer textboxes where the user can enter the fields he wants ... Then use some smart code to create filters/queries etc.

The main thing tho is... You dont want users mucking around in your DB, your DB, your control, Users will only F*ck things up...

 

[cardgunner]

I did post my db's in there. db1 and cardgunner2 database.

db1 was before I created the filter by query.

cardgunner2 I had it created the filter by query and had some issues on how to reset the query and a couple others issues.


As far as the casacding boxes I looked at the present form and was unsure how to fit them in and then getting the smart code to work them.

I'm not experienced at this at all. I just learned how to and where to use code with this db. Up till now wizards and macros.

I could put them in another form but again the code to run it plus the time involved.

I was minutes away from being done ready to take the check and then the question "How do I....?". Unfortunetly it was a good one.

So here I am again at the very end now rethinking what I got. This project has taken 4 times the hours I planned. But it needs to be right. With the reset button how bad can the user screw things up? And if he does can't I get paid to fix them?

Really I do want this right. Also won't this give hime the experience he made need to create his own queries. It is a one user or two user db.

 

[cardgunner]

Rabbie,

You are right

I am a little puzzled by your request because Normally the Mainform will show the Parent side of a 1 to Many relationship and the subform will display the Child records that are linked to that particular Parent.

What are the relationships between your forms? it would be helpful if you can post a copy of your DB so we can see what is what.

They do have one to many and they are parent to child.

However, and maybe I'm not doing something right, if you filter your subform it will filter the records in that recordsource. It will not filter the mainform and show only the records in the mainform that meet the criteria in the subform.

 

[cardgunner]

MainForm CustID
1
2
3
4

Subform CustID, OrderID
1,1
4,1
3,3
2,5
2,1

criteria in subform OrderID=1

subform results
1,1
4,1
2,1

Mainform results
1
2
3
4

I want mainform results
1
4
2

 

[namliam]

However, and maybe I'm not doing something right, if you filter your subform it will filter the records in that recordsource. It will not filter the mainform and show only the records in the mainform that meet the criteria in the subform.

Nope you are right... Filtering a subform does NOT filter the main....

You can do this thing 2 ways... both require code offcourse...
How good are your coding skills?

General comment:
- Naming convention, you are sortoff doing it without actually doing it...
Your tables should all be named something like tblName, then queries qryName, forms frmName. This way you can keep things apart but the same.
- Query1??
One of the forms is based on Query1?? Uhm... still in development I hope...

Back to the question at hand...
A filter by form will not work if you want to filter on something that is in the subform. But you will have to make something yourself. Then you have to apply some sort of filter to the main form.

I made a REALLY quick and REALLY simple sample, for you to get an idea...

Note:
The attached file is actually a RAR file, but this site will not accept rar uploads... so I renamed it ZIP to be able to upload it.

Good luck !

 

[gemma-the-husky]

MainForm CustID
1
2
3
4

Subform CustID, OrderID
1,1
4,1
3,3
2,5
2,1

criteria in subform OrderID=1

subform results
1,1
4,1
2,1

Mainform results
1
2
3
4

I want mainform results
1
4
2

Can we go back to the beginning

If you want the main form to be in order
1
4
2

then design a query that puts it in order 1,4,2

surely the subform is subordinate to the main form? ie If the mainform doesnt have customer 3 (say there are n oorders yet) then how can the subform EVER show customer 3. How can the subform manage the mainform?

Or am i missing something?


---------
clearly I am - in your subform, you are showing the same orderid being related to several customers - therefore is this a normalisation thing? can you clarify your system a bit please, cardgunner?

 

[cardgunner]

You can do this thing 2 ways... both require code offcourse...
How good are your coding skills?

My skills in code are NULL. well almost. Untill this db I was doing everything with wizards or macros. Yeak I know...

However I think I have learned a bunch(In the realm of things I think my bunch is still a grain)

I have tried, not hard on opening up a rar file with no success. I'm sure it's not rocket science so I will take a look.

Umm.. Query1.. yeah that is still in development. I'm sure it's not in the final version.

Thank you so much for this. I have had such good advice from this forum. I've learned A LOT because of it.

 

[namliam]

Hmz, I send it to a Vista compacted folder, which should be "normal zip"

Try this (zipped) attachement

 

[cardgunner]

Sorry my goof.

then design a query that puts it in order 1,4,2

It should be 1,2,4
I'm wasn't worried or concerned about the order by for this forum but it is 1,2,4

If the mainform doesnt have customer 3 (say there are n oorders yet) then how can the subform EVER show customer 3. How can the subform manage the mainform?

You are right. IF the mainform has no custID as 3 then the subform cannot and will not display it. Filter or not.

in your subform, you are showing the same orderid being related to several customers

Again, my misytake cause I rush things. I should have said something like foodname( Anything other then ID) for the subform field.


Mainform custID
Harry
Larry
Kate
Pete

subform custID, foodname, orderID
Harry, steak,1
Pete, steak,2
Kate , berry,3
Larry,egg,4
Larry,steak,5

Filter subform foodname=steak

I get

Harry, steak,1
Pete, steak,2
Kate ,
Larry,
Larry,steak,5

I want
Harry, steak,1
Pete, steak,2
Larry,steak,5

 

[gemma-the-husky]

i still think you need to go back to the original query/mainform. The subform should just show the items related to the mainform, the mainform should not be DEPENDENT on the subform.

if you are making a selection in the subform, and want to requery the main form, you need to get this parameter IN to the query that drives the mainform

then simply requery the mainform, and the subform which is linked to the mainform will automatically reflect the change

PERHAPS this filter selection would be better placed in the HEADER of the mainform, not in the subform.

ie put a cbobox with
steak, berry, egg in the main form header (populate this with all the available options that might show in the subform, make it multi select - then you can pick one ingredient, or several) - thats the way i would do it.

hope this helps