Recordset Question (Record modification)

[fuldingo]

I need to select a recorset from a Table A and modify the value of one field. I have (previously stored in a text box)the "Record_Id "from the Table A where a want to go and perform a change in the field "Served". I´m using the navigations buttons to add new records to Table A trhoug a form, but i don´t want to use the navigation buttons in order to change the value. The question is, how do i select a recorset from Table A (knowing the value of the field record_id), then change the value of the field "Served" and then update the changing (is its apply).

Thank you

 

[kewo99]

Try this:

Dim rs as DAO.Recordset
set rs = Currentdb.openrecordset(&quot;SELECT [Served] from [Table A] Where Record_ID = &quot; & <textbox record_id>,dbOpenDynaset)
rs.Edit
rs(&quot;Served&quot = <new value for Served>
rs.Update
rs.Close
set rs = Nothing

This code assumes that Record_ID is a number; if text change the set line to:
set rs = Currentdb.openrecordset(&quot;SELECT [Served] from [Table A] Where Record_ID = '&quot; & <textbox record_id> & &quot;'&quot;,dbOpenDynaset)

Ken

 

[fuldingo]

I think that this is works but i still having some troubles with the WHERE option of the SQL instruction because Record ID is located at column 0 of a combo box instead a text box (my mistake). Now, Record ID is a number but how does the SQL instruction change for a combo box ?

Thanks.

 

[kewo99]

The syntax should be ok for a combo box as long as the bound column is the one containing the record ID.

An alternate approach would be to open all records in the table and then use rs.findfirst to goto the one you want to edit.

Ken

 

[AvGuy]

Perhaps easier to understand, but functionally equivalent to what Ken suggests, is to just open the recordset and then use the seek method to find the record you want to change. The following assumes that the Record ID is the indexed primary key of the target table:

Dim rs as DAO.Recordset
Set rs = Currentdb.OpenRecordset(&quot;Name of Table&quot
With rs
.Index = &quot;PrimaryKey&quot;
.Seek &quot;=&quot;, Forms!MyForm!TextBoxWithRecordID
if not .Nomatch then
.Edit
!FieldName = <new value>
.Update
Else
Msgbox &quot;Record not found in table.&quot;
End if
.Close
End with
set rs = Nothing

Cheers,