Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I am wanting to take the hyperlink that is entered on a form (As a hyperlink) to the table it needs to go to, through VBA. I am using Access 2010. It keeps giving me a SQL statement error. I know it has to do with the hyperlink #'s signs. I do not quite grasp how hyperlinks are handled. I have read plenty of forum posts, but they all are different (Different years), and I can't seem to hack up their examples to meet my needs. Can anyone please let me know what it is that I am doing wrong? Thanks

Private Sub SaveReq_Click()
' 
' Saves the current entry to the database
' Into the TABLE 'pr_req_table'
' 

' Open a connection to the database
dim data_base as Database
set data_base = OpenDatabase(CurrentProject.Path & "\test_database.accdb")

' Grab all information from form
' Add information to pr_req_table
data_base.Execute "INSERT INTO pr_req_table " _
    & "(pr_no, pr_date, pr_owner, pr_link, pr_signed) " _
    & "VALUES (" & pr_num.Value & ", #" &  Format(pr_date.Value, "mm/dd/yyyy") & "#, " _
        &  List22.Value & ", " & "Excel Copy #" & elec_copy.Value & ", " & "Signed Copy #" & sign_copy.Value & ");"

' Close Database connection
data_base.Close

End Sub

Thanks in advance for any help!

Nathan

share|improve this question
Can you post the exact error message and the value for elec_copy.Value? – Holger Brandt Jul 9 '12 at 21:45

2 Answers

up vote 3 down vote accepted

I would use a parameter query as follows:

Sub InsertRecord()
Dim data_base As Database
Set data_base = OpenDatabase(CurrentProject.Path & "\test_database.accdb")

' Grab all information from form
' Add information to pr_req_table
Dim qd As QueryDef
Set qd = data_base.CreateQueryDef("")
qd.sql = "INSERT INTO pr_req_table(pr_no, pr_date, pr_owner, pr_link, pr_signed) " & _
    "values([p1],[p2],[p3],[p4],[p5])"
qd.Parameters("p1").Value = pr_num.Value
qd.Parameters("p2").Value = Format(pr_date.Value, "mm/dd/yyyy")
qd.Parameters("p3").Value = List22.Value
qd.Parameters("p4").Value = "Excel Copy #" & elec_copy.Value
qd.Parameters("p5").Value =  "Signed Copy #" & sign_copy.Value 
qd.Execute

End Sub
share|improve this answer
That's a good point. – Remou Jul 9 '12 at 22:04
up vote 1 down vote

If you separate out the sql it is much easier to find the mistakes.

' Grab all information from form
' Add information to pr_req_table
sSQL="INSERT INTO pr_req_table " _
    & "(pr_no, pr_date, pr_owner, pr_link, pr_signed) " _
    & "VALUES (" & pr_num.Value & ", #" _
    &  Format(pr_date.Value, "mm/dd/yyyy") & "#, " _
    &  List22.Value & ", " & "Excel Copy #" & elec_copy.Value & ", " _
    & "Signed Copy #" & sign_copy.Value & ");"
data_base.Execute sSQL

You can see that the hyperlink is not quoted, nor is owner.

INSERT INTO pr_req_table (pr_no, pr_date, pr_owner, pr_link, pr_signed) 
VALUES (pr_num, #07/09/2012#, List22, Excel Copy
#elec_copy, Signed Copy #sign_copy);

So:

sSQL="INSERT INTO pr_req_table " _
    & "(pr_no, pr_date, pr_owner, pr_link, pr_signed) " _
    & "VALUES (" & pr_num.Value & ", #" _
    &  Format(pr_date.Value, "mm/dd/yyyy") & "#, '" _
    &  Replace(List22.Value, "'","''") & "', '" _
    & "Excel Copy #" & elec_copy.Value & '",' " _
    & "Signed Copy #" & sign_copy.Value & "');"

You should probably use Replace on elec_copy and sign_copy.

share|improve this answer
Thanks for the help! – w3rthl3ss Jul 10 '12 at 2:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.