RE: [MS_AccessPros] RE: Splitting access 2010 database

 

Hi John

When you open a DAO.Recordset on a linked table, it opens a Dynaset, not a Table-type recordset (which is why Seek does not work).  The RecordCount property of a Dynaset is either 0 (no records) or 1 (some records).  This is so as to avoid needing to retrieve all the records in order to get a proper count.

Instead of this:
    For i = 0 To rsIPAllocation.RecordCount - 1
        . . .
    Next i

try this:
    Do Until rsIPAllocation.EOF
        . . .
    Loop

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Heinze
Sent: Wednesday, 26 February 2014 11:07
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] RE: Splitting access 2010 database

 
I have trusted both the front and back end locations and it still does not work. For some reason it does not increment the i variable. The for next loop only executes 2 time. 
Here is the code:

Public Sub cmdListIPAllocation_Click()
       
    Dim db As Database
    Dim rsCustInfo As Recordset
    Dim rsIPAllocation As Recordset
    Dim tdf As TableDef
    Dim i As Integer
    Dim intAcctNumLen As Integer
    Dim strRecord As String
    Dim strRecordsFound As String
    Dim strIPAllocationAcctNum As String
    Dim intRecordLimit As Integer
    Dim intBuffered As Integer
    Dim strBuff1 As String
    Dim strBuff2 As String
    'spacing for text
    Dim strLSetIPAddress As String
    Dim strLSetIPRange As String
    Dim strLSetSubnetMask As String
    Dim strLSetStaticRoute As String
    Dim strLSetVLAN As String
    Dim strLSetCustomer As String
    'spacing for text
    Dim strBuffIPAdd As String
    Dim strBuffIPRange As String
    Dim strBuffSubnetMask As String
    Dim strBuffVLAN As String
    Dim strBuffStaticRoute As String
    Dim strBuffCustomer As String
        
    strBuffIPAdd = "123456789|1234567"
    strBuffIPRange = "123456789-"
    strBuffSubnetMask = "123456789-123456789-123456789-123456789-"
    strBuffVLAN = "12345"
    strBuffStaticRoute = "123456789|1234567"
    strBuffCustomer = "123456789|123456789-123456789-"
    
    Set db = CurrentDb
    Set rsCustInfo = db.OpenRecordset("tblCustInfo")
    Set rsIPAllocation = db.OpenRecordset("tblIPAllocation")
    
  
    For i = 0 To rsIPAllocation.RecordCount - 1
        'Searches through db on record at a time
        strIPAllocationAcctNum = rsIPAllocation.Fields("Account Num")
        If strIPAllocationAcctNum = (Me.AccountNum) Then
            MsgBox (i)
            MsgBox ("hello")
            intRecordLimit = intRecordLimit + 1
                
            strLSetIPAddress = rsIPAllocation.Fields("IP Address")
            strLSetIPRange = rsIPAllocation.Fields("IP Range")
            strLSetSubnetMask = rsIPAllocation.Fields("Subnet Mask")
            strLSetVLAN = CStr(rsIPAllocation.Fields("VLAN ID Num"))
            strLSetStaticRoute = rsIPAllocation.Fields("Static Route")
            strLSetCustomer = rsIPAllocation.Fields("Customer")
            
            LSet strBuffIPAdd = strLSetIPAddress
            LSet strBuffIPRange = strLSetIPRange
            LSet strBuffSubnetMask = strLSetSubnetMask
            LSet strBuffVLAN = strLSetVLAN
            LSet strBuffStaticRoute = strLSetStaticRoute
            LSet strBuffCustomer = strLSetCustomer
             
            strRecord = strBuffIPAdd & strBuffIPRange & strBuffSubnetMask & strBuffVLAN & strBuffStaticRoute & strBuffCustomer
            
            strRecordsFound = strRecordsFound + strRecord + vbCrLf
            
            If intRecordLimit = 10 Then
                
                'calls sub TextBoxIPAllocatioin and passes strRecordsFound
                TextBoxIPAllocatioin (strRecordsFound)
                MsgBox ("OK to continue. Displaying 10 records.")
                
                intRecordLimit = 0
                strRecordsFound = ""
            End If
            
        End If
        rsIPAllocation.MoveNext
    Next i
    
    'calls sub TextBoxIPAllocatioin and passes strRecordsFound
    If strRecordsFound <> "" Then
        TextBoxIPAllocatioin (strRecordsFound)
    Else
        'do nothing
    End If
    
    On Error GoTo ErrorHandler
    Exit Sub
    
ErrorHandler:
'Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
'Resume with statement following occurrence of error.
Resume Next
        
    rsCustInfo.Close
    rsIPAllocation.Close
    Set rsCustInfo = Nothing
    Set rsIPAllocation = Nothing
    db.Close

End Sub
 
."The only real mistake is the one from which we learn nothing."     
       John Powell (1834-1902)
"Patience is the companion of wisdom." 
        Saint Augustine (354 AD - 430 AD)
"Success is the ability to go from one failure
               to another with no loss of enthusiasm."
       Sir Winston Churchill (1874 - 1965)

---

From: "wrmosca@comcast.net" <wrmosca@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, February 25, 2014 3:45 PM
Subject: Re: [MS_AccessPros] RE: Splitting access 2010 database

 
John H.
Under the File tab (in 2010) Options>Trust center>Trust Center Settings> add the FE's folder to the list of Trusted Locations.
Access 2007: Office button>Access Options>Trust center>Trust Center Settings> add the FE's folder to the list of Trusted Locations.

Bill


---In MS_Access_Professionals@yahoogroups.com, <shelbysalpha@yahoo.com> wrote:
How do I get my front end trusted? I am not using seek. I am using a for next loop to "look" at each record.
 
."The only real mistake is the one from which we learn nothing."     
       John Powell (1834-1902)
"Patience is the companion of wisdom." 
        Saint Augustine (354 AD - 430 AD)
"Success is the ability to go from one failure
               to another with no loss of enthusiasm."
       Sir Winston Churchill (1874 - 1965)

---

From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, February 25, 2014 2:50 PM
Subject: Re: [MS_AccessPros] RE: Splitting access 2010 database

 
Also, if you were using Seek on your recordset when they were local tables, that won't work with linked tables because any recordset opened on a linked table is always a Dynaset recordset, not a table recordset.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
http://www.viescas.com/ 
(Paris, France)



On Feb 25, 2014, at 9:49 PM, <wrmosca@comcast.net> <wrmosca@comcast.net> wrote:


Your VBA code will not work unless the front end is in a trusted location. The network settings have nothing to do with whether or not the VBA code will run as long as you have read/write/create/delete permissions on the back end folder.

Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com



---In MS_Access_Professionals@yahoogroups.com, <shelbysalpha@yahoo.com> wrote:
I have a database with VBA code searching through and listing records. Works great! Then I split the database and the VBA code quit working. Any ideas? Network settings? Path to back end? 
 
."The only real mistake is the one from which we learn nothing."     
       John Powell (1834-1902)
"Patience is the companion of wisdom." 
        Saint Augustine (354 AD - 430 AD)
"Success is the ability to go from one failure
               to another with no loss of enthusiasm."
       Sir Winston Churchill (1874 - 1965)

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (8)

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___