|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
June 23rd, 2009, 10:13 PM
|
|||
|
|||
Forms - Generating job number depending on numerous selections
Ok I will try keep this short, but I'm struggling!
I have customers and jobs. (1 to many) Every job will have it's own job number but this then needs to be shared over the months depending on the following: When raising a job, the user will select [Period] whether the maintenance is to be completed Annually, quarterly, monthly User will then select the month that the maintenance commences. Under that is a table of Jan through to Dec where the user inputs data (hours on site etc) and under each month is the job number Each month will have the same job number but will be pre and post fixed from the selections above. The problem I'm faced with is my code going to be huge, as the below code will have to be replicated for each month. Is there a way I can shorten this? A better way of coding it? This code is for if january is selected only If Me.Month_Commenced.Value = "Jan" Then If Me.Period = "Quarterly" Then Me.Janjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "1" Me.Apjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "2" Me.Juljob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "3" Me.Octjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "4" 'along with resetting all other months to "" Else If Me.Period = "Annual" Then Me.Janjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "1" 'along with setting all other months to "" Else If Me.Period = "Monthly" Then Me.Janjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "1" Me.Febjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "2" Me.Marjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "3" Me.Apjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "4" Me.Mayjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "5" Me.Junjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "6" Me.Juljob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "7" Me.Augjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "8" Me.Septjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "9" Me.Octjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "10" Me.Novjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "11" Me.Decjob_ = Me.Jobtypeprefix & Me.Autonumber & (Left$([Period], 1)) & "12" End If End If End If End If Hope this makes sense. thanks in advance
|
|
#2
June 24th, 2009, 01:14 AM
|
|||
|
|||
|
First problem I see is with the Monthly identifiers:
"M12" will sort before "M9", should use filler zeros, make it "M09". As for whether there is something better for the whole works, will have to study for a while. Might help to know why you feel this strucure necessary, how you will use the entity. Last edited by June7 : June 24th, 2009 at 01:17 AM.
|
|
#3
June 24th, 2009, 10:30 AM
|
|||
|
|||
|
Like June said, If you want to use the month as part of your job numbering system I'd really recommend you use a leading 0 for any single digit months. You'll find it easier to deal with. Secondly it looks to me like you want to store your data like this:
Code:
Customer Jan Feb Mar Apr May Jun --------> remaining months Cust A JN1 JN2 JN3 JN4 JN5 JN6 ------> Cust B JN1 JN2 ------> Cust C JN1 JN2 ------> Cust D JN11 JN12 JN1 JN2 JN3 JN4 ------> Where Customers A and D are monthly, B and C are quarterly When you really probably should be storing the data like: Code:
Customer Month JN Cust A 1 JN1 Cust A 2 JN2 Cust A 3 JN3 (remaining cust A jobs) Cust B 1 JN1 Cust B 4 JN2 (remaining cust B jobs) Cust C 2 JN1 Cust C 5 JN2 (remaining cust C jobs) etc Storing your information the first way is a lot harder to deal with if you're searching for a particular job or really doing any sort of queries against this table, it would probably involve a lot of union queries. The second way is pretty efficient in terms of searches and queries and overall will make your code a lot easier to run. That being said here is an example to do what you want.
__________________
---------------- If we've helped you and you have solved your problem please post that it's been resolved so we know! The suspense kills me!
|
|
#4
June 29th, 2009, 08:22 PM
|
|||
|
|||
|
ok I finally worked out that it was a db for vista version, so I finally managed to open it.
But yes it's near what I want, very clever!  I couldn't access the design view of the form or tables tho, not sure if it's protected?? Sorry I can't work bloody vista out! Was after the code for it if that's ok Thank you soooo much for your help too
|
|
#5
June 29th, 2009, 11:19 PM
|
|||
|
|||
|
I didn't protect any of the content in the database. You should be able to get to the design of the tables even if it tells you you can't make modifications you should be able to get past that and see the design, just not modify them (if you have the data entry form open)
|
|
#6
June 30th, 2009, 03:22 AM
|
|||
|
|||
|
Quote:
the design tabs are just greyed out no matter what I click on, it's really bizare. Is there anything else I can try?? Really sorry to be a pain about this, but it's spot on for what I need and have no idea how you programmed it Thanks again
|
|
#8
July 2nd, 2009, 08:53 AM
|
|||
|
|||
|
Ok here it is. In case I've botched the conversion the code behind the buttons is:
Code:
Option Compare Database
Private Sub Cmd_FillJobs_Click()
Dim sPeriod As String
Dim iMonth As Integer
Dim iGap As Integer
Dim sMonth As String
Dim sFieldName As String
Dim iIterations As Integer
Dim iNextMonth As Integer
Dim sNextField As String
sPeriod = Period
iMonth = Month_Commenced
'lookup the gap between jobs
iGap = DLookup("[MonthGap]", "Tbl_Periods", "[Period] = '" & Me.Period & "' ")
'lookup the label for the month chosen
sMonth = DLookup("[MonthName]", "Tbl_Months", "[MonthNum] = " & Me.Month_Commenced & " ")
'go to the first field that needs a job entered
sFieldName = sMonth & "job_"
DoCmd.GoToControl ("" & sFieldName & "")
Me.ActiveControl = Left(sPeriod, 1) & "01"
'Generate the number of jobs to be filled
iIterations = 12 / iGap
iNextMonth = iMonth
For iIterations = 1 To iIterations - 1
If iNextMonth + iGap > 12 Then
iNextMonth = iNextMonth + iGap - 12
Else
iNextMonth = iNextMonth + iGap
End If
sNextField = DLookup("[Monthname]", "tbl_months", "[MonthNum] = " & iNextMonth & " ") & "Job_"
DoCmd.GoToControl ("" & sNextField & "")
Me.ActiveControl = Left(sPeriod, 1) & Right("0" & iIterations + 1, 2)
Next iIterations
End Sub
Private Sub Cmd_Clear_Click()
JanJob_ = Null
FebJob_ = Null
MarJob_ = Null
AprJob_ = Null
MayJob_ = Null
JunJob_ = Null
JulJob_ = Null
AugJob_ = Null
SepJob_ = Null
OctJob_ = Null
NovJob_ = Null
DecJob_ = Null
End Sub
|
|
| Viewing: ASP Free Forums > Database > Microsoft Access Help > Forms - Generating job number depending on numerous selections |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
