field cannot be updated......

[jeffmoore]

The code shown below returns the error listed in the subject line. BUT when I test to see if the recordset is updatable the code returns TRUE.
TIA
Jeff


Dim ssql As String
Dim db As Database
Set db = CurrentDb
Dim rs As DAO.Recordset
ssql = "SELECT tbl_mill_qc_tests.index AS testindex, tbl_mill_qc_tests.*, tbl_mill_qc_test_names.tst_name_1, tbl_mill_qc_test_names.tst_name_2, tbl_mill_qc_test_names.tst_name_3, tbl_mill_qc_test_names.tst_name_4, tbl_mill_qc_test_names.tst_name_5, tbl_mill_qc_test_names.tst_name_6, tbl_mill_qc_test_names.tst_name_7 FROM tbl_mill_qc_test_names INNER JOIN tbl_mill_qc_tests ON tbl_mill_qc_test_names.index = tbl_mill_qc_tests.name_id WHERE (((tbl_mill_qc_tests.index)= " & Me.OpenArgs & "));"
Set rs = db.OpenRecordset(ssql)
If rs.EOF And rs.BOF Then
'NO RECORD NEED TO ADD A RECORD THEN SYNC INDEX
rs.AddNew
rs.Update
rs.MoveLast
rs.Edit
msgbox rs.updatable <<< this returns TRUE
rs!index = Me.OpenArgs <<< error here
rs.Update
Else
End If
rs.Close
Set rs = Nothing

 

[PHV]

Is by chance index an autonumber ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

I think you might be strugling with reserwed words. Both in the sql string, and as field, use [brackets], btw you have aliased "index" with "testindex", so you could try referencing that too:

[tt]rs![index]=me.openargs ' or
rs!testindex=me.openargs[/tt]

Roy-Vidar

 

[jeffmoore]

Nope I have tried the same thing on another field (yes/no) and get the same result.

 

[jeffmoore]

It fails in all these cases:
rs!index=me.openargs
rs!testindex=me.openargs
rs!tst_fail_1=me.openargs

Jeff

 

[jeffmoore]

All I'm trying to do is to sync two tables. The form that uses the first table as it's datasource takes it's index (autonumber) and passes it into the second form using the openargs prop.
The code in the first msg is in the onOpen event.
It trys to find a record in the second table. If there is no record, we add a new record, then stick the passed openagrs value into the second tables index. Then I want to make that the current record for the second form.
This should not be this difficult.
I must be forgeting something here.
Jeff

 

[RoyVidar]

So, you are not able to update any of the fields in the recordset, then perhaps the recordset isn't updateable at all (even if your msgbox states so?)

Another thought, when using an addnew - which position is the newly added record on? One would assume the the last, but it might not be (see the helpfiles on DAO AddNew), have you tried for instance

[tt]rs.bookmark=me.lastmodified[/tt]

Or try assigning the value before the first update:

[tt]rs.AddNew
rs!testindex=me.openargs
rs.update[/tt]

Roy-Vidar

 

[RoyVidar]

And or check if the field is updateable:

[tt]msgbox rs.fields("indextest").dataupdatable[/tt]

Roy-Vidar

 

[jeffmoore]

me.lastmodified returns method not found!!!

 

[RoyVidar]

typing to fast, off course, meant rs.lastmodified, the bookmark of the last modified record.

Roy-Vidar

 

[jeffmoore]

okay here is what i have
rs.addnew
rs.bookmark = rs.lastmodified
etc...

I get "no current record" error at the rs.bookmark... statment


tell you what is the an easier way to do this i'm open to suggestions here...
jeff

ps I have to leave for the afternoon and won't be avalible till tomorrow afternoon.
any ideas/suggestions are welcome. I'll talk to you all tomorrow
Thanks for the help
Jeff

 

[jeffmoore]

Okay this works!!!!!
Don't ask me why I can set the fields now ... It just started working 5 minutes ago....
I have run into a similar problem before where thwe code fails but after screwing around with it it starts to work.
I mean the code listed below is the same as the code in the first msg.
If anyone can explain the reason for access behaving this way I'd love to know. It gets real frusterating when I code something that I know runs and it dosen't, only to have the same code run alittle later.
Thanks again for all the help.
Jeff


rs.AddNew
rs!tst_fail_1 = True
rs!index = Me.OpenArgs
rs.Update
rs.Bookmark = rs.LastModified
Forms!testtemp.RecordSource = "SELECT tbl_mill_qc_tests.index AS testindex, tbl_mill_qc_tests.*, tbl_mill_qc_test_names.tst_name_1, tbl_mill_qc_test_names.tst_name_2, tbl_mill_qc_test_names.tst_name_3, tbl_mill_qc_test_names.tst_name_4, tbl_mill_qc_test_names.tst_name_5, tbl_mill_qc_test_names.tst_name_6, tbl_mill_qc_test_names.tst_name_7 FROM tbl_mill_qc_test_names INNER JOIN tbl_mill_qc_tests ON tbl_mill_qc_test_names.index = tbl_mill_qc_tests.name_id WHERE (((tbl_mill_qc_tests.index)= " & Me.OpenArgs & "));"

 

[PHV]

Have you compiled the code ? saved the form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244