Quick Question...

[andy_dyer]

Ok...

Is there anyway I can delay the setting of the AutoNumber until I start typing on a form?

I thought I had my form locked down nice and tight and wanted to be able to exit as long as my form was not "dirty" without incurring the wrath of my error handling...

Because autonumber is setting itself staright away, the form is being picked up as "dirty"...

Anyone??


Thanks

Andy

 

[AncientOne]

The autonumber doesn't increment until you "dirty" the form by entering data anyway

 

[andy_dyer]

I didn't think it either!

It didn't do, but it does now...

Instead of showing (autonumber) in the field it is filling it in straight away and my first field has the cursor in it...

Any ideas what I could have changed??

Thanks

Andy

 

[andy_dyer]

Delay Autonumber

Hi,

I still cannot work out what could be causing my autonumber now to be setting as soon as the form opens as opposed to when I input text.

Any ideas, anyone??



Thanks

Andy

 

[Nero]

Do you have any On_Open events running?

 

[andy_dyer]

Not for that form... lots of On_Current... but all of that is just resetting my make visible code for various fields and command buttons.

I am going crazy trying to figure out why the autonumber is behaving in this way!!

Thanks

Andy

 

[AncientOne]

post the db with sample data.

 

[andy_dyer]

Even zipped it is still to big to be posted...

Here is all the On_Current Code for my problem form...

Private Sub Form_Current()

If Me.Recordset.EOF Then
cmdlastrec.Enabled = False
cmdnextrec.Enabled = False
Else
cmdlastrec.Enabled = True
cmdnextrec.Enabled = True
End If

If Me.Recordset.BOF Then
cmdprevrec.Enabled = False
cmdfirstrec.Enabled = False
Else
cmdprevrec.Enabled = True
cmdfirstrec.Enabled = True
End If

If Me.Referrer = "GP" Or Me.Referrer = "Practice Nurse" Then
Me.Surgery.Visible = True
End If

If Me.Referrer = "Health Visitor" Or Me.Referrer = "Consultant" Or Me.Referrer = "Cardiac Rehabilitation" Or Me.Referrer = "Physiotherapist" Or Me.Referrer = "District Nurse" Then
Me.Surgery.Visible = False
End If

If IsNull(Referrer) Then
Me.Surgery.Visible = False
End If

If Me.Surgery.Visible = True And Me.Surgery = "N/A" Then
DoCmd.OpenForm "frmError3"
Me.Surgery.SetFocus
Exit Sub
End If

If Me.NonStarter <> "N/A" And Me.Non_completer <> "N/A" Then
DoCmd.OpenForm "frmError2"
Me.Non_completer.SetFocus
Exit Sub
End If

If Me.NonStarter <> "N/A" And Me.Non_completer = "N/A" Then
Me.DropOutQSent.Visible = True
Me.cmdDropOutLetterToWord.Visible = True
Me.cmdDropOutQToWord.Visible = True
Me.cmdDropOutLetterToWord2.Visible = False
Me.cmdDropOutQToWord2.Visible = False
Me.DropOutQSent2.Visible = False
Me.Pre_RHR.Visible = False
Me.Pre_SBP.Visible = False
Me.Pre_DBP.Visible = False
Me.Pre_BF.Visible = False
Me.Pre_BMI.Visible = False
Me.Pre_Flexibility.Visible = False
Me.Pre_Peakflow.Visible = False
Me.Pre_Grip_R.Visible = False
Me.Pre_Grip_L.Visible = False
Me.Pre_Waist.Visible = False
Me.Pre_Hip.Visible = False
Me.Pre_Ratio.Visible = False
Me.Pre_Activity_Levels_Week.Visible = False
Me.Pre_Behaviour.Visible = False
Me.TwelveWeekReview_Label.Visible = False
Me.txt12WkDate.Visible = False
Me.cmd12WkDate.Visible = False
Me.Non_completer.Visible = False
Me.TwelveWeeksCompleted = False
Me.TwelveWeeksCompleted.Visible = False
Me.TwelveWeekQReceived = False
Me.TwelveWeekQReceived.Visible = False
Me.Post_RHR.Visible = False
Me.Post_SBP.Visible = False
Me.Post_DBP.Visible = False
Me.Post_BF.Visible = False
Me.Post_BMI.Visible = False
Me.Post_Flexibility.Visible = False
Me.Post_Peakflow.Visible = False
Me.Post_Grip_R.Visible = False
Me.Post_Grip_L.Visible = False
Me.Post_Waist.Visible = False
Me.Post_Hip.Visible = False
Me.Post_Ratio.Visible = False
Me.Total_positive_results.Visible = False
Me.Post_Activity_Levels_Week.Visible = False
Me.Post_Behaviour.Visible = False
Me.SixMonthReview_Label.Visible = False
Me.cmdReviewLetterToWord.Visible = False
Me.cmdReviewQToWord.Visible = False
Me.SixMonthQSent = False
Me.SixMonthQSent.Visible = False
Me.SixMonthQReturned = False
Me.SixMonthQReturned.Visible = False
Me.SixMonthActivityLevelsWeek.Visible = False
Me.SixMonthBehaviour.Visible = False
Me.TwelveMonthReview_Label.Visible = False
Me.TwelveMonthQSent.Visible = False
Me.TwelveMonthActivityLevelsWeek.Visible = False
Me.TwelveMonthBehaviour.Visible = False
End If

If Me.NonStarter = "N/A" And Me.Non_completer = "N/A" Then
Me.DropOutQSent = False
Me.cmdDropOutLetterToWord.Visible = False
Me.cmdDropOutQToWord.Visible = False
Me.cmdDropOutLetterToWord2.Visible = False
Me.cmdDropOutQToWord2.Visible = False
Me.DropOutQSent.Visible = False
Me.Pre_RHR.Visible = True
Me.Pre_SBP.Visible = True
Me.Pre_DBP.Visible = True
Me.Pre_BF.Visible = True
Me.Pre_BMI.Visible = True
Me.Pre_Flexibility.Visible = True
Me.Pre_Peakflow.Visible = True
Me.Pre_Grip_R.Visible = True
Me.Pre_Grip_L.Visible = True
Me.Pre_Waist.Visible = True
Me.Pre_Hip.Visible = True
Me.Pre_Ratio.Visible = True
Me.Pre_Activity_Levels_Week.Visible = True
Me.Pre_Behaviour.Visible = True
Me.TwelveWeekReview_Label.Visible = True
Me.txt12WkDate.Visible = True
Me.cmd12WkDate.Visible = True
Me.Non_completer.Visible = True
Me.DropOutQSent2.Visible = False
Me.TwelveWeeksCompleted.Visible = True
Me.TwelveWeekQReceived.Visible = True
Me.DropOutQSent2.Visible = False
Me.Post_RHR.Visible = True
Me.Post_SBP.Visible = True
Me.Post_DBP.Visible = True
Me.Post_BF.Visible = True
Me.Post_BMI.Visible = True
Me.Post_Flexibility.Visible = True
Me.Post_Peakflow.Visible = True
Me.Post_Grip_R.Visible = True
Me.Post_Grip_L.Visible = True
Me.Post_Waist.Visible = True
Me.Post_Hip.Visible = True
Me.Post_Ratio.Visible = True
Me.Total_positive_results.Visible = True
Me.Post_Activity_Levels_Week.Visible = True
Me.Post_Behaviour.Visible = True
End If

If Me.NonStarter = "N/A" And Me.Non_completer <> "N/A" Then
Me.DropOutQSent = False
Me.cmdDropOutLetterToWord.Visible = False
Me.cmdDropOutQToWord.Visible = False
Me.DropOutQSent.Visible = False
Me.Pre_RHR.Visible = True
Me.Pre_SBP.Visible = True
Me.Pre_DBP.Visible = True
Me.Pre_BF.Visible = True
Me.Pre_BMI.Visible = True
Me.Pre_Flexibility.Visible = True
Me.Pre_Peakflow.Visible = True
Me.Pre_Grip_R.Visible = True
Me.Pre_Grip_L.Visible = True
Me.Pre_Waist.Visible = True
Me.Pre_Hip.Visible = True
Me.Pre_Ratio.Visible = True
Me.Pre_Activity_Levels_Week.Visible = True
Me.Pre_Behaviour.Visible = True
Me.TwelveWeekReview_Label.Visible = True
Me.txt12WkDate.Visible = True
Me.cmd12WkDate.Visible = True
Me.Non_completer.Visible = True
Me.DropOutQSent2.Visible = True
Me.cmdDropOutLetterToWord2.Visible = True
Me.cmdDropOutQToWord2.Visible = True
Me.TwelveWeeksCompleted = False
Me.TwelveWeeksCompleted.Visible = False
Me.TwelveWeekQReceived = False
Me.TwelveWeekQReceived.Visible = False
Me.Post_RHR.Visible = False
Me.Post_SBP.Visible = False
Me.Post_DBP.Visible = False
Me.Post_BF.Visible = False
Me.Post_BMI.Visible = False
Me.Post_Flexibility.Visible = False
Me.Post_Peakflow.Visible = False
Me.Post_Grip_R.Visible = False
Me.Post_Grip_L.Visible = False
Me.Post_Waist.Visible = False
Me.Post_Hip.Visible = False
Me.Post_Ratio.Visible = False
Me.Total_positive_results.Visible = False
Me.Post_Activity_Levels_Week.Visible = False
Me.Post_Behaviour.Visible = False
Me.SixMonthReview_Label.Visible = False
Me.cmdReviewLetterToWord.Visible = False
Me.cmdReviewQToWord.Visible = False
Me.SixMonthQSent = False
Me.SixMonthQSent.Visible = False
Me.SixMonthQReturned = False
Me.SixMonthQReturned.Visible = False
Me.SixMonthActivityLevelsWeek.Visible = False
Me.SixMonthBehaviour.Visible = False
Me.TwelveMonthReview_Label.Visible = False
Me.TwelveMonthQSent.Visible = False
Me.TwelveMonthActivityLevelsWeek.Visible = False
Me.TwelveMonthBehaviour.Visible = False
End If

If Me.TwelveWeeksCompleted = True Then
Me.Line.Visible = True
Me.SixMonthReview_Label.Visible = True
Me.cmdReviewLetterToWord.Visible = True
Me.cmdReviewQToWord.Visible = True
Me.SixMonthQSent.Visible = True
End If

If Me.TwelveWeeksCompleted = False Then
Me.Line.Visible = False
Me.SixMonthReview_Label.Visible = False
Me.cmdReviewLetterToWord.Visible = False
Me.cmdReviewQToWord.Visible = False
Me.SixMonthQSent = False
Me.SixMonthQSent.Visible = False
Me.SixMonthQReturned = False
Me.SixMonthQReturned.Visible = False
Me.SixMonthActivityLevelsWeek.Visible = False
Me.SixMonthBehaviour.Visible = False
Me.TwelveMonthReview_Label.Visible = False
Me.cmdReviewLetterToWord2.Visible = False
Me.cmdReviewQToWord2.Visible = False
Me.TwelveMonthQSent = False
Me.TwelveMonthQSent.Visible = False
Me.TwelveMonthQReturned = False
Me.TwelveMonthQReturned.Visible = False
Me.TwelveMonthActivityLevelsWeek.Visible = False
Me.TwelveMonthBehaviour.Visible = False
End If

If Me.SixMonthQSent = True Then
Me.SixMonthQReturned.Visible = True
Me.TwelveMonthReview_Label.Visible = True
Me.cmdReviewLetterToWord2.Visible = True
Me.cmdReviewQToWord2.Visible = True
Me.TwelveMonthQSent.Visible = True
End If

If Me.SixMonthQSent = False Then
Me.SixMonthQReturned = False
Me.SixMonthQReturned.Visible = False
Me.SixMonthActivityLevelsWeek.Visible = False
Me.SixMonthBehaviour.Visible = False
Me.TwelveMonthReview_Label.Visible = False
Me.cmdReviewLetterToWord2.Visible = False
Me.cmdReviewQToWord2.Visible = False
Me.TwelveMonthQSent = False
Me.TwelveMonthQSent.Visible = False
End If

If Me.SixMonthQReturned = True Then
Me.SixMonthActivityLevelsWeek.Visible = True
Me.SixMonthBehaviour.Visible = True
End If

If Me.SixMonthQReturned = False Then
Me.SixMonthActivityLevelsWeek.Visible = False
Me.SixMonthBehaviour.Visible = False
End If

If Me.TwelveMonthQReturned = True Then
Me.TwelveMonthActivityLevelsWeek.Visible = True
Me.TwelveMonthBehaviour.Visible = True
End If

If Me.TwelveMonthQReturned = False Then
Me.TwelveMonthActivityLevelsWeek.Visible = False
Me.TwelveMonthBehaviour.Visible = False
End If

If Me.TwelveMonthQSent = True Then
Me.TwelveMonthQReturned.Visible = True
End If

If Me.TwelveMonthQSent = False Then
Me.TwelveMonthQReturned = False
Me.TwelveMonthQReturned.Visible = False
Me.TwelveMonthActivityLevelsWeek.Visible = False
Me.TwelveMonthBehaviour.Visible = False
End If
End Sub

I can't see anything there that would cause this tho...

 

[Nero]

I would try commenting out different parts of your code at a time to see if it solves the problem. At least then you can better identify which part of the code is causing the problem.
If the problem still persists then you will need to look at other parts of your app.

 

[AncientOne]

Try single-stepping the On Current event and seeing when the autonumber appears. The form only becomes dirty when a field or combo box changes. There is an On Dirty event too, so you could set a breakpoint on that. Canceling the Dirty event will cause the changes to the current record to be rolled back. It is equivalent to pressing the Esc key.

 

[andy_dyer]

Sounds worth a go...

I have only ever deugged when told to by the database when something is wrong...

How can I set a breakpoint on dirty and then step thru?

Thanks

Andy

 

[AncientOne]

You can put a breakpoint at the first command in your On-Current . On single stepping, the code should branch when the On Dirty event occurs. If you put a debug.print command in the Dirtyevent and set a breakpoint there you'll have visual confirmation when the code has branched.

 

[andy_dyer]

Ok...

This makes no sense to me...

I did that and it immediately worked....

The autonumber stopped allocating immediately, BUT when you attempted to type anything an error comes up saying that it cannot find the macro "debug".

So i think oh, I'll take that out... I do that and it immediately goes back to setting the autonumber...

Put it back in, the autonumber stops but I get the error message...

Any ideas whats going on??

Thanks

Andy

 

[AncientOne]

Debug .Print must specify a variable to print of course. Debug.Print "I'm Dirty!" will do

 

[AncientOne]

More important, at which line of your code did the branch occur?

 

[andy_dyer]

Sorry I didn't make myself clear...

Just for having something there in that box stopped the autonumber calculating for me. (which is what I wanted!)

It only goes to appear when i start typing and obviously at that point the record is dirty.

I cannot leave something in the On_Dirty field otherwise I will never be able to input anything!

Does that make more sense?

Andy

 

[andy_dyer]

I'm sorry...

I've very little knowledge about VBA and no knowledge whatsoever about debugging, and stepping through...

I've set a breakpoint at the start of On_current

I've set the On_Dirty property to debug.print "I'm Dirty" (although this still seems to not work right)

What am i supposed to do??

I am running Access 2000.

Sorry...

Andy

 

[AncientOne]

when the On Current event happens, the code will halt at the breakpoint. You then start to single step the code. After the line of code that "Dirties" the form, you will branch to the code in the "On Dirty" event. If you stop single stepping at that point, you will know at what Current Event line the Dirtiness occurs. If you continue stepping, you will resume at the next line of the On current Event and the autonumber will increment, probably at the end of the Current Event. If the code stops with an error the autonumber doesn't increment. That's the theory!

If Debug print is being problematical, put a MsgBox "I'm Dirty" which has the same effect.

Please don't apologise. I'm as baffled as you are!

PS- It could be that the form is dirtied before the current event occurs, in which case, you would not even get to the first breakpoint!

 

[andy_dyer]

Thanks AncientOne for all your help...

It's fixed!!

The problem branch of code I was having trouble with was:

If Me.NonStarter = "N/A" And Me.Non_completer = "N/A" Then
Me.DropOutQSent = False

These two combo boxes for non-starter and non-completer are set to N/A by default.

I only wanted these lines of code to run if the rest of the record was "dirty" so I tried changing the first line of code to;

If Me.Dirty = True And Me.NonStarter = "N/A" And Me.Non_completer = "N/A" Then

And it works!!

Thanks ever so much for your patience!

Andy

 

[andy_dyer]

Ok it's not fixed...

Well the first part of my problem is fixed but I now have a knock on error...

I have some records that are displaying as tho NonStarter was N/A and NonCompleter was anything else except N/A, when indeed they are both N/A.

Being a little suspicious I implemented the OnDirty check and true enough although these records are stuffed full of information they do not recognise as "Dirty"....

So therefore they are not implementing the If dirty and If N/A and If N/A code sequence because they are not being recognised as Dirty.

Any suggestions for this one?

Thanks

Andy