Help with receipt numbering!

[jlm722]

ok folks, I'm gonna try to make as much sense out of this as i can. I had a db written for me so i could cash checks in my small business and keep up with the records of such. However, the person who designed the db has vanished and will no longer support it. I know just enough about access to be dangerous

) So, here's my dillemma. I had the db configured to where my receipt numbering worked like this; on today, September 22, 2009, the first receipt number generated is 90922001: (the first 9 being the year, the 09 being the month, the 22 being the day, and 001 being the first check cashed today) (actually the format is yymmdd, but for some reason the 0 doesn't show on the year 09). Here's where my problem begins. One year ago i started using this db. on september 21, 2008 i cashed two checks. (receipt #'s 80921001 & 80921002). yesterday, on september 21, 2009 i cashed a check and it picked up from where the 2008 receipts left off. ie: it numbered the receipt 80921003. I'm gonna post the portion of code that does this. Can someone please look and tell me what is screwed up in here to cause this?

'**If all is OK, now assign sequential number
maxdate = Me.txtcashdate
char2 = Format(Me.txtcashdate, "mmdd")
'===new per jody
Dim xan As String
Dim howsql
Dim rshow
howsql = "Select * from a_datescheck"
Set rshow = CurrentDb.OpenRecordset(howsql)
'Dim dayx As Long
'dayx = DateDiff("d", rshow.lastdate, Now())
If rshow.RecordCount > 0 Then
xan = rshow.nextnumber + 1
Else
xan = Format(Me.txtcashdate, "yymmdd") & "001"
End If

p.s. i noticed that 3 lines down, it shows char2 = Format(Me.txtcashdate, "mmdd"). i tried changing to yymmdd, but it appears that it caused the code to jump to line 15 (xan = Format(Me.txtcashdate, "yymmdd") & "001") because it tries to number everyreceipt as 90921001. which in this case attempts to duplicate a primary key (receipt number) and causes an error. PLEEEEASE HELP!!! much thanks in advance!

 

[Mr. B]

First, it is not clear where the "xan" variable may be being used, but it appears that this is the variable that should be holding your next receipt number.

Place a single quote at the beginning of the line:
xan = rshow.nextnumber + 1
This is just to keep it there in the event that you need to go back to the original code for any reason. (Saftey first.)

Make a new link just below that line and place the following line in this space:
xan = Format(Me.txtcashdate, "yymmdd") & Str(rshow.nextnumber + 1)

The line of code above is "air code" and is not tested. This is just my best guess as to a guick fix for the problem.

 

[jlm722]

Mr. B, well, thanks for replying, but that didnt help either. do you by any chance know what the line means that says: char2 = Format(Me.txtcashdate, "mmdd")
i may be wrong, but i think some of my problem comes from here. why does it say mmdd? it's like it is looking at the month and day and ignoring the year. but if i change mmdd to anything else (yymmdd or mmddyy) it doesnt work at all.

 

[Mr. B]

You are correct that the line:
char2 = Format(Me.txtcashdate, "mmdd")
is assigning a value to the variable "char2". It is formatting the value in the text box named "txtcashdata" to just provide the two character of the month and the two characters of the day. There is no reference to the year.

Can you find where the value being stored in the "xan" variable is being used? If all else fails, can you post your database file so we can look at the entire process, or at least enough of it so we can see the entire picture.

It still appears to me that there just has to be more code that what we are seeing here. This code does not do all of what you are describing.

 

[jlm722]

Yes sir! I am at lunch for a minute. As soon as I get back I will post all of it. You are my hero! Thank you so much for taking your time for me

 

[Mr. B]

No problem, we'll see if we can figure it out.

 

[jlm722]

ok, here are the files. Im attaching 4 files. They get placed in a folder called cashnchecks, inside a folder called databases. ie: c:\databases\cashnchecks. also the pictures folder has to be placed in the cashnchecks folder as well. If it's not there, this db will not work. if you'll use test as a password, there are two test customer records. click on check cashing, choose your customer, then choose a check type when prompted. fill all the red fields in and click submit. tell it NO on printing the receipt. then go to the main screen again and choose reports menue, daily transactions, and you will see how it's numbering the receipts. Thanks again, hope i havent left anything out. p.s. the ccdata and checkdata both have tables linked to the cc.mdb. zipped it's 2696.kb. can i send that?

 

[jlm722]

here are three of the 4 files, the other i sent to your email on the doctor access web page

 

[Mr. B]

I have downloaded all three zip files and tried to find any form in any db. There is not a form in either of the mdb files. There are tables. I was not prompted for any password. I placed the files in the path you stated they should be in. C:\databases\cashnchecks

Am I missing something?

 

[jlm722]

yes. the main frontend db is too large to attach here, i sent it to an email at askdoctoraccess

 

[Mr. B]

Ok, I'll watch for it. I have not received it as yet, but I will be on the watch for it.