Form problem with navigation and display of records

[beebelbrox]

Hi, I am new VB programming in Access and I am requesting help with the following code.

WIndows OS MSaccess 2003

This code is attached to an unbound form that will display a specific recordset based in information passed to the form from another form. The problem I am having is that for each of the case statements the proper records (values and Number of records) are being returned but the movement commands do not work or do not display the record, only the first record in the set is displayed. I can use the troubleshooting code to advance the recordset to any point in the file at which the display is blank until I click the cmdMoveFirst button. I’m missing something. I don’t think it is the code for the nav buttons that pretty simple stuff. From what research I’ve done I think/suspect, but mostly guess it’s the cursor type and location. Any help would be greatly appreciated, especially tips on trouble shooting this type of problem.

TIA
b

Option Compare Database
 
Private Sub Form_Load()
Dim SQL As String
Dim cmdFormPop As ADODB.Command
Dim cnn As ADODB.Connection
Dim fldUserAssignment As ADODB.Field
'Dim prmValue1 As ADODB.Parameter
Dim S1 As Integer
 
    Set cmdFormPop = New Command
    Set cmdFormPop.ActiveConnection = CurrentProject.Connection
 
    S1 = gUserAssignment 'pass user ID to parameter value
 
    Debug.Print gFormState
 
    With gRS
        .CursorType = adOpenDynamic
        .CursorLocation = adUseServer
        .LockType = adLockBatchOptimistic
    End With
 
    Select Case gFormState
 
    Case "Unworked"
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
        "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) = 1);"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    Case "Worked"
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
        "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) <> 1);"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    Case "All"
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
        "WHERE (PPmaster.UserAssignment) = ?;"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    End Select
 
    'troulbe shooting code to validatecorrect records are returned
    Do Until gRS.EOF
        Debug.Print gRS.Fields(0)
        Debug.Print gRS.Fields(1)
        Debug.Print gRS.Fields(2)
        gRS.MoveNext
    Loop
 
    Debug.Print "record count: "; gRS.RecordCount
 
    gRS.MoveFirst
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
    'MasterDisplay
 
End Sub
 
Public Sub cmdMoveFirst_Click()
On Error GoTo DbError
 
    'Move to the first record in the result set.
    gRS.MoveFirst
 
    Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
    Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
    Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
    'MasterDisplay
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMoveLast_Click()
On Error GoTo DbError
 
    'Move to the last record in the result set.
    gRS.MoveLast
 
    Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
    Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
    Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
    'MasterDisplay
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMoveNext_Click()
On Error GoTo DbError
 
    'Move to the next record in the result set if the cursor is not
    'already at the last record.
    If gRS.AbsolutePosition < _
        gRS.RecordCount Then
 
        gRS.MoveNext
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
        'MasterDisplay is a seperate module that will dispaly all of
        'the fields in a single instance instead of under each move control
        'MasterDisplay
    End If
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMovePrevious_Click()
On Error GoTo DbError
 
    'Move to the previous record in the result set, if the
    'current record is not the first record.
    If gRS.AbsolutePosition > 1 Then
        gRS.MovePrevious
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
        'MasterDisplay is a seperate module that will dispaly all of
        'the fields in a single instance instead of under each move control
        'MasterDisplay
    End If
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 

[ADezii]

Hi, I am new VB programming in Access and I am requesting help with the following code.

WIndows OS MSaccess 2003

This code is attached to an unbound form that will display a specific recordset based in information passed to the form from another form. The problem I am having is that for each of the case statements the proper records (values and Number of records) are being returned but the movement commands do not work or do not display the record, only the first record in the set is displayed. I can use the troubleshooting code to advance the recordset to any point in the file at which the display is blank until I click the cmdMoveFirst button. I’m missing something. I don’t think it is the code for the nav buttons that pretty simple stuff. From what research I’ve done I think/suspect, but mostly guess it’s the cursor type and location. Any help would be greatly appreciated, especially tips on trouble shooting this type of problem.

TIA
b

Option Compare Database
 
Private Sub Form_Load()
Dim SQL As String
Dim cmdFormPop As ADODB.Command
Dim cnn As ADODB.Connection
Dim fldUserAssignment As ADODB.Field
'Dim prmValue1 As ADODB.Parameter
Dim S1 As Integer
 
    Set cmdFormPop = New Command
    Set cmdFormPop.ActiveConnection = CurrentProject.Connection
 
    S1 = gUserAssignment 'pass user ID to parameter value
 
    Debug.Print gFormState
 
    With gRS
        .CursorType = adOpenDynamic
        .CursorLocation = adUseServer
        .LockType = adLockBatchOptimistic
    End With
 
    Select Case gFormState
 
    Case "Unworked"
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
        "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) = 1);"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    Case "Worked"
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
        "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) <> 1);"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    Case "All"
        cmdFormPop.CommandType = adCmdText
        cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
        "WHERE (PPmaster.UserAssignment) = ?;"
 
        Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
        prm0.Value = S1
 
        cmdFormPop.Parameters.Append prm0
 
        Set gRS = cmdFormPop.Execute
 
    End Select
 
    'troulbe shooting code to validatecorrect records are returned
    Do Until gRS.EOF
        Debug.Print gRS.Fields(0)
        Debug.Print gRS.Fields(1)
        Debug.Print gRS.Fields(2)
        gRS.MoveNext
    Loop
 
    Debug.Print "record count: "; gRS.RecordCount
 
    gRS.MoveFirst
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
    'MasterDisplay
 
End Sub
 
Public Sub cmdMoveFirst_Click()
On Error GoTo DbError
 
    'Move to the first record in the result set.
    gRS.MoveFirst
 
    Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
    Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
    Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
    'MasterDisplay
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMoveLast_Click()
On Error GoTo DbError
 
    'Move to the last record in the result set.
    gRS.MoveLast
 
    Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
    Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
    Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
    'MasterDisplay is a seperate module that will dispaly all of
    'the fields in a single instance instead of under each move control
    'MasterDisplay
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMoveNext_Click()
On Error GoTo DbError
 
    'Move to the next record in the result set if the cursor is not
    'already at the last record.
    If gRS.AbsolutePosition < _
        gRS.RecordCount Then
 
        gRS.MoveNext
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
        'MasterDisplay is a seperate module that will dispaly all of
        'the fields in a single instance instead of under each move control
        'MasterDisplay
    End If
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 
Public Sub cmdMovePrevious_Click()
On Error GoTo DbError
 
    'Move to the previous record in the result set, if the
    'current record is not the first record.
    If gRS.AbsolutePosition > 1 Then
        gRS.MovePrevious
 
        Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
        Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
        Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
 
        'MasterDisplay is a seperate module that will dispaly all of
        'the fields in a single instance instead of under each move control
        'MasterDisplay
    End If
 
    Exit Sub
 
DbError:
 
    MsgBox "There was an error retrieving information " & _
    "from the database." _
    & Err.Number & ", " & Err.Description
 
End Sub
 

Before I look over your situation in detail, I just have 2 questions:

1. Why not explicitly 'Bind' your Form to this Recordset by setting your Form's Recordset Property? In this manner, navigation will be handled internally by Access and shouldn't be a consideration.
   

   Set Forms("<your form>").Recordset = gRS

2. If this is not acceptable, and I can't see why it wouldn't, try changine the Cursor Location to the Client ans see what happens.
   

   With gRS
     .CursorLocation = adUseClient
   End With

3. I will not look into this any further until I receive your response.

[missinglinq]

It's been said that you're a man of few words, ADezii, but this is carrying things a little too far!

Linq ;0)>

[ADezii]

I was unable to respond directly to an Orphaned Question so I placed it here in the hope that the OP would find it.

Before I look over your situation in detail, I just have 2 questions:

1. Why not explicitly 'Bind' your Form to this Recordset by setting your Form's Recordset Property? In this manner, navigation will be handled internally by Access and shouldn't be a consideration.
   
   

   Set Forms("<your form>").Recordset = gRS

2. If this is not acceptable, and I can't see why it wouldn't, try changing the Cursor Location to the Client and see what happens.
   
   

   With gRS
    
     .CursorLocation = adUseClient
    
   End With

3. I will not look into this any further until I receive your response.

**Edit**
Merged the threads to keep it all together.

[ADezii]

It's been said that you're a man of few words, ADezii, but this is carrying things a little too far!

Linq ;0)>

You are quite correct! For some strange reason, I cannot respond to this Post in the normal fashion, so I placed the response in the Access Forum itself in the hope that the OP will see it. Since you obviously can reply, can you let the OP know that I did respond, and it is in the general Access Forum. Thanks ling! Nevermind ling, I'm sure he'll see at least this response. See you around.

[NeoPa]

I've merged the threads for you ADezii (your post came in at #4), so this is easier to work with.

PS. You need to put your reading glasses back on. It's MissingLinQ (rather than ...LinG) :D I'd make an agist joke here, but you'd probably just wait a year or two then throw it right back in my face so I wont ;)

[ADezii]

I've merged the threads for you ADezii (your post came in at #4), so this is easier to work with.

PS. You need to put your reading glasses back on. It's MissingLinQ (rather than ...LinG) :D I'd make an agist joke here, but you'd probably just wait a year or two then throw it right back in my face so I wont ;)

Hello NeoPa, it is NeoPa and not NeoPq, right? LOL. Thanks for both the spelling lesson and Post Merging - it is appreciated.

[NeoPa]

Hello NeoPa, it is NeoPa and not NeoPq, right? LOL. Thanks for both the spelling lesson and Post Merging - it is appreciated.

NeoPq?
NeoPq?
Were you never told to mind your 'P's & 'Q's?

I do understand that an underlined q can look very similar to a g though (especially at your age - Ooops, said it now) ;)

[ADezii]

NeoPq?
NeoPq?
Were you never told to mind your 'P's & 'Q's?

I do understand that an underlined q can look very similar to a g though (especially at your age - Ooops, said it now) ;)

Thanks NeoPa, now I finally realize why I get an excessive amount of Syntax Errors whenever I code! (LOL)

[missinglinq]

Why not explicitly 'Bind' your Form to this Recordset by setting your Form's Recordset Property? In this manner, navigation will be handled internally by Access and shouldn't be a consideration.

I agree wholeheartedly! I've been doing this a number of years, and to be honest, I wouldn't want to be working with this! I can't imagine a self-professed newbie tackling it! And going this route, I suspect this will be only the first of many trips here for help!

Welcome to TheScripts, Beebelbrox!

Linq ;0)>

[ADezii]

I agree wholeheartedly! I've been doing this a number of years, and to be honest, I wouldn't want to be working with this! I can't imagine a self-professed newbie tackling it! And going this route, I suspect this will be only the first of many trips here for help!

Welcome to TheScripts, Beebelbrox!

Linq ;0)>

If Mr. Beebelbrox is tackling, comprehending, and successfully executing the above listed code, he is a very humble individual and definately not a Newbie! Beebelbrox, linq and I anxiously await your response. This is a rather unorthodox approach, and I'm curious to see how it came about.

[beebelbrox]

If Mr. Beebelbrox is tackling, comprehending, and successfully executing the above listed code, he is a very humble individual and definately not a Newbie! Beebelbrox, linq and I anxiously await your response. This is a rather unorthodox approach, and I'm curious to see how it came about.

Linq, ADezii:

This came about as most things do. I was given an assignment because there were no other resources available. I have been working with access for several years but strictly thru the GUI, with out ever touching code. My professional background is comprised of Software QA, Project Management and anything for buck. I do not claim to be a developer and this is my first attempted at coding, other than “Hello World” college stuff.

The Code I posted here is the result of reading a few books (armed and dangerous), not “Fully” understanding the best approach, and many hours of searching the net for answers.

The actual origins of this part of the project came about because I was not satisfied by the initial solution. Trading expediency for time the initial solution had three copies of the form driven by three queries. I knew enough that this would come back to haunt me and need to be corrected. I also knew that I could use just one form and select the query to populate it. ( note: If I change the table design slightly I could pass two parameters vice the one I use and simplify down to one query. I realized this after coming up with the case approach. Figuring its better not to go to far a field while I had problems to solve, I stuck with this solution). I thought that this approach was reasonably sound but most importantly I did understand it and it seemed to work. Well mostly work…….


I’d be happy to discuss the solution I chose short comings and, I’d be most interested in learning how I should have approached the solution. Gentlemen educate me and I will be very grateful and appreciative.

[beebelbrox]

I was unable to respond directly to an Orphaned Question so I placed it here in the hope that the OP would find it.

Before I look over your situation in detail, I just have 2 questions:

1. Why not explicitly 'Bind' your Form to this Recordset by setting your Form's Recordset Property? In this manner, navigation will be handled internally by Access and shouldn't be a consideration.
   
   

   Set Forms("<your form>").Recordset = gRS

2. If this is not acceptable, and I can't see why it wouldn't, try changing the Cursor Location to the Client and see what happens.
   
   

   With gRS
    
     .CursorLocation = adUseClient
    
   End With

3. I will not look into this any further until I receive your response.

**Edit**
Merged the threads to keep it all together.

ADezii:

I did not do as you suggest simply, and worst yet, most embarrassingly because I did not know I could. I will try your solution today (090407) and let you know how I fare. Allowing windows to handle the navigation is preferred since the users may be confused by or resistant to using the NAV buttons I created.

[ADezii]

ADezii:

I did not do as you suggest simply, and worst yet, most embarrassingly because I did not know I could. I will try your solution today (090407) and let you know how I fare. Allowing windows to handle the navigation is preferred since the users may be confused by or resistant to using the NAV buttons I created.

For starters:

1. Use a BACKUP Copy of the Database when attempting any modifications from this point on.
2. Remove all Reverences to Navigation code, namely the MoveFirst, MoveLast, MoveNext, and MovePrevious Sub-Routine Procedures. You will not need these since we are going to let Access handle the Record Navigation internally.
3. After Line #74, and prior to End Sub, inserts the following line of code:
   

   Set Forms!frmMasterDisplayA.Recordset =gRS

4. The above line of code will 'bind' the gRS Recordset to Form frmMasterDisplayA and will basically be the same as if you set the RecordSource of the Form to aither a Query or Table.
5. I'll help you out as much as I can on this and I feel that linq feels the same way since we are usually working in tandem these days. LOL.
6. Let us know how you make out.

[beebelbrox]

Why not explicitly 'Bind' your Form to this Recordset by setting your Form's Recordset Property? In this manner, navigation will be handled internally by Access and shouldn't be a consideration.

ADezii

Alas, this did not work. I tried various syntax of the cmd and I received an error. Placing the suggested line after the case statements produces:

'Forms.frmMasterDisplayB.Recordset = gRS

ERROR MSG: 438 Object Doesnt support this property or Method

'Set Forms!frmMasterDisplayB.Recordset = gRS
'Set Forms.frmMasterDisplayB.Recordset = gRS
'Set Forms(frmMasterDispalyB).Recordset = gRS
Set Forms("frmMasterDisplayB").Recordset = gRS

ERROR MSG: 7965 the object you entered is not a vaild recordset property (for all of the above)

I retried the same code after changing the line from:

CursorLocation = adUseServer to CursorLocation = adUseClient

with the same errors



Thanks,

b

[beebelbrox]

For starters:

1. Use a BACKUP Copy of the Database when attempting any modifications from this point on.
2. Remove all Reverences to Navigation code, namely the MoveFirst, MoveLast, MoveNext, and MovePrevious Sub-Routine Procedures. You will not need these since we are going to let Access handle the Record Navigation internally.
3. After Line #74, and prior to End Sub, inserts the following line of code:
   

   Set Forms!frmMasterDisplayA.Recordset =gRS

4. The above line of code will 'bind' the gRS Recordset to Form frmMasterDisplayA and will basically be the same as if you set the RecordSource of the Form to aither a Query or Table.
5. I'll help you out as much as I can on this and I feel that linq feels the same way since we are usually working in tandem these days. LOL.
6. Let us know how you make out.

ADezii

After seeing your reply I Have done exactly as you directed, the NAV code is gone and I tried the line of code:

"Set Forms!frmMasterDisplayB.Recordset = gRS"

it produced the same error
ERROR MSG: 7965 the object you entered is not a vaild recordset property

[ADezii]

ADezii

After seeing your reply I Have done exactly as you directed, the NAV code is gone and I tried the line of code:

"Set Forms!frmMasterDisplayB.Recordset = gRS"

it produced the same error
ERROR MSG: 7965 the object you entered is not a vaild recordset property

I know it can't possibly be this easy but in your original code wasn't the name of your Form frmMasterDisplayA? What Version of Access are you running? This Property is not supported in all Versions.

[beebelbrox]

I know it can't possibly be this easy but in your original code wasn't the name of your Form frmMasterDisplayA? What Version of Access are you running? This Property is not supported in all Versions.

ADezii

I am running MS2003. The form name changed after I back up the data but everything is the same just a name change. I am trying to keep things the same to minimize compounding errors.

[ADezii]

ADezii

I am running MS2003. The form name changed after I back up the data but everything is the same just a name change. I am trying to keep things the same to minimize compounding errors.

1. Are the Debug.Print Statements in Lines 65 to 72 producing valid results, namely, does the Recordset contain valid Records and if so how many for each Criteria?
2. How are you dimensioning gRS - Public gRS As ADODB.Recordset?
3. Can you post the code that actually creates the Recordset (gRS)?
4. I do not see a New Instance of gRS being created (Set gRS = New ADODB.Recordset). Is there one?
5. adLockBatchOptimistic for the LockType Property is typically used for Batch Updates - try adLockOptimistic instead.

[FishVal]

To the best of my knowledge Access.Form.Recordset property accepts DAO.Recordset only.
And what about using Form.Filter property?

Regards,

Fish

[ADezii]

ADezii

I am running MS2003. The form name changed after I back up the data but everything is the same just a name change. I am trying to keep things the same to minimize compounding errors.

I hope you don't mind, but I have taken the liberty to perform a major re-write of the entire code segment rather than apply multiple patches. I'll should have the newly created code by this evening or tomorrow evening at the latest.

[ADezii]

To the best of my knowledge Access.Form.Recordset property accepts DAO.Recordset only.
And what about using Form.Filter property?

Regards,

Fish

The Recordset Property can be used equally well with ADO Type Recordsets.

[FishVal]

The Recordset Property can be used equally well with ADO Type Recordsets.

Are you sure?

I tried to assign ADO.Recordset object to Form.Recordset and got "ERROR MSG: 7965 the object you entered is not a vaild recordset property".

kind regards,

Fish

[NeoPa]

There are some restrictions on what is available when.
From the A2K help system :

Recordset Property

You can use the Recordset property to specify or retrieve the ADO Recordset or DAO Recordset object representing a form's record source.

Note You cannot use this property with ODBCDirect recordset types in DAO.

Setting

The Recordset property returns the recordset object that provides the data being browsed in a form. If a form is based on a query, for example, referring to the Recordset property is the equivalent of cloning a Recordset object by using the same query. However, unlike using the RecordsetClone property, currency changes made to the Form.Recordset property are automatically reflected in the current record of the form.

This property is available only by using Visual Basic.

Remarks

When a recordset is asked for in a Microsoft Access database (.mdb), a DAO recordset is returned, in a Microsoft Access project (.adp), an ADO recordset is returned.

The read/write behavior of a form whose Recordset property has been set using Visual Basic is determined by the type of recordset (ADO or DAO) and the type of data (Jet or SQL) contained in the recordset identified by the property.

Recordset type  Based on SQL data  Based on Jet data 
ADO             Read/Write (1)     Read Only
DAO             N/A                Read/Write

(1) Note The ADO Recordset.CursorLocation property must be set to adUseClient. The UniqueTable property is available in client/server on the property sheet, but not for Access databases (.mdb). This property must be set in Visual Basic code.

[ADezii]

Are you sure?

I tried to assign ADO.Recordset object to Form.Recordset and got "ERROR MSG: 7965 the object you entered is not a vaild recordset property".

kind regards,

Fish

I'm positive on this one, FishVal. As soon as I get the chance I'll post the code. BTW, are you opening your Form Hidden prior to assignment?

[ADezii]

ADezii

I am running MS2003. The form name changed after I back up the data but everything is the same just a name change. I am trying to keep things the same to minimize compounding errors.

I've greatly reduced the amount of code but it should accomplish the same thing but with a different approach:

1. Change your Unbound Form to a Bound Form by setting its Record Source Property to PPmaster.
2. Set the Control Source of the 4 Text Boxes on the Form representing the [CLAMNO], [VENDMN], [Question1], and [User Assignment] Fields to their respective values.
3. Place the following code in your Form's Open() NOT Load() Event.
4. You can Delete the For Testing Purposes Only segment.
5. I have no control over your Global Variables, as long as they have valid data contained within them, all should be fine.
6. Copy and Paste the code, then let me know how you make out.
   

   Private Sub Form_Open(Cancel As Integer)
   On Error GoTo Err_Form_Open
   Dim Msg As String, strSQL As String
   Msg = "Records cannot be Filtered - all Records will be shown"
    
   '----------------------- For Testing Purposes Only!' -----------------------
   'Test Declarations
   Dim gFormState As String, gUserAssignment As Integer
   gUserAssignment = 1
    
   'Test Values for Global Variable gFormState
      gFormState = "Unworked"
      'gFormState = "Worked"
      'gFormState = "All"
   '----------------------- For Testing Purposes Only!' -----------------------
    
   Select Case gFormState
     Case "Unworked"
       strSQL = "Select [CLAMNO], [VENDMN], [Question1], [User Assignment] From PPmaster Where " & _
                "[User Assignment] = " & gUserAssignment & " And [Question1] = 1;"
     Case "Worked"
       strSQL = "Select [CLAMNO], [VENDMN], [Question1], [User Assignment] From PPmaster Where " & _
                "[User Assignment] = " & gUserAssignment & " And [Question1] <> 1;"
     Case "All"
       strSQL = "Select [CLAMNO], [VENDMN], [Question1], [User Assignment] From PPmaster Where " & _
                "[User Assignment] = " & gUserAssignment
     Case Else
       strSQL = "PPmaster"         'escape route - show all
   End Select
    
   Me.RecordSource = strSQL
    
   Exit_Form_Open:
     Exit Sub
    
   Err_Form_Open:
     MsgBox Err.Description & vbCrLf & vbCrLf & Msg, vbExclamation, "Error in Form_Open()"
     Me.RecordSource = "PPmaster"      'If Error, display All Records
     Resume Exit_Form_Open
   End Sub

[ADezii]

Are you sure?

I tried to assign ADO.Recordset object to Form.Recordset and got "ERROR MSG: 7965 the object you entered is not a vaild recordset property".

kind regards,

Fish

1. rst is declared Publically in a Standard Code Module and is intentionally kept Open until the Form is closed.
2. The Recordset is READ ONLY.
   

   Dim MySQL As String
    
   MySQL = "Select * From Employees"
    
   Set rst = New ADODB.Recordset
    
   With rst
     .Source = MySQL
     .CursorLocation = adUseServer
     .ActiveConnection = CurrentProject.Connection
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
       .Open
   End With
    
   rst.MoveFirst
    
   DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acHidden
   Set Forms!frmRecordset2.Recordset = rst
   DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acWindowNormal

[FishVal]

1. rst is declared Publically in a Standard Code Module and is intentionally kept Open until the Form is closed.
2. The Recordset is READ ONLY.
   

   Dim MySQL As String
    
   MySQL = "Select * From Employees"
    
   Set rst = New ADODB.Recordset
    
   With rst
     .Source = MySQL
     .CursorLocation = adUseServer
     .ActiveConnection = CurrentProject.Connection
     .CursorType = adOpenKeyset
     .LockType = adLockOptimistic
       .Open
   End With
    
   rst.MoveFirst
    
   DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acHidden
   Set Forms!frmRecordset2.Recordset = rst
   DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acWindowNormal

Nice, ADezii.

In form module works too as well as without preliminary open in hidden view.
But read-only recordset. Almost just an academic issue.

regards,
Fish

[beebelbrox]

1. Are the Debug.Print Statements in Lines 65 to 72 producing valid results, namely, does the Recordset contain valid Records and if so how many for each Criteria?
2. How are you dimensioning gRS - Public gRS As ADODB.Recordset?
3. Can you post the code that actually creates the Recordset (gRS)?
4. I do not see a New Instance of gRS being created (Set gRS = New ADODB.Recordset). Is there one?
5. adLockBatchOptimistic for the LockType Property is typically used for Batch Updates - try adLockOptimistic instead.

This may be OBE but;

Adezii
The lines 65 to 72 do produce valid results as viewed in the immediate window. Each SQL returns the appropriate records, I've verified this. I will try what you have suggested here as well as the new code you've provided.

BTW thank you for the time you've spent on this to date.

[ADezii]

This may be OBE but;

Adezii
The lines 65 to 72 do produce valid results as viewed in the immediate window. Each SQL returns the appropriate records, I've verified this. I will try what you have suggested here as well as the new code you've provided.

BTW thank you for the time you've spent on this to date.

Not a problem. If we can get you back on track, it will be well worth it. The new code shoud work fine, it has been thoroughly tested in a similar context. When it comes to this business though, never, never say should. (LOL). Remember to Delete Lines 6 through 15 - they existed for testing purposes only.

[beebelbrox]

Not a problem. If we can get you back on track, it will be well worth it. The new code shoud work fine, it has been thoroughly tested in a similar context. When it comes to this business though, never, never say should. (LOL). Remember to Delete Lines 6 through 15 - they existed for testing purposes only.

Adezii

As expected your code work great, very smooth and tight. I hadnt considered adding an escape if none of the three conditions were meet. Nice catch there.

Thanks You very much

[ADezii]

Adezii

As expected your code work great, very smooth and tight. I hadnt considered adding an escape if none of the three conditions were meet. Nice catch there.

Thanks You very much

Glad we got it all worked out for you.

[beebelbrox]

Not a problem. If we can get you back on track, it will be well worth it. The new code shoud work fine, it has been thoroughly tested in a similar context. When it comes to this business though, never, never say should. (LOL). Remember to Delete Lines 6 through 15 - they existed for testing purposes only.

ADezii

I've had a chance to look at the code more and have a couple of questions. The solution you developed doesnt open a connection to the db/table which is in a separate db stored in a remote network location.

Why is a connection not needed? The table PPmaster is a linked table does that have any effect? If it wasnt linked would I then need to set up a connection? Is this because the new code is acting as a filter to the now bound form?

What is the difference between line 31 "Me.RecordSource = strSQL" and Set Forms!frmMasterDisplayA.Recordset =gRS? In one it appears to take the the SQL output and bind it to the form with out a record set.
Could the original code be re-written to Set Forms!frmMasterDiapalyA.Recordsource = strSQL?

[NeoPa]

ADezii

I've had a chance to look at the code more and have a couple of questions.
...
Could the original code be re-written to Set Forms!frmMasterDiapalyA.Recordsource = strSQL?

It's good to see a member taking up and moving on with assistance provided. Always gratifying to see the learning progress.
Good attitude Zaphod ;)

[beebelbrox]

It's good to see a member taking up and moving on with assistance provided. Always gratifying to see the learning progress.
Good attitude Zaphod ;)

Please asccept my whole hearted and sincer thanks for your help Adezii.

b

[ADezii]

ADezii

I've had a chance to look at the code more and have a couple of questions. The solution you developed doesnt open a connection to the db/table which is in a separate db stored in a remote network location.

Why is a connection not needed? The table PPmaster is a linked table does that have any effect? If it wasn't linked would I then need to set up a connection? Is this because the new code is acting as a filter to the now bound form?

What is the difference between line 31 "Me.RecordSource = strSQL" and Set Forms!frmMasterDisplayA.Recordset =gRS? In one it appears to take the the SQL output and bind it to the form with out a record set.
Could the original code be re-written to Set Forms!frmMasterDiapalyA.Recordsource = strSQL?

Why is a connection not needed? The table PPmaster is a linked table does that have any effect?

Yes. The Linkage itself provides the Connection.

If it wasn't linked would I then need to set up a connection?

Yes. You would need some type of Connection to the External Data Source.

Is this because the new code is acting as a filter to the now bound form?

No. A Filter actually applies specific criteria to an existing Record Source and usually restricts the result set. The new code creates a new Record Source based on values contained in Global Variables.

What is the difference between line 31 "Me.RecordSource = strSQL" and Set Forms!frmMasterDisplayA.Recordset =gRS?

The RecordSource Property of a Form specifies the source of data for the Form and can be set to either a Table, Query, or SQL Statement. In this case, the Recordset Property of the Form sets the actual ADO Recordset Object that supplies the data for the Form.

Could the original code be re-written to Set Forms!frmMasterDiapalyA.Recordsource = strSQL?

No. The code be written in either 1 or 2 ways:

Me.RecordSource = strSQL         OR
Forms!frmMasterDisplayA.Recordsource = strSQL

'Will not work since Set is usually used to Set Object References to a Variable and here we are setting the value of the RecordSource Property.
Set Forms!frmMasterDisplayA.Recordsource = strSQL

[ADezii]

Please asccept my whole hearted and sincer thanks for your help Adezii.

b

Anytime - all the Experts, Moderators, and Members are here for the same reason.