[mat11281]

Hi Scott - I was talking to you on the allexperts

I am still having some issues. Can you pm me your email?

[ScottGem]

Matt,

This is about the Sequential Numbering, FY & number? I reserve direct e-mail for people who contract for my services on a fee basis. But I would be happy to continue to help you through this forum (or All Experts). I asked you some questions in my last response on (I've pasted it in below). Can you please respond to them.

QUESTION: Thanks for taking the time and working with me on this. Everything about Access I have studied on my own and asked my IT for some advice but nobody knows too much about Access and my Agency doesn't want to spend $500 to send me to an Access Class. In your blog I'm looking at Scenario 2 or Scenario 3 whichever one will be best. Here is what I'm attempting The database consists of a main table titled "Inspections" This is where all the Physical Security Inspections are stored. The Autonumber is the primary key but it's the Inspection Number that needs to have the Fiscal Year and the actual Inspection Number. "FY15-XXX" and/or now that we are in FY 2016 "FY16-XXX" The Fiscal Year is always from OCT 1 - SEP 30 The Fields that I have in the "Inspections" Table are ID InspectionNumber Location ServiceLine Division Inspector Building Room PointofContact Compliant Status (Open, Closed, Submitted for Project) SurveyDate NextAnnualSurveyDate Notes Attachments In my "Inspection Details" Form I have a field for the Inspection Report Number but I have not been successful in programming it to generate the report number correctly. even when I use the DMax code. The part of your blog that is throwing me off is the scenarios that you are giving are not related to my line of work and I'm still having a little trouble wrapping my head around the database concept, I'm used to Excel.

Answer:

Ok, First this site limits the amount of follow up. So if we get cut off we can pick this up in the Access forum at Askmehelpdesk.com I don't see the Sequence field the blog instructs you to add to your table. Also, what field determines the Fiscal Year? SurveyDate? Some other points. As the blog states the Inspection Number shouldn't be stored since its calculated from the Sequence number and other data that exists in the table. Name your PK InspectionID, don't leave it as plain ID. If NextAnnualSurveyDate is one year from the SurveyDate, then that field should also not exist either, Finally, Most of your fields appear to be items that should be selected from lookup tables. So they should be foreign Keys from those lookup tables.

[mat11281]

No problem - I really do appreciate the assistance as this problem has been stumping me for the better part of September.

I added the Sequence field and created a control for it on the form. Where do I enter the code under the tags section?

The Survey date does determine the Fiscal Year

I renamed the ID = InspectionID.

Most of the other fields are related to other tables. Divisions, Locations, Service Lines. I also have a table for deficiencies that is a 1 to many relationship with the main Inspections Table.

[ScottGem]

I added the Sequence field and created a control for it on the form. Where do I enter the code under the tags section?

This is the one issue I can't definitively answer. What I can tell (as the blog says) is that you need to commit the record immediately after generating the sequence number. So you don't want to do it too soon. My recommendation is to have a button on the form that generates the Sequence #. Behind that button is the DMAX command followed by Me.Dirty = False.

The second problem you have is in generating the Fiscal Year. Since it is determined by the Survey Date I would create a function to return it.

Public Function FY(dteSurvey As Date) As String

Dim intMonth As Integer, intYear As Integer


intMonth = Month(dteSurvey)
intYear = CInt(Format(dteSurvey, "yy"))


If intMonth = 10 Or intMonth = 11 Or intMonth = 12 Then
     intYear = intYear + 1
     FY = "FY" & intYear
Else
     FY = "FY" & intYear
End If

End Function

What you need to do is create a Global module (Press Ctrl+G) and select to add a new module. Then paste the code above into it EXACTLY as shown. The will return FYxx for the fiscal year. You use this like any function:

= FY(datevalue)

for example FY(Date()) will return FY16, FY(#9/15/15#) will return FY15.

You need to use this in your DMax

Me.Sequence = Nz(DMAX("[Sequence]","Inspections","FY([SurveyDate]) = '" & FY(Me.SurveyDate) & "'"),0)+1

Inspections is the name of your table and Me.surveyDate the name of the control bound to the Survey Date field.

That should then work, giving you the next number depending on the Fiscal Year.

[mat11281]

I'm trying to figure this out but I'm sorry I am just not able to understand this.

This is the one issue I can't definitively answer. What I can tell (as the blog says) is that you need to commit the record immediately after generating the sequence number. So you don't want to do it too soon. My recommendation is to have a button on the form that generates the Sequence #. Behind that button is the DMAX command followed by Me.Dirty = False.

I have created a text box field for the InspectionNumber. From reading the blog this is what I need to populate that box.

Me.InspectionNumber = Nz(DMax(“[InspectionNumber]”,”Inspections”),0)+1

Where do I put that code at? Does it go into the Default Value under the Data Tab in the Property Sheet?


I then created a Option button and bound that to the InspectionNumber with the default value being Me.InspectionNumber = Nz(DMax(“[InspectionNumber]”,”Inspections”),0)+1.

In the Form I click on that option it generates a -1 in the InspectionNumber Text Box. If I press the Option Button again it goes to 0 no higher.

[ScottGem]

First, that means you are using InspectionNumber for the field I've labeled Sequence. That is fine.

I'm not sure how else to explain this. To assign the number you have to trigger some code to run. Just like a gun doesn't fire unless someone pulls the trigger. The problem here is when to pull the trigger. Unfortunately, I can't decide that for you. I can ( and have) suggested that you trigger it by pressing a button. So the code would be placed in the On Click event of that button. If you can't decide on a better trigger, then use my suggestion.

The reason your code isn't working is because its not what I said. You are not saving the record. Therefore the record is not updating so the Max value is never changing. I've said that you need to save the record immediately after generating the number.

Another issue is you want the sequence to start anew with each Fiscal year. But you aren't using a filter for the Fiscal year. Look at my precious response. I gave you very specific instructions.