Ghost Record

[nIGHTmAYOR]

Here is a peculier case that i was faced by alot and this is my discovered way around.
In a continuious form , if you managed to bind the primary key to a combo box you might be faced by weired cases of random value display in field in combo box that are not what were initialy assigned by user.
what is odd is that in actual table in on running form in Datasheet View , the field value is set correctly to value assigned by user , however in form view the problem remains.
This would lead the users to try and delete or modify the record despite that there is nothing realy wrong with it.
However one might think it'd be a sign of a corrupted database but however it is not , you can run whatever fix / analysis / migration tools but the problem will remain.
Reconstructing form from scratch helps , however copying / pasteing defected form controls copy the same problem to the newer form.
So how I managed to solve this error ? well by trial and error I managed to find this one solution that would save me the hassel of reconstruction from scratch and that'd be :

In After Update event of combo box control enter the following code:
Me![combo_box_name_here].Requery

The problem with such solution is that it renders form usage heavy since control will requery on every input.
Smarties will suggest Refresh / Repaint / Focus Cycle but no, nothing helps but requering and only in After Update event of combo box.
So anybody faced such problem before ?

 

[Galaxiom]

Pardon my ignorance but why would you ever use a combobox bound to the primary key in a continuous form? As the PK is meant to relate the record to associated records in other tables, changing it via a combo box would inevitably corrupt that relationship.

Perhaps the programmers at Microsoft didn't test for this because they didn't expect anyone to use one either. I'll hazard a guess that nobody has ever faced this problem before.

What is so "heavy" about requerying a combobox when data is entered into it?

 

[Galaxiom]

OK. Out of curiosity I "managed" to bind the PK of the Record Source to a combobox in a continuous form.

Tried updating a few different variations with no unexpected outcomes.
Tables with autonumbered fields quietly ignored the selection.
Tables with an ordinary PK field accepted the new value provided it was not already used.
Otherwise it gave the usual message that it could not be udated due to rules.

Even tried it with a set of related tables using referential integrity and nothing unexpected happened.
(Forgot about this possibility where I said it would inevitably corrupt the relationship my first post.)

No repaint, refresh, requery or focus change.

Maybe Microsoft fixed it for Access 2007.

By the way. Could you please post that 10 line php ERP Application? We were looking at improving the management of our resources at work and it will save us a lot of time.

 

[nIGHTmAYOR]

Ok I like sarcasm , but think of the following scenario :
A stuppid sales module :
You have two tables -
1 - Items Records
2 - Sales Records
And then you want to update Sales Records judging that : -
-you want to type a number of letters
-auto complete kicks in
-drop down menu show availability
-columns show statistical amounts
and then you finaly add quantity and date and PK retrieved from Items Table.

Are you satisfied with such an example ?

Now now , trying to replicate such a problem isnt realy easy , but it happens and thus I quoted :

if you managed to bind the primary key to a combo box you might be faced by weired cases of random value display in field in combo box that are not what were initialy assigned by user.

and its applicable to access 2007 and pre .

And seriously the post suggests that those who have encountered the problem are to share so I guess your entry is irrelevent to us who have faced it.

 

[nIGHTmAYOR]

Oh as for the 10 line code ERP , I never asked the PHP programmer to show them for the reason that I assumed they must be listed open source somewhere

 

[Galaxiom]

Clearly I have misunderstood the intent of your original description. In my defence I offer that your description was not consistent with the example presented in your reply.

As I now understand:
The Record Source of the form is the Sales table.
The combo is bound to the item field containing a foreign key (ie the PK from the Items table).
The combo RowSource is a selection of fields from the Item table.
The bound column is the Item table PK and the first non-zero column width is the item description. Hence it is the column which autocompletes.

The control exhibiting the error is one showing the foreign key (PK from Items table) but the description combo is correct. This field is also a combo bound to the Item field in the form's Record Source. Presumably it is a combo to allow the item to be entered by Item Number is desired.

How does this possible scenario fit with the observed behaviour:
The use of the autocomplete would be pivotal. The incorrect Item Number ultimately displayed would be the first matching record of the substring you typed before dropping down the combo. Remember that the first record probably won't be the first alphabetically or perhaps even the first to appear in your table when you view it.

The new record is created when the combo is dropped. Initially the item field is loaded with the first matching item and then updated with the final selection. The quantity field is entered on the form.

The Item Code comes back from the table when the new record is created. Although the table is subsequently updated the Current Record does not change and hence the the form is not automatically requeried.

The Item Code control is the only one not entered from the form so it lags behind the record until it is requeried.