Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2015
    Posts
    1

    Unanswered: VBA in Microsoft Access

    Hi everyone I'm here for some guidance on a project I'm currently working on. I have very minimal knowledge of VBA and currently performing a task on access that will require the use of VBA.

    Problem statement: I have created a query that has 4000 plus cells. Initially there were a total of 18000 plus but I have refined my query based on what is required(by types, buildings, ratings) so now it's roughly 4200. I am to program it such a way where I can randomly generate 10% of my sample(4200) by buildings every so often and then next time it would be 10% of 90% that's left and so on till I reach 100% (end of cycle).

    I can provide specific information and even attach a screenshot or my file. I'm new to VBA and any help would be appreciated.

    Thanks!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    what if you put in a USE field? This code will scan the records and mark 10% of the USE = true.
    then you only need query where [USE] = true

    Code:
       'this code marks 10% of total records then flags that many records to be used.
    Public Sub RandomRecs()
    Dim iTot As Long, iRec As Long
    Dim iSample As Integer
    Dim vQry
    Dim rst  'As Recordset
    Dim i As Integer
    
    vQry = "tNAMES"          'query to get items NOT marked as USE
    iTot = DCount("*", vQry)  'get the total rec count
    iSample = 10    'iTot * 0.1         '10% of total
    iRec = 1
    Set rst = CurrentDb.OpenRecordset(vQry)
    With rst
       While Not .EOF
           If iRec = iSample Then
              .Edit
              .Fields("USE").Value = True
              .Update
              iRec = 0
           End If
          
          .MoveNext
          iRec = iRec + 1
       Wend
    End With
    Set rst = Nothing
    End Sub

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    HERE IS A BETTER METHOD:

    usage: MarkRandomRecs "tTable", 10

    this will mark 10% of the records in tTable.
    NOTE: the table MUST have fields:
    [USE], boolean, 'a marker to flag
    [RecNum], long, 'we must assign recordnumbers to each record in order to pick a 'random' one.



    Code:
       'this code marks 10% of total records then flags that many records to be used.
    '-----------------
    Public Sub MarkRandomRecs(ByVal pvTbl, ByVal piPct As Integer)
    '-----------------
    
    Dim iTot As Long, iRec As Long, iRnd As Long
    Dim iSample As Integer
    Dim vQry
    Dim rst  'As Recordset
    Dim i As Integer
    
    DoCmd.SetWarnings False
    sSql = "UPDATE [" & pvTbl & "] SET [USE] = False;"
    DoCmd.RunSQL sSql
    AssignRecNum pvTbl
    
    vQry = pvTbl              'query to get items NOT marked as USE
    iTot = DCount("*", vQry)  'get the total rec count
    iSample = iTot * (piPct / 100)   'get the # records we want sampled from the %
    iRec = 1
    
    While iRec < iSample
        iRnd = Int(iTot * Rnd + 1)
        sSql = "select * from [" & pvTbl & "] where [RecNum] = " & iRnd
        
        Set rst = CurrentDb.OpenRecordset(sSql)
        With rst
               If Not .Fields("USE").Value Then
                  .Edit
                  .Fields("USE").Value = True
                  .Update
                  
                  iRec = iRec + 1
               End If
        End With
    Wend
    DoCmd.SetWarnings True
    DoCmd.OpenTable pvTbl
    MsgBox "Done"
    Set rst = Nothing
    End Sub
    
       'assign record numbers to all records
    '-----------------
    Public Sub AssignRecNum(ByVal pvTbl)
    '-----------------
    Dim iTot As Long, iRec As Long
    Dim iSample As Integer
    Dim vQry
    Dim rst  'As Recordset
    Dim i As Integer
    
    vQry = pvTbl
    iRec = 1
    Set rst = CurrentDb.OpenRecordset(vQry)
    With rst
       While Not .EOF
              .Edit
              .Fields("RecNum").Value = iRec
              .Update
          
          .MoveNext
          iRec = iRec + 1
       Wend
    End With
    Set rst = Nothing
    End Sub

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •