add.new help

[EBee]

I still cannot get any record to copy to a new table, I am trying to get records from two table and put it in one table

If rst!Number > 1 Then

rstNew.AddNew
rstNew!GetRows = rst.GetRows
rstNew!GetRows = rstOpen.GetRows
rstNew.Update

end If

this simple addNew does not work, is this correct syntax??
erwin

 

[Aivars]

rstNew!GetRows = rst!GetRows
rstNew!GetRows = rstOpen!GetRows

Is these rows example? If not one of them is needless (double update of field).

Aivars

 

[EBee]

these are real syntax on my code. I am trying to grab 2 separete rowes from 2 diff. tables and then put it in another table. The problem is it won't even work on a simple If. . . Then

I will try putting a ! rst!getrows

thanks
erwin

 

[jasonp45]

Here is some code on a project I am working on in VBA/Access. I was having trouble getting the recordset to actually update the table; turned out I had the locktype set to 'adLockBatchOptimistic'. Once I reset to 'adLockOptimistic' it worked fine. You may have your cursortype set to read only, as this is the default.

With rs
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Source = "MyTable"
.Open
For a = 0 To UBound(MyProductArray)
.AddNew
.Fields(1).Value = iCustNum
.Fields(2).Value = MyProductArray(a)
.Update
Next a
a = 0
.Close
End With

 

[EBee]

Do i need to list all the fields on the table for it to update? I need to read up on some of the syntax on your code since i don't understand most of them yet. which ones are table names in your code

erwin. . lost

 

[EBee]

this does not work... help

Private Sub Buildfile()

Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstNew As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("auxil1&quot
Set rstOpen = db.OpenRecordset("Auxil_Logic_Open&quot
Set rstNew = db.OpenRecordset("table6&quot

rst.MoveFirst
rstOpen.MoveFirst
Do Until Not rst.EOF

If rst!Trans_Number = "6" Then
If rst!Number = rstOpen!Number Then

rstNew.AddNew
rstNew!GetRows = rst!GetRows ' no collection error
rstNew!GetRows = rstOpen!GetRows
rstNew.Update


End If
End If

rst.MoveNext
' rstOpen.MoveNext

Loop


End Sub

 

[mvdmeij]

This will not work...

rstNew!GetRows = rst!GetRows

First of all the part to the right rstnew!GetRows doesn't work, Access expects a Fieldname to which a value is parsed.
Something like rstNew![Name] = rst![Name] (so you have to specifiy the fieldnames).

Second: for adding another record you will have to add another AddNew...Update

with rstNew
.Addnew
!Name = rst!Name
.Update

.Addnew
!Name = rstOpen!Name
.Update
end with

Unless you want to combine the two records into one new record then it would read something like this:

with rstNew
.Addnew
!Name = rst!Name
!Birhtday = rstOpen!Birthday
.Update
end with

The only other way is using SQL statement INSERT INTO..








Good luck,

Mark.

 

[EBee]

Thank you, this more make sense to me, please give me the syntax for the SQL- I just want to know it anyway just in case i want to do it that way. I deal with a lot of copy and insert, and the sql maybe easier. . i am still a novice at modules.

thanks again
erwin

 

[EBee]

Private Sub Newtable()

Dim db As Database
Dim rst As Recordset
Dim rstOpen As Recordset
Dim rstNew As Recordset
Dim rstExcept As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("auxil1&quot
Set rstOpen = db.OpenRecordset("Auxil_Logic_Open", dbOpenDynaset)
Set rstNew = db.OpenRecordset("table6&quot
Set rstExcept = db.OpenRecordset("tableExcept&quot



rst.MoveFirst
rstOpen.MoveFirst
Do Until rst.EOF


If rst!Number = rstOpen!Number Then
If rst!Trans_Number = "6" Then
If (rst!Tx_Date = rstOpen!Tx_Date) Then
If (DateDiff(&quot;s&quot;, rst!Tx_Time, rstOpen!Tx_Time) < 60) Then
With rstNew
.AddNew
!Number = rst!Number
!Denom = rst!Denom
!Location = rst!Location
!Emp_Name = rst!Emp_Name
!Tx_Date = rst!Tx_Date
!Tx_Time = rst!Tx_Time
!Trans_Number = rst!Trans_Number
!Trans_Desc = rst!Trans_Desc
.Update
End With

With rstNew
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With
Else

With rstExcept
.AddNew
!Number = rstOpen!Number
!Denom = rstOpen!Denom
!Location = rstOpen!Location
!Emp_Name = rstOpen!Emp_Name
!Tx_Date = rstOpen!Tx_Date
!Tx_Time = rstOpen!Tx_Time
!Trans_Number = rstOpen!Trans_Number
!Trans_Desc = rstOpen!Trans_Desc
.Update
End With
End If
End If
End If

End If
rstOpen.Delete
rst.MoveNext
rstOpen.MoveNext

Loop

End Sub

It works, i am finally getting some data in the new table. Now, my problem is i want to delete the record from the rstOpen table. First, before i delete the line record in the rstOpen, I want to insert it in a new table called rstExcept. I tried to do the .Delete and it deleted all the record in the rstOpen. I just wanted to delete the line record that did satisfy the criteria. I should have an empty rstOpen table when done. Another thing, how can i set my loop so that rstOpen(firstrecord) match itself to all the rst records. then rstOpen(second record) match itself to all the rst records.

thanks
erwin