I am using the code below to get some data out of access and put it in column
"G". Instead of puting it in "G" how can I append it to what is in Column "A"


' look in names database and get ORG code based on user
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "A" 'column B that have project number in it
Const projectDescColumn = "G" 'column C that I would like to populate
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant

'C:\PathToYourMdb\Database.mdb (Change)
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "\names.mdb;Persist Security
Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
sSQL = "SELECT EMP.* FROM EMP WHERE (((EMP.FullName)='" & cellPointer &
"'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
(((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("OrgCode").Value) Then
Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing

from
Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
to
Range("A" & looper) = Range("A" & looper) & rs.Fields("OrgCode").Value

If you want a space between the data then this
Range("A" & looper) = Range("A" & looper) & " " & rs.Fields("OrgCode").Value



"Bobbo" wrote:

> I am using the code below to get some data out of access and put it in column
> "G". Instead of puting it in "G" how can I append it to what is in Column "A"
>
>
> ' look in names database and get ORG code based on user
> 'Needs reference the Axtive X Library 2.0 or higher
> Const projectIDColumn = "A" 'column B that have project number in it
> Const projectDescColumn = "G" 'column C that I would like to populate
> Dim cnn As ADODB.Connection
> Dim rs As ADODB.Recordset
> Dim sSQL As String, strConn
> Dim looper As Long
> Dim cellPointer As Variant
>
> 'C:\PathToYourMdb\Database.mdb (Change)
> '
> strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
> & "Data Source=" & ThisWorkbook.Path & "\names.mdb;Persist Security
> Info=False"
> Set cnn = New ADODB.Connection
> cnn.Open strConn
>
> For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
> Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
> 'If you project number field is text use this sSQL
> sSQL = "SELECT EMP.* FROM EMP WHERE (((EMP.FullName)='" & cellPointer &
> "'));"
> 'If you project number field is number use this sSQL
> 'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
> (((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
> Set rs = New ADODB.Recordset
>
> rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
> If Not IsNull(rs.Fields("OrgCode").Value) Then
> Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
> End If
> rs.Close
> Set rs = Nothing
> Next looper
>
> cnn.Close
> Set cnn = Nothing

Thanks for the response but now I get the following Run-time error .
Either BOF or EOF is True, or the current record has been deleted.

"Joel" wrote:

> from
> Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
> to
> Range("A" & looper) = Range("A" & looper) & rs.Fields("OrgCode").Value
>
> If you want a space between the data then this
> Range("A" & looper) = Range("A" & looper) & " " & rs.Fields("OrgCode").Value
>
>
>
> "Bobbo" wrote:
>
> > I am using the code below to get some data out of access and put it in column
> > "G". Instead of puting it in "G" how can I append it to what is in Column "A"
> >
> >
> > ' look in names database and get ORG code based on user
> > 'Needs reference the Axtive X Library 2.0 or higher
> > Const projectIDColumn = "A" 'column B that have project number in it
> > Const projectDescColumn = "G" 'column C that I would like to populate
> > Dim cnn As ADODB.Connection
> > Dim rs As ADODB.Recordset
> > Dim sSQL As String, strConn
> > Dim looper As Long
> > Dim cellPointer As Variant
> >
> > 'C:\PathToYourMdb\Database.mdb (Change)
> > '
> > strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
> > & "Data Source=" & ThisWorkbook.Path & "\names.mdb;Persist Security
> > Info=False"
> > Set cnn = New ADODB.Connection
> > cnn.Open strConn
> >
> > For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
> > Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
> > 'If you project number field is text use this sSQL
> > sSQL = "SELECT EMP.* FROM EMP WHERE (((EMP.FullName)='" & cellPointer &
> > "'));"
> > 'If you project number field is number use this sSQL
> > 'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
> > (((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
> > Set rs = New ADODB.Recordset
> >
> > rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
> > If Not IsNull(rs.Fields("OrgCode").Value) Then
> > Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
> > End If
> > rs.Close
> > Set rs = Nothing
> > Next looper
> >
> > cnn.Close
> > Set cnn = Nothing

The change I made has nothing to do with the error message. All I did was to
change the location of where the data went in the workbook. The error you
are getting is refering to the data in the access database where the data is
coming from. You should of only changed one line in the orininal macro. It
is possible that if you eliminate the IF statement around the line of code
that was changed this error could produce this error message.

"Bobbo" wrote:

> Thanks for the response but now I get the following Run-time error .
> Either BOF or EOF is True, or the current record has been deleted.
>
> "Joel" wrote:
>
> > from
> > Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
> > to
> > Range("A" & looper) = Range("A" & looper) & rs.Fields("OrgCode").Value
> >
> > If you want a space between the data then this
> > Range("A" & looper) = Range("A" & looper) & " " & rs.Fields("OrgCode").Value
> >
> >
> >
> > "Bobbo" wrote:
> >
> > > I am using the code below to get some data out of access and put it in column
> > > "G". Instead of puting it in "G" how can I append it to what is in Column "A"
> > >
> > >
> > > ' look in names database and get ORG code based on user
> > > 'Needs reference the Axtive X Library 2.0 or higher
> > > Const projectIDColumn = "A" 'column B that have project number in it
> > > Const projectDescColumn = "G" 'column C that I would like to populate
> > > Dim cnn As ADODB.Connection
> > > Dim rs As ADODB.Recordset
> > > Dim sSQL As String, strConn
> > > Dim looper As Long
> > > Dim cellPointer As Variant
> > >
> > > 'C:\PathToYourMdb\Database.mdb (Change)
> > > '
> > > strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
> > > & "Data Source=" & ThisWorkbook.Path & "\names.mdb;Persist Security
> > > Info=False"
> > > Set cnn = New ADODB.Connection
> > > cnn.Open strConn
> > >
> > > For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
> > > Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
> > > 'If you project number field is text use this sSQL
> > > sSQL = "SELECT EMP.* FROM EMP WHERE (((EMP.FullName)='" & cellPointer &
> > > "'));"
> > > 'If you project number field is number use this sSQL
> > > 'sSQL = "SELECT tblProjects.* FROM tblProjects WHERE
> > > (((tblProjects.PROJECT_NUM)=" & cellPointer & "));"
> > > Set rs = New ADODB.Recordset
> > >
> > > rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
> > > If Not IsNull(rs.Fields("OrgCode").Value) Then
> > > Range(projectDescColumn & looper) = rs.Fields("OrgCode").Value
> > > End If
> > > rs.Close
> > > Set rs = Nothing
> > > Next looper
> > >
> > > cnn.Close
> > > Set cnn = Nothing