Combining SQL Queries

[frustrating]

Greetings:

I am trying to create an application that pulls text from a pdf and displays it into its applicable form fields. I will be extracting over 40 pieces of data from these certificates that get sent. I want each certificate to act as one record set, yet when I put in a request for the second piece of information, it doesn't add it to the Current form, but rather creates a new one. Here is the code I am using:

Private Sub Command3_Click()
Dim TheCert As String
Dim CarrierPlace As String
Dim CarrierName As String
Dim InsuranceCompanyAplace As String
Dim InsuranceCompanyA As String
Dim CertEnd As Integer
Dim strSQL As String
Dim strSQLINSA As String

CertEnd = FreeFile
Open Text1.Value For Input As CertEnd
Do Until EOF(CertEnd)
Input #CertEnd, TheCert
CarrierPlace = InStr(1, TheCert, "insured")
If CarrierPlace > 0 And InStr(1, TheCert, "IMPORTANT") = False And InStr(1, TheCert, "$") = False And InStr(1, TheCert, "Document") = False Then
CarrierName = Mid(TheCert, CarrierPlace + 8, 50)
strSQL = "INSERT INTO Temp ([CarrierName]) VALUES ('" & CarrierName & "');"
CurrentDb.Execute strSQL, dbFailOnError
Forms!Extractor.Requery
End If
Loop
Close CertEnd
End Sub

Here is the second statement I also want to add.

InsuranceCompanyAplace = InStr(1, TheCert, "INSURER A")
InsuranceCompanyA = Mid(TheCert, InsuranceCompanyAplace + 9, 30)
If InsuranceCompanyAplace > 0 Then
strSQLINSA = "INSERT INTO Temp ([InsuranceCompanyA]) VALUES ('" & InsuranceCompanyA & "');"
CurrentDb.Execute strSQLINSA, dbFailOnError
Forms!Extractor.Requery

Any ideas on how to combine these two statements so both values go into the same record in their respective fields?

Thanks for any help!

 

[pr2-eugin]

Hello frustrating, Well if they need to go in the same row, then you have to have your SQL statement as..

strSQL  = "INSERT INTO Temp ([CarrierName], [InsuranceCompanyA]) VALUES ('"  & CarrierName & "', '" & InsuranceCompanyA & "');"

However there is just a few things I noticed, you have declared CarrierName and InsuranceCompanyAplace as String, sometimes InStr would return a Null, which Strings cannot handle, either declare them as Variant or try to Wrap them using Nz().

 

[frustrating]

Hello frustrating, Well if they need to go in the same row, then you have to have your SQL statement as..

strSQL  = "INSERT INTO Temp ([CarrierName], [InsuranceCompanyA]) VALUES ('"  & CarrierName & "', '" & InsuranceCompanyA & "');"

However there is just a few things I noticed, you have declared CarrierName and InsuranceCompanyAplace as String, sometimes InStr would return a Null, which Strings cannot handle, either declare them as Variant or try to Wrap them using Nz().

I tried doing that, but it returns nothing. I then DIMd them to Variants. Still nothing.

They both work separately, however.
Here is the code.

CertEnd = FreeFile
Open Text1.Value For Input As CertEnd
Do Until EOF(CertEnd)
Input #CertEnd, TheCert
CarrierPlace = InStr(1, TheCert, "insured")
If CarrierPlace > 0 And InStr(1, TheCert, "IMPORTANT") = False And InStr(1, TheCert, "$") = False And InStr(1, TheCert, "Document") = False Then
CarrierName = Mid(TheCert, CarrierPlace + 8, 50)
InsuranceCompanyAplace = InStr(1, TheCert, "INSURER A")
If InsuranceCompanyAplace > 0 Then
InsuranceCompanyA = Mid(TheCert, InsuranceCompanyAplace + 9, 30)
strSQL = "INSERT INTO Temp ([CarrierName], [InsuranceCompanyA]) VALUES ('" & CarrierName & "', '" & InsuranceCompanyA & "');"
CurrentDb.Execute strSQL, dbFailOnError
Forms!Extractor.Requery

 

[mdlueck]

Any ideas on how to combine these two statements so both values go into the same record in their respective fields?

Here are examples of both INSERT / UPDATE statements using ADO objects:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

To specify multiple field values to be INSERTed at one time, merely define multiple column names separated by comas, and supply each value which is to be placed in the respective column based on the same position. Field1, Field2, etc...

 

[frustrating]

I think my SQL statement is correct.

strSQL = "INSERT INTO Temp ([CarrierName], [InsuranceCompanyA]) VALUES ('" & CarrierName & "', '" & InsuranceCompanyA & "');"

But I'm still having issues getting the information to appear correctly. I think it may have to do with where my End IFs are?

Any ideas anyone?

 

[frustrating]

Nevermind I figured it out!

I had the SQL inside my loop which was displaying it 183 times.
Whoops!


...sometimes it pays to take a break.
Thanks for all your help.