?
Solved

Convert autonumber Access VB script to CRM JavaScript

Posted on 2012-08-16
7
Medium Priority
?
635 Views
Last Modified: 2012-08-20
Hi Experts,

I need some help converting some Access Code to JavaScript for the salesorder entity in a Microsoft Dynamics CRM database.

This code was created for me by ExpertsExchange a couple of years ago and has worked very well for our database. Now, we are migrating to Microsoft Dynamics CRM and I would like to use this code or something like it to generate an auto-incremented number based on a prefix of either MA or MS for business unit 12. The prefix is determined by the job type (vsc_jobtype). In CRM this is an option set field with the following values: EP (865,420,007), Fire Alarm (865,420,003), and Special Hazards (865,420,006).

I anticipate having to modify the script down the road as other offices are added to CRM.

Here’s what I have from the Access database:

This is for the alarms jobs

     Private Sub Form_BeforeInsert(Cancel As Integer)

         Me!JobNo = CreateAutonumber("MA", 4)
         Me.Dirty = False

     End Sub
 

This is for EP and Special Hazards jobs

      Private Sub Form_BeforeInsert(Cancel As Integer)

         Me!JobNo = CreateAutonumber("MS", 4)
         Me.Dirty = False

      End Sub

Thanks for your help.
0
Comment
Question by:jlblanke
  • 4
  • 3
7 Comments
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 38303577
Hi jblanke,

It is difficult to convert this as it is. It has a missing functions call: CreateAutonumber

and also Me denotes this i.e. you will have to define a type in JavaScript and then implement the properties like JobNo, Dirty and so on. I am adding few more tags to the topic and lets hope if someone else can help you out.

Regards,
Chinmay.
0
 

Author Comment

by:jlblanke
ID: 38305810
Thanks for responding so quickly! :)

You are correct. I remembered there was another part to the code and couldn't find it yesterday. I found it just now and have added the second part:

= = = = = = = = =

Private Sub Job_No_AfterUpdate()

    Me!JobNo = CreateAutonumber("MA", 4)
    Me.Dirty = False

    Dim strAutonumber As String 'strAutonumber will hold our final result
    Dim rst As DAO.Recordset    'rst is a general recordset variable which we will use to retrieve existing data from our table
    Dim strTemp As String       'strTemp is a temporary string which we'll use to store information

Set rst = CurrentDb.OpenRecordset("SELECT Max([JobNo]) As MaxJobNoFROM Jobs WHERE [JobNo] Like " & Chr(34) & strPrefix & "*" & Chr(34))

If IsNull(rst!MaxJobNo) Then
   strAutonumber = strPrefix & Format(1, String(lngDigits, "0"))
    Else
        strTemp = Mid$(rst!MaxJobNo, Len(strPrefix) + 1)
       strAutonumber = strPrefix & Format(Val(strTemp) + 1, String(lngDigits, "0"))
    End If
rst.Close
Set rst = Nothing
CreateAutonumber = strAutonumber

End Sub
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 38306098
Sorry jblanke I will not be able to help you with this script.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:jlblanke
ID: 38306307
Thanks Chinmay. Do you think there will be anyone else who can help?
0
 
LVL 27

Accepted Solution

by:
Chinmay Patel earned 2000 total points
ID: 38307248
No. Also I could not add new topics and tags to this post. I suggest you do it and add topics and tags like VBScript, JavaScript to get attention of the right peope.
0
 

Author Closing Comment

by:jlblanke
ID: 38312377
Thanks Chinmay - I'll repost with the tags that you have suggested.
0
 
LVL 27

Expert Comment

by:Chinmay Patel
ID: 38312393
Oh no.. you shouldn't assign points if the question is not answered, you could modify the tags as it is your question.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part II
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question