Editing query data via code

[Peps]

Hi Guys,

I've go a code that edits data in my [Taxi_Service] table:


Function Crew3_To_Crew2() As Variant

Dim db As Database
Dim Table As Recordset
Set db = DBEngine(0)(0)
Set Table = db.OpenRecordset("Taxi_Service")
Table.Edit

Table("Rank_3") = Forms!Taxi_Service!Rank_2
Table("Name_3") = Forms!Taxi_Service!Name_2
Table("Sirname31") = Forms!Taxi_Service!Sirname21
Table("Sirname32") = Forms!Taxi_Service!Sirname22
Table("Nationality_3") = Forms!Taxi_Service!Nationality_2
Table("Crew_Visa3") = Forms!Taxi_Service!Crew_Visa2


Forms!Taxi_Service!Rank_2 = Null
Forms!Taxi_Service!Name_2 = Null
Forms!Taxi_Service!Sirname21 = Null
Forms!Taxi_Service!Sirname22 = Null
Forms!Taxi_Service!Nationality_2 = Null
Forms!Taxi_Service!Crew_Visa2 = False

db.Close


End Function

But I'd like to edit data from a query Called [Update_Taxi_Service] using the same method. Since Set Table = db.OpenRecordset("Update_Taxi_Service") is not doing anything, I'd like to know where I'm going wrong.

Thanks for any answers.
Peps

 

[dhookom]

What is the SQL of your query "Update_Taxi_Service"? Also, I would avoid creating a recordset with a name of "Table".

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("Update_Taxi_Service")

I also think you need to add
rs.Update
after updating the values.

I am surprised there is no code to find a specific record.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Peps]

Thanks for the reply,

Here's a copy of the SQL:

SELECT Taxi_Service.WOrder, Taxi_Service.Id, Taxi_Service.Oper, Taxi_Service.Ref, Taxi_Service.Code, Taxi_Service.VesselName, Taxi_Service.Voyage, Taxi_Service.Trip, Taxi_Service.Rank_1, Taxi_Service.Name_1, Taxi_Service.Sirname1, Taxi_Service.Sirname2, Taxi_Service.Nationality_1, Taxi_Service.Rank_2, Taxi_Service.Name_2, Taxi_Service.Sirname21, Taxi_Service.Sirname22, Taxi_Service.Nationality_2, Taxi_Service.Rank_3, Taxi_Service.Name_3, Taxi_Service.Sirname31, Taxi_Service.Sirname32, Taxi_Service.Nationality_3, Taxi_Service.SDate, Taxi_Service.STime, Taxi_Service.Flight, Taxi_Service.Notes, Taxi_Service.Taxi, Taxi_Service.Total, Taxi_Service.Doctor, Taxi_Service.Repeng, Taxi_Service.Pckgs, Taxi_Service.User, Taxi_Service.UserNow, Taxi_Service.Modified, Taxi_Service.ModifiedNow, Taxi_Service.Embarking, Taxi_Service.Disembarking, Taxi_Service.Actual_Date
FROM Taxi_Service
WHERE (((Taxi_Service.WOrder)=[forms]![txisvc]![Worder]));



Here you have a copy of the code that still does not work.


Function Crew3_To_Crew2() As Variant

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("Update_Taxi_Service")

rs.Edit

rs("Rank_2") = Forms!Taxi_Service!Rank_3
rs("Name_2") = Forms!Taxi_Service!Name_3
rs("Sirname21") = Forms!Taxi_Service!Sirname31
rs("Sirname22") = Forms!Taxi_Service!Sirname32
rs("Nationality_2") = Forms!Taxi_Service!Nationality_3
rs("Crew_Visa2") = Forms!Taxi_Service!Crew_Visa3


rs.Update
db.Close

End Function

As you can see, I've changed the name of the recordset, to be honest I've already tried renaming but without any luck.
I have spent far too long on this but I'm not going to give up.

Thanks for your help
Peps

 

[dhookom]

Try add the code
rs.MoveFirst
If Not rs.EOF and rs.BOF Then
rs.Edit
'etc
rs.Update
Else
Msgbox "No record to edit"
End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]