AddNew - Pocket PC

[clivegisworkuk]

I am running this on a Pocket PC NOT on a PC.
I am trying to add a row from Table A into table B.
This must be possible!!!!!

Any assistance appreciated.

Cheers
Clive

+++++++++++++++++++++++++++++++++++++++
Private Sub Test_Click()
Dim RS2 As ADOCE.Recordset
Dim SQL As String
Dim InputText As String
Dim ColumnNames As String
Dim ColumnValues As String
Dim ParamName As String
Dim MyVar As String
Set RS2 = CreateObject("ADOCE.Recordset.3.1")

RS2.Open Test, "\My Documents\Housing.cdb"

' UPDATE test INNER JOIN HousingEstates ON (test.BlockName = HousingEstates.Block)
' AND (test.ContractArea = HousingEstates.ContractArea) SET;

'SQL = "SELECT * FROM ContractAreas INNER JOIN HousingEstates"
'SQL = SQL & " ON ContractAreas.ContractArea = HousingEstates.ContractArea"
'SQL = SQL & " WHERE ContractAreas.ContractArea = 2"
'RS("Blockname") = Block

MyVar = "Scott Court"
MsgBox (MyVar)

SQL = "SELECT * FROM HousingEstates "
SQL = SQL & " WHERE HousingEstates.Block = '" & MyVar & "'" 'Scott House'"
Block = SQL
RS2.AddNew
RS("BlockName") = Block

Dim ifield As Integer
For ifield = 0 To RS.Fields.Count - 1
RS2.Fields(ifield).Value = RS.Fields(ifield).Value
Next

RS2.Update

'Remove existing data from grid
For i = 1 To GridCtrl1.Rows
GridCtrl1.RemoveItem 0
Next

'Set the Grid columns equal to the field count
GridCtrl1.Cols = RS.Fields.Count

'Get the column names
For i = 0 To RS2.Fields.Count - 1
ColumnNames = ColumnNames & RS2.Fields(i).Name & vbTab
Next

'Add the column headers to the grid
GridCtrl1.AddItem ColumnNames

'Loop through the recordset
While Not RS.EOF

'Get the column values for this row
For i = 0 To RS2.Fields.Count - 1
ColumnValues = ColumnValues & RS2.Fields(i).Value & vbTab
Next

'Add the column values to the row
GridCtrl1.AddItem ColumnValues

'Set ColumnValues to a zero length string
'so it can be refilled with the next row
ColumnValues = ""

RS.MoveNext
Wend

'Close and dereference the Recordset
RS2.Close
Set RS2 = Nothing

End Sub

[Douglas J. Steele]

Pocket Access isn't the same thing as Access, so I have no way of checking,
but is there an INSERT INTO statement available to you?

The usual syntax is

Multiple-record append query:

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

Single-record append query:

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

<cl************@yahoo.co.uk> wrote in message
news:41*************************@posting.google.co m...

I am running this on a Pocket PC NOT on a PC.
I am trying to add a row from Table A into table B.
This must be possible!!!!!

Any assistance appreciated.

Cheers
Clive

+++++++++++++++++++++++++++++++++++++++
Private Sub Test_Click()
Dim RS2 As ADOCE.Recordset
Dim SQL As String
Dim InputText As String
Dim ColumnNames As String
Dim ColumnValues As String
Dim ParamName As String
Dim MyVar As String
Set RS2 = CreateObject("ADOCE.Recordset.3.1")

RS2.Open Test, "\My Documents\Housing.cdb"

' UPDATE test INNER JOIN HousingEstates ON (test.BlockName = HousingEstates.Block) ' AND (test.ContractArea = HousingEstates.ContractArea) SET;

'SQL = "SELECT * FROM ContractAreas INNER JOIN HousingEstates"
'SQL = SQL & " ON ContractAreas.ContractArea = HousingEstates.ContractArea" 'SQL = SQL & " WHERE ContractAreas.ContractArea = 2"
'RS("Blockname") = Block

MyVar = "Scott Court"
MsgBox (MyVar)

SQL = "SELECT * FROM HousingEstates "
SQL = SQL & " WHERE HousingEstates.Block = '" & MyVar & "'" 'Scott House'"
Block = SQL
RS2.AddNew
RS("BlockName") = Block

Dim ifield As Integer
For ifield = 0 To RS.Fields.Count - 1
RS2.Fields(ifield).Value = RS.Fields(ifield).Value
Next

RS2.Update

'Remove existing data from grid
For i = 1 To GridCtrl1.Rows
GridCtrl1.RemoveItem 0
Next

'Set the Grid columns equal to the field count
GridCtrl1.Cols = RS.Fields.Count

'Get the column names
For i = 0 To RS2.Fields.Count - 1
ColumnNames = ColumnNames & RS2.Fields(i).Name & vbTab
Next

'Add the column headers to the grid
GridCtrl1.AddItem ColumnNames

'Loop through the recordset
While Not RS.EOF

'Get the column values for this row
For i = 0 To RS2.Fields.Count - 1
ColumnValues = ColumnValues & RS2.Fields(i).Value & vbTab
Next

'Add the column values to the row
GridCtrl1.AddItem ColumnValues

'Set ColumnValues to a zero length string
'so it can be refilled with the next row
ColumnValues = ""

RS.MoveNext
Wend

'Close and dereference the Recordset
RS2.Close
Set RS2 = Nothing

End Sub