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

?