Editing non-editable Form (1 Viewer)

[David44Coder]

I have a datasheet Form with a Union Query as it's recordsource.
So it can't be updated or edited. But I'm looking for some way to achieve this.

My thoughts are to get the table and field target from a click event, update the table directly and refresh the Form
But how to edit or add the new text? I presume it is not possible to (temporary) make the field(cell) editable?
Perhaps popup a new Form just to accept the data? Or it is feasible to create an unbound copy of the Form?

Any suggestions welcome. Thanks.

 

[theDBguy]

If you can uniquely identify the record row you want to update, then you should be able to use an UPDATE query.

 

[CJ_London]

Most uses for union queries is to temporarily resolve non normalised data. If this is the case then you would be better spending your time normalising your data rather than building sticking plaster solutions on top of sticking plaster solutions.

But assuming you can identify a specific record a better way might be to have an unbound main form with a datasheet subform showing your union query. (something like a split form). When the user clicks on a row it populates some unbound fields on the main form. You would still need code to update the record and requery the subform to show the changes.

 

[David44Coder]

Identifying the record is no problem, the issue was entering the data to update to.
I've seen mention of "normalising" data but it's never made much sense to me. I've three different databases and sometimes want to see all (matching) records together for whatever purpose. At other times each db is used individually. So whatever normalising is, I assume would need a lot of changes for no apparent reason ? Not that anyone has ever said what it is but I can't envisage the data in a 'better' or more normal state than it is already.
CJ that's an idea. I'll experiment with 2 datasheet forms or subforms on a main form. When you click a record it'll populate the other form with that record and permit editing. A bit of code needed but that's be fun getting it working.

 

[MarkK]

Perhaps popup a new Form just to accept the data?

Yes. You can create a wizard--a popup form--to add rows.
1) User clicks 'Add' button on un-editable union-data form.
2) Wizard opens to accept input. (Wizard may be able to determine calling form using Screen.PreviousControl.Parent)
3) User clicks 'OK' button on wizard to save data and closes. (Wizard may be able to requery calling form)

for no apparent reason

If you don't understand normalization, there is no apparent reason. When you understand normalization, the reason is clear, simple, and saves you hours and hours and hours mucking around with union queries.

: )

 

[David44Coder]

Can you put in one sentence what normalisation is/does ?
But another question please, is there some magic to putting a Form on a Form? I've got a Main Form called FormUE and two datasheet forms on it, frmUnion and frmEdit. I've spent over ah hour chasing these around the place. In design view as I drag to alter their Top or Height values then Open the form their placement changes and I can never get it right. e.g. altering the height of frmUnion (the bottom one) from 5 to 11 cm
will cause frmEdit to not be seen, or change it Top position. Has anyone else had this happen and figured out what is causing it ?

 

[The_Doc_Man]

So whatever normalising is, I assume would need a lot of changes for no apparent reason ?

Normalizing, in reference to databases, is a tried-and-true method for restructuring data to avoid side-effects - where changing something either creates orphan records or duplicate records. I doubt that I can do it justice with a detailed explanation in this thread.

This is the Wikipedia article on database normalization:

Database normalization - Wikipedia

en.wikipedia.org

This is what Microsoft has to say about it:

Database normalization description - Microsoft 365 Apps

Describe the method to normalize the database and gives several alternatives to normalize forms. You need to master the database principles to understand them or you can follow the steps listed in the article.

docs.microsoft.com

Here are a couple of other articles:

Database Normalization - in Easy to Understand English - Essential SQL

Database normalization is used to organize a database. Get a simple explanation to first, second, and third normal forms.

www.essentialsql.com

DBMS Normalization: 1NF, 2NF, 3NF Database Example

Normalization in Database 1NF, 2NF, 3NF, BCNF, 4NF, 5NF, 6NF. Normalization is a database design technique which organizes tables in a manner that reduces redundancy and dependency of data.

www.guru99.com

The goal of normalization is to minimize the effects of changes on things such that the changes don't spill over into other things that really aren't related to each other. It is a way of laying out a database with a sensitivity towards data interrelationships. It is also, in most of our experiences, the best way to - long-term - reduce the amount of work you must do in ANY data operations. The "no apparent reason" comment could not possibly be farther from the reality.

It is true that some databases are so simple that normalization will not appear to make a difference. But the problem is that most databases are meant to follow or model a real-world process, and the functionality of real-world processes includes that they grow. Normalization reduces growing pains.

 

[CJ_London]

If you are comparing data from different databases then that is probably a legitimate use of a union query but a simple rule of normalisation is a specific bit of data is only stored once in the whole db. There are potentially some small exceptions typically around data required for legal reasons such as a product price on an invoice.

Excel is a good place to find non normalised data - a typical example might be customer invoices with columns such as

customer number, customer name, delivery address, invoice address, invoice number, invoice date, item#1 description, item#1 price, item#1 quantity, item#2 description, item#2 price, item#2 quantity

customer name is repeated on each line, as are the other customer related fields, whilst items stretch across the sheet in column blocks of 3.

so there is a risk of typo's in each cell making further analysis harder.

The first step to normalisation would be to the unique list of customer numbers and names and put in a table on a different worksheet. You would then change the customer name column to be a vlookup function to lookup customer name from your new table which matches what was entered in the customer number column.

you can extend that principle to the other customer data.

with regards your form design issues - sounds like you have used a wizard which makes use of stacked or tabular layouts. In design view go to the arrange tab and remove layouts.

I would be using a single form with your datasheet in the detail or footer section and individual controls (not another datasheet) in the header section

 

[mike60smart]

Can you put in one sentence what normalisation is/does ?
But another question please, is there some magic to putting a Form on a Form? I've got a Main Form called FormUE and two datasheet forms on it, frmUnion and frmEdit. I've spent over ah hour chasing these around the place. In design view as I drag to alter their Top or Height values then Open the form their placement changes and I can never get it right. e.g. altering the height of frmUnion (the bottom one) from 5 to 11 cm
will cause frmEdit to not be seen, or change it Top position. Has anyone else had this happen and figured out what is causing it ?

Hi David

If you can upload a zipped copy of the database we may be able to help.

 

[David44Coder]

It's very good of you all to be so helpful, thank you.

Doc Man, Kris Wenzel's article was the most informative, and CJ's explorations. My table could be best described as an 'active' spreadsheet with some Access routines doing stuff better than say, Excel. There's no relationships or Primary keys, and the duplicate data is meant to be displayed. Anything entered in the table is reflected 1:1 in the datasheet and I can do what I like with it. But, some fields are repeated multiple times. My understanding (maybe not quite right)) is that data could be in its own table with a FK that matches the PK in the main table and through some wizardry is re-populated into the main datasheet every place it's required. Is that right? It leaves me some problems.
1) How to create this result
2) Can these same fields continue to be directly edited, added, removed like now
3) How much control would I lose

I succeeded with the Form layout and placement after about 5 hours. Changing the main Form to Popup seems to help, then very minor adjustments plotting what altered.
An easy means to set an absolute width height and position would have been nice. Docmd Move Size seemed to cause problem, and was eventually removed (on the 2 datasheets) and used only on the main Form (which I had dragged the 2 datasheets onto)

It's working quite well so far. I've never used a header or footer and in the Arrange tab everything is grayed out, apart from Size/Space then only the Grid section active.
I prefer the datasheets as they are a mirror images and a click in Union datasheet, copies that entire record to the Edit datasheet. I'm looking forward to finishing the next bit, to update the appropriate source table and requery the form.
Mike I could mockup the 3 Forms if you'd like to look and tell me how they should be arranged without it taking 5 hours!

 

[mike60smart]

Hi David
Upload the file and I will take a look

 

[David44Coder]

Thanks Mike, just give me a little time to organise an upload. I'll be very interested in what your say as I've just found (although it works) at times the Forms are empty, when they shouldn't be, but I haven't yet found when or how. And if course, it always ok when you want it to show a failure.

 

[CJ_London]

1) How to create this result

use a query joining the tables on FK>PK

2) Can these same fields continue to be directly edited, added, removed like now

depends - using my invoice example - you can change a customer by selecting a different one, but not edit the customer name without going to the customer table

3) How much control would I lose

depends on your point of view - you have more control over the organisation of the data and data quality.

Just be aware that trying to use Access with an Excel methodology is doomed to all sorts of problems at best and failure at worst

 

[David44Coder]

Say I have a table with 16 records and 23 fields, and some of these are to be populated with data from a second table, how does the PK (or FK) or query put that data where it's wanted? e.g. so rows 2-8, col 3 shows "Fred".

 

[The_Doc_Man]

One of the things you have to understand is that Access is not Excel and Excel is not Access. You have just asked a question that straddles that big difference square in the middle. In Excel, every cell is independent and you can control display formats at the individual cell level. In Access, you have an expressed dichotomy between storage and display. Structures CAN be displayed but you aren't limited to raw tables. The structure that manages the melding of the two is often a QUERY - in your case, probably a JOIN query between two tables, where there is something about the two tables to allow you to know how they relate to each other.

Let's say you have employees and departments. In the department table, you have the department name and probably have a department code number. In the employees table you have the employee's name, employee number, and the code number (but not the long name) of the department. That allows a query to JOIN the two tables on the department code number - and the QUERY has the correct department name with the employee data even though the employee table didn't have that department name. The JOIN query put the two tables together and sorted out which records went together. With a normalized set of tables, you can do this.

But here's even better fun... in theory, the supervisor of a department is in fact an employee, so you can ALSO have the department supervisor's employee number in the department table. With an appropriate 3-way JOIN, you can show the department supervisor's name, too. BUT the query does all of this on-the-fly. When the query is closed, it contains only an SQL statement. It fills in the selected fields only when you open it. Yet you can build reports from queries with great ease.

 

[David44Coder]

I see a lot of articles where there's talk of Customers And Customer IDs and so on. I don't have such things a bit hard to imagine and so used an example that meant something to me. It is used in Access, not Excel. At the moment the table has this data duplicated, so if I can change that to something (more) normalised I'd give it a try. Is the bottom line this isn't done (or can;t be done) as I asked ?

 

[CJ_London]

Tables can have different purposes although they all store data in one form or another, they each represent an 'entity'. A 'data' table will contain a number of different bits of data (fields) plus a primary key. A 'lookup' table might contain just one bit of data plus a primary key whilst a 'join' table might not contain any 'data' at all other than a primary key and foreign keys to the tables it joins. Every table needs a primary key in order to uniquely identify a specific record and foreign keys to tables it relates to.

The objective is to ensure that data is not repeated but more importantly is treated consistently. So 'Dave' does not get confused with 'Dave1' and 'Dave' is not entered on one occasion and 'David' when entered on another occasion.

Without knowing what your data is about it is difficult to come up with a relevant analogy. But lets say it is about students and classes. Students are an entity so would have a table, and the same for classes. If the students only ever attended one specific class you could include a FK in the student to identify that class in the classes table. But that is rarely the case, students go to many classes (and by the same token, classes can have many students) So you also need a joining table so you know which students attend each class. It simply contains a PK and a FK to each of the two other tables.

However that probably doesn't go far enough. This semester a student attends certain classes, next semester they attend different classes. You may only be interested in the current situation, but you may need a history to track the student through their entire academic life. So you include another bit of data, this time in the joining table to identify the semester when the student attended the class.

 

[David44Coder]

Hello Mike I have attached a file showing the three forms. Don't feel obliged but if you want to offer any thoughts it'd be appreciated. The positioning is about right but the the top datasheet will acquire a horiz. scrollbar if dragged wider, but the bottom one doesn't (which is preferred). But I can't see what's doing that.
I've left three records to show the data moving to the edit Form when clicked, although the code to update the tables isn't done yet.
I just noticed the main FormUE when opened says the On Load event has a Return Without Gosub error. But there's no return and no on load event either. Maybe as it's being opened directly, as normally its opens after a d-click event.

 

[CJ_London]

don't suppose you can provide any context about what your db is actually about. struggling to understand the relevance of 'red', 'oxford street' and 'whiskey' being in the same column (F4)

 

[arnelgp]

My thoughts are to get the table and field target from a click event, update the table directly and refresh the Form
But how to edit or add the new text? I presume it is not possible to (temporary) make the field(cell) editable?
Perhaps popup a new Form just to accept the data? Or it is feasible to create an unbound copy of the Form?

you can of course Insert the record from the Union Query to a Temporary table and use the Temp table
as the Recordsource of your datasheet form. that is it is now editable.
the only thing you cannot do is "add" new record to it since, there are multiple tables, so, to which table to save
is another question.

see this demo.