Results 1 to 7 of 7

Thread: recordset Q

  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: recordset Q

    Hi everybody,

    I see many examples where people use:

    Dim db As Database

    but if I try using this, it flunks out in my editor.
    (I use Access 2002, sp2)

    Basically, I'm trying to open a recordset based on a dynamic query and count the records, then allow the use to select one of the records and repopulate the form based on the selection.

    Right now, I just want help with the opening of the table and counting of the records!

    Here is the code I have so far but it does not want to work!

    Code:
    Dim db As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSql As String
    Dim numRecords As Long
     
    strSql = "SELECT FIELD_NAME, TBL_NAME FROM TBL_FIELD_NAME where FIELD_NAME = " & Me.Field_Lkup & ";"
     
    Set db = Application.CurrentProject.Connection
    Set rs = db.OpenRecordset(strSql) ' Create recordset based on SQL
     
    numRecords = rs.RecordCount
     
    If numRecords = 0 Then
    MsgBox "No records match the requested fieldname"
    Else
    If Count = 1 Then
    Else	' I will set these up later
    End If		 '.....
    End If
    Thanks in advance,

    mk
    Last edited by jimmyswinger; 01-03-06 at 13:39.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    try using rs.open
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Thanks, I have modified the code as such:

    Code:
     
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strCount As Long
    ' Set our connection
    Set con = Application.CurrentProject.Connection
    ' Set our source (You need to change the table name to your table name)
    strSQL = "SELECT FIELD_NAME, TBL_NAME FROM TBL_FIELD_NAME WHERE FIELD_NAME = " & Me.Field_Lkup & ";"
    ' Open our recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, con, adOpenKeyset, adLockPessimistic
    strCount = rs.RecordCount
    But I get an error, "No value given for one or more required parameters" and it highlights the rst.open line...

  4. #4
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Okay, I put quotes on the line where the field was being referenced from the form and now I get a different error...

    Latest code:
    Code:
     
    Private Sub Command11_Click()
    Dim con As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strCount As Long
    ' Set our connection
    Set con = Application.CurrentProject.Connection
    ' Set our source (You need to change the table name to your table name)
    strSQL = "SELECT FIELD_NAME, TBL_NAME FROM TBL_FIELD_NAME WHERE FIELD_NAME = '" & Me.Field_Lkup & "';"
    ' Open our recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, con, adOpenKeyset, adLockPessimistic
    rst.MoveLast 
    strCount = rs.RecordCount
    The new error is "Object Required" and it points to the last line, strCount = rs.RecordCount...

  5. #5
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Oops, typo!

    I was using rs.recordset

    I needed to use RST.recordset

    Thanks for your help!

    mk

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Opening recordset

    Try changing this:

    strSQL = "SELECT FIELD_NAME, TBL_NAME FROM TBL_FIELD_NAME WHERE FIELD_NAME = " & Me.Field_Lkup & ";"
    ' Open our recordset
    Set rst = New ADODB.Recordset
    rst.Open strSQL, con, adOpenKeyset, adLockPessimistic

    to this:

    strSQL = "SELECT FIELD_NAME, TBL_NAME FROM TBL_FIELD_NAME WHERE FIELD_NAME = '" & Me!Field_Lkup & "'"
    ' Open our recordset
    Set rst = New ADODB.Recordset
    rst.open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Thanks, Paul... You were dead on target!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •