Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Nikh is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2016
    Posts
    19

    How to Update Selected Subform Data Into Table

    Hi..



    I want to update selected field from subform datasheet into access table using vba code or query...

    For eg. If I tick on SFO_ID then auto Field update into table with EDIT DATE update into respective table...

    Click image for larger version. 

Name:	fff.png 
Views:	16 
Size:	12.3 KB 
ID:	25958

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Use an update query, using the key in the subForm.

    update field from table where [fld]= forms!frmMaster!subForm!form!ID

  3. #3
    Nikh is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2016
    Posts
    19
    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

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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);"

  5. #5
    Nikh is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2016
    Posts
    19
    In Immediate Window below msg showing...

    Update SFO SET SFO_Export_Date = #9/28/2016# WHERE Forms.SFO_Subform.Form.SFO_Export = TRUE;

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    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;

  7. #7
    Nikh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    19
    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"

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    In VBA, True is the same as -1 and False is the same as 0.

    The syntax in SQL is
    Code:
    = True
    Can 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?

  9. #9
    Nikh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    19
    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
    Click image for larger version. 

Name:	SFO_Main Form.png 
Views:	10 
Size:	3.7 KB 
ID:	25966

    2) SFO_Subform

    Click image for larger version. 

Name:	Subform.png 
Views:	10 
Size:	15.1 KB 
ID:	25967



    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

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    My best guess is to try the following syntax changes.
    Code:
    todaydate = Now()
    strInsert = "Update SFO SET SFO_Export_Date = #" & todaydate & "# WHERE MyColumnName = Forms!SFO_Addition!SFO_Subform!SFO_Export"
    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.

  11. #11
    Nikh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    19
    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




  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Nikh View Post
    Error in Immediate Window = Cannot find reference form
    ...

    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

  13. #13
    Nikh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    19
    Quote Originally Posted by ItsMe View Post
    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

  14. #14
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by Nikh View Post
    ...
    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.
    Code:
    WHERE SFO_Export=Forms!SFO_Addition!SFO_Subform!SFO_Export
    Does that look like it makes any sense? What about ...
    Code:
    WHERE SFO_Export= True
    Do you want to UPDATE all the records in tbl SFO that meet the True criteria?

    What about something like ...
    Code:
    WHERE SFO_ID = 'SV498'
    Would it be preferable to target unique records and update those records, only?

  15. #15
    Nikh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    19
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 04-15-2015, 04:20 PM
  2. Replies: 17
    Last Post: 10-27-2014, 04:15 AM
  3. Replies: 8
    Last Post: 05-30-2013, 05:06 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Replies: 1
    Last Post: 10-13-2010, 12:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums