More on Blank Records

[Mrs.Meeker]

I've been reading about blank records and have tried a few options found here but have not solved all my of problems. (I tried going to the table and making them required fields - didn' t like that, so I used Pat Hartmans 'if statement':
If IsNull(Name) Or IsNull(Office) Then
MsgBox "Record will not be save. Please enter required data", vbOKOnly
Cancel = True
End If
(I used this in the beforeupdate event according to her recommendation.) Here's the problem.

My database opens to a main menu, users select a "view projects" button which opens a master list. From here they select a project and press the view button. This opens the project details and from here they can fill out one of three surveys. Upon making their selection, the user form opens. This contains the survey number (which is generated at that point), the survey type and the project number. Users are required to fill in their name and office and press the next button.

If a user, for one reason or another, closes this form, the survey number is still generated. I would like to prevent that. I know I can disable the close button but what if they have realized they chose the wrong project. I would like the ability to add a cancel button which will remove the survey number and use it on the next user form.

This is the SQL on the query that creates the survey number:

INSERT INTO tblQuestionsResults ( QuestionID, PageCode, Answer, SurveyNumber )
SELECT tblQuestions.QuestionID, tblQuestions.PageCode, 0 AS answer1, tblNextSurveyNumber.NextSurveyNumber
FROM tblNextSurveyNumber, tblSurveyNumber INNER JOIN tblQuestions ON tblSurveyNumber.SurveyID = tblQuestions.SurveyID;

Is there anything I can do to correct this problem? By using the if statement in the BeforeUpdate event it prohibits the creation of blank records but I would like to also prohibit the creation of Survey Numbers.

thanks
Rhonda

 

[daveUK]

Rhonda

Is the survey number an autonumber? If so I don't think there is a way to roll this back if the record isn't saved. The only thing you can do is to run a delete query, which deletes the survey number, thus stopping any blank records, after they have pressed the 'cancel' button.

 

[Mrs.Meeker]

Yes, it is an autonumber. I didn't think of that.

I have written a delete query to remove these instances from the past. I was hoping to stop the problem. Thanks anyway!

 

[Rich]

Create your own Numbering system using DMax([YourField])+1. There have been many examples posted here.
Autonumbers shouldn't be used for this purpose.

 

[Mrs.Meeker]

Thanks Rich, I'll do a search on that.

 

[Rich]

Post back if you need further help, oh and by the way, He's a She

 

[Mrs.Meeker]

Who's a she???

 

[Rich]

Patricia Hartman , hope I spelt it correctly

 

[Mrs.Meeker]

Oh (hits self in forehead with palm of hand) DUH! So very sorry to Pat!!!

Pardon me!

Now I know, won't do it again!

 

[Mrs.Meeker]

I need a little more help.

I was going to try this code:

NextNumber = Nx(DMax("[SurveyNumber] = '" & me.SurveyNumber & "'"), 0) +1

I don't know where to put it.

BeforeUpdate?

 

[Mrs.Meeker]

Tried that - Survey Number = 0 and does not increase.