ACCESS FEED: Re: [MS_AccessPros] RE: Splitting access 2010 database

Selasa, 25 Februari 2014

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

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)