Use an update query, using the key in the subForm.
update field from table where [fld]= forms!frmMaster!subForm!form!ID
I Am Using Below code btgetting runtime error '3061' - Description- Too Few Parameters. Expected1.
How to solve this....
Private Sub Command6_Click()
Dim strInsert As String
Dim db As DAO.Database
Dim todaydate As Date
todaydate = format_date(Now())
'Me.SFO_Subform.Form.SFO_Export = "True"
strInsert = "Update SFO SET SFO_Export_Date = '" & todaydate & "' WHERE ([Forms]![SFO_Subform]![SFO_Export] = " & "True" & ");"
Debug.Print strInsert
Set db = CurrentDb()
db.Execute strInsert, dbFailOnError
Set db = Nothing
End Sub
What are you showing in your Immediate Window? Maybe you need to add the Date Qualifiers
SFO_Export_Date = #" & todaydate & "# WHERE
Also, what is going on here?
]![SFO_Export] = " & "True" & ");"
Maybe
]![SFO_Export] = 'True');"
or
]![SFO_Export] = True);"
In Immediate Window below msg showing...
Update SFO SET SFO_Export_Date = #9/28/2016# WHERE Forms.SFO_Subform.Form.SFO_Export = TRUE;
I do not have Access open but I think that would produce the error you described. So use the Bang operator instead of the Dot operator and see if that helps.
Update SFO SET SFO_Export_Date = #9/28/2016# WHERE Forms!SFO_Subform.Form.SFO_Export = TRUE;
Also, you seem to be missing a reference to the main form name. Bring your SQL into a new blank Query object to see if you can fix the reference to the form.
Update SFO SET SFO_Export_Date = #9/28/2016# WHERE Forms!MainFormName!SFO_Subform.Form.SFO_Export = TRUE;
I have done changes but still showing same ....I found below result of where cluase (WHERE Forms.SFO_Subform.Form.SFO_Export = TRUE
as Result = Forms.SFO_Subform.Form.SFO_Export = -1 instead of "TRUE"
In VBA, True is the same as -1 and False is the same as 0.
The syntax in SQL isCan you show us what you have tried? You said you made changes and the changes are creating the same issue. What did you use that is creating the same issue?Code:= True
I Have Two Form...What I Want Whenever I made changes in SFO_Subform (Please find Subform Highligted True / False) and Then I will Click on Update button the changes will done main table ...
1) Main Form Name - SFO_Addition
2) Subform Name- SFO_Subform
1)SFO Main Form
2) SFO_Subform
Code:Private Sub Command6_Click() Dim strInsert As String Dim db As DAO.Database Dim todaydate As String todaydate = format_date(Now()) strInsert = "Update SFO SET SFO_Export_Date = " & todaydate & " WHERE Forms!SFO_Addition!SFO_Subform!SFO_Export = 'True';" Debug.Print strInsert Set db = CurrentDb() db.Execute strInsert, dbFailOnError Set db = Nothing End Sub '---------------------------------------------- Public Function format_date(t As String) format_date = Format(t, "DD-MM-YYYY") End Function
My best guess is to try the following syntax changes.Then, see what is displayed in the Immediate Window. Copy that and open a new Query. Paste that into the SQL view of a new query. Troubleshoot the syntax in your new query. Then, translate the working SQL into your VBA module.Code:todaydate = Now() strInsert = "Update SFO SET SFO_Export_Date = #" & todaydate & "# WHERE MyColumnName = Forms!SFO_Addition!SFO_Subform!SFO_Export"
Error in Immediate Window = Cannot find reference form
in SQL View= Asking for parameter value for below code
Code:Forms!SFO_Addition!SFO_Subform!SFO_Export
I do not understand what this means. YOu code should produce a result in the Immediate Window. That result is what you need to analyze. You can use the Query Designer to help you analyze the result that is located within the Immediate Window. The following link will help to further illustrate ...
http://www.baldyweb.com/ImmediateWindow.htm
'I do not understand what this means. YOu code should produce a result in the Immediate Window. That result is what you need to analyze. You can use the Query Designer to help you analyze the result that is located within the Immediate Window. The following link will help to further illustrate ...
http://www.baldyweb.com/ImmediateWindow.htm
Sorry ....
I am getting below line in immediate window
Update SFO SET SFO_Export_Date = #9/29/2016 6:22:05 PM# WHERE SFO_Export=Forms!SFO_Addition!SFO_Subform!SFO_Expo rt
You are going to need to analyze your WHERE criteria.Does that look like it makes any sense? What about ...Code:WHERE SFO_Export=Forms!SFO_Addition!SFO_Subform!SFO_Export
Do you want to UPDATE all the records in tbl SFO that meet the True criteria?Code:WHERE SFO_Export= True
What about something like ...
Would it be preferable to target unique records and update those records, only?Code:WHERE SFO_ID = 'SV498'
When I use Update SFO_Export= True then its updating already "TRUE" records....How to call field value from subform_Datasheet so that same can be used as Unique.....Or How to update only those records which are currently showing in subform_datasheet