trouble with next button on a filtered form  | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| |
Anyone getting sick of me yet? :D
I have a button on my form that opens a new form but with a filter: - stDocName = "frm_Specimen_Entrainment"
-
stLinkCriteria = "[Entrainment_ID]=" & Me![Entrainment_ID]
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
This works really well except that, as expected, I can't add new records. Interestingly, you can click the "next record" button and it will show a blank form (and then grey out) but the Entrainment_ID goes blank and the record doesn't get saved.
If I change the above code and pass the Entrainment_ID as OpenArgs then I can add a new record. However, I can keep hitting the next button as many times as I want and it keeps adding new records, whether I have actual data in there or not.
I am looking for something in between - the next button goes to a new record, then greys out so I can't keep adding new records until someone hits the "add new record" button.
Is this possible?
Thanks!
|  | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot Anyone getting sick of me yet? :D
I have a button on my form that opens a new form but with a filter:
- stDocName = "frm_Specimen_Entrainment"
-
stLinkCriteria = "[Entrainment_ID]=" & Me![Entrainment_ID]
-
DoCmd.OpenForm stDocName, , , stLinkCriteria
-
This works really well except that, as expected, I can't add new records. Interestingly, you can click the "next record" button and it will show a blank form (and then grey out) but the Entrainment_ID goes blank and the record doesn't get saved.
If I change the above code and pass the Entrainment_ID as OpenArgs then I can add a new record. However, I can keep hitting the next button as many times as I want and it keeps adding new records, whether I have actual data in there or not.
I am looking for something in between - the next button goes to a new record, then greys out so I can't keep adding new records until someone hits the "add new record" button.
Is this possible?
Thanks! Opening the form with the stLinkCriteria filter shouldn't prevent you from adding records.
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
One would think! And yet that Entrainment_ID isn't getting populated on the new record.
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot One would think! And yet that Entrainment_ID isn't getting populated on the new record. It shouldn't, a filter doesn't limit what goes into a field.
Isn't that why you passed the Entrainment_ID in OpenArgs as well? So that you can assign it as the Entrainment_ID for new records? But of course that's also the reason why it keeps adding new records. Assigning it an entrainment_id saves it as a new record with just that one field.
What you could do is on form close, run a delete SQL that deletes all records where only the entrainment_id has data in it.
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Cool. I didn't realize that's what was happening. I figured once the correct Entrainment_ID was passed through the filter then one could keep going with that same Entrainment_ID.
How does one specify in the delete clause records that only contain the ID field? Do you have to cycle through all the controls and look for blanks?
thanks again for the help Rabbit. :)
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot Cool. I didn't realize that's what was happening. I figured once the correct Entrainment_ID was passed through the filter then one could keep going with that same Entrainment_ID.
How does one specify in the delete clause records that only contain the ID field? Do you have to cycle through all the controls and look for blanks?
thanks again for the help Rabbit. :) Not a problem.
It's not as easy as looking for records that only have info in the ID field. Rather you have to find records where the required fields are blank. Something along the lines of: - DELETE FROM tbl_Name
-
WHERE Field1 Is Null AND Field2 Is Null AND Field3 Is Null;
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Yuck - there are lots of controls, and a subform to boot.
Pardon my ignorance but how do I create a SQL query in the On Close event? Can I do it in the VBA or do I create a macro or . . . ?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot Yuck - there are lots of controls, and a subform to boot.
Pardon my ignorance but how do I create a SQL query in the On Close event? Can I do it in the VBA or do I create a macro or . . . ? You don't have to check every control, just enough of them to be sure that it was an accidental click of the create new record button.
But I doubt anyone's going to go on a clicking spree creating a bunch of mostly empty records.
There are two ways to do a query in VBA. One is to create the actual query in query design that does what you want. Then you open the query in VBA using. - DoCmd.OpenQuery "Query Name"
Or you can build the string yourself in VBA - DoCmd.RunSQL "DELETE FROM ..."
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Ahh, DoCmd.RunSQL is what I was looking for. :)
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Hmmm, I must be doing something wrong. I get the pop up that says I"m about to delete 0 records from my table. Do I need to have the table name in front of each field name? My code right now is: - Private Sub Form_Close()
-
DoCmd.RunSQL "DELETE FROM tbl_Specimen_Replicate WHERE Species_ID Is Null AND Gender_ID Is Null AND Lifestage_ID Is Null AND Disp_Capture_ID Is Null AND Disp_Release_ID Is Null AND Anomalies Is Null AND Comments Is Null;"
-
End Sub
Maybe I need - Private Sub Form_Close()
-
DoCmd.RunSQL "DELETE FROM tbl_Specimen_Replicate WHERE tbl_Specimen_Replicate.Species_ID Is Null AND tbl_Specimen_Replicate.Gender_ID Is Null AND tbl_Specimen_Replicate.Lifestage_ID Is Null . . . etc"
-
End Sub
?
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot Hmmm, I must be doing something wrong. I get the pop up that says I"m about to delete 0 records from my table. Do I need to have the table name in front of each field name? My code right now is:
- Private Sub Form_Close()
-
DoCmd.RunSQL "DELETE FROM tbl_Specimen_Replicate WHERE Species_ID Is Null AND Gender_ID Is Null AND Lifestage_ID Is Null AND Disp_Capture_ID Is Null AND Disp_Release_ID Is Null AND Anomalies Is Null AND Comments Is Null;"
-
End Sub
Maybe I need - Private Sub Form_Close()
-
DoCmd.RunSQL "DELETE FROM tbl_Specimen_Replicate WHERE tbl_Specimen_Replicate.Species_ID Is Null AND tbl_Specimen_Replicate.Gender_ID Is Null AND tbl_Specimen_Replicate.Lifestage_ID Is Null . . . etc"
-
End Sub
? You don't need to put the the table name in front of each one. Are all those fields really going to be null? If it's an autonumber then it won't be null. If it has a default value, then it won't be null. You might have to adjust the criteria depending on the field. If it's a subform and is linked by a field, then those key values get filled in automatically by the form and won't be null.
So open up the table and take a look at the accidental records and see what's being automatically filled even though all you did was click New Record.
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
There is one field that has a default value but I didn't list it in the code. I only chose fields that could be left totally blank.
All the fields that end with "_ID" are combo boxes though and are foreign keys. Does that make a difference? I can't just use "Comments" and "Anomalies" which are text boxes because it is likely that they will be empty even if the rest of the info is filled out.
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Go to design view and make a select query that pulls all the correct records then convert it to a delete query and look at the SQL.
Sometimes when I have trouble with a SQL statement I use the query design to get it to return the correct records and then look at how the SQL is set up.
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Ahhh, excellent suggestion. I should have seen what I was doing wrong - I forgot to specify the fields in the beginning of the Delete clause! Should be
Delete Field1, Field2, Field3
From table
Where Field1 Is Null AND Field2 Is Null AND Field3 Is Null.
Access DID put the table name in front of everything but I'm not sure that's totally necessary. So the full query looks like this: - DELETE tbl_Specimen_Entrainment.Anomalies, tbl_Specimen_Entrainment.Comments, tbl_Specimen_Entrainment.Species_ID, tbl_Specimen_Entrainment.Gender_ID, tbl_Specimen_Entrainment.Lifestage_ID, tbl_Specimen_Entrainment.Disp_Capture_ID, tbl_Specimen_Entrainment.Disp_Release_ID
-
FROM tbl_Specimen_Entrainment
-
WHERE (((tbl_Specimen_Entrainment.Anomalies) Is Null) AND ((tbl_Specimen_Entrainment.Comments) Is Null) AND ((tbl_Specimen_Entrainment.Species_ID) Is Null) AND ((tbl_Specimen_Entrainment.Gender_ID) Is Null) AND ((tbl_Specimen_Entrainment.Lifestage_ID) Is Null) AND ((tbl_Specimen_Entrainment.Disp_Capture_ID) Is Null) AND ((tbl_Specimen_Entrainment.Disp_Release_ID) Is Null));
-
Thanks again Rabbit! You are on it today! :)
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Sorry, I forgot you needed that.
No, you don't need to put the table name in front and you probably don't need all those parentheses.
You might want to change that first listing of fields to just DELETE * FROM because a listing of fields will only empty those fields rather than delete the records. At least that's what I'm assuming.
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
I tried running it from the Query window and it seemed to work. I'll give your version a whirl too though as it seems a lot cleaner and easier to read.
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot I tried running it from the Query window and it seemed to work. I'll give your version a whirl too though as it seems a lot cleaner and easier to read. After you run it from the query window did you check the table?
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Yes and it worked great so I'm just going to go with it.
A related question though (so I'm not starting a new thread yet) - the message "you are about to delete one record" is going to scare the pants off the users. Can I either hide that message or create a custom one? I tried to follow the tutorial for creating custom error messages done by ADezii but it's a bit beyond me.
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
You can hide that with: - DoCmd.SetWarnings False
- SQL stuff
-
DoCmd.SetWarnings True
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Obviously silly but just making sure question - by sql stuff you mean put the DoCmd before and after my Delete statement? :)
| | Needs Regular Fix | | Join Date: Feb 2007 Location: Stumptown
Posts: 402
| | | re: trouble with next button on a filtered form
Yep, works great, thanks again! Wow, i'm getting so much done today with your help! :D
| | Expert | | Join Date: Jan 2007 Location: California
Posts: 3,835
| | | re: trouble with next button on a filtered form
Not a problem.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,747
| | | re: trouble with next button on a filtered form
Quote:
Originally Posted by AccessIdiot Obviously silly but just making sure question - by sql stuff you mean put the DoCmd before and after my Delete statement? :) Notice that the DoCmd line before the SQL statement(s) is different from the one after them.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,537 network members.
|
