Etxezarreta
Member
- Local time
- Today, 13:58
- Joined
- Apr 13, 2020
- Messages
- 175
Hello everyone,
I would like to find the best way to modify a table, out of two methods:
I usually create a table builinding a string with SELECT INTO and the content of form controls as variables in the WHERE clause, then using this a sourrce for anADO recordset.. Then I use and UPDATE query in VBA to modify the table. I am sure there are best ways for the same result.
-I have just tried almost the same sql string, only with the using SELECT instead of SELECT INTO in an access query object, then I have used this query in a subform: the table is directly modified if you modify this subform. Quick and simple.
I wonder how you can do the same without using the access query object, using a 100% VBA code and only SELECT instead of INSERT INTO and UPDATE : if I use the following code to populate an independant subform, the correct information appears, but no way to change the subform content, impossible to change the table content. I checked the properties of the form and subform: nothing is locked or restricted.
date_DateSelection = Me.txtbox_AppelDate.Value
str_NomDSPSelection = Me.combo_AppelNomDsp.Value
'MsgBox Format(date_DateSelection, "dd\/mm\/yyyy")
'Sql eskaria:
str_Sql = "SELECT Id_TacheAdmin, Date_tache, FK_ID_DSP, FK_ID_Tache, Duree, Lieu" & _
" FROM DSP_TachesAdmin_SaisirTachesAdmin" & _
" WHERE FK_ID_DSP =" & str_NomDSPSelection & " " & _
" AND Date_tache = #" & Format(date_DateSelection, "mm\/dd\/yyyy") & "#"
With oRcdset
.Source = str_Sql
.ActiveConnection = oConnection
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With
Set Me.DSP_TachesAdmin_sf_Modif_SaisirTachesAdmin.Form.Recordset = oRcdset
Me.Refresh
oRcdset.Close
oConnection.Close
Set oConnection = Nothing
Set oRcdset = Nothing
Thank you for your help.
Etxe.
I would like to find the best way to modify a table, out of two methods:
I usually create a table builinding a string with SELECT INTO and the content of form controls as variables in the WHERE clause, then using this a sourrce for anADO recordset.. Then I use and UPDATE query in VBA to modify the table. I am sure there are best ways for the same result.
-I have just tried almost the same sql string, only with the using SELECT instead of SELECT INTO in an access query object, then I have used this query in a subform: the table is directly modified if you modify this subform. Quick and simple.
I wonder how you can do the same without using the access query object, using a 100% VBA code and only SELECT instead of INSERT INTO and UPDATE : if I use the following code to populate an independant subform, the correct information appears, but no way to change the subform content, impossible to change the table content. I checked the properties of the form and subform: nothing is locked or restricted.
date_DateSelection = Me.txtbox_AppelDate.Value
str_NomDSPSelection = Me.combo_AppelNomDsp.Value
'MsgBox Format(date_DateSelection, "dd\/mm\/yyyy")
'Sql eskaria:
str_Sql = "SELECT Id_TacheAdmin, Date_tache, FK_ID_DSP, FK_ID_Tache, Duree, Lieu" & _
" FROM DSP_TachesAdmin_SaisirTachesAdmin" & _
" WHERE FK_ID_DSP =" & str_NomDSPSelection & " " & _
" AND Date_tache = #" & Format(date_DateSelection, "mm\/dd\/yyyy") & "#"
With oRcdset
.Source = str_Sql
.ActiveConnection = oConnection
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With
Set Me.DSP_TachesAdmin_sf_Modif_SaisirTachesAdmin.Form.Recordset = oRcdset
Me.Refresh
oRcdset.Close
oConnection.Close
Set oConnection = Nothing
Set oRcdset = Nothing
Thank you for your help.
Etxe.