We help IT Professionals succeed at work.

Is it all or nothing with ADO

rhat
rhat asked
on
516 Views
Last Modified: 2013-12-05
Must I declare the connection string etc. etc. ad nauesm at the begining of a report or form?
Can't I just use what the form is using?

I'm trying to determine what track a student is in.

Will this work or do I need to define a recordset (code appreciated)?
Can this be done in a report?
The answer as to WHY must it be a report is waay to long to answer here (but I will offline if you're interested)

Option Compare Database

Private Sub Report_Open(Cancel As Integer)
Dim PilotA As String
Dim PilotB As String
Dim FE As String

If Me.StudentTrack.Value = "Pilot A" Then
Me.txtPilotA.Text = Me.StudentTrack.Value
Me.txtPilotB.Text = ""
Me.txtFE.Text = ""
Else
    If Me.StudentTrack.Value = "Pilot B" Then
    Me.txtPilotB.Text = Me.StudentTrack.Value
    Me.txtPilotA.Text = ""
    Me.txtFE.Text = ""
    Else
        If Me.SylName = "FIQ" Then
            If Me.StudentTrack.Value = "Default" Then
            Me.txtFE.Text = Me.StudentTrack.Value
            Me.txtPilotA.Text = ""
            Me.txtPilotB.Text = ""
        End If
    End If
   End If
End If
Comment
Watch Question

i assume you are using ADO to return whatever records from sql server etc to your access db. i would only use ADO for a single record form or if you need to get the return value of a paramater from a stored proc. otherwise i would use ODBC and DAO to connect to your back end and store the results to a temp table which you can then use as a data source for your report.
re: connection string, i would just create a global string variable say gsConnect to store the string, rather than build each time you connect.

Commented:
You can set up a global connection object when your system loads, containing the connection string etc, then bind your recordsources for forms/reports to this connection.

If you are using an adp project, you can bind the queries to Currentproject.Connection - as in the code below:

    Set rstCheckPassword = New ADODB.Recordset
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "spSEAuthorisationPassword"
    cmd.CommandType = adCmdStoredProc
    rstCheckPassword.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc

Author

Commented:
I think cquinn is on the right track, but doesn't address the population of the fields in the report.
Cover that and I think we'll have a winner (I'm working on it too btw).

Author

Commented:
Changed the code to:

Private Sub Report_Open(Cancel As Integer)

Set rstCheckPassword = New ADODB.Recordset
    Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "dbo.S_FSSC_STUDENT_ROSTER"
    cmd.CommandType = adCmdStoredProc
    rstCheckPassword.Open cmd, , adOpenDynamic, adLockOptimistic, adCmdStoredProc

Dim PilotA As String
Dim PilotB As String
Dim FE As String

If rstCheckPassword.Fields.Item("StudentTrack") = "Pilot A" Then

'Me.txtPilotA.Text = Me.StudentTrack.Value
Me.txtPilotA.SetFocus
Me.txtPilotA.Text = rstCheckPassword.Fields.Item("Student")
Me.txtPilotB.SetFocus
Me.txtPilotB.Text = ""
Me.txtFE.SetFocus
Me.txtFE.Text = ""
Else
    If rstCheckPassword.Fields.Item("StudentTrack") = "Pilot B" Then
    Me.txtPilotB.SetFocus
    Me.txtPilotB.Text = rstCheckPassword.Fields.Item("Student")
    Me.txtPilotA.SetFocus
    Me.txtPilotA.Text = ""
    Me.txtFE.SetFocus
    Me.txtFE.Text = ""
    Else
        If rstCheckPassword.Fields.Item("SylName") = "FIQ" Then
            If rstCheckPassword.Fields.Item("StudentTrack") = "Default" Then
            Me.txtFE.SetFocus
            Me.txtFE.Text = rstCheckPassword.Fields.Item("Student")
            Me.txtPilotA.SetFocus
            Me.txtPilotA.Text = ""
            Me.txtPilotB.SetFocus
            Me.txtPilotB.Text = ""
        End If
    End If
   End If
End If




End Sub

Now getting a run time error 2478.
"Microsoft Office Access doen't allow you to use this method in the current view"

What method??  Thoughts?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Not sure what you mean by the recordset object (can you provide a line of code as an example), but moving to something other than  (on_format) on_open seemed to do the trick.
Could you also look at the logic of the nested If statements?

Thanks.

Points to cquinn.

Author

Commented:
nver mind my last comment, I went back and actually read it, and it made more sense.
I won't know the params for the stored procedure as they're comming from the user, outside the report(any hints with this would be appreciated)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.