Field Concatenation Code

M

Matthew DeAngelis

Hi everyone,

Please bear with me; my coding skills are subpar.

I am trying to write some VBA code that will create a customized SQL
Update statement based on a series of "If" statements. Basically, I
want the sub to test a field to see if it is null and, if not, build
the SQL statement to include that field preceded by its name (ie
'Technology: ' & [Technology]). The SQL statement itself is updating a
memo field to include the information from a number of other fields (so
that I can delete those other fields).

Here is what I have so far:

Public Sub MergeMemoFields()
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "[tbl: Data - Deals]", cnn

Dim strSQL As String

strSQL = "UPDATE [tbl: Data - Deals] SET [tbl: Data -
Deals].ConcatMemoField = "

If rst!Technology Is Not Null Then
strSQL = strSQL + "'Technology: ' & [tbl: Data - Deals].Technology
& Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!IP Is Not Null Then
strSQL = strSQL + " & 'IP: ' & [tbl: Data - Deals].IP &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!BusinessModel Is Not Null Then
strSQL = strSQL + " & 'Business Model: ' & [tbl: Data -
Deals].BusinessModel & Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!Issues Is Not Null Then
strSQL = strSQL + " & 'Issues: ' & [tbl: Data - Deals].Issues &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!MarketOpportunity Is Not Null Then
strSQL = strSQL + " & 'Market Opportunity: ' & [tbl: Data -
Deals].MarketOpportunity & Chr(13)+Chr(10)+Chr(13)+Chr(10);"
End If

End Sub

I haven't even gotten to running the SQL statement, because I am
getting an 'object required' error at the first If. However, when I
enter Debug mode and hover over rst!Technology, the correct value of
the field is displayed. I can also get the correct value by typing
?rst!Technology. If I reset the sub and type ?rst!Technology into the
Immediate Window, then I get the same 424 error. It would appear that
the system can see the value in debug mode, but not at run-time.

Any ideas?


Thanks,
Matt
 
D

Doug Bell

Matthew,

Is the table you are trying to open named [tbl: Data - Deals] (without
[] )
try using naming conventions like [tblDataDeals] to reduce typo errors and
errors caused by special characters.

you can use vbCrLf instead of Chr(13)+Chr(10)

I think the problem you have is that your connection is not open.

e.g.:
stConnectionString = "C:\MyDocs\DatabaseName.mdb"
stUserID="Admin"
stPwd=""
cnn.open stConnectionString, stUserID, stPwd

rst.Open "[tbl: Data - Deals]", cnn

Dim strSQL As String
strSQL = "UPDATE [tblDataDeals] SET [tblDataDeals].ConcatMemoField = "

With rst
If Not IsNull(!Technology) Then
strSQL = strSQL + "'Technology: '" & [tblDataDeals].Technology &
vbCrLf & vbCrLf
End If



End With


Matthew DeAngelis said:
Hi everyone,

Please bear with me; my coding skills are subpar.

I am trying to write some VBA code that will create a customized SQL
Update statement based on a series of "If" statements. Basically, I
want the sub to test a field to see if it is null and, if not, build
the SQL statement to include that field preceded by its name (ie
'Technology: ' & [Technology]). The SQL statement itself is updating a
memo field to include the information from a number of other fields (so
that I can delete those other fields).

Here is what I have so far:

Public Sub MergeMemoFields()
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "[tbl: Data - Deals]", cnn

Dim strSQL As String

strSQL = "UPDATE [tbl: Data - Deals] SET [tbl: Data -
Deals].ConcatMemoField = "

If rst!Technology Is Not Null Then
strSQL = strSQL + "'Technology: ' & [tbl: Data - Deals].Technology
& Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!IP Is Not Null Then
strSQL = strSQL + " & 'IP: ' & [tbl: Data - Deals].IP &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!BusinessModel Is Not Null Then
strSQL = strSQL + " & 'Business Model: ' & [tbl: Data -
Deals].BusinessModel & Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!Issues Is Not Null Then
strSQL = strSQL + " & 'Issues: ' & [tbl: Data - Deals].Issues &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!MarketOpportunity Is Not Null Then
strSQL = strSQL + " & 'Market Opportunity: ' & [tbl: Data -
Deals].MarketOpportunity & Chr(13)+Chr(10)+Chr(13)+Chr(10);"
End If

End Sub

I haven't even gotten to running the SQL statement, because I am
getting an 'object required' error at the first If. However, when I
enter Debug mode and hover over rst!Technology, the correct value of
the field is displayed. I can also get the correct value by typing
?rst!Technology. If I reset the sub and type ?rst!Technology into the
Immediate Window, then I get the same 424 error. It would appear that
the system can see the value in debug mode, but not at run-time.

Any ideas?


Thanks,
Matt
Click to expand...
 
¹

¹Ú¹®¼ö

Doug Bell said:
Matthew,

Is the table you are trying to open named [tbl: Data - Deals] (without
[] )
try using naming conventions like [tblDataDeals] to reduce typo errors and
errors caused by special characters.

you can use vbCrLf instead of Chr(13)+Chr(10)

I think the problem you have is that your connection is not open.

e.g.:
stConnectionString = "C:\MyDocs\DatabaseName.mdb"
stUserID="Admin"
stPwd=""
cnn.open stConnectionString, stUserID, stPwd

rst.Open "[tbl: Data - Deals]", cnn

Dim strSQL As String
strSQL = "UPDATE [tblDataDeals] SET [tblDataDeals].ConcatMemoField = "

With rst
If Not IsNull(!Technology) Then
strSQL = strSQL + "'Technology: '" & [tblDataDeals].Technology &
vbCrLf & vbCrLf
End If



End With


Matthew DeAngelis said:
Hi everyone,

Please bear with me; my coding skills are subpar.

I am trying to write some VBA code that will create a customized SQL
Update statement based on a series of "If" statements. Basically, I
want the sub to test a field to see if it is null and, if not, build
the SQL statement to include that field preceded by its name (ie
'Technology: ' & [Technology]). The SQL statement itself is updating a
memo field to include the information from a number of other fields (so
that I can delete those other fields).

Here is what I have so far:

Public Sub MergeMemoFields()
Set rst = New ADODB.Recordset
Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "[tbl: Data - Deals]", cnn

Dim strSQL As String

strSQL = "UPDATE [tbl: Data - Deals] SET [tbl: Data -
Deals].ConcatMemoField = "

If rst!Technology Is Not Null Then
strSQL = strSQL + "'Technology: ' & [tbl: Data - Deals].Technology
& Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!IP Is Not Null Then
strSQL = strSQL + " & 'IP: ' & [tbl: Data - Deals].IP &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!BusinessModel Is Not Null Then
strSQL = strSQL + " & 'Business Model: ' & [tbl: Data -
Deals].BusinessModel & Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!Issues Is Not Null Then
strSQL = strSQL + " & 'Issues: ' & [tbl: Data - Deals].Issues &
Chr(13)+Chr(10)+Chr(13)+Chr(10)"
End If

If rst!MarketOpportunity Is Not Null Then
strSQL = strSQL + " & 'Market Opportunity: ' & [tbl: Data -
Deals].MarketOpportunity & Chr(13)+Chr(10)+Chr(13)+Chr(10);"
End If

End Sub

I haven't even gotten to running the SQL statement, because I am
getting an 'object required' error at the first If. However, when I
enter Debug mode and hover over rst!Technology, the correct value of
the field is displayed. I can also get the correct value by typing
?rst!Technology. If I reset the sub and type ?rst!Technology into the
Immediate Window, then I get the same 424 error. It would appear that
the system can see the value in debug mode, but not at run-time.

Any ideas?


Thanks,
Matt
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word VBA - Delete text from bulleted paragraphs 1
Extract Text in braquet and list of terms from 2 Docs 3
VBA for Dependents shortcut menu 0
Concatenating Memo Fields 2
Adding table into existing access database via ADO 0
VBA works in orignal but fails in copy of database 1
Dynamic Pass Through Query Error 3075 Extra ) in Query Expression 3
Can I get a record count of this??? 2

Top