Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
  1. #16
    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
    ...Or How to update only those records which are currently showing in subform_datasheet
    I think this is what you need to consider. For me, it is very easy to manage these situations. However, this is because I have already struggled through many scenarios until I understood many possible solutions.

    I can tell you that you will likely employ a DAO recordset to update one or more records that meet your criteria. However, you are skipping over important information contained in this thread. Before you can get to the point of updating records, you need to understand the dataset you will be working with.

    I suggest you take a step back from your forms for a minute and try to create an SQL statement that simulates a test scenario. Consider a query that contains records you wish to update. Create this query via the Query Designer. Create a SELECT query. Add criteria to your SELECT query. Add something like
    WHERE SFO_ID = 'SV498'

    Then look at the SQL. Then ask yourself how this can be more dynamic. Maybe
    WHERE SFO_ID = 'SV498' AND WHERE SFO_ID = 'SV499'

    Work on the Criteria as the Criteria pertains to your business rules. Hardcode some criteria that creates a dataset you can work with. Develop a WHERE statement that retrieves the data you need, the records that will be updated. Then consider alternative ways to create that dataset. Then understand how one solution may be better than another solution. Then begin the next step, the step of implementation.

  2. #17
    Nikh is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Apr 2016
    Posts
    19
    Thanks for suggessition...its doen but using below code..but now I want to export Ony "True" records into excel sheet how to do this using vba code...

    Code:
    
    Dim rst As DAO.Recordset
    
    
    Set rst = Me.SFO_Subform.Form.Recordset
    If rst.RecordCount = 0 Then
    MsgBox " No  Records to Export"
    Exit Sub
    End If
        rst.MoveFirst
        Do While Not rst.EOF()
            With rst
                            
                If !SFO_Export = -1 And IsNull(!SFO_Export_Date) Or !SFO_Export_Date < Now() - 15 Then
                'rst.Edit
                '!SFO_Export_Date = Now()
                'rst.Update
                End If
                 Debug.Print !SFO_Export = "True" 'Statment is the field name
                
                .MoveNext
                
         
           End With
    
    
        Loop

  3. #18
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I am curious what the purpose of the following was/is ...
    Code:
                If !SFO_Export = -1 And IsNull(!SFO_Export_Date) Or !SFO_Export_Date < Now() - 15 Then
                'rst.Edit
                '!SFO_Export_Date = Now()
                'rst.Update
                End If
                 Debug.Print !SFO_Export = "True" 'Statment is the field name
                
                .MoveNext
    I have not tested it, but I believe you can assign the Value "True" to the current record's !SFO_Export field if you have the rst.Update line of code uncommented. Also, it is recommended not to use special characters in names, e.g. ! or ? or (. The exception is the underscore).

    Aside from that, you can export a DAO recordset to an excel spreadsheet by Automating Excel and using its CopyFromRecordset method of the Worksheet Object.

    Here is one untested example
    https://www.accessforums.net/showthr...494#post281494

    Here is an example that exports to a specific range of cells
    https://www.accessforums.net/showthr...089#post305089

Page 2 of 2 FirstFirst 12
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