Enter Parameter - message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,
I feel that I have a 'decent' grasp on my code, but I cannot figure out why
I am being prompted for a parameter for 'htmlfilename' when I am assigning a
value to it from within. Can someone help, please? (watch the word wraps)
Thank you in advance!

Option Compare Database

Public Sub cbSubmitProject_Click()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim strfilename As String
Dim htmlfilename As String
If IsNull(BudgetLine) = False Then
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWorkbook =
xlApp.Workbooks.Open("v:\distcntr\dcdesign\projmgmt\Capital05\CER\CER_V15.xls")
Set xlsheet = xlWorkbook.Sheets(3)
strfilename = "v:\distcntr\dcdesign\projmgmt\Capital" &
Forms!frmNewProject.ProjectYear & "\CER\" &
Left(Forms!frmNewProject.BudgetLine, 2) & "\" &
Left(Forms!frmNewProject.BudgetLine, 2) & Forms!frmNewProject.ProjectName &
".xls"
xlWorkbook.SaveAs (strfilename)
htmlfilename = "#file:///" & strfilename & "#"
DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear,
ProjectID, BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID,
[TimeStamp]) SELECT forms!frmNewProject.ProjectName,
[Forms]![frmNewProject].[ProjectYear], [forms].[frmNewProject].[ProjectID],
[Forms]![frmNewProject].[BudgetLine], [Forms]![frmNewProject].[BudgetCost],
'ProofReading', htmlfilename, fOSUserName(), Now()"
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "A CER
is ready for your review", "Frank," & Chr$(13) & "Please open the Budget Tool
and proofread the CER for " & Forms!frmNewProject.ProjectName & ". In the
application, you will have the option to request me to make corrections or to
pass on to my GM for signature.", False
DoCmd.OpenForm "frmAuths"
End If
DoCmd.Close acForm, "frmNewProject"
End Sub
 
Hi Derek,

It looks to me like the problem may be that you have typed the name of the
variable in your sql string, rather than concatenating the string with the
variable (if that makes any sense).

Take a look at the part of your code where you have:

DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', htmlfilename, fOSUserName(),
Now()"

Try changing it to:
DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', " & htmlfilename & ",
fOSUserName(), Now()"

HTH, Ted Allen
 
Hmmm... I'll have to give that a shot. I'm pretty sure you are correct as I
have a habit of poor concatenation and quotations.

Thank you!
Derek


Ted Allen said:
Hi Derek,

It looks to me like the problem may be that you have typed the name of the
variable in your sql string, rather than concatenating the string with the
variable (if that makes any sense).

Take a look at the part of your code where you have:

DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', htmlfilename, fOSUserName(),
Now()"

Try changing it to:
DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', " & htmlfilename & ",
fOSUserName(), Now()"

HTH, Ted Allen

Derek Wittman said:
Good morning,
I feel that I have a 'decent' grasp on my code, but I cannot figure out why
I am being prompted for a parameter for 'htmlfilename' when I am assigning a
value to it from within. Can someone help, please? (watch the word wraps)
Thank you in advance!

Option Compare Database

Public Sub cbSubmitProject_Click()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim strfilename As String
Dim htmlfilename As String
If IsNull(BudgetLine) = False Then
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWorkbook =
xlApp.Workbooks.Open("v:\distcntr\dcdesign\projmgmt\Capital05\CER\CER_V15.xls")
Set xlsheet = xlWorkbook.Sheets(3)
strfilename = "v:\distcntr\dcdesign\projmgmt\Capital" &
Forms!frmNewProject.ProjectYear & "\CER\" &
Left(Forms!frmNewProject.BudgetLine, 2) & "\" &
Left(Forms!frmNewProject.BudgetLine, 2) & Forms!frmNewProject.ProjectName &
".xls"
xlWorkbook.SaveAs (strfilename)
htmlfilename = "#file:///" & strfilename & "#"
DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear,
ProjectID, BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID,
[TimeStamp]) SELECT forms!frmNewProject.ProjectName,
[Forms]![frmNewProject].[ProjectYear], [forms].[frmNewProject].[ProjectID],
[Forms]![frmNewProject].[BudgetLine], [Forms]![frmNewProject].[BudgetCost],
'ProofReading', htmlfilename, fOSUserName(), Now()"
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "A CER
is ready for your review", "Frank," & Chr$(13) & "Please open the Budget Tool
and proofread the CER for " & Forms!frmNewProject.ProjectName & ". In the
application, you will have the option to request me to make corrections or to
pass on to my GM for signature.", False
DoCmd.OpenForm "frmAuths"
End If
DoCmd.Close acForm, "frmNewProject"
End Sub
Click to expand...
Click to expand...
 
Good morning again. Seems I have another problem - similar. With the
download time of the network to the Excel application (the workbook is a
network file), I've included a vbyesnocancel msgbox that asks the user if he
or she wants to download the latest workbook format. The problem I still
have is that I get an error about a date (since my pathname has #'s in it).

DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear, ProjectID,
BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID, [TimeStamp]) SELECT
forms!frmNewProject.ProjectName, [Forms]![frmNewProject].[ProjectYear],
[forms].[frmNewProject].[ProjectID], [Forms]![frmNewProject].[BudgetLine],
[Forms]![frmNewProject].[BudgetCost], 'ProofReading', " &
htmlfilename(strfilename) & ", fOSUserName(), Now()"

htmlfilename is a function that inserts "#file:///" before and "#" after my
path - to allow the user to hyperlink from a status form. Any suggestions
would be really appreciated.

Thank you very much!
Derek
Ted Allen said:
Hi Derek,

It looks to me like the problem may be that you have typed the name of the
variable in your sql string, rather than concatenating the string with the
variable (if that makes any sense).

Take a look at the part of your code where you have:

DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', htmlfilename, fOSUserName(),
Now()"

Try changing it to:
DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', " & htmlfilename & ",
fOSUserName(), Now()"

HTH, Ted Allen

Derek Wittman said:
Good morning,
I feel that I have a 'decent' grasp on my code, but I cannot figure out why
I am being prompted for a parameter for 'htmlfilename' when I am assigning a
value to it from within. Can someone help, please? (watch the word wraps)
Thank you in advance!

Option Compare Database

Public Sub cbSubmitProject_Click()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim strfilename As String
Dim htmlfilename As String
If IsNull(BudgetLine) = False Then
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWorkbook =
xlApp.Workbooks.Open("v:\distcntr\dcdesign\projmgmt\Capital05\CER\CER_V15.xls")
Set xlsheet = xlWorkbook.Sheets(3)
strfilename = "v:\distcntr\dcdesign\projmgmt\Capital" &
Forms!frmNewProject.ProjectYear & "\CER\" &
Left(Forms!frmNewProject.BudgetLine, 2) & "\" &
Left(Forms!frmNewProject.BudgetLine, 2) & Forms!frmNewProject.ProjectName &
".xls"
xlWorkbook.SaveAs (strfilename)
htmlfilename = "#file:///" & strfilename & "#"
DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear,
ProjectID, BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID,
[TimeStamp]) SELECT forms!frmNewProject.ProjectName,
[Forms]![frmNewProject].[ProjectYear], [forms].[frmNewProject].[ProjectID],
[Forms]![frmNewProject].[BudgetLine], [Forms]![frmNewProject].[BudgetCost],
'ProofReading', htmlfilename, fOSUserName(), Now()"
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "A CER
is ready for your review", "Frank," & Chr$(13) & "Please open the Budget Tool
and proofread the CER for " & Forms!frmNewProject.ProjectName & ". In the
application, you will have the option to request me to make corrections or to
pass on to my GM for signature.", False
DoCmd.OpenForm "frmAuths"
End If
DoCmd.Close acForm, "frmNewProject"
End Sub
Click to expand...
Click to expand...
 
Derek,

The parameter that you pass to RunSQL must be all text, with the functions
all evaluated. I would strongly recommend you create a local string
variable to hold it so that you can debug it as needed.

Air code:
Dim strSQL as String
strSQL = "INSERT INTO tblProject (ProjectName, ProjectYear, ProjectID,
BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID, [TimeStamp])"

strSQL = strSQL & " SELECT '" & Forms!frmNewProject.ProjectName & "', "
'assumes ProjectName is Text
strSQL = strSQL & [Forms]![frmNewProject].[ProjectYear] & ", " 'assumes
ProjectYear is numeric
strSQL = strSQL & [Forms].[frmNewProject].[ProjectID] & ", " 'assumes
ProjectID is numeric
strSQL = strSQL & [Forms]![frmNewProject].[BudgetLine] & ", " 'assumes
BudgetLine is numeric
strSQL = strSQL & [Forms]![frmNewProject].[BudgetCost] & ", " 'assumes
BudgetCost is numeric
strSQL = strSQL & "'ProofReading', " 'assumes ProjectStatus is text
strSQL = strSQL & "'" & htmlfilename(strfilename) & "', " 'assumes
ProjectPath is text
strSQL = strSQL & "'" & fOSUserName() & "', " assumes SYSMID is text
strSQL = strSQL & Format(Now(), "\#mm\/dd\/yyyy hh:nn:ss\#") 'assumes
TimeStamp is Date

Debug.Print strSQL

DoCmd.RunSQL
--------------

Using a string variable lets you track the formation of the SQL and solve
errors before the final execution. Local variables are cheap. Splurge and
save yourself some time and headaches!

HTH,

Kevin


Derek Wittman said:
Good morning again. Seems I have another problem - similar. With the
download time of the network to the Excel application (the workbook is a
network file), I've included a vbyesnocancel msgbox that asks the user if
he
or she wants to download the latest workbook format. The problem I still
have is that I get an error about a date (since my pathname has #'s in
it).

DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear, ProjectID,
BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID, [TimeStamp])
SELECT
forms!frmNewProject.ProjectName, [Forms]![frmNewProject].[ProjectYear],
[forms].[frmNewProject].[ProjectID], [Forms]![frmNewProject].[BudgetLine],
[Forms]![frmNewProject].[BudgetCost], 'ProofReading', " &
htmlfilename(strfilename) & ", fOSUserName(), Now()"

htmlfilename is a function that inserts "#file:///" before and "#" after
my
path - to allow the user to hyperlink from a status form. Any suggestions
would be really appreciated.

Thank you very much!
Derek
Ted Allen said:
Hi Derek,

It looks to me like the problem may be that you have typed the name of
the
variable in your sql string, rather than concatenating the string with
the
variable (if that makes any sense).

Take a look at the part of your code where you have:

DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', htmlfilename,
fOSUserName(),
Now()"

Try changing it to:
DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', " & htmlfilename & ",
fOSUserName(), Now()"

HTH, Ted Allen

Derek Wittman said:
Good morning,
I feel that I have a 'decent' grasp on my code, but I cannot figure out
why
I am being prompted for a parameter for 'htmlfilename' when I am
assigning a
value to it from within. Can someone help, please? (watch the word
wraps)
Thank you in advance!

Option Compare Database

Public Sub cbSubmitProject_Click()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim strfilename As String
Dim htmlfilename As String
If IsNull(BudgetLine) = False Then
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWorkbook =
xlApp.Workbooks.Open("v:\distcntr\dcdesign\projmgmt\Capital05\CER\CER_V15.xls")
Set xlsheet = xlWorkbook.Sheets(3)
strfilename = "v:\distcntr\dcdesign\projmgmt\Capital" &
Forms!frmNewProject.ProjectYear & "\CER\" &
Left(Forms!frmNewProject.BudgetLine, 2) & "\" &
Left(Forms!frmNewProject.BudgetLine, 2) &
Forms!frmNewProject.ProjectName &
".xls"
xlWorkbook.SaveAs (strfilename)
htmlfilename = "#file:///" & strfilename & "#"
DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear,
ProjectID, BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID,
[TimeStamp]) SELECT forms!frmNewProject.ProjectName,
[Forms]![frmNewProject].[ProjectYear],
[forms].[frmNewProject].[ProjectID],
[Forms]![frmNewProject].[BudgetLine],
[Forms]![frmNewProject].[BudgetCost],
'ProofReading', htmlfilename, fOSUserName(), Now()"
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "A
CER
is ready for your review", "Frank," & Chr$(13) & "Please open the
Budget Tool
and proofread the CER for " & Forms!frmNewProject.ProjectName & ". In
the
application, you will have the option to request me to make corrections
or to
pass on to my GM for signature.", False
DoCmd.OpenForm "frmAuths"
End If
DoCmd.Close acForm, "frmNewProject"
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Kevin,
I'm not too familiar with the debugging functionality of Access. I've got
more of a trial and error kinda deal going on.

The good news is that I was able to figure it out. The better news is that
I have 3 application options done (Prepare Cap Ex Request, Prepare Short Form
Contract, and Prepare Confidentiality Agreement). 1 is Excel and 2 are Word.
I'd like to make the documents friendlier, perhaps using Mail Merge with the
data coming from the database itself. (All I really need is Vendor Names and
aka's). So the user doesn't need to enter all the fields each time.

And since our fileserver is so slow, I have a fileexists check to see if
there's a copy on the user's hard drive before downloading a backup to work
with.

Thanks!
Derek

Kevin K. Sullivan said:
Derek,

The parameter that you pass to RunSQL must be all text, with the functions
all evaluated. I would strongly recommend you create a local string
variable to hold it so that you can debug it as needed.

Air code:
Dim strSQL as String
strSQL = "INSERT INTO tblProject (ProjectName, ProjectYear, ProjectID,
BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID, [TimeStamp])"

strSQL = strSQL & " SELECT '" & Forms!frmNewProject.ProjectName & "', "
'assumes ProjectName is Text
strSQL = strSQL & [Forms]![frmNewProject].[ProjectYear] & ", " 'assumes
ProjectYear is numeric
strSQL = strSQL & [Forms].[frmNewProject].[ProjectID] & ", " 'assumes
ProjectID is numeric
strSQL = strSQL & [Forms]![frmNewProject].[BudgetLine] & ", " 'assumes
BudgetLine is numeric
strSQL = strSQL & [Forms]![frmNewProject].[BudgetCost] & ", " 'assumes
BudgetCost is numeric
strSQL = strSQL & "'ProofReading', " 'assumes ProjectStatus is text
strSQL = strSQL & "'" & htmlfilename(strfilename) & "', " 'assumes
ProjectPath is text
strSQL = strSQL & "'" & fOSUserName() & "', " assumes SYSMID is text
strSQL = strSQL & Format(Now(), "\#mm\/dd\/yyyy hh:nn:ss\#") 'assumes
TimeStamp is Date

Debug.Print strSQL

DoCmd.RunSQL
--------------

Using a string variable lets you track the formation of the SQL and solve
errors before the final execution. Local variables are cheap. Splurge and
save yourself some time and headaches!

HTH,

Kevin


Derek Wittman said:
Good morning again. Seems I have another problem - similar. With the
download time of the network to the Excel application (the workbook is a
network file), I've included a vbyesnocancel msgbox that asks the user if
he
or she wants to download the latest workbook format. The problem I still
have is that I get an error about a date (since my pathname has #'s in
it).

DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear, ProjectID,
BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID, [TimeStamp])
SELECT
forms!frmNewProject.ProjectName, [Forms]![frmNewProject].[ProjectYear],
[forms].[frmNewProject].[ProjectID], [Forms]![frmNewProject].[BudgetLine],
[Forms]![frmNewProject].[BudgetCost], 'ProofReading', " &
htmlfilename(strfilename) & ", fOSUserName(), Now()"

htmlfilename is a function that inserts "#file:///" before and "#" after
my
path - to allow the user to hyperlink from a status form. Any suggestions
would be really appreciated.

Thank you very much!
Derek
Ted Allen said:
Hi Derek,

It looks to me like the problem may be that you have typed the name of
the
variable in your sql string, rather than concatenating the string with
the
variable (if that makes any sense).

Take a look at the part of your code where you have:

DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', htmlfilename,
fOSUserName(),
Now()"

Try changing it to:
DoCmd.RunSQL "INSERT INTO ..., 'ProofReading', " & htmlfilename & ",
fOSUserName(), Now()"

HTH, Ted Allen

:

Good morning,
I feel that I have a 'decent' grasp on my code, but I cannot figure out
why
I am being prompted for a parameter for 'htmlfilename' when I am
assigning a
value to it from within. Can someone help, please? (watch the word
wraps)
Thank you in advance!

Option Compare Database

Public Sub cbSubmitProject_Click()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim strfilename As String
Dim htmlfilename As String
If IsNull(BudgetLine) = False Then
Set xlApp = CreateObject("excel.application")
xlApp.Visible = True
Set xlWorkbook =
xlApp.Workbooks.Open("v:\distcntr\dcdesign\projmgmt\Capital05\CER\CER_V15.xls")
Set xlsheet = xlWorkbook.Sheets(3)
strfilename = "v:\distcntr\dcdesign\projmgmt\Capital" &
Forms!frmNewProject.ProjectYear & "\CER\" &
Left(Forms!frmNewProject.BudgetLine, 2) & "\" &
Left(Forms!frmNewProject.BudgetLine, 2) &
Forms!frmNewProject.ProjectName &
".xls"
xlWorkbook.SaveAs (strfilename)
htmlfilename = "#file:///" & strfilename & "#"
DoCmd.RunSQL "INSERT INTO tblProject (ProjectName, ProjectYear,
ProjectID, BudgetLI, BudgetCost, ProjectStatus, ProjectPath, SYSMID,
[TimeStamp]) SELECT forms!frmNewProject.ProjectName,
[Forms]![frmNewProject].[ProjectYear],
[forms].[frmNewProject].[ProjectID],
[Forms]![frmNewProject].[BudgetLine],
[Forms]![frmNewProject].[BudgetCost],
'ProofReading', htmlfilename, fOSUserName(), Now()"
DoCmd.SendObject acSendNoObject, , , "(e-mail address removed)", , , "A
CER
is ready for your review", "Frank," & Chr$(13) & "Please open the
Budget Tool
and proofread the CER for " & Forms!frmNewProject.ProjectName & ". In
the
application, you will have the option to request me to make corrections
or to
pass on to my GM for signature.", False
DoCmd.OpenForm "frmAuths"
End If
DoCmd.Close acForm, "frmNewProject"
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top