Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access Switchboard code.

Posted on 2009-05-01
26
Medium Priority
?
641 Views
Last Modified: 2013-11-27
Started having problems with my switcboard after I pulled the frontend of my Access app into SQL Server 2005 backend.  

This is the standard switchboard code used by just about everyone.  It quit working because I was no longer using the backend Access DB.  So I changed some of the code to do ADO...  

The problems I am having is in the While (Not (pRS.EOF)) loop...  Seems the ADODB.Recordset doesn't like it..    Can someone show me a way to fix this.

The old code in the loop is commented out so you can see what the old switch did...  basically it loops through changed the option buttons ... IE  option1, option2, etc...

Thanks...
Private Sub FillOptions()
' Fill in the options for this switchboard page.
 
    ' The number of buttons on the form.
    Const conNumButtons = 8
    Dim pConn As ADODB.Connection
    Dim pRS As ADODB.Recordset
    Dim cmd As New ADODB.Command
    Set pConn = New ADODB.Connection
    Set pRS = New ADODB.Recordset
    
    'Dim dbs As Database
    'Dim rst As Recordset
    Dim strSQL As String
    Dim intOption As Integer
    Dim i As Integer
    
    pConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ERS;Data Source=EMMISSQL\SQLEXPRESS;"
    pConn.Open
    
    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with the focus.
   ' Me.Option1.SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).visible = False
        Me("OptionLabel" & intOption).visible = False
    Next intOption
    
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    'Set dbs = CurrentDb()
    strSQL = "SELECT * FROM [Switchboard Items]"
    strSQL = strSQL & " WHERE ItemNumber > 0 AND SwitchboardID=7"  ' & Me.SwitchboardID.Value
    strSQL = strSQL & " ORDER BY [ItemNumber];"
    'Set rst = dbs.OpenRecordset(strSQL)
    
    cmd.ActiveConnection = pConn
    cmd.CommandText = strSQL
 
    Set pRS = cmd.Execute
    ' Open the recordset
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (pRS.EOF) Then
        Me.OptionLabel1.Caption = "There are no items for this switchboard page"
    Else
        i = 1
        While (Not (pRS.EOF))
          '***Old Code***
          'Me("Option" & rst![ItemNumber]).visible = true
          'Me("OptionLabel" & rst![ItemNumber]).visible = True
          'Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
          '***End Old Code***
 
           Me("Option" & pRS(i).Value) = True
           Me("OptionLabel" & pRS(i)).visible = True
           Me("OptionLabel" & pRS(i)).Caption = pRS(i).Name
           pRS.MoveNext
           i = i + 1
        Wend
    End If
    
    ' Close the recordset and the database.
    pRS.Close
    pConn.Close
 
End Sub

Open in new window

0
Comment
Question by:StrongD1
  • 12
  • 8
  • 4
  • +1
26 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24280451
I think you need to use the Do While ... Loop syntax:

    If pRS.EOF Then
        Me.OptionLabel1.Caption = "There are no items for this switchboard page"
    Else
        i = 1
        Do While Not pRS.EOF
          '***Old Code***
          'Me("Option" & rst![ItemNumber]).visible = true
          'Me("OptionLabel" & rst![ItemNumber]).visible = True
          'Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
          '***End Old Code***
 
           Me("Option" & pRS(i).Value) = True
           Me("OptionLabel" & pRS(i)).visible = True
           Me("OptionLabel" & pRS(i)).Caption = pRS(i).Name
           pRS.MoveNext
           i = i + 1
        Loop
    End If

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24280536
You could maybe use just a recordset, dont see the need for a command object

And any reason why you use the fieldnames instead of fieldnumbr?


Private Sub FillOptions()
' Fill in the options for this switchboard page.
 
    ' The number of buttons on the form.
    Const conNumButtons = 8
    Dim pConn As ADODB.Connection
    Dim pRS As ADODB.Recordset
    Dim cmd As New ADODB.Command
    Set pConn = New ADODB.Connection
    Set pRS = New ADODB.Recordset
   
    'Dim dbs As Database
    'Dim rst As Recordset
    Dim strSQL As String
    Dim intOption As Integer
    Dim i As Integer
   
    pConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ERS;Data Source=EMMISSQL\SQLEXPRESS;"
    pConn.Open
   
    ' Set the focus to the first button on the form,
    ' and then hide all of the buttons on the form
    ' but the first.  You can't hide the field with the focus.
   ' Me.Option1.SetFocus
    For intOption = 2 To conNumButtons
        Me("Option" & intOption).Visible = False
        Me("OptionLabel" & intOption).Visible = False
    Next intOption
   
    ' Open the table of Switchboard Items, and find
    ' the first item for this Switchboard Page.
    'Set dbs = CurrentDb()
    strSQL = "SELECT * FROM [Switchboard Items]"
    strSQL = strSQL & " WHERE ItemNumber > 0 AND SwitchboardID=7"  ' & Me.SwitchboardID.Value
    strSQL = strSQL & " ORDER BY [ItemNumber];"
   
    pRS.Open strSQL, pConn, adOpenStatic, adLockPessimistic
   
    MsgBox "Number of recs found is " & pRS.recordCount
   
    ' Open the recordset
    ' If there are no options for this Switchboard Page,
    ' display a message.  Otherwise, fill the page with the items.
    If (pRS.EOF) Then
        Me.OptionLabel1.Caption = "There are no items for this switchboard page"
    Else
   
        i = 1
        'LOOP THRU
        Do While Not pRS.EOF
         
          '***Old Code***
          'Me("Option" & rst![ItemNumber]).visible = true
          'Me("OptionLabel" & rst![ItemNumber]).visible = True
          'Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
          '***End Old Code***
 
           Me("Option" & pRS(i).Value) = True
           Me("OptionLabel" & pRS(i)).Visible = True
           Me("OptionLabel" & pRS(i)).Caption = pRS(i).Name
           
           pRS.MoveNext
           i = i + 1
        Loop
    End If
   
    ' Close the recordset and the database.
    pRS.Close
    pConn.Close
 
End Sub
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24280556
See this link and click ADORecordsetExample()
                   http://allenbrowne.com/func-ADO.html
HTH
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 65

Expert Comment

by:rockiroads
ID: 24280583
ah, reading again its the fieldnunber

why are u using a number?

if you had 3 fields in your recordset but 15 rows, you will definitely get an error

Looking at old code, I think thats what you still need to do, assuming it is the same names

Me("Option" & pRS("ItemNumber").Value) = True
Me("OptionLabel" & pRS("ItemNumber")).Visible = True
Me("OptionLabel" & pRS("ItemNumber")).Caption = pRS("ItemText")

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24280646
If you are using Access 2000, see this link :
           http://support.microsoft.com/kb/262847
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24280921
rockiroads...

I get errors with

Me("Option" & pRS("ItemNumber").Value) = True
Me("OptionLabel" & pRS("ItemNumber")).Visible = True
Me("OptionLabel" & pRS("ItemNumber")).Caption = pRS("ItemText")

This is the same error I got before with the old code....  the error is:

Run-time 438

"Object doesn't support this property or method"

puppydogbuddy...   I am using Access 2007...  never had problems with any of this code until I went to SQL Server 2005 on the backend.

Anymore ideas?  
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24280955
Ok, tell me, what are the fieldnames in your table called?
and what datatypes are they?

The last sample I took was based on your old code

which line does the error 438 come on?
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281178
The error starts at Me("Option" & pRS("ItemNumber").Value) = True

I am attaching a jpeg of a screen shot I took from SQL Server Management Studio of the table...

Thanks rockiroads for helping out...  


SwitchBoard-Table.jpg
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281190
The s_  tables names are not used...  those were left over from replicate stuff I was doing with Access...

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24281433
So ItemNumber relates to your option field right?
and itemText is the description of the optionfield

lets do this first, to prove your ado code is working
When u run it, nothing will appear on your switchboard but it should run without errors and list the values from the table in the immediate window (alt-f11 vba)

    Do While Not pRS.EOF

        debug.print pRS!ItemNumber, pRS!ItemText          
           
        pRS.MoveNext
        i = i + 1
    Loop


if this works fine then need to look at numbers and your option groups
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281447
rockiroads:

Is there a few I can use the old code, but with the database  (Dim dbs As Database)

connect it to SQL Server with my SQL connection string...  

This would work I think...   I tried dbs.connection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=ERS;Data Source=EMMISSQL\SQLEXPRESS;"

But I am getting an error...  not sure how to use the connection string with the database object....

0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281578
rockiroads...   ok I will try to see if the your loop will work .

0
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 1000 total points
ID: 24281587
strong,
     this could be your problem.
See this link:         http://support.microsoft.com/kb/q162229/

Excerpt:
If you use the Database Splitter Wizard to split your application into a front-end and a back-end database, the Wizard automatically moves all tables to the back-end database and links them to the front-end database, including the Switchboard Items table if it exists. However, if you split your database first, and then use the Switchboard Manager to create a new switchboard, the Switchboard Manager Wizard automatically creates a local Switchboard Items table, and the Wizard works correctly.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24281666
Is the problem with the connection string? I wasnt sure as I thought u had said problem was with loop

If you are using sql express, Im not totally sure the connection string, didnt see it on connectionstrings.com either

this is vb.net code here but the connection string shouldnt make a difference
http://www.java2s.com/Code/VB/Database-ADO.net/OleDbSQLserverExpressConnectionstring.htm
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281684
rockiroads:  You loop worked fine...  I stepped through it and saw the values come up in the tooltip...

I changed the code to this...

         Me("Option" & pRS!ItemNumber).Value = True
           Me("OptionLabel" & pRS!ItemNumber).Visible = True
           Me("OptionLabel" & pRS!ItemNumber).Caption = pRS("ItemText")
           pRS.MoveNext

And I am still getting the Run-time 438

"Object doesn't support this property or method"

I don't get this... it is start to annoy me..  

0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24281703
I think you should be using pRS.Fields(ItemNumber) or pRS.Fields("ItemNumber").
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 1000 total points
ID: 24281753
Ok, excellent, that means your connection string is right and fetching data is no problem.

So the values that were displayed, it should be a number followed by text

Does your switchboard have controls then called Option<<number>> and OptionLanel<<number>>

?

Here is the code, checking errors on each line to determine the failure


On Error Resume Next

Do While Not pRS.EOF

    Debug.Print pRS!ItemNumber, pRS!ItemText
   
    err.clear
    Me("Option" & pRS!ItemNumber).Value = True
    If err.Number > 0 Then
        MsgBox "Setting Option" & pRS!ItemNumber & vbCrLf & vbCrLf & err.Description
    End If
   
    err.clear
    Me("OptionLabel" & pRS!ItemNumber).Visible = True
    If err.Number > 0 Then
        MsgBox "Setting Visibility on OptionLabel" & pRS!ItemNumber & vbCrLf & vbCrLf & err.Description
    End If
           
    err.clear
    Me("OptionLabel" & pRS!ItemNumber).Caption = pRS!ItemText
    If err.Number > 0 Then
        MsgBox "Setting Caption on OptionLabel" & pRS!ItemNumber & vbCrLf & vbCrLf & err.Description
    End If
   
    pRS.MoveNext
Loop

0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281790
Let me trying this out...  well tell you the errors...  me a few...

0
 
LVL 1

Author Comment

by:StrongD1
ID: 24281832
cwood-wm-com:  I tried your suggestion out and got another error:

Run-time error 3265

Item cannot be found in the collection corresponding to the requested name or ordinal.

Ok...  going to try rockiroads...
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 24282204
strong,
Did you see my previous post? the switchboard items table needs to be a local table on each FE.  Is yours set up that way?

PDB
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24282748
puppydogbuddy:....    Yes, I did... It is funny, I was thinking about that as a solution coming into work today to just keep this switchboard table on the Access side as a local table since it is static and won't change.  

I am going to try a few more things before I try that.  

But yes, I did read the article and it is one of the solutions.   I will let you guys know what I do...  

Just came back from lunch, going to do the error catching stuff rockiroad suggested....  
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24282780
the error catching was just to find out where and when exactly the problem was occuring.
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24283267
This is weird... in the message box it won't show the itemnumber, but in the tooltip when stepping into it..I can see it.

Might be a bug of some type...   Anyways, thanks everyone for your help...   I appreciate it.   I am spliting the point rockiroads (For all his help) and puppydogbuddy...    

I am going to keep the switchboard table local like Microsoft suggested...  I need to move with this project... this was a weird road block...  maybe later I need to rewrite the code controlling the switchboard pull the data from SQL Server switchboard table, but right now, this turn into to much of a hassel.

Thanks again...

0
 
LVL 1

Author Closing Comment

by:StrongD1
ID: 31576933
Thanks again...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24283308
No worries. Well at the very least, Im hoping you can see you can achieve reading data by just using recordsets, no need for ado command objects
0
 
LVL 1

Author Comment

by:StrongD1
ID: 24283382
Yeah...I actually learned some interesting things from you...

If I have more questions, I will ask on here....  

I have been messing with Access on and off for a year now, I am a .NET developer, not really an Access guy...  so sometimes I don't know the capibilites of Access...  





0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question