473,320 Members | 2,054 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Local Data Not Editable in Form

(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.

3 1056
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: Iain Miller | last post by:
Struggling a bit here & would be grateful for any help. I have a table which has a list of people in it. Each person has a unique ID automatically allocated by Access but also belongs to one of 5...
3
by: intl04 | last post by:
Is it possible to create a Word form as the data entry form for an Access database? I didn't see any reference to this possibility in my Access books, so I'm sorry if this is a question that is...
4
by: Martin | last post by:
I want to build a data entry form for creating and updating info on something. When I'm first creating an item all the fields need to be editable. There after some fields become readonly. Most...
5
by: Aspnot | last post by:
Background: I have a data entry form that is bound to a DataSet. This DataSet contains 9 tables and the form displays data from each table in textboxes, not a DataGrid. One of the tables in the...
2
by: filbennett | last post by:
Hi Everyone, I'm generally unfamiliar with Access form design, but have programmed Cold Fusion applications for a couple of years. I'd like to build a data entry form in Access that allows the...
2
by: seltzer | last post by:
I am using Access 2000 but I also have the 2003 version. I am working on creating a data entry form in Access for a research study. Since there is a maximum of 255 fields per table in Access, I...
0
by: kousinovalis | last post by:
Hello I'm adding a Local Data Cache in a project and only when i'm selecting the NorthWind database its available the Add button to add tables. Every other database i'm using the add button its...
7
Jerry Maiapu
by: Jerry Maiapu | last post by:
Hello everyone, I have searched the forum and internet on collecting data from outlook into access with custom formatting but no there is no resource to assist. I 'have looked at the following...
2
by: Amian | last post by:
So I'll explain to you everything that I'm doing and then let you know what I need help with b/c maybe I should be doing this a different way. I am trying to create a database for all patient...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.