Insert a hyperlink from a form into a VBA SQL Access table

Question

I want to take a hyperlink that is entered in the form (like a hyperlink) into a table through which you need to go through VBA. I am using Access 2010. It continues to give me an SQL query error. I know this is due to # hyperlink signs. I don’t quite understand how hyperlinks are handled. I read a lot of posts on the forum, but they are all different (different years), and I can’t crack their examples to satisfy my needs. Can someone please let me know what I'm 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 your help!

Nathan

Answer 1

I would use a parameter request 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 

Answer 2

If you highlight sql, it is much easier to find errors.

 ' 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 cited and is not the 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 for elec_copy and sign_copy.