recordset.AddNew to RS based on query

K

Kamil

Hi.
Is it possible to add new record to my recordset which is based on a
query?
I want to use this recordset only for temp starage of data, and I
don't want to update values from this recordset to a table.

Code:
Q2 = "SELECT T_SMP_RCNR.KEY_ORDER, T_SMP_RCNR.RCNR,
T_SMP_RCNR.KEY_RCNR " & _
"FROM T_SMP_RCNR LEFT JOIN Q_MaxOfSMPRCNR ON
T_SMP_RCNR.KEY_ORDER = Q_MaxOfSMPRCNR.KEY_ORDER " & _
"WHERE T_SMP_RCNR.RCNR = [MaxOfSMPRCNR];"

rstQ2.Open Q2, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

[...]
rstQ2.Find "KEY_ORDER LIKE '" & keyOrd & "'"
If rstQ2.EOF Then
new_RCNR = 1
rstQ2.AddNew
rstQ2!key_order = keyOrd
rstQ2!RCNR = 1
rstQ2!key_rcnr = keyOrd & "." & new_RCNR
rstQ2.Update
MsgBox rstQ2!key_rcnr
Else
new_RCNR = rstQ2!RCNR + 1
rstQ2!RCNR = new_RCNR
rstQ2!key_rcnr = keyRcnr
End If

If it's not possible (or senseless) - then what different option can I
choose?

thanks for help,
best regards,
Kamil
 
K

Kamil

Hi.
Is it possible to add new record to my recordset which is based on a
query?
I want to use this recordset only for temp starage of data, and I
don't want to update values from this recordset to a table.

Code:
Q2 = "SELECT  T_SMP_RCNR.KEY_ORDER, T_SMP_RCNR.RCNR,
T_SMP_RCNR.KEY_RCNR " & _
        "FROM T_SMP_RCNR LEFT JOIN Q_MaxOfSMPRCNR ON
T_SMP_RCNR.KEY_ORDER = Q_MaxOfSMPRCNR.KEY_ORDER " & _
        "WHERE T_SMP_RCNR.RCNR = [MaxOfSMPRCNR];"

    rstQ2.Open Q2, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

[...]
rstQ2.Find "KEY_ORDER LIKE '" & keyOrd & "'"
        If rstQ2.EOF Then
            new_RCNR = 1
            rstQ2.AddNew
            rstQ2!key_order = keyOrd
            rstQ2!RCNR = 1
            rstQ2!key_rcnr = keyOrd & "." & new_RCNR
            rstQ2.Update
            MsgBox rstQ2!key_rcnr
        Else
            new_RCNR = rstQ2!RCNR + 1
            rstQ2!RCNR = new_RCNR
            rstQ2!key_rcnr = keyRcnr
        End If

If it's not possible (or senseless) - then what different option can I
choose?

thanks for help,
best regards,
Kamil
Click to expand...

I forgot to mention, that when I run my sub I get an error on
rstQ2.AddNew stating that it's read only.
 
B

Bill Mosca

In a word, no. If the recordset is updatable, the record will be inserted.
On the other hand if you did a
SELECT myfield INTO MyTempTable
FROM MyRealTable

the query would put the records in a new table named MyTempTable. You can do
whatever you want to that table and no records will go into the original
table. When you were finished you could run this to delete the table:
CurrentDB.Execute "DROP MyTempTable"
 
K

Kamil

In a word, no. If the recordset is updatable, the record will be inserted..
On the other hand if you did a
SELECT myfield INTO MyTempTable
FROM MyRealTable

the query would put the records in a new table named MyTempTable. You cando
whatever you want to that table and no records will go into the original
table. When you were finished you could run this to delete the table:
CurrentDB.Execute "DROP MyTempTable"

--
Bill Mosca, MS Access MVPhttp://www.thatlldoit.comhttp://mvp.suppor...roups.yahoo.com/group/MS_Access_Professionals


Hi.
Is it possible to add new record to my recordset which is based on a
query?
I want to use this recordset only for temp starage of data, and I
don't want to update values from this recordset to a table.
Click to expand...
Code:
Q2 = "SELECT  T_SMP_RCNR.KEY_ORDER, T_SMP_RCNR.RCNR,
T_SMP_RCNR.KEY_RCNR " & _
       "FROM T_SMP_RCNR LEFT JOIN Q_MaxOfSMPRCNR ON
T_SMP_RCNR.KEY_ORDER = Q_MaxOfSMPRCNR.KEY_ORDER " & _
       "WHERE T_SMP_RCNR.RCNR = [MaxOfSMPRCNR];"
Click to expand...
   rstQ2.Open Q2, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
Click to expand...
[...]
rstQ2.Find "KEY_ORDER LIKE '" & keyOrd & "'"
       If rstQ2.EOF Then
           new_RCNR = 1
           rstQ2.AddNew
           rstQ2!key_order = keyOrd
           rstQ2!RCNR = 1
           rstQ2!key_rcnr = keyOrd & "." & new_RCNR
           rstQ2.Update
           MsgBox rstQ2!key_rcnr
       Else
           new_RCNR = rstQ2!RCNR + 1
           rstQ2!RCNR = new_RCNR
           rstQ2!key_rcnr = keyRcnr
       End If
Click to expand...
If it's not possible (or senseless) - then what different option can I
choose?
Click to expand...
thanks for help,
best regards,
Kamil
Click to expand...
Click to expand...

thanks for help.
I have a problem with select into now:

Q2 = "SELECT T_SMP_RCNR.KEY_ORDER, T_SMP_RCNR.RCNR,
T_SMP_RCNR.KEY_RCNR INTO TEMP_SMP_RCNR " & _
"FROM T_SMP_RCNR LEFT JOIN Q_MaxOfSMPRCNR ON
T_SMP_RCNR.KEY_ORDER = Q_MaxOfSMPRCNR.KEY_ORDER " & _
"WHERE (T_SMP_RCNR.RCNR = [MaxOfSMPRCNR]);"

rstQ2.Open Q2, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

error message:
Syntax error in FROM clause.
This query works perfectly in query designer...
Where I did something wrong?
 
K

Kamil

In a word, no. If the recordset is updatable, the record will be inserted.
On the other hand if you did a
SELECT myfield INTO MyTempTable
FROM MyRealTable
Click to expand...
the query would put the records in a new table named MyTempTable. You can do
whatever you want to that table and no records will go into the original
table. When you were finished you could run this to delete the table:
CurrentDB.Execute "DROP MyTempTable"
Click to expand...
news:37063f1c-b684-4aae-9fcb-ee37118ef56d@z72g2000hsb.googlegroups.com....
Click to expand...
Hi.
Is it possible to add new record to my recordset which is based on a
query?
I want to use this recordset only for temp starage of data, and I
don't want to update values from this recordset to a table.
Code:
Q2 = "SELECT  T_SMP_RCNR.KEY_ORDER, T_SMP_RCNR.RCNR,
T_SMP_RCNR.KEY_RCNR " & _
       "FROM T_SMP_RCNR LEFT JOIN Q_MaxOfSMPRCNR ON
T_SMP_RCNR.KEY_ORDER = Q_MaxOfSMPRCNR.KEY_ORDER " & _
       "WHERE T_SMP_RCNR.RCNR = [MaxOfSMPRCNR];"
   rstQ2.Open Q2, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
[...]
rstQ2.Find "KEY_ORDER LIKE '" & keyOrd & "'"
       If rstQ2.EOF Then
           new_RCNR = 1
           rstQ2.AddNew
           rstQ2!key_order = keyOrd
           rstQ2!RCNR = 1
           rstQ2!key_rcnr = keyOrd & "." & new_RCNR
           rstQ2.Update
           MsgBox rstQ2!key_rcnr
       Else
           new_RCNR = rstQ2!RCNR + 1
           rstQ2!RCNR = new_RCNR
           rstQ2!key_rcnr = keyRcnr
       End If
If it's not possible (or senseless) - then what different option can I
choose?
thanks for help,
best regards,
Kamil
Click to expand...
Click to expand...

thanks for help.
I have a problem with select into now:

    Q2 = "SELECT T_SMP_RCNR.KEY_ORDER, T_SMP_RCNR.RCNR,
T_SMP_RCNR.KEY_RCNR INTO TEMP_SMP_RCNR " & _
        "FROM T_SMP_RCNR LEFT JOIN Q_MaxOfSMPRCNR ON
T_SMP_RCNR.KEY_ORDER = Q_MaxOfSMPRCNR.KEY_ORDER " & _
        "WHERE (T_SMP_RCNR.RCNR = [MaxOfSMPRCNR]);"

    rstQ2.Open Q2, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdTable

error message:
Syntax error in FROM clause.
This query works perfectly in query designer...
Where I did something wrong?
Click to expand...

I used disconnected recordset instead of temp table, and it works very
fast :)
 
Top