uneditable textbox record source from recordset (1 Viewer)

allanlai

New member
Local time
Today, 21:49
Joined
Apr 22, 2019
Messages
6
Hi,

I have a form with some unbounded textboxes and an Edit button.

When the Edit button is clicked, it should run a SQL query then retrieve a record from a table into the recordset then the record source of those unbounded textboxes set to the fields of the recordset.

Before I clicked the Edit button all the textboxes are editable. When I click the button all the textboxes show the correct data from the recordset but they become uneditable.

Here are the codes. Can anyone tell me what I have done wrong?


Option Compare Database
Option Explicit
Private rst As New ADODB.Recordset

Private Sub btnNew_Click()
Dim sSql As String
sSql = "select * from tblSO where ID=xxx"
rst.Open sSql, CurrentProject.Connection, adOpenKeyset, dLockOptimistic
Set Me.Recordset = rst
tbxID.ControlSource = rst.Fields.Item("ID").name
tbxDate.ControlSource = rst.Fields.Item("SODate").name
End Sub
 

June7

AWF VIP
Local time
Today, 05:49
Joined
Mar 9, 2014
Messages
5,425
Why are you setting RecordSet property? Why use Item property? I have never seen code like this. Exactly what are you trying to accomplish? Have you studied an introductory tutorial book?
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 06:49
Joined
Oct 29, 2018
Messages
21,358
Hi. Just curious, what is the Record Source of the form itself?
 

JHB

Have been here a while
Local time
Today, 14:49
Joined
Jun 17, 2012
Messages
7,732
Could it be so simple that you need the Recordset?
Code:
Set Me.Recordset = rst[B][COLOR=Red].Recordset[/COLOR][/B]
Else post your database with some sample data + name of the form. Zip the database because you haven't post 10 post yet.
 

allanlai

New member
Local time
Today, 21:49
Joined
Apr 22, 2019
Messages
6
Hi all,

Thanks a lot for the feedback.
Here is the complete picture.

This is a simple ERP with back-end MYSQL database and Access front-end user interface which I am creating. Around 20 users may concurrently working on it. The interface include: Sales Order, Production Order, Inventory Control, Invoice, Account Statement...

The Sales Order will have the table structure something like this:
tblSO (ID, CustomerID, Date , ...)
tblSOItems (autoID, SOID, ItemCode, Qty, ....)

The Invoice will have the table structure something like this:
tblInvoice (ID, CustomerID, Date, ...)
tblInvoiceItems (autoID, InvoiceID, ItemCode, amount, ...)

The user form is just all the usual operation of Create, Edit, View, Delete.
But for Edit operation the user is not really editing the database table. The old version of the record is still in the table while the edited version (the new version) is treated as new record appended on that table. These are controlled by a field named Version.

Similar as Delete operation, the user is not really delete the record of the table. It only "marked" deleted by changing a field named Deleted.

For this reason on Edit operation I cannot directly accessing the table to avoid another user accessing the same record at the same time. I need to make a copy of the operating record, do the edit on the copy, then append the edited copy back to the database table.

As the database table is on MYSQL, I have used local Access table as the copy. This worked fine by the codes are quite complicated. Furthers, I need to create quite a lot of local Access tables to serve all the application needs.

Then I learnt something like virtual table, which is ADODB.recordset. I want to use ADODB.recordset as the COPY of the record then use unbound userform and textboxes then assign the textbox control source to the recordset field.

The codes I show on my first post can let the textboxes showing the data from the recordset correctly but I don't know why the textboxes are not editable.

Please share your comment!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:49
Joined
Oct 29, 2018
Messages
21,358
Hi. When you say "virtual tables," I think of "disconnected recordsets." If this is what you mean, then I think those are non-updatable.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:49
Joined
Jan 20, 2009
Messages
12,849
Hi. When you say "virtual tables," I think of "disconnected recordsets." If this is what you mean, then I think those are non-updatable.

A disconnected recordset with a client cursor and batch optimistic locking is updateable. In fact they can be updateable even when the query they are based on isn't. Of course they cannot be reconnected under this circumstance.

I have used them to add a Boolean field to a recordset to support checkboxes on a form without requiring a temporary table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:49
Joined
Oct 29, 2018
Messages
21,358
A disconnected recordset with a client cursor and batch optimistic locking is updateable. In fact they can be updateable even when the query they are based on isn't. Of course they cannot be reconnected under this circumstance.

I have used them to add a Boolean field to a recordset to support checkboxes on a form without requiring a temporary table.

Actually, I do remember that now. Thanks!
 

Users who are viewing this thread

Top Bottom