trouble with next button on a filtered form

[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!

[Rabbit]

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.

[AccessIdiot]

One would think! And yet that Entrainment_ID isn't getting populated on the new record.

[Rabbit]

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.

[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. :)

[Rabbit]

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;

[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 . . . ?

[Rabbit]

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 ..."

[AccessIdiot]

Ahh, DoCmd.RunSQL is what I was looking for. :)

[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

?

[Rabbit]

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.

[AccessIdiot]

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.

[Rabbit]

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.

[AccessIdiot]

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! :)

[Rabbit]

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.

[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.

[Rabbit]

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?

[AccessIdiot]

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.

[Rabbit]

You can hide that with:

DoCmd.SetWarnings False
 SQL stuff
DoCmd.SetWarnings True

[AccessIdiot]

Obviously silly but just making sure question - by sql stuff you mean put the DoCmd before and after my Delete statement? :)

[AccessIdiot]

Yep, works great, thanks again! Wow, i'm getting so much done today with your help! :D

[Rabbit]

Not a problem.

[NeoPa]

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.