• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Access/VBA - Display linked fields in Updateable continuous Form

Hi Experts

Here is little background to my issue at hand.

I have a continuous Form where data source is a simple query from one table with no aggregate functions. This allow users to search, filter, sort and update single/multiple records, kinda similar to spreadsheet style. I have new requirement where another similar table needs to be joined (left join) to display few fields.

I tried following:

Create Left Join in underlying query of Form (sample: Select .... FROM TblFirst LEFT JOIN QrySecondTable ON TblFirst.SerialNum = QrySecondTable.SerialNum;)

Now Form is not Updateable, based on some previous recommendations i tried to change RecordsetType = Dynaset (Inconsistent Updates)... However i get into all kinds of trouble with existing filter, combos list throwing errors. So gave up on this idea..

Next I tried Dlookup to unbound fields but performance is way too slow for values to appear on screen. Its also affecting existing load and refresh times...

So based on above situation, i would like to know what else can be tried, that i can have updateable form with join fields displayed (read only) without suffering  from performance issues.

thanks
0
drivers
Asked:
drivers
  • 2
  • 2
1 Solution
 
als315Commented:
You can use subform for secondary table
0
 
peter57rCommented:
The join type should have no effect on updateability.
It sounds like your second query uses something that makes it not updateable such as group by or other summary.

If you cannot get Inconsistent Updates to work in this case then I don't see you have much alternative to creating a temporary table from your query and using that as the recordsource for your form.  OIf you are allowing updates as opposed to just filtering etc then there is more work to do in writing the records back to the source table as you will need to include an 'updated' field in the temporary table (and obviously set in the form_beforeupdate procedure)
That will identify which records need to be written back.
 SInce we are talking about record volumes suitable for use in aform then I would be inclined to write all fields back rather than get into complexities of what has changed.
0
 
driversAuthor Commented:
sorry guys for coming back late, got away with other projects.

als315,  subform cannot be used with continous form

peter57r, i revaluate your first suggestion that join type should have no effect on updateability and found it is true if I use PK on both sides. However my current situation its bit tricky as the field (serialnumber) is the only common field on both sides but can have duplicate on both sides, i beleive that is preventating from udpates.

Creating Temp table idea require overhead in terms of development and testing, so i am not inclined for this unless this is the only option available.

Any further thoughts?

thanks
0
 
peter57rCommented:
You cannot create an updateable query using joins of this nature (non_PK, duplicated fields) except by using InconsistentUpdates.

The join type does not have any bearing on it.

0
 
driversAuthor Commented:
Thanks everyone, looks like I have some thinking to do.. Points goes to Peter57r
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now