Please Help 2

[sotghalz]

Hello, I need help with part of a script that I've been stuck on for a few days.

First, I need an inputbox that will ask if the project is a NEW or a REVISION.

Second, If NEW is typed then the script needs to browes or navigate to a folder structure and read part of a folder name which is a 5 digit number long and pick the next availble number.

Third, if REVISION is typed then script resumes.

This is what I have so far.

'Do
' TryAgin = "No"

' NewRevision = InputBox("Is this a NEW project or a Revision?")

' If NewRevision = "" Then
' MsgBox "You must enter in New or Revision."
' TryAgain = "Yes"
' End If

'Loop While TryAgain = "Yes"



Thanks

 

[PHV]

I meant VBScript doesn't have a Format function ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sotghalz]

MakeItSo,

Why in this example are we calling for Project Serial Number then in the WHERE part we want the year?

Dim maxnum, RStemp, NewNum
Set objConnection = CreateObject("ADODB.Connection")
'your connection goes here
....
Set RStemp=objConnection.execute("SELECT MAX([Project Serial Number]) as m FROM [yourtable] WHERE [Year]=" & MyArr(0))
maxnum=format(RStemp.Fields("m"),"00000")
NewNum=maxnum+1
Set RStemp=nothing

Same thing here:
Why are we SELECT Max Revision and then in the WHERE we put Project Serial Number?

Dim NewRev
Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m] FROM [yourtable] WHERE [Project Serial Number]='" & MyArr(1) & "';")

In other word, What does the WHERE statement mean?

PH,

Then how do you you populate MyArr(1) with this:

Set RStemp=objConnection.execute("SELECT MAX([Project Serial Number]) AS m FROM [Master] WHERE [Project Serial Number]=" & MyArr(1))
maxnum=format(RStemp.Fields("m"),"00000")
NewNum=maxnum+1
Set RStemp=nothing

Do you have to put a MyArr(1) = somewhere?

I really appretiate that you both are helping me out.

Chase

 

[MakeItSo]

@PH: Oh! Bummer!

@Chase: The "WHERE" clause is a filter. That is:
SELECT Max([Project Serial Number] FROM [Master]....
==>Get the highest entry for PSN stored in the table Master.
...as m
==>And for shortness sake, let's call this maximum entry "m" in this query
....WHERE [Year]=" & MyArr(0))
Only query records, in which the field Year equals the value of MyArr(0).
So, you might have a project number 55556 for projects of 2006, but for the year 2007, your highest project number so far is 22224.
In that case
SELECT MAX([Project Serial Number]) as m FROM [Master] WHERE [Year]=2007
will return 22224.

Same with revision:
As the revision is always an existing project revised, you want the respective highest revision for that project.

You might have revision H for project 12345, and revision E for project 12344.
The user enters 12344, so you want the next possible revision letter for that specific project.
SELECT Max(Revision) AS [m] FROM [Master] WHERE [Project Serial Number]=12344
will therefore return "E".

NewRev=Chr(ASC(RStemp.Fields("m"))+1)
==>In words:
1. Take the ASCII value of the returned max revision m, in this case the value of "E". This value will be 69.
2. Add 1, you'll get 70
3. Assign the Character value of 70 to the variable NewRev.
4. That character value is "F", hence NewRev will contain the next available revision letter for project 12344.

For the new project number, just add

MyArr(1)=NewNum

right after the code lines you posted at the end.



[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[sotghalz]

I see your point and thank you for explaining "WHERE". The issue is that the Project Serial Number will never go back to 00001. By the time we would have reached 99999, which would be in about 100 years, a new technology would have come out and this script would be obsolete.

 

[sotghalz]

MakeItSo,

I get a "Type mismatch: 'format' error here:

maxnum=format(RStemp.Fields("m"),"00000")

Do you have an suggestions?

Chase

 

[MakeItSo]

Yeah, that's what PH meant.
Just leave away the "format" and use the conversion to long integer instead:

maxnum=clng(RStemp.Fields("m"))

[blue]Help us, join us, participate 
[url=http://www.freewebs.com/humanrightsadvocates/]IAHRA - International Alliance of Human Rights Advocates[/url][/blue]

 

[sotghalz]

That helped and let the script run to the end. The only thing now is it is not adding the Project Serial Number (#####) to the folder structure.

 

[sotghalz]

Nevermind I had MyArr(4)=NewRev in the wrong spot...although im not getting a five digit output (#####)from this.

 

[sotghalz]

MakeItSo,

OK....I have half the script working and it looks great. Thank you. The other half "Revision" part I have no clue.

From the first code you gave me

Dim Revision
 
Revision = (MsgBox("Is this a revised project? (<No> = New project)", vbYesNo + vbQuestion) = vbYes)
 
If Revision Then
    ' Enter your code to process revised projects here
Else
    'Here goes your code for new projects
End If

With this code as well

Dim NewRev
Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m] FROM [yourtable] WHERE [Project Serial Number]='" & format(inputbox("Please enter project number in format #####"),"00000") & "';")
If RStemp.Fields("m")<>"Z" then
   NewRev=Chr(ASC(RStemp.Fields("m"))+1)
End if
Set RStemp=nothing
MyArr(4)=NewRev

This takes care of MyArr(4)...

Where should I reference the rest of "MyArr" and have it create the new fodler? ie.

2008_00001_100Dollar_R_A = Original (from New Project part)
2008_00001_100Dollar_R_B = B is the next version/revision

Thanks Agian

You all have been a great help!

Chase

 

[sotghalz]

Good Morning:

This is what I have so far and I'm getting a few errors:

Format issue (type mismatch)

  Dim Years
  Dim Project
  Dim Denomination
  Dim Side
  
  'Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m] FROM [Master] WHERE [Project Serial Number]='" & format(inputbox("Please enter project number in format #####"),"00000") & "';")
    'If RStemp.Fields("m")<>"Z" then
       'NewRev=Chr(ASC(RStemp.Fields("m"))+1)
    'End if
  'Set RStemp=Nothing

How do I have this part of the script pull data from a specific line in the database identified in the first Revision question.

  Set RStemp=objConnection.execute ("SELECT Max([Year]) AS [a] FROM [Master]")
    Years=(RStemp.Fields("a"))
  Set RStemp=Nothing  
  Set RStemp=objConnection.execute ("SELECT Max([Project Serial Number]) AS [b] FROM [Master]")
    Project=(RStemp.Fields("b"))
  Set RStemp=Nothing  
  Set RStemp=objConnection.execute ("SELECT Max([Denomination]) AS [c] FROM [Master]")
    Denomination=(RStemp.Fields("c"))
  Set RStemp=Nothing  
  Set RStemp=objConnection.execute ("SELECT Max([Side]) AS [d] FROM [Master]")
    Side=(RStemp.Fields("d"))
  Set RStemp=Nothing

Works fine.

  MyArr(0)=Years
  MyArr(1)=Project
  MyArr(2)=Denomination
  MyArr(3)=Side
  'MyArr(4)=NewRev
  
  StrName = Join(MyArr, "_")
  
  StrName = InitPath & StrName & "\"
  
  Dim NewFolders
  Set filesys = CreateObject("Scripting.FileSystemObject")
  Set NewFolders = filesys.CreateFolder(StrName)
  For counter = 0 To UBound(MyPaths)
    Set NewFolders = filesys.CreateFolder(StrName & MyPaths(counter))
  Next

  intResults = MsgBox("The following directories have been successfully created: " & vbNewLine _
    & StrName & vbNewLine & strName & Join(MyPaths, vbNewLine & strName), vbOKOnly, "New Directories")

Thanks for your help.

Chase

 

[PHV]

Seems you didn't pay attention to my advice stamped 11 May 07 6:36.

 

[sotghalz]

Please remember that I am a beginner at VB and I just use what you guys give me and play around with it until it works, so I can't use a format option. Sorry, I missed that back on the 11.

Can I still have a user type in a number and have the script query a single line\field in a database and have the rest of the script grad the field data from that line?

 

[PHV]

Replace this:
& format(inputbox("Please enter project number in format #####"),"00000") &
with this:
& Right("00000" & InputBox("Please enter project number in format #####"),5) &

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sotghalz]

PH,

That worked out great.

Still an issue with this (highlighted in red):

  Dim Years
  Dim Project
  Dim Denomination
  Dim Side
  
  [COLOR=blue]Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m] FROM [Master] WHERE [Project Serial Number]='" & Right("00000" & InputBox("Please enter project number in format #####"),5) & "';")
    NewRev=Chr(ASC(RStemp.Fields("m"))+1)
  Set RStemp=Nothing[/color]
    [COLOR=red]Set RStemp=objConnection.execute ("SELECT Max([Year]) AS [a] FROM [Master]")
      Years=(RStemp.Fields("a"))
    Set RStemp=Nothing  
    Set RStemp=objConnection.execute ("SELECT Max([Project Serial Number]) AS [b] FROM [Master]")
      Project=(RStemp.Fields("b"))
    Set RStemp=Nothing  
    Set RStemp=objConnection.execute ("SELECT Max([Denomination]) AS [c] FROM [Master]")
      Denomination=(RStemp.Fields("c"))
    Set RStemp=Nothing  
    Set RStemp=objConnection.execute ("SELECT Max([Side]) AS [d] FROM [Master]")
      Side=(RStemp.Fields("d"))
    Set RStemp=Nothing[/color]  
  
  MyArr(0)=Years
  MyArr(1)=Project
  MyArr(2)=Denomination
  MyArr(3)=Side
  MyArr(4)=NewRev
  
  StrName = Join(MyArr, "_")
  
  StrName = InitPath & StrName & "\"
  
  Dim NewFolders
  Set filesys = CreateObject("Scripting.FileSystemObject")
  Set NewFolders = filesys.CreateFolder(StrName)
  For counter = 0 To UBound(MyPaths)
    Set NewFolders = filesys.CreateFolder(StrName & MyPaths(counter))
  Next

  intResults = MsgBox("The following directories have been successfully created: " & vbNewLine _
    & StrName & vbNewLine & strName & Join(MyPaths, vbNewLine & strName), vbOKOnly, "New Directories")

How do you make this code call information from the same line in the database that the Revision (highlighted in Blue) calls its information

Thanks again!

Chase

 

[MakeItSo]

Hi Chase,

a) it is useless setting RStemp to pull data and setting it back to nothing in the very next line.

b) Max([Project Serial Number]) does not make sense in case of a revision, as you do not pull the PSN from database, but have the user enter it.

c) Max (Denomination) does not make sense either, as the denomination, too, is directly specified via user entry.

d) I don't know about the side, but I don't think you want a Max value there either.

e) So, in fact, you probably only want to determine the next available revision number and else use user entries. That makes your entire red segment superfluous, doesn't it?



Cheers,
Andy

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[sotghalz]

Thanks for the info. I was trying to use what you gave me. As far as the revision part of the script I really want the user just to enter the serial number that needs the revision and the script do the rest as far as populating the MyArr statements with the information on the same line in the database and joining them together to create the new folder.

Thanks

Chase

 

[MakeItSo]

OK. In that case, replace the blue part with this:

psn=Right("00000" & InputBox("Please enter project number in format #####"),5)
Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m], Max([Year]) as y, Max([Denomination]) as d, Max([Side]) as s FROM [Master] WHERE [Project Serial Number]='" & psn & "';")

NewRev=Chr(ASC(RStemp.Fields("m"))+1)
Arr(0)=RStemp.Fields("y")
Arr(1)=psn
Arr(2)=RStemp.Fields("d") 'Arr(2) was Denomination, if I'm not mistaken
...
Set RStemp=Nothing

The red part is superfluous and can be deleted.

Cheers,
Andy

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[sotghalz]

MakeItSo & PH,

Thank you so much for getting me through this. I have learned a lot and really appreciate your help.

Thanks

Chase