2 Tables in 1 Form

[allepa]

Hi,
I have 2 different tables I need to display in 1 form. The 2 tables would be for Trades and Notes (on those trades).

The tbl_Trades has full information of what the detail of the trade is etc, and the tbl_Notes has information on the last input note for that trade. The 2 tables are not identical, and are only linked by Deal_Folder.

When I create a SQL query for the form to join the 2 tables, I cannot update information in either table as it says that the recordset is not updateable.

I have to create a query to show the latest entry in the tbl_Notes table (by input date) for the appropriate entry in tbl_Trades and the form recordsource is the result of this query (tbl_Trades left join to qry_Notes left join to tbl_Notes).

Any ideas of how I can have these 2 tables in 1 form and both tables as updateable would be a great help.

 

[mrf1xa]

I think the only way to do this is to base your form on tblTrades and then have a subform on it based on the other table. Link the subform and form using the Deal_folder field and the subform will show the records related to the record selected on the mainform, and both can be updated.

HTH Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[KenReay]

Hi

In Principle I would use a mainform for tbl_Trades and a subform for tbl_Notes.

On the Subform you could have an addnew button, since I assume you need to be able to add a new note?

If you wanted to show all notes, with the most recent visible on load, just arrange them in descending date order (on adding a new note you need to requery the sunb form to make the notes appear in the right order).

If you want only the lates note to appear, base you subform on a query, which selectes only the latest note (ie Max() of Note date), again after adding anew note you will need to requery, since the latest note will now be the one you have just added.

Hope that all makes sense.

Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[allepa]

Hi
Thanks for the info - I'll give it a try.

 

[allepa]

Hi,
The solution works well, however. . . .
The only problem now is that I cannot have continuous records on 1 form. I need users to have the option of reviewing several trades at a time, and even filtering on several fields.
Is this same type solution available with continuous forms?
Thanks again

 

[KenReay]

Hi

First the bad news, no you cannot have a subform within a continuos form as far as I know.

In your oginal method, which was a continuous form, how could the see the notes, which are presumably lengthy and not ideally suited to being displayed in a continuos form?

Now the idea, a bit difficult to explain without drwing but here goes:

How about having three forms.

A form which is a continuos form of trades, which user may brows and filter,

A form (the current main form) which displays a single trade, ie the 'current' trade which the user is on in above browse list)

A subform within the above form which displays the notes.

So it is pretty much as we have now except we are adding a 'brower' form to the exiting mainform and linking the position (record) on the browser, to the currently displayed record on the mainform.

Now as for doing it, well that will need some thinking, but I feel it is 'doable'

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[allepa]

Ken,
Thanks for your suggestion.
I think this is the only way to complete this, but the problem exists where the main form would be 100,000 records long, and so creating a browser form around that main form (which would also have 100,000 records) would take quite a while, especially as the users would be accessing the data from a server.

In short, we would have the main form/subform that's updateable and an additional browser form that is continouos but not updateable.

One other suggestion I would like your opinion on would be to include the note_id in the main tbl_trades and update the table on a record by record basis to show the latest note, as and when it changes. Thus we could use the main form as updateable, as the latest note would now be part of that main form it would be open to review, and the user could add a new note by adding a button on the form to add a new record to the tbl_notes (which would then require a requery/update to bring back the latest note). The record would be updateable, but the only problem is the fact that I need to update each time a note is added. I'm going to limit notes to 100char, and if longer is needed, then it means 2 notes. One problem would be this EOD report to show all notes per trade. I'll have to see how long this takes.
What do you think ?
Writing this on the go, and, as you say, is a bit difficult to explain without diagram.

Thanks again for your help

 

[KenReay]

HI

100,000 records, yes, some combo box

While your idea if having the NotesId of the most recent note breaks the 'good' practice rules, It would solve your performance problem.

I recently well about two years ago, worked on a project which invoved a database of vehicles, and had a similar problem of multiple notes against each vehicle.

In the end I did it pretty much as you say,e xcept I put the actual most recent note into the 'main' record, given you are talking about 100 chars max, this would seem like a reasonable thing for you to do also.

It means when user elects to make a new note, you have to create a Notes REcord, and assign it an Id (Autonumber?) and populate it with existing content of notes field from teh 'main' record, before putting in the details of the new note.

To overcome the problem of the user beginning to create a note, then changing their mind (damned users do things like taht), I accepted the New Note into an unbound field, then did the updates after an 'Are You Sure?' dialog

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use