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

 

[MakeItSo]

Hi!

1) I recommend to let the user CHOOSE rather than type. This way you won't get unexpected results through typos...

==>

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

2) You say "read PART of a folder name". Which part? Start of it? All folders on same level?

Assumed your base folder is "C:\Projects\", and your folders are named like this: "xxxxx_projectname" with xxxxx being the five digit number, then:

Dim BasePath
Dim NewNum, fso, fol, f, tmp

NewNum=0
BasePath = "C:\Projects\"

set fso=createobject("Scripting.FileSystemObject")
set fol=fso.getfolder(BasePath)
for each f in fol.folders
  tmp=CLng(mid(f.name, 12, 5) 'read number from folder path
  if tmp > NewNum then NewNum=tmp + 1 'highest folder number + 1
next f

'... do your magic here, like e.g. creating a newfolder with a higher number
'...
set fol=fso.createfolder(BasePath & NewNum & "_newproject")

Hope this gives you some ideas.


CHeers,
Andy

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

 

[sotghalz]

Thank you for your reply.

The folder name would look like this:

2007_#####_100Dollar_O_A
2007_#####_10Dollar_O_A
2008_#####_1Cent_R_A
2008_#####_1Cent_R_B

and yes all folders will be under one parent dir.

I can send you the whole script off line if you want.

 

[MakeItSo]

Sure I can help you with it.
However you will also have to provide a little more info together with the script:

- how do you run it? Is it part of an ASP page? Or an Outlook form?

- what is supposed to happen with new projects - and what is such a "project" in the first place?

- shall the script create a new folder AND save some file into that if the project is a new one?

Stuff like that...

Feel free to e-mail me at:
info[at-sign]bonobo-poetry[dot]com

E-Mail masked for spam protection.

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

 

[sotghalz]

I sent an email from "name"[at]usmint[dot]treas[dot]gov please let me know if you got the email. Sometime our spam filter actually works. If you have sent a reply please make sure you rename the file to .txt

 

[MakeItSo]

Nothing received so far.

You may also try with
andy[at]andygalambos[dot]com


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

 

[sotghalz]

Sent the email.

 

[MakeItSo]

Just sent you one back.
You really might want to consider an ASP as sort of a simple web client interface for your users.

This will also enable you to update your script any time you like, without having to deploy a new script to x machines...

Hope this helps.

Cheers,
Andy

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

 

[sotghalz]

I sent another email to you and was wondering if you got it.

 

[MakeItSo]

OK, I've got it.
But there hasn't changed anything vital, as far as I can tell.
And you haven't addressed my queries either.

What exactly is your problem?
At what point in the script?

Please understand, that I really like to help you, but I do not have the time to analyse such a large script in full depth.


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

 

[sotghalz]

Andy,

It is not run form an ASP page or Outlook form. It is a shortcut on there desktop to the script file.

What is supposed to happen with a "New Project" is the script runs asking a series of questions and with the answers from the questions the script creates a folder.

No files will be created. At the end of the script I added lines to take the answers and populate a database.

The issue is I need the first part of the script to ask the question "Is this a New Project or a Revision". With a New Project the script will run while adding +1 to the ##### part. If it is a revision then I need it to have an input box for the user to type in the ##### and add the next letter to the end.
Example:
2008_#####_100Dollar_R_A
2008_#####_100Dollar_R_B
See end of script for Revision question.

I hope this helps.

Thank you again for your help.

Chase

 

[MakeItSo]

OK. I am beginning to understand now.
Two things:
a) You ask the user to TYPE in the project name. You obviously want to have the project names be sequentially increasing numbers in format #####. How shall the user now which was the highest number last used, hence how can you determine the next number available for a new project?

The answer to this is at the end of your script:
You populate a DATABASE with all info entered, hence the database contains all the info you need:

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

Now, if the user wants to create a new project, don't make him enter a number in the first place - you decide which number shall be used.


Same thing with the revision. You have all previous revisions stored in the database. Use them!
==>

Dim NewRev
Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m] FROM [yourtable] WHERE [Project Serial Number]='" & MyArr(1) & "';")
'Now I am assuming, you are using capital letters only.
''Z' will then have ASC-value 90. 
If RStemp.Fields("m")<>"Z" then
   NewRev=Chr(ASC(RStemp.Fields("m"))+1)
Else
   'do something here. Either think of an "AA" Revision or 
'   other. More than 26 revisions possible?
End if
Set RStemp=nothing

;-)
Now you will probably also see, why I recommended using ASP, or perhaps a VB.exe rather than a script: You can use dropdowns populated with database content and thus correctly DETERMINE the correct revision letter or new project number rather than having the user type it in.

Same with the "100Dollar" part and so forth. Making the user pick the value from pre-populated dropdowns, and thus not only storing data in the database but actually using the values you already have, you avoid typos, you avoid having to go through multiple cycles until the user finally managed to type everything correctly, and so forth.

Dropdowns however are not part of a VB script, hence you might want to consider a VB.exe - or an ASP page.

Heck, you could even do it in an Excel macro!


Don't make yourself suffocate in code and procedures you actually don't need.
Use the force, Luke.


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

 

[sotghalz]

This all make sense now but I have one question:

I get a syntax error here: (Syntax error (missing operator) in query expression 'MAX(Project Serial Number)'

'Set RStemp=objConnection.execute("SELECT MAX(Project Serial Number) AS [m] FROM [Master] WHERE [Project Serial Number]=", MyArr(1))

 

[PHV]

SELECT MAX([!][[/!]Project Serial Number[!]][/!]) AS

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

 

[sotghalz]

Errr...

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

Same error:
Syntax error (missing operator) in query expression '[Project Serial Number]='

 

[PHV]

Seems that MyArr(1) is not populated ...

 

[sotghalz]

Because of domain restrictions I can't use a vb.exe and .asp page. So I'm still having an issue with the Revision part. How would I have a user input a certain existing Project Serial Number (#####) and have the script add the next sequential revision letter?

 

[MakeItSo]

Just put the second query I posted (SELECT max(Revision)...) right after your code segment, where you have read and checked that projectnumber.
As you can see in the code, the project number is included in the WHERE clause. hence, it does not matter whether you query for project number=Arr(1) or for e.g.

Set RStemp=objConnection.execute("SELECT Max(Revision) AS [m] FROM [yourtable] [b]WHERE [Project Serial Number]='" & format(inputbox("Please enter project number in format #####"),"00000") & "';")[/b]

Hmmmm.... Now that I think of it:
Never tried to use an inputbox within a where clause - would that work, PH? Or do you have to read the inputbox into a variable first, and then use the variable in the where clause?



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

 

[PHV]

MakeItSo, a worst problem with your last suggestion is the Format function ...

 

[MakeItSo]

PH:

You mean because it cannot be guaranteed that the input will be done in correct numeric format?
That's right. In that case, you must read it to variable and validate the input first anyway.

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