A different way of thinking required!! - updatable forms

[branston]

Hi,

Im not sure how well I can explain this, so please ask questions if im not making myself clear!

I have a form which shows various status' for a every record in a table. It is a continuous form. I need the status to be updatable. I set this up and it works fine, running from a query linking 2 one-to-one tables. However, I need to add another field to it. I have tried 2 ways:

1 - Adding the field to the query. This required a 3rd table to be linked. The 3rd table is not 1-to-1 with the other tables (although it is with the one its linked on) so when i brought in that field it meant the status was no longer updatable.

2 - I thought that i could put this 1 field in a new form, and have it as a sub form on each record, but of course you are not allowed a subform when the form is set to continuous.

Im all out of ideas of how to get this field into the form. So i thought i would call apon the brain of others to throw a few ideas at me!!!

Any help would be greatly appreciated.

Thank you!!

 

[Endre]

If you don't want the new field to be updateable, then use DLookUp. If you do......maybe Access is trying to tell you something about updateable queries......But seriously, if you do, we can make another plan.

 

[branston]

Ooh, no the new field doesnt need to be updateable. I hadnt thought of dlookup. Ill have a go tomorrow and let you know if it works - i would think it will though!
Thank you!

 

[Banana]

If it's one-one relationship, why not just merge it into one table?

One-one relationship are rare for a good reason: In formal terms, one-one relationship is nonsensical, but we came up with it as a pragmatic answer to physical limitations such as security context (e.g. allow junior HR staff to view employees data but not their salaries, reserving this for more senior HR) or due to hard drive limitations requiring vertical partitioning (that was before we had partitioning as a built-in functions) but continue to exist.

But as you have found out, one-one is a PITA and you would have easily accomplished this if it was just one table.

You spoke of 3rd table... Are you actually describing a many-many relationship?

Maybe listing columns of the table and how they relate would help.

 

[branston]

Endre - Right, I tried the DLookUp, and it sort of worked... The problem is is that all the records on the continuous form show the value for the 1st record... but if you click on another of the records they all change to that value. Any idea how to make it look at that record (I thought the me. would work) The code im using is shown below. I put it on the current event of the subform this is all happening in:

Dim strCriteria As String

strCriteria = "[ProjectNo] = '" & Me.ProjectNo & "' And [EmpNo] = " & Me.EmpNo& " And [Rev] = " & Me.Rev & " And [Discipline] = '" & Me.Discipline & "'"

Me.Hours = Nz(DLookup("SumOfQuantity", "QryQuantitiesTotalDisp", "[ProjectNo] = '" & Me.ProjectNo & "' And [EmpNo] = " & Me.EmpNo& " And [Rev] = " & Me.Rev & " And [Discipline] = '" & Me.Discipline & "'"), 0)

Banana - Thanks for the information. A bit more about the tables: All the tables have 3 key fields, ProjectNo, EmpNo and Rev. The 2 tables that are linked have information such as title, description etc. The 3rd table hold hours and the details of the hours, I think this would make it a 1-to-many relationship, as for each record in table 1 and 2 there can be multiple fields in table 3.
Whats does PITA mean?

 

[Rich]

It is possible to have a subform on a continuous form but it may not work as envisioned, drag it into the footer of the cont. form

 

[branston]

Hi Rich, Thanks for the suggestion. I would need the subform against each record in the continuous form though. The footer would just go at the bottom of all of them, would it not?

 

[Rich]

As you scroll through the records the subform changes accordingly

 

[branston]

Nice idea, but i need all of them visible for comparison.

 

[branston]

AhHa! I may have mis-informed you all... "Table3" was actually a query. I just changed that to a make table query, and linked the table instead of the query and it works fine now.
Thank you all for your suggestions though, always interesting to see how other people go about the same thing!!

 

[DCrake]

AS knowone has answered your all important question PITA stands for Pain In The... I'll let you guess the rest.

 

[branston]

Ahhhh, Thank you DCrake. And here was me thinking it was some fancy access lingo!!!