Change record value

[Sadukar]

Hi All,
Bit stuck with this. I would appreciate any suggestions.

I want to say if txt box1 = yes Then
table2, record number 14 has a value of -1.

If txtbox.value = "Yes" then
What code do I need here??????????
Else
End if

Note: table2 is not the data source for the form which contains the above code.

Thanks for taking a look
S.

 

[redapples]

on the hoof

You will need a reference to ADO for this tools|references on the menu bar

    Dim cnn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim stringtemp As String 'the find string for ADO

        Set cnn = CurrentProject.Connection
        rs.Open "Table2", cnn, 3, 3
        rs.MoveFirst
        stringtemp = "RecordID = '14'" 'builds find string
        rs.Find stringtemp
        rs.Edit
        rs!YourFeild = "-1" 'edits record
        rs.update
        rs.Close
        Set rs = Nothing
        Set cnn = Nothing

see how you go on I scrambled this from memory so may not work fully let me know.


redapples

 

[Sadukar]

I think my db is in DAO

I started in access 97 and converted to 2000.
Does this matter?

 

[redapples]

Should not matter as such. Things to consider
1. you can use DAO and ADO in the same DB. If you do ensure you always prefix declaration with DAO or ADODB as in ADODB.Recordset or DOA.Recordset so as not to confuse the poor thing.
2. make sure you have the aproppriate references added Tools|references
3. There is similar code for DAO have a look in the help files under Seek and Find for DAO. Seek is apparently faster than find (?)
4. the code is for Access 2000 not sure about it working in 97 but if you have updated should not matter

Redapples

 

[Sadukar]

I dont understand all you have said. I am not as well up on VBA as you are I thinks. I also have no access to a help file.

Anyway
I kind of got something working.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("TableName", dbOpenDynaset)

rs.Move 14
rs.Edit
rs!Myfield = "-1" 'setting y/n field to yes
rs.Update
rs.Close

This works fine except it modify's record number 15 rather than 14. I just changed it to 13 and it works fine.

Why is this?

Thanks for the help
S.

 

[redapples]

try hitting F1 while in VBA for help.
I think the problem is in the line;

       rs.move 14

in effect what you have asked it to do is move 14 records(rows) from its current position.
Also this is bad code as it assumes that records 1 to 14 all exist. What happens if a record is deleted? Records might not always exist in the same place. However well done for trying.

Is the number 14 the primary key (autonumber)?

      rs.Index = "PrimaryKey"
      rs.Seek "=", 14

try this if it is the primary key

 

[Sadukar]

I do not have an autonumber. The first field is numeric and sequencial but not auto.
The records will never be deleted. Information is constantly updated and a copy of the record is sent to another table as history.

rs.move 14
It does not matter what value I have in my primary field, It will go to the requested record (14) +1. Maybe it sees number 0 as a record even though there is no such record.
IE. If I try to change number 14 it will change record number 15 etc...

It seems to work fine and my info in my table is not changed. I know this is a messy way of going about this but should I be able to use this without having to worry that the records will be changed in any way???

 

[redapples]

rs.move 14

what this does is move a number of records.
what the code is saying is recordset.move (integer)
the integer is the number of rows you want to move from the current position. from what you have said it sound like when you ask it to do this it moves from the 1st row, 14 rows (to 15) if you are sticking with that code (I would suggest plugging away at another solution though) try rs.move 13 and see what happens

The index in the seek method requires a primary key but this does not need to be an autonumber field just needs to be a primary key

recordset.seek  = strmyprimarykey

you could try findfirst

recordset.{FindFirst | FindLast | FindNext | FindPrevious} criteria

criteria is a string like an SQL statement so your criteria would be something similar to

recordset.findfirst "[your field] = '14'"

hope this clears some of it up

 

[Sadukar]

Cant get any to work.

when I try rs.Index = "my primary Key Field" it crashs. I debug and hold cursor over object and it says Not a valid operation for this type of object.

I understand that it is a bad idea to use the 'move' so I will avoid it if I can.
I will try messing around with it again tomorrow and I will let you know how I get on.

I'm off home.
Thanks for help
S.

 

[redapples]

sorry should have been more specific, my bad.
rs.Index = "my primary Key Field"
should work if you use
rs.Index = "PrimaryKey"
this I think is a specific command related to the field whatever it is that is the primary key, and you need not specify what that field is as Access already knows.

 

[Sadukar]

I could not get rs.Index = "PrimaryKey" working. Does not matter now though.

Have it working perfect with this now
rs.FindNext "[Id] = Val(14)"
rs.Edit
rs!MyField= "-1"
rs.Update
I have no rs.close (I took it out because I do the same for several [Id] fields - using IF Satement). It works Fine.

Does it matter much that I have no rs.close? When I close the form would it close anyway?

 

[redapples]

I think it would close when you close the form.
do you just use the

set rs.openrecordset...

statement once?

the only problem with leaving it open is that you get a memory hit as a consequence (apparently). I have seen posted and read in book that you should always use close and

set rs = nothing

to clear the memory but early on I rarely did this. I now always do and have not noticed much difference.

Glad you got something working

 

[Sadukar]

Yes I only use set rs.openrecordset... once.

All works fine. I am only working with a small amount of records anyway. I dont think I have to worry about memory problems.

Anyway, Thanks for all the help.

Regards,
S.