Related Topics

* Using Dynaset(inconsistent Updates) *

Save Changes - inconsistent updates
I'm currently using a query-by-form search query, and I have it set as Dynaset (inconsistent updates) so the user can also edit records they are searching for.

Every now and then after I make changes to records, it asks me if I want to save. However on closing the query, I randomly receive the "do you want to save" window. I want this window 100% of the time in case of mistakes.

Is this save option hiding somewhere


Can't update query
I had a form that used some Dlookups, but it was really slow, so I changed the form so that it is based on a query that uses subqueries in place of the Dlookups. But now I get an error that the recordset is not updateable.

I googled this error and have tried to sift through the many explanations, I even changed the recordset type of the form and the underlying query to Dynaset (inconsistent updates) and it still doesn't work. Here's a skeleton view of my query:


Refresh All goes back to First Record
In the form I have created, which is recordset type - dynaset (inconsistent updates) - since I have linked in Queries that are based on indeterminate relationships.

I need to click "Refresh All" - since its linked to Queries. When I click "Refresh All" above, it automatically reverts back to the First Record.

How can I keep it on the record in view?

Also - is it possible to have the Form, refresh all on its own, without having to click


Upgrade questions, 2003 to 2007
Using 2007 now as a frontend with Oracle RDMS. Existing 2003 Access .mdb is opened with the new version, 2007. However, adding of records, via a form or query, fails. (Note, it works fine with Access 2003.) The query behind the form and the other query consists of two tables with a one-to-one relationship. (The left-side table contains a foreign key which the right- hand table is the parent table.) The error message is that the Access engine cannot find the record, in the right-side table, with key matching fields.

I have figured out a few different fixes. The first one is to change the query's relationship to one-to-many. The results are still the same since the many side (the right side table) is the parent table, and thus the same set of records are returned by the query. The other solution is to set the form's Recordset Type property from Dynaset to Dynaset (Inconsistent Updates). plus the name of the property's value explains enough.

I have a fix but still have yet to confirm if this is the true fix. Can't find anything on the web. Note: I also converted the database to 2007 but the issue still persists.


Updating using form bound to a query
I have a form whose data comes from a query. This query draws data from four different tables, and I have noticed on this forum that there are issues as to whether you can update records using queries like this.

I am able to update data directly in my query so I thought I had got around the problem. I did this by changing some properties of the query and I think it is the "recordset type" property on the query (changed to "Dynaset (inconsistent updates)") (whatever that is), that is enabling me to do this. However, when I try to change data in my form, it doesn't let me make changes in the fields at all even though I can do so in the query.

I want to be able to make changes to the data in the form FRMPupilAchievementByPiece. Any idea on how I can do this? My query only has inner joins, if that's important. I know a bit, but obviously not enough! Any help appreciated.
(PS I deleted most of the DB in the uploaded copy to reduce its size. Any better way


Invisible Controls In Form View/DE "No" Prop Issue
Yet again, buggy, frustrating, confounding Microsoft has managed to make a horrifyingly simple task inspire me to throw my PC out the window and shortly follow it. Consider this new gem:

I'm not getting existing table records showing up in Form View.

a) There's records in the table.
b) The form's Record Source currently contains one-to-one textbox controls from one table. The only control not sourced to that table is not currently sourced to ANYTHING.
c) The Form "Data Entry" property is set to "No"; the "Recordset Type" is "Dynaset (Inconsistent Updates)".for flexibility; the "Allow Additions" property is set to "Yes".
d) The Form Detail "Visible" property is set to "Yes".

Given those, why in the world would I not be seeing the 14 records in the source table, especially with the "Data Entry" property set to "No"?!


New Version of Invisible Controls In Form View/DE "No" Prop Issue
Yet again, buggy, frustrating, confounding Microsoft has managed to make a horrifyingly simple task inspire me to throw my PC out the window and shortly follow it. Consider this new gem:

I'm not getting existing table records showing up in Form View.

a) There's records in the table.
b) The form's Record Source currently contains one-to-one textbox controls from one table. The only control not sourced to that table is not currently sourced to ANYTHING.
c) The Form "Data Entry" property is set to "No"; the "Recordset Type" is "Dynaset (Inconsistent Updates)".for flexibility; the "Allow Additions" property is set to "Yes".
d) The Form Detail "Visible" property is set to "Yes".

Given those, why in the world would I not be seeing the 14 records in the source table, especially with the "Data Entry" property set to "No"?!

Confounding Mr. Gates. Confounding.

Mucho thanks ahead of time for advice on this normally/supposedly easy, yet persistent fix.
Windows XP, Access 2007, .accdb file


How to: Find a Record in a Dynaset-Type or Snapshot-Type DAO Recordset
Learn how to use the Find methods to locate records in a dynaset-type or snapshot-type recordset.


Copy Data From One Table To Another
I have a database that is messed up somehow.It gives me a error saying that the field can't be updated.After 3 tries it lets me update it. So I created of the tables again with no data ofcourse and now need to get the old data into the new db.

I don't know why but I changed the Recordset Type to Inconsistent Updates and the problem went away. I don't understand but it works.Still I messed up by creating a replica and now I can'tcopy the tables since they have weird names now for some of the fields.


Another Permutation of Invisible Form Records
First, I have a .accdb 2007 form that.

1) Has a query SELECT/FROM record source and a "Dynaset (Inconsistent updates) recordset type.
2) Has controls that are bound to two tables. The two tables have primary keys of the same name and data type.
3) Has one control that is a listbox bound to a query that gets its data from my detail/secondary table.
4) Has no "bound" errors in any control.

My detail table only has two fields. One is a date, which is bound to a date control on the form. I need to repeat one of the fields in this detail table in my master table in order to associate an employee with a code (I know this is poor database structure, but in this case, it needs to be done somehow).

I repeated a field ("code") and made it the primary key in both tables to do this.

In order to populate my master table with the "code" value from my listbox (bound to my detail table), I created an non-enabled form control that issourced to the "code" field in the master table and populates on a lost focus event of the listbox.

The listbox based on the query populates fine.

Secondly, here is my question: Is there anything in the above description that would prevent me from seeing the 15 records in my master table?

There's records in every primary key record in the master and detail tables. The Form "Data Entry" property is set to "No". The Form Detail "Visible" property is set to "Yes". For flexibility; the "Allow Additions" property is set to "Yes".

I know I can't simply change the source control of the form off from a query because it references multiple tables. There seems to be no end to the permutations Microsoft can come up with that cause this problem. Shoddy products - just like Steve Jobs said.


Recordset not updateable
I have a "Make-table" query, based of the source table being a linked Excel spreadsheet, which I know are read-only, and therefore I think is the reason I am getting an error in code "Recordset is not updateable" when attempting to open a 'Hyperlink' field in the destination table.

From reading, it seems I need to make the query properties set to "Inconsistent updates" somewhere.


Lock column width in datasheet subforms
I have a couple of very simple linked subforms that I'm trying to keep as clean as possible. They're both in datasheet format.

There should be no need of resizing columns on either; but if the user plays with the forms, they can drag them out, which results in a) horizontal scroll bar, and b) potentially inconsistent column widths as the child form updates from record to record.

I've tinkered in the properties for each of the text boxes, but can't seem to lock them down.

Surely I'm missing something simple


How to: Make Bulk Changes to a DAO Recordset
After you have created a table-type or dynaset-type Recordset object, you can change, delete, or add new records. You cannot change, delete, or add records to a snapshot-type or forward-only-type Recordset object. Learn how to efficiently make bulk updates to a recordset.


Relational Database Normalization Process
Illogically or inconsistently stored data can cause a number of problems. In a relational database, a logical and efficient design is just as critical. A poorly designed database may provide erroneous information, may be difficult to use, or may even fail to work properly.
Most of these problems are the result of two bad design features called: redundant data and anomalies. Redundant data is unnecessary reoccurring data (repeating groups of data). Anomalies are any occurrence that weakens the integrity of your data due to irregular or inconsistent storage (delete, insert and update irregularity, that generates the inconsistent data).


Is there a way to ...
I am using a form with a subform - this issue is about the subform.

The subform accesses a dynaset and data can be entered into a quantity field in each record of the subform. Owing to intentional design no records may be added to the subform.

Here's the issue . the subform also wants to total the amount of the quantity field and display it on the subform heading but if I disallow additions Access will not allow the last record in the dynaset to update and therefore its value will not be added to the total counter that uses a domain aggregate to sum the quantity.

If I do allow additions I can get the totals to update but I expose the application to the possibility of a User adding an unwanted record or having Access display one of its Error Dialogs that only developers can understand.

Seems to me I've been fighting this problem forever and I would like to get it finally resolved.

Appreciate any assistance.


View a dynaset (recordset) w/SELECT
I want to view a dynaset of a tbl recordset.

However in the code below I get a run time 3011 . "make sure the object exists and that you spelled.etc" Hours of varifing spelling is correct I am stumped.

'_______
Private Sub cmd_Go_Click() dialog frm with combo

' get the varable from the dialog frm's combo
projectID = Combo_projectNumber.Value

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frmNewSepProject As String
Dim strSQL As String

'close the dialog frm
DoCmd.Close

'dim the recordset to include all projetID = [Project_ID]
strSQL = "SELECT * FROM tbl_separationKeyInputs WHERE Project_ID = " & projectID

Set db = CurrentDb
Set rs = db.OpenRecordSet(strSQL, dbOpenTable)


Updates Needed - Tickbox??
I've got this idea I would really like to do - but my access skills are limited.

I would like a table of updates


Inconsistent SQL behavior for Yes/No field type
I am noticing inconsistent SQL behavior for the Yes/No field type in Access 2007 running on Windows XP.

I have several tables / SQL queries which must select a value of "1" in order to select records with a true / "active" value in the Yes/No field.

I just implemented a SQL query to do the same with another table and this time records will not be found as "active" unless I select based on a "-1" value in the Yes/No field.

I have known that SQL Server seems to like -1 for Boolean fields, and I am obtaining the records in the FE temp table from SQL Server as the BE.

But why would differences exist between Access tables in the FE DB as to how Yes/No fields behave? With all of the FE tables, they are all getting populated via SQL Queries from the SQL Server BE.


If...then question
The following line of code is used to look at all the values of controls except the field named "Updates".

If C.Name <> "Updates" Then

What would be the proper code to look at all values except "Updates" and another one named "LastUpdated


go to existing record
I have a form in access that associates an owner to a device. I am trying to get the form to behave so that if I try to enter a new record for an existing owner, it tells me that there is already a record for that owner (in which case I would be forced to go to that record and make updates rather than inserting a new record).

the code below works fine.but when I go the existing record to make updates, the same test is run and I cannot make the updates. how do I get around this?