By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Local Data Not Editable in Form

P: 18
(This question was split from another (that can be found at MS Access Frontend/SQL Backend) because multiple questions are not allowed in a single thread - NeoPa Admin).

I have a local Access table and a form attached to it, the table has no records in it and when it is opened it runs the following vb: -
Expand|Select|Wrap|Line Numbers
  1. Dim tmpConn_String As String
  2. Dim cmd As New ADODB.Command
  3. Dim rs As ADODB.Recordset
  4.  
  5. tmpConn_String = DLookup("[Conn_String]", "T_Company_Details")
  6.  
  7. Set cmd = New ADODB.Command
  8. Set rs = New ADODB.Recordset
  9.  
  10. With cmd
  11.  
  12.     .ActiveConnection = tmpConn_String
  13.     .CommandType = adCmdStoredProc
  14.     .CommandText = Me.SP_Name
  15.  
  16. End With
  17.  
  18. Set rs = cmd.Execute
  19.  
  20. If rs.RecordCount > 0 Then
  21.  
  22.     Set Me.Recordset = rs
  23.  
  24.     Do Until rs.EOF
  25.  
  26.      CurrentDb.Execute "INSERT INTO T_Stock_Codes" _
  27.     & "(Stock_Code_No, Stock_Code, Stock_Description, Branch_Code, Sub_Branch_Code, Unit_Of_Sale, Unit_Weight, Net_UCP, Supplier_No," _
  28.     & "Supplier_Stock_Code, Supplier_Stock_Description, VAT_Code, Minimum_Quantity, Reorder_Quantity, Stock_Type, Stock_Status, Stock_Group," _
  29.     & "Qty_In_Stock, NL_Account, NL_Analysis, Allow_FT_Stock_Description," _
  30.     & "Active_Record , Entered_By, Entered_Date, Entered_IP_Address, Modified_By, Modified_Date, Modified_IP_Address)" & _
  31.    "VALUES('" & rs("Stock_Code_No") & "','" & rs("Stock_Code") & "','" & rs("Stock_Description") & "','" & rs("Branch_Code") & "','" _
  32.     & rs("Sub_Branch_Code") & "','" & rs("Unit_Of_Sale") & "','" & rs("Unit_Weight") & "','" & rs("Net_UCP") & "','" & rs("Supplier_No") & "','" _
  33.     & rs("Supplier_Stock_Code") & "','" & rs("Supplier_Stock_Description") & "','" & rs("VAT_Code") & "','" & rs("Minimum_Quantity") & "','" _
  34.     & rs("Reorder_Quantity") & "','" & rs("Stock_Type") & "','" & rs("Stock_Status") & "','" & rs("Stock_Group") & "','" _
  35.     & rs("Qty_In_Stock") & "','" & rs("NL_Account") & "','" & rs("NL_Analysis") & "','" & rs("Allow_FT_Stock_Description") & "','" _
  36.     & rs("Active_Record") & "','" & rs("Entered_By") & "','" & rs("Entered_Date") & "','" & rs("Entered_IP_Address") & "','" _
  37.     & rs("Modified_By") & "','" & rs("Modified_Date") & "','" & rs("Modified_IP_Address") & "')"
  38.  
  39.     rs.MoveNext
  40.  
  41.     Loop
  42.  
  43. End If
  44.  
  45. rs.Close
  46.  
  47. Set rs = Nothing
  48. Set cmd = Nothing
This all works a treat and populates the local table with data. The problem that I have is that I cannot edit the data via the form, if I click on a field the data cannot be edited even although the data is now stored in the local table and the form is set to that as its record source?
Apr 14 '17 #1

✓ answered by NeoPa

I'm not sure this will solve this particular problem but certainly you have rs declared as an ADODB recordset. Forms use DAO recordsets typically so I'm not sure how well that would work for you.

Bear in mind also, that when working with SQL Server there is always the requirement that at least one unique index value is included in the data. This usually means thet the PK is required but there are exceptions that mean that's not always the full story. That will always do if you have one mind.

Good luck.

Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
I'm not sure this will solve this particular problem but certainly you have rs declared as an ADODB recordset. Forms use DAO recordsets typically so I'm not sure how well that would work for you.

Bear in mind also, that when working with SQL Server there is always the requirement that at least one unique index value is included in the data. This usually means thet the PK is required but there are exceptions that mean that's not always the full story. That will always do if you have one mind.

Good luck.
Apr 15 '17 #2

P: 18
This helped and pointed me it the right direction. I added the following to the VB and it works a treat: -
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs2 As DAO.Recordset
  3.  
  4. Set rs2 = db.OpenRecordset("T_Stock_Codes", dbOpenDynaset)
  5.  
  6. Set Me.Recordset = rs2
  7.  
  8. Set rs2 = Nothing
  9. Set db = Nothing
Apr 16 '17 #3

NeoPa
Expert Mod 15k+
P: 31,186
I'm very pleased to hear it Max. I wasn't sure that was the whole answer but knew it could be an issue at least.

Glad it's working for you now :-)
Apr 16 '17 #4

Post your reply

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