467,168 Members | 1,035 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,168 developers. It's quick & easy.

Form problem with navigation and display of records

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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_Load()
  4. Dim SQL As String
  5. Dim cmdFormPop As ADODB.Command
  6. Dim cnn As ADODB.Connection
  7. Dim fldUserAssignment As ADODB.Field
  8. 'Dim prmValue1 As ADODB.Parameter
  9. Dim S1 As Integer
  10.  
  11.     Set cmdFormPop = New Command
  12.     Set cmdFormPop.ActiveConnection = CurrentProject.Connection
  13.  
  14.     S1 = gUserAssignment 'pass user ID to parameter value
  15.  
  16.     Debug.Print gFormState
  17.  
  18.     With gRS
  19.         .CursorType = adOpenDynamic
  20.         .CursorLocation = adUseServer
  21.         .LockType = adLockBatchOptimistic
  22.     End With
  23.  
  24.     Select Case gFormState
  25.  
  26.     Case "Unworked"
  27.         cmdFormPop.CommandType = adCmdText
  28.         cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
  29.         "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) = 1);"
  30.  
  31.         Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
  32.         prm0.Value = S1
  33.  
  34.         cmdFormPop.Parameters.Append prm0
  35.  
  36.         Set gRS = cmdFormPop.Execute
  37.  
  38.     Case "Worked"
  39.         cmdFormPop.CommandType = adCmdText
  40.         cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
  41.         "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) <> 1);"
  42.  
  43.         Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
  44.         prm0.Value = S1
  45.  
  46.         cmdFormPop.Parameters.Append prm0
  47.  
  48.         Set gRS = cmdFormPop.Execute
  49.  
  50.     Case "All"
  51.         cmdFormPop.CommandType = adCmdText
  52.         cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
  53.         "WHERE (PPmaster.UserAssignment) = ?;"
  54.  
  55.         Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
  56.         prm0.Value = S1
  57.  
  58.         cmdFormPop.Parameters.Append prm0
  59.  
  60.         Set gRS = cmdFormPop.Execute
  61.  
  62.     End Select
  63.  
  64.     'troulbe shooting code to validatecorrect records are returned
  65.     Do Until gRS.EOF
  66.         Debug.Print gRS.Fields(0)
  67.         Debug.Print gRS.Fields(1)
  68.         Debug.Print gRS.Fields(2)
  69.         gRS.MoveNext
  70.     Loop
  71.  
  72.     Debug.Print "record count: "; gRS.RecordCount
  73.  
  74.     gRS.MoveFirst
  75.  
  76.     'MasterDisplay is a seperate module that will dispaly all of
  77.     'the fields in a single instance instead of under each move control
  78.     'MasterDisplay
  79.  
  80. End Sub
  81.  
  82. Public Sub cmdMoveFirst_Click()
  83. On Error GoTo DbError
  84.  
  85.     'Move to the first record in the result set.
  86.     gRS.MoveFirst
  87.  
  88.     Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  89.     Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  90.     Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  91.  
  92.  
  93.     'MasterDisplay is a seperate module that will dispaly all of
  94.     'the fields in a single instance instead of under each move control
  95.     'MasterDisplay
  96.  
  97.     Exit Sub
  98.  
  99. DbError:
  100.  
  101.     MsgBox "There was an error retrieving information " & _
  102.     "from the database." _
  103.     & Err.Number & ", " & Err.Description
  104.  
  105. End Sub
  106.  
  107. Public Sub cmdMoveLast_Click()
  108. On Error GoTo DbError
  109.  
  110.     'Move to the last record in the result set.
  111.     gRS.MoveLast
  112.  
  113.     Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  114.     Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  115.     Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  116.  
  117.     'MasterDisplay is a seperate module that will dispaly all of
  118.     'the fields in a single instance instead of under each move control
  119.     'MasterDisplay
  120.  
  121.     Exit Sub
  122.  
  123. DbError:
  124.  
  125.     MsgBox "There was an error retrieving information " & _
  126.     "from the database." _
  127.     & Err.Number & ", " & Err.Description
  128.  
  129. End Sub
  130.  
  131. Public Sub cmdMoveNext_Click()
  132. On Error GoTo DbError
  133.  
  134.     'Move to the next record in the result set if the cursor is not
  135.     'already at the last record.
  136.     If gRS.AbsolutePosition < _
  137.         gRS.RecordCount Then
  138.  
  139.         gRS.MoveNext
  140.  
  141.         Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  142.         Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  143.         Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  144.  
  145.         'MasterDisplay is a seperate module that will dispaly all of
  146.         'the fields in a single instance instead of under each move control
  147.         'MasterDisplay
  148.     End If
  149.  
  150.     Exit Sub
  151.  
  152. DbError:
  153.  
  154.     MsgBox "There was an error retrieving information " & _
  155.     "from the database." _
  156.     & Err.Number & ", " & Err.Description
  157.  
  158. End Sub
  159.  
  160. Public Sub cmdMovePrevious_Click()
  161. On Error GoTo DbError
  162.  
  163.     'Move to the previous record in the result set, if the
  164.     'current record is not the first record.
  165.     If gRS.AbsolutePosition > 1 Then
  166.         gRS.MovePrevious
  167.  
  168.         Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  169.         Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  170.         Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  171.  
  172.         'MasterDisplay is a seperate module that will dispaly all of
  173.         'the fields in a single instance instead of under each move control
  174.         'MasterDisplay
  175.     End If
  176.  
  177.     Exit Sub
  178.  
  179. DbError:
  180.  
  181.     MsgBox "There was an error retrieving information " & _
  182.     "from the database." _
  183.     & Err.Number & ", " & Err.Description
  184.  
  185. End Sub
  186.  
Aug 30 '07 #1
  • viewed: 4200
Share:
36 Replies
ADezii
Expert 8TB
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

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub Form_Load()
  4. Dim SQL As String
  5. Dim cmdFormPop As ADODB.Command
  6. Dim cnn As ADODB.Connection
  7. Dim fldUserAssignment As ADODB.Field
  8. 'Dim prmValue1 As ADODB.Parameter
  9. Dim S1 As Integer
  10.  
  11.     Set cmdFormPop = New Command
  12.     Set cmdFormPop.ActiveConnection = CurrentProject.Connection
  13.  
  14.     S1 = gUserAssignment 'pass user ID to parameter value
  15.  
  16.     Debug.Print gFormState
  17.  
  18.     With gRS
  19.         .CursorType = adOpenDynamic
  20.         .CursorLocation = adUseServer
  21.         .LockType = adLockBatchOptimistic
  22.     End With
  23.  
  24.     Select Case gFormState
  25.  
  26.     Case "Unworked"
  27.         cmdFormPop.CommandType = adCmdText
  28.         cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
  29.         "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) = 1);"
  30.  
  31.         Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
  32.         prm0.Value = S1
  33.  
  34.         cmdFormPop.Parameters.Append prm0
  35.  
  36.         Set gRS = cmdFormPop.Execute
  37.  
  38.     Case "Worked"
  39.         cmdFormPop.CommandType = adCmdText
  40.         cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
  41.         "WHERE ((PPmaster.UserAssignment) = ? And (PPmaster.Question1) <> 1);"
  42.  
  43.         Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
  44.         prm0.Value = S1
  45.  
  46.         cmdFormPop.Parameters.Append prm0
  47.  
  48.         Set gRS = cmdFormPop.Execute
  49.  
  50.     Case "All"
  51.         cmdFormPop.CommandType = adCmdText
  52.         cmdFormPop.CommandText = "SELECT CLAMNO,VENDNM,Question1 FROM PPmaster " & _
  53.         "WHERE (PPmaster.UserAssignment) = ?;"
  54.  
  55.         Set prm0 = cmdFormPop.CreateParameter("prmUserAssignment", adVarChar, adParamInput, 15)
  56.         prm0.Value = S1
  57.  
  58.         cmdFormPop.Parameters.Append prm0
  59.  
  60.         Set gRS = cmdFormPop.Execute
  61.  
  62.     End Select
  63.  
  64.     'troulbe shooting code to validatecorrect records are returned
  65.     Do Until gRS.EOF
  66.         Debug.Print gRS.Fields(0)
  67.         Debug.Print gRS.Fields(1)
  68.         Debug.Print gRS.Fields(2)
  69.         gRS.MoveNext
  70.     Loop
  71.  
  72.     Debug.Print "record count: "; gRS.RecordCount
  73.  
  74.     gRS.MoveFirst
  75.  
  76.     'MasterDisplay is a seperate module that will dispaly all of
  77.     'the fields in a single instance instead of under each move control
  78.     'MasterDisplay
  79.  
  80. End Sub
  81.  
  82. Public Sub cmdMoveFirst_Click()
  83. On Error GoTo DbError
  84.  
  85.     'Move to the first record in the result set.
  86.     gRS.MoveFirst
  87.  
  88.     Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  89.     Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  90.     Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  91.  
  92.  
  93.     'MasterDisplay is a seperate module that will dispaly all of
  94.     'the fields in a single instance instead of under each move control
  95.     'MasterDisplay
  96.  
  97.     Exit Sub
  98.  
  99. DbError:
  100.  
  101.     MsgBox "There was an error retrieving information " & _
  102.     "from the database." _
  103.     & Err.Number & ", " & Err.Description
  104.  
  105. End Sub
  106.  
  107. Public Sub cmdMoveLast_Click()
  108. On Error GoTo DbError
  109.  
  110.     'Move to the last record in the result set.
  111.     gRS.MoveLast
  112.  
  113.     Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  114.     Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  115.     Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  116.  
  117.     'MasterDisplay is a seperate module that will dispaly all of
  118.     'the fields in a single instance instead of under each move control
  119.     'MasterDisplay
  120.  
  121.     Exit Sub
  122.  
  123. DbError:
  124.  
  125.     MsgBox "There was an error retrieving information " & _
  126.     "from the database." _
  127.     & Err.Number & ", " & Err.Description
  128.  
  129. End Sub
  130.  
  131. Public Sub cmdMoveNext_Click()
  132. On Error GoTo DbError
  133.  
  134.     'Move to the next record in the result set if the cursor is not
  135.     'already at the last record.
  136.     If gRS.AbsolutePosition < _
  137.         gRS.RecordCount Then
  138.  
  139.         gRS.MoveNext
  140.  
  141.         Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  142.         Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  143.         Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  144.  
  145.         'MasterDisplay is a seperate module that will dispaly all of
  146.         'the fields in a single instance instead of under each move control
  147.         'MasterDisplay
  148.     End If
  149.  
  150.     Exit Sub
  151.  
  152. DbError:
  153.  
  154.     MsgBox "There was an error retrieving information " & _
  155.     "from the database." _
  156.     & Err.Number & ", " & Err.Description
  157.  
  158. End Sub
  159.  
  160. Public Sub cmdMovePrevious_Click()
  161. On Error GoTo DbError
  162.  
  163.     'Move to the previous record in the result set, if the
  164.     'current record is not the first record.
  165.     If gRS.AbsolutePosition > 1 Then
  166.         gRS.MovePrevious
  167.  
  168.         Forms!frmMasterDisplayA!txtCLAMNO = gRS.Fields(0)
  169.         Forms!frmMasterDisplayA!txtQuestion1 = gRS.Fields(1)
  170.         Forms!frmMasterDisplayA!txtVENDNM = gRS.Fields(2)
  171.  
  172.         'MasterDisplay is a seperate module that will dispaly all of
  173.         'the fields in a single instance instead of under each move control
  174.         'MasterDisplay
  175.     End If
  176.  
  177.     Exit Sub
  178.  
  179. DbError:
  180.  
  181.     MsgBox "There was an error retrieving information " & _
  182.     "from the database." _
  183.     & Err.Number & ", " & Err.Description
  184.  
  185. End Sub
  186.  
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.
    Expand|Select|Wrap|Line Numbers
    1. 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.
    Expand|Select|Wrap|Line Numbers
    1. With gRS
    2.   .CursorLocation = adUseClient
    3. End With
  3. I will not look into this any further until I receive your response.
Sep 2 '07 #2
missinglinq
Expert 2GB
It's been said that you're a man of few words, ADezii, but this is carrying things a little too far!

Linq ;0)>
Sep 2 '07 #3
ADezii
Expert 8TB
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.

    Expand|Select|Wrap|Line Numbers
    1. 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.

    Expand|Select|Wrap|Line Numbers
    1. With gRS
    2.  
    3.   .CursorLocation = adUseClient
    4.  
    5. 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.
Sep 2 '07 #4
ADezii
Expert 8TB
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.
Sep 2 '07 #5
NeoPa
Expert Mod 16PB
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 ;)
Sep 2 '07 #6
ADezii
Expert 8TB
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.
Sep 2 '07 #7
NeoPa
Expert Mod 16PB
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) ;)
Sep 2 '07 #8
ADezii
Expert 8TB
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)
Sep 2 '07 #9
missinglinq
Expert 2GB
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)>
Sep 2 '07 #10
ADezii
Expert 8TB
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.
Sep 3 '07 #11
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.
Sep 4 '07 #12
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.

    Expand|Select|Wrap|Line Numbers
    1. 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.

    Expand|Select|Wrap|Line Numbers
    1. With gRS
    2.  
    3.   .CursorLocation = adUseClient
    4.  
    5. 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.
Sep 4 '07 #13
ADezii
Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Sep 4 '07 #14
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
Sep 4 '07 #15
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:
    Expand|Select|Wrap|Line Numbers
    1. 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
Sep 4 '07 #16
ADezii
Expert 8TB
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.
Sep 4 '07 #17
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.
Sep 5 '07 #18
ADezii
Expert 8TB
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.
Sep 5 '07 #19
FishVal
Expert 2GB
To the best of my knowledge Access.Form.Recordset property accepts DAO.Recordset only.
And what about using Form.Filter property?

Regards,

Fish
Sep 5 '07 #20
ADezii
Expert 8TB
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.
Sep 5 '07 #21
ADezii
Expert 8TB
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.
Sep 5 '07 #22
FishVal
Expert 2GB
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
Sep 5 '07 #23
NeoPa
Expert Mod 16PB
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.

Expand|Select|Wrap|Line Numbers
  1. Recordset type  Based on SQL data  Based on Jet data 
  2. ADO             Read/Write (1)     Read Only
  3. 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.
Sep 5 '07 #24
ADezii
Expert 8TB
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?
Sep 5 '07 #25
ADezii
Expert 8TB
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.
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Open(Cancel As Integer)
    2. On Error GoTo Err_Form_Open
    3. Dim Msg As String, strSQL As String
    4. Msg = "Records cannot be Filtered - all Records will be shown"
    5.  
    6. '----------------------- For Testing Purposes Only!' -----------------------
    7. 'Test Declarations
    8. Dim gFormState As String, gUserAssignment As Integer
    9. gUserAssignment = 1
    10.  
    11. 'Test Values for Global Variable gFormState
    12.    gFormState = "Unworked"
    13.    'gFormState = "Worked"
    14.    'gFormState = "All"
    15. '----------------------- For Testing Purposes Only!' -----------------------
    16.  
    17. Select Case gFormState
    18.   Case "Unworked"
    19.     strSQL = "Select [CLAMNO], [VENDMN], [Question1], [User Assignment] From PPmaster Where " & _
    20.              "[User Assignment] = " & gUserAssignment & " And [Question1] = 1;"
    21.   Case "Worked"
    22.     strSQL = "Select [CLAMNO], [VENDMN], [Question1], [User Assignment] From PPmaster Where " & _
    23.              "[User Assignment] = " & gUserAssignment & " And [Question1] <> 1;"
    24.   Case "All"
    25.     strSQL = "Select [CLAMNO], [VENDMN], [Question1], [User Assignment] From PPmaster Where " & _
    26.              "[User Assignment] = " & gUserAssignment
    27.   Case Else
    28.     strSQL = "PPmaster"         'escape route - show all
    29. End Select
    30.  
    31. Me.RecordSource = strSQL
    32.  
    33. Exit_Form_Open:
    34.   Exit Sub
    35.  
    36. Err_Form_Open:
    37.   MsgBox Err.Description & vbCrLf & vbCrLf & Msg, vbExclamation, "Error in Form_Open()"
    38.   Me.RecordSource = "PPmaster"      'If Error, display All Records
    39.   Resume Exit_Form_Open
    40. End Sub
Sep 5 '07 #26
ADezii
Expert 8TB
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim MySQL As String
    2.  
    3. MySQL = "Select * From Employees"
    4.  
    5. Set rst = New ADODB.Recordset
    6.  
    7. With rst
    8.   .Source = MySQL
    9.   .CursorLocation = adUseServer
    10.   .ActiveConnection = CurrentProject.Connection
    11.   .CursorType = adOpenKeyset
    12.   .LockType = adLockOptimistic
    13.     .Open
    14. End With
    15.  
    16. rst.MoveFirst
    17.  
    18. DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acHidden
    19. Set Forms!frmRecordset2.Recordset = rst
    20. DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acWindowNormal
Sep 5 '07 #27
FishVal
Expert 2GB
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim MySQL As String
    2.  
    3. MySQL = "Select * From Employees"
    4.  
    5. Set rst = New ADODB.Recordset
    6.  
    7. With rst
    8.   .Source = MySQL
    9.   .CursorLocation = adUseServer
    10.   .ActiveConnection = CurrentProject.Connection
    11.   .CursorType = adOpenKeyset
    12.   .LockType = adLockOptimistic
    13.     .Open
    14. End With
    15.  
    16. rst.MoveFirst
    17.  
    18. DoCmd.OpenForm "frmRecordset2", acNormal, , , acFormEdit, acHidden
    19. Set Forms!frmRecordset2.Recordset = rst
    20. 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
Sep 5 '07 #28
  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.
Sep 5 '07 #29
ADezii
Expert 8TB
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.
Sep 5 '07 #30
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
Sep 5 '07 #31
ADezii
Expert 8TB
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.
Sep 6 '07 #32
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?
Sep 6 '07 #33
NeoPa
Expert Mod 16PB
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 ;)
Sep 6 '07 #34
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
Sep 6 '07 #35
ADezii
Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Me.RecordSource = strSQL         OR
  2. Forms!frmMasterDisplayA.Recordsource = strSQL
Expand|Select|Wrap|Line Numbers
  1. '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.
  2. Set Forms!frmMasterDisplayA.Recordsource = strSQL
Sep 7 '07 #36
ADezii
Expert 8TB
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.
Sep 7 '07 #37

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by troy_lee@comcast.net | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.