Incremental number control in a form 4

[THWatson]

I have a form in which one field is a Receipt#. Is there a way to insert a control so that when you enter a new record the control looks up the receipt number of the previous record and then increments that number by 1, in order to produce the receipt number for the current record?<br><br>I can't use an AutoNumber field because only one such field is allowed, and it is already taken up by the AutoRecordID field.<br>Tom<br>

 

[RoyVidar]

I've used this code in the got focus event of the ID-field. It does the trick for me.<br><br>First I check if the field alredy has a value, if not I check whether there are any records at all, if not the first receipt get #1 else it gets the highest receipt# +1<br><br>If IsNull(Me!Receipt#) Then <br>&nbsp;&nbsp;&nbsp;If IsNull(DMax(&quot;Receipt#&quot;,&quot;tblYourTable&quot) Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me!Receipt# = 1<br>&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Me!Receipt# = DMax(&quot;Receipt#&quot;, &quot;tblYourTable&quot + 1<br>&nbsp;&nbsp;&nbsp;End If<br>End If<br><br><br><br>

 

[THWatson]

RoyVidar<br>Thanks. I will give it a try. I appreciate your help.<br>Tom<br>

 

[elizabeth]

BTW, if it is a receipt table and each is unique, you can use the AutoRecordID as the Reciept number too. <br>me.receipt=autorecordid

 

[THWatson]

Thanks, Elizabeth.<br>Your suggestion is a way I never even contemplated. What I will do is to make the AutoRecordID start its incremental steps at a higher number, so that receipts coming out of this particular form don't get confused with our &quot;more regular&quot; receipts...as the receipt that issued can be used for income tax purposes.<br>Thanks again.<br>Tom

 

[THWatson]

Roy and Elizabeth<br>I have tried the formulas you gave me, but it seems that no matter what I do I end up with an error message saying &quot;Cannot find receipt field.&quot; I am new to VBA programming, and sort of groping my way through this stuff, so it would appear I am doing something wrong somewhere, but it's not clear to me where.<br><br>Additionally, I thought - based on Elizabeth's reply - that one approach would be just to plug the RecordID number into the receipt# field, and that works on the form, but it won't place the value back in the table. As a matter of fact, I have had that difficulty with other forms. Why doesn't a derived value get transferred back into the table from the form?<br>Tom

 

[Kimby]

I'm trying create an increment of 001-01, 001-02, ... 001,10... 002,01.&nbsp;&nbsp;can you help??

 

[RoyVidar]

THWatson<br>Some troubleshooting. <br>Be sure that the control representing your Receipt# has a valid controlsource, which tells Access where to store your value.<br>Next ensure that the controls name is the same name as you are referring in your code.<br>Then try again, please report back, and we will try to assist;-)<br><br>BTW I find it easyer to read and maintain code when using a naming convention. I always name any textboxcontrol on a form starting with &quot;txt&quot; (Referring to &quot;Me!txtReceiptNo&quot<br><br>Roy-Vidar

 

[THWatson]

RoyVidar<br>My table is called &quot;tblMiscellaneousDonations&quot; and it includes the following fields...<br>RecordID (autorecordID)<br>LastName<br>FirstName<br>Address (separate fields for street, city, province, postal code)<br>ReceiptNumber (which I changed from Receipt# in case the # sign was problematic)<br><br>My form, where I enter data is based on this table, and has identical fields.<br><br>What I have wanted to do was to have the ReceiptNumber field control automatically generate a receipt number, based on the receipt number in the previous record + 1.<br><br>Following your reply, I have checked to be sure the control names are consistent and I can see no problems.<br><br>Here is the code I have tried in the &quot;Gof Focus&quot; event for the form.<br>Option Compare Database<br>Option Explicit<br>Private Sub Form_GotFocus()<br>If IsNull(Me!ReceiptNumber) Then<br>If IsNull(DMax(&quot;ReceiptNumber&quot;, &quot;tblMiscellaneousDonations&quot) Then<br>Me!ReceiptNumber = 1<br>Else<br>Me!ReceiptNumber = DMax(&quot;ReceiptNumber&quot;, &quot;tblMiscellaneousDonations&quot + 1<br>End If<br>End If<br>End Sub<br><br>I have also tried this form in the &quot;Got Focus&quot; event for the ReceiptNumber control itself.<br><br>I have done programming in Basic and QuickBasic, but am new to Visual Basic, and am trying to learn VBA to manage Microsoft Access better. Therefore I feel that I am doing something wrong in the above code, since I can't get anything to work at all. If you could point out my error, I would be grateful.<br>Thanks.<br>Tom

 

[RoyVidar]

There's one thing, If you use this code, placing it in the forms got focus event isn't the right place. Sorry if I've been misguiding you there. <br><br>You could place it in the forms on current event or on the controls (ReceiptNumber) got focus event.<br><br>Other than that, I'm not sure if I can give any specific help. There are some things though;<br><br>I'm not familiar with the &quot;autorecordid&quot;, suggested by Elizabeth, so if you also use this somewhere, there might be anomalities.<br><br>If this is a subform, try it as a &quot;stand alone&quot; form.<br><br>You might also try other things to ensure that the code is triggered, and performing, set a &quot;breakpoint&quot; in your code (If you're not familiar with this; click on the grey part just to the left of the codeline where you want the break, this should highlight the line of code and you might check on the values of the controls, use Step Into (F8) to excecute the code line by line)<br><br>You might try to set the value excplicit; <br><br>Private Sub txtReceiptNumber_GotFocus()<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!ReceiptNumber = 1<br>End Sub<br><br>Hope some of these advices are of assistance, the code I posted, does work in numerous of my apps, so I...<br><br>Good luck<br><br>Roy-Vidar<br>

 

[THWatson]

RoyVidar<br>Well, at long last I got it to work. Since you were so certain about the code, I knew it had to be right but just couldn't get it to work. So I created a brand new table with only 2 fields - a LastName and a ReceiptNumber. But I couldn't get it to work there either...until I set the ReceiptNumber field so that it was &quot;required&quot; and &quot;Indexed with no duplicates.&quot; Then it worked! That was, therefore, my problem in the main form.<br><br>Back in the main form in which I was working, I did encounter one anomaly. Since I didn't want receipt numbers to start at 1, I tried plugging a new number in the form. Wrong move. From then on I got that number every time I went to a new record. But that went away when I exited Access and came back in. Interesting the way these programs work.<br><br>So what I think I will do is hide the ReceiptNumber field so that the person entering date can't, inadvertently, put a number in that field. That will it will be fully automatic but hidden from view.<br><br>By the way, the code seems to work with either the Bang &quot;!&quot; operator, that you suggested, or the &quot;.&quot; operator. Can you explain to me the difference in the two, which should be used where, and why they both work in this case?<br><br>Thanks for all your help.<br>Tom

 

[RoyVidar]

Hi Tom!<br><br>Who hasn't tried to understand dots &quot;.&quot; versus bangs &quot;!&quot;.<br><br>Referencing a forms control, would look something like this;<br>Forms!YourForm.Controls!YourControl<br>Since Controls are the default collection, you can shorten it to;<br>Forms!YourForm!YourControl<br><br>In general you follow the bang &quot;!&quot; with something you created, a form, report or control. You also follow the bang with an item of a collection.<br><br>You usually follow the dot &quot;.&quot; with a property, collection or method name.<br><br>Me!MyControl.visible = True<br><br>I'm one of, at least according to this forum, few people being happy with using Access 2000. There you can bypass the whole dot vs bang problem by using Quotes &quot;&quot; (cause I sometimes had the feeling this could drive completely mad, especially cause it seems you can use both for the same job;-)<br><br>Previous excamples;<br>Forms(&quot;YourForm&quot.Controls(&quot;YourControl&quot<br>Forms(&quot;YourForm&quot(&quot;YourControl&quot<br>Me(&quot;MyControl&quot.visible = False<br><br>NB - this does not apply to queries though, but the built in &quot;Build&quot;-thingy helps you out there.<br>(Access 2000 Developers handbok by Getz, Litwin and Gilbert)<br><br>I'm afraid I can't explain exactly why both operators seem to work in the same case. Perhaps other members of the forum might shed some light? <br><br>Roy-Vidar

 

[THWatson]

Thanks, Roy.<br>Tom