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
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
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.
