Parameter query that returns no records

[Roncc]

Thank you.

[pbaldy]

No problem, post back if you get stuck.

[Roncc]

This worked!!!! Thank you very, very much.

Ron C.

[pbaldy]

Glad you got it working Ron.

[Roncc]

pbaldy; In this post I had previously described a problem I was having but have since figured out a solution. I don't know how to cancel a post once it has been issued so I have edited the original post to say I figured the solution.
Sorry to bother you/

Ron

[pbaldy]

No problem Ron, glad you sorted it out.

[Micron]

Curious as to what the solution was, given the problem you described in the email I/we got. I was wondering if you figured out that a record with a bunch of nulls is still a record, providing your table will accept nulls. Or maybe that wasn't the problem at all. Might as well mark the thread as solved if you haven't already.
Good luck with your project.

[Roncc]

@Micron; I'm assuming you are referring to the post I made to pbaldy describing how the wrong record would come up on a form for a table I was trying to add data to. I solved the problem by using a select query as the data source for the form. The table for the form has date field which has a default date of the current date, date(), I set the select query to open a record with the current date in that field and with a null in another field that wouldn't be null for a record that had already been properly created. If I changed my mind about creating a new record I would undo any changes made to the new record and use code on the close button, reset the date field that was defaulted to todays date to null so the record would contain no data and not interfere with future attempts to create a new record in the table. I'm avoiding deleting records as you have advised me that doing so would create problems for server based programs and I thank you for that advice. My problem has been solved and I'll mark it accordingly. Again, thanks to all of you for the help. I've learned a lot.

[Micron]

I'm avoiding deleting records as you have advised

Don't take that too literally. If you start a record but don't finish it, you're not really deleting a record as it doens't actually exist yet. If it does get saved but the record contains wrong data, that's OK too because it's not meaningful. That statement really only applies to records that were once meaningful, become insignificant but could one day be needed.

Note too that a field with a default value in a record being created is still not a saved record. The value you see doesn't actually exist as a record until the record is saved. The best approach is to cancel the creation of the record if possible as opposed to trying to delete or reuse it.
Good luck with your project!

[Roncc]

@Micron; thanks so much for taking the time to provide this kind of guidance. I've been hesitant about deleting records because of the server use aspect, concerned that if I may impact records that are "open" by other users. I understand what you are saying and will look to use the "Record Delete" option that will allow the end user to delete a record if he changes his/her mind when the record is in the process of being created. You have no idea how much your assistance (and the assistance of others on this thread) has helped me. I've learned more about ACCESS in the last 2 weeks than I have over the last year.

[Micron]

Glad you're getting much out of this forum. There is one aspect you don't seem to be grasping

will look to use the "Record Delete" option that will allow the end user to delete a record if he changes his/her mind when the record is in the process of being created.

You cannot "delete" a record that doesn't exist, you can only cancel its creation process. Anything you do during this process that commits the record to being saved will do just that. This can include
- using form navigation controls to move to a different existing record
- moving off of a continuous or datasheet subform to its parent form
- moving from one record to another in a continuous or datasheet form, regardless of whether it is a subform or not
- requerying the form holding the record being created*
- taking any action on a form (such as changing a combobox selection)* where the action causes the form to requery (*AFAIK, a Refresh of the form = same result)
- closing the form containing the uncommitted record
There may be others that are not coming to mind at the moment.
If the creation is cancelled, your autonumber field will increment, which is one reason why they should never be used for meaningful data.

If you're going to present a user interface to permit cancelling the creation of a record (such as a message box), identify the process as Cancelling, not Deletion. AFAIK, the only way to guarantee that any new record is committed by what most users might consider to be purposeful action is to create the record when the form is bound to a temp table and require a button click to copy the temp record to the proper table. Regardless if cancelled or saved, you then delete the temp record. Such temp tables normally exist only in the user's own FE so that they only affect their local copy. This temp table stuff comes to mind because altering or creating a record on a subform, then doing anything noted above that commits the change (all while remaining on the parent form record) causes the subform Dirty property to be False. The resulting impression could be that the subform record can be cancelled when it cannot, regardless of what options the process might present to the user. For subforms, I use a module level variable such as bolDirty and set it to true if subform records are altered or added. You check the variable value rather than the subform property, but since it has been saved, the best way I know of to get rid of the changes is to use temp tables so that the main ones are not affected unless there is a conscious effort to do so.

Hope that's not too confusing.

[Roncc]

I think I understand what you are saying. I have put a Button labeled "Cancel Event" on one of the forms that is used to input information for a new record. I used the default code available when the button was created which reads: 'On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
I also placed a button on the form to allow the user to print a report containing data from the new record and I did find that I had to "Save" the record or the report would be empty. I found that after I had saved the record I could still use the Cancel Event button to remove the record form the table. Am I doing something wrong?

[Micron]

something doesn't seem right. can't see how acCmdSaveRecord can cancel record creation or delete one. Maybe you're actually setting all the fields to an empty string ("") or Null, which might make it look like there's no record in the table, but there would have to be a row in the table with nothing visible in it, or at most, an autonumber. That would still make it a record.

[Roncc]

I think you're correct. I tried it out and it is not deleting the record regardless of when I select the "cancel" button (the record is still in the table when I close the form). I use an append query to begin the creation of the new record and I suspect it is automatically saved as soon at the query runs. Thanks for the tip.

[Roncc]

After reviewing the posts here I am hoping the following might solve this problem. I create a "temporary" table that is available to each user on the front end. If they create and save a record in the temporary file it is appended to the permanent table on the backend after which the record in the temporary table (on the front end) is deleted with a delete query. If they chose to "cancel", the single record in the front end temporary table is removed with the delete query but no append query is run. Thoughts?