Creating a RecordSet object and defining the SQL statement parameterto return a dataset...

[R Tanner]

Any ideas what is wrong with the following with statement? I have
been coding ASP.NET pages in C# for the past several weeks so coming
back to VB in Access 2007 is really kinda throwing me off. I'm trying
to figure out the equivalent of myInt = (int)MyComm.executeScalar();
in VB and stuff...

With RS
.ActiveConnection = CurrentProject.Connection
.Open ("SELECT Date FROM dbo_Data WHERE DT = #" & Forms!
[UpdateRecord]![Date] & "# AND Scope = '" & Forms![UpdateRecord]!
[Scope] & "' AND " &
"Department = '" & Forms![UpdateRecord]![Dept] &
"';",currentproject.Connection)
If .Fields(0) = Null Then
MsgBox "False"
Else
MsgBox "True"
End If
End With


Thanks guys for your help...

 

[Douglas J. Steele]

It would help to know what problem you're having...

Try:

With RS
Set .ActiveConnection = CurrentProject.Connection
.Open "SELECT Date FROM dbo_Data WHERE DT = #" & _
Forms![UpdateRecord]![Date] & "# AND Scope = '" & _
Forms![UpdateRecord]![Scope] & "' AND " & _
"Department = '" & Forms![UpdateRecord]![Dept] & "';"
If rs.EOF Then
MsgBox "False"
Else
MsgBox "True"
End If
End With

 

[R Tanner]

It would help to know what problem you're having...

Try:

With RS
  Set .ActiveConnection = CurrentProject.Connection
  .Open "SELECT Date FROM dbo_Data WHERE DT = #" & _
    Forms![UpdateRecord]![Date] & "# AND Scope = '" & _
    Forms![UpdateRecord]![Scope] & "' AND " & _
    "Department = '" & Forms![UpdateRecord]![Dept] & "';"
    If rs.EOF Then
        MsgBox "False"
    Else
        MsgBox "True"
    End If
End With

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Any ideas what is wrong with the following with statement?  I have
been coding ASP.NET pages in C# for the past several weeks so coming
back to VB in Access 2007 is really kinda throwing me off.  I'm trying
to figure out the equivalent of myInt = (int)MyComm.executeScalar();
in VB and stuff...

With RS
   .ActiveConnection = CurrentProject.Connection
   .Open ("SELECT Date FROM dbo_Data WHERE DT = #" & Forms!
[UpdateRecord]![Date] & "# AND Scope = '" & Forms![UpdateRecord]!
[Scope] & "' AND " &
           "Department = '" & Forms![UpdateRecord]![Dept]&
"';",currentproject.Connection)
   If .Fields(0) = Null Then
       MsgBox "False"
       Else
           MsgBox "True"
   End If
End With

Thanks guys for your help...- Hide quoted text -

- Show quoted text -

Oh yeah, sorry...I tried your code...I'm getting the same runtime
error message I was before when I try to run it -

Runtime error '-2147217904' (80040e10)':

"no value given for one or more required parameters. "

When I run this sql statement from a query window it runs fine...it's
pretty simple actually...I can't figure out what the problem is..

 

[David W. Fenton]

:

When I run this sql statement from a query window it runs
fine...it's pretty simple actually...I can't figure out what the
problem is..

Try concatenating your SQL string into a string variable first, so
you can see what SQL you're actually trying to open:

Dim strSQL As String

strSQL = "SELECT Date FROM dbo_Data WHERE DT = #" _
& Forms![UpdateRecord]![Date] & "# AND Scope = '" _
& Forms![UpdateRecord]![Scope] & "' AND " _
& "Department = '" & Forms![UpdateRecord]![Dept] & "';"
Debug.Print strSQL
With RS
.ActiveConnection = CurrentProject.Connection
.Open (strSQL, CurrentProject.Connection)
If .Fields(0) = Null Then
MsgBox "False"
Else
MsgBox "True"
End If
End With

You can then copy the result that is printed in the Immediate Window
into the Query designer and see what it tells you.