Indexing/Duplicates/Data refresh

[siculster]

Hi

I have the field ID.tableA field linked by a left join to ID.tableB field which is in an ODBC table. The index setting of ID.tableA is Yes (Duplicates OK), and the index setting of ID.tableB is Yes (No Duplicates). I have the query set so that when i type in a correct value in ID.tableA other corresponding fields in tableB are instantly displayed on screen.

However, I wish to change the index setting of ID.tableA to Yes (No Duplicates), but the knock-on effect of this is that the corresponding fields of tableB are not displayed until i reload the query. Is there any way i can make the indexing unique and have the data instantly refresh onscreen?


Sorry if this isn't explained well - i havent used access for years.

Thanks

 

[wiklendt]

what datatype are your ID's in each table?
you can force your subform to requery by using VBA in the "after update" or "on current" or "on click" of whatever control you are using to invoke the change. code would look something like this:

[INDENT]Me.sfrmTableB!Form.Requery
[/INDENT]

 

[siculster]

Both ID fields are text fields with an alpha-numeric input mask.

Unfortunately the form data that needs refreshed/requeryed is not within a sub-form, so if i requery the main form, the form skips to the first record.

 

[wiklendt]

so your problem isn't that you need to refresh the data, it's that you need to keep it on the current record?

to refresh data you generally use the requery command, which i assume you've already got working because you state that the form jumps to the first record... is that correct?

 

[siculster]

I guess i do need the data to refresh, but while keeping it on the current record. It refreshes fine when i have Yes (Duplicates OK), but when i change the table to Yes (No Duplicates), it doesnt refresh.

Yes i tried your suggestion with a requery, but quickly realised that it will only work on a subform. Perhaps I need to put some of the fields in a subform?

 

[The_Doc_Man]

There is such a thing as writing code in a "before update" event to capture the value of your key field, then using "after update" to re-seek the record having that value as the key.

You need a little bit of VBA to do it, but if the fields involved are all key fields, it is not that bad to do.

 

[wiklendt]

having said all that, if your data is in the structure of Table and SubTable, then logically, they should be presented as Form and SubForm.

i find data entry and viewing is easier with a parent-child displayed as main-sub...

which leads me to ask - what is the data you are collecting in each table? i'd be making sure it was normalised first.