2 Tables 1 Form (1 Viewer)

Good Day all,

I have 2 Tables:

TableA: TableA_ID, SourceID, EntryDate and OpeningAmount
TableB: TableB_ID, TotalIN, TotalOut

Is it possible (on one form) to have all 7 fields. I want to be able to update the 2 tables at the same time. Also each record in TableA is linked to each TotalIN and TotalOut in TableB and if data already exist in TableA, the form displays all the data from TableA and the user can update the missing TableB fields.

you need to be much clearer about what you are trying to do - provide some background - is this a reconciliation process? provide some example data before and after - mock it up in excel if necessary

Is it possible (on one form) to have all 7 fields.

Click to expand...

yes - but they may not be updateable

I want to be able to update the 2 tables at the same time

Click to expand...

with what?

Also each record in TableA is linked to each TotalIN and TotalOut in TableB

Click to expand...

how?

Hi

Yes Table A would be the Main Form

Table B would be a Subform.

Table structures would be as follows:-


TableA:
-TableA_ID (This would normally be the name of your table vice TableA)
-SourceID
-OpeningAmount

TableB:
-TableB_ID (This would normally be the name of your table vice TableB)
-EntryDate
-TypeID (Combobox to select In or Out)
-Amount

Thank you for the quick response.
1. I want all the fields to be updateable on the form
2. On TableB I want 1 record of TotalIN, TotalOut be related to SourceID, EntryDate and OpeningAmount of TableA

for eg

TableA
SourceID....EntryDate.....OpeningAmount
1................9/30/18...............1,200
1................9/29/18...............1,000
7................9/28/18...............1,500

TableB
SourceID...EntryDate.....OpeningAmount.....TotalIN.....TotalOut
1................9/30/18.............1,200..............700..........200
1................9/29/18.............1,500..............200..........500
7................9/28/18.............1,500..............250..........300

Can it be done without the use of a subform?

Hi

Well I for one am not understanding your process

Usually you would have a relationship between tables

Table A is the Parent which would have say 1 Customer has an Opening Balance of 1200

Then table be would be a table where you would store all the transactions related to that Customer ie Money In / Out

Please explain your process using real field / table names rather than table A / B

have to ask, why are you storing data in table A when you already have it in table B?

if this is a transactions table, you should have a structure something like

transPK...AccountFK...TransDate….TransType...TransAmount
1...………..1...…………….9/30/18...…..OBal…………..1200
2............7................9/28/18...…..OBal...........1500
3.......…..1................9/30/18.......IN..............700
4.......…..1................9/30/18.......OUT...........200
5......…...1................9/29/18...…..IN..............200
6......…...1................9/29/18...…..OUT...………..50
7...........7................9/28/18...…..IN..............250
8...........7................9/28/18...… OUT......……..300

then it is very easy to determine the balance on any day

Yes mike60smart that is exactly the relationship between the 2 tables I want to achieve.

I enter the opening values in DayOpen(TableA) and then enter the Day's tranasctions on DailyTransactions(TableB) but I want SourceID, EntryDate and OpeningAmount from DayOpen to be displayed on the DailyTransactions Form when entering the TotalIN, TotalOut for the day.

CJ_London said:

have to ask, why are you storing data in table A when you already have it in table B?

if this is a transactions table, you should have a structure something like

transPK...AccountFK...TransDate….TransType...TransAmount
1...………..1...…………….9/30/18...…..OBal…………..1200
2............7................9/28/18...…..OBal...........1500
3.......…..1................9/30/18.......IN..............700
4.......…..1................9/30/18.......OUT...........200
5......…...1................9/29/18...…..IN..............200
6......…...1................9/29/18...…..OUT...………..50
7...........7................9/28/18...…..IN..............250
8...........7................9/28/18...… OUT......……..300

then it is very easy to determine the balance on any day

Click to expand...

Ooooh. I like that approach. So then I create a table to store the TransType. Great, that's really smart. Thank you.

But I still want to know if it is possible to have the both table fields in one form that can be updateable.

the basic rule for forms is one form, one table, so you would use a mainform/subform arrangement. Note you can still use a query as the form recordsource (which is the preferred method anyway), just so long as the query only references one table

if you combine two tables in a query to use in in one form, changes to the form will not normally be updateable. The exceptions depend on how you have structured your relationships, the type of join and what you want to change in the form - you may also need code to update the foreign key

Although CJ_London has stated the general rule about one table per form, it's perfectly possible to use queries or sql with two or more tables BUT you will always need to check the form can be updated.

I regularly break the 'rule' and can think of a complex form of mine based on 8 tables that is still updateable. However that is an exceptional case. I recently pushed my luck and added table 9 but that was one step too far and I had to do a redesign

raziel3 - here is the way to understand the restriction.

First, let's start with query rules on a two-table JOIN. If the JOIN involves any (a) formula with or without functions, (b) constant, or (c) aggregate function like COUNT() or SUM() - then the JOIN query will not be possible to update because any field matching my a, b, or c conditions has no place to store an updated value. And it doesn't matter if you don't provide data for those fields in the query because SQL would merely store NULL in that case. If you have no place to store any part, you can't store the record. PERIOD.

Now, it IS theoretically possible to define a JOIN that DOESN'T involve any of a, b, or c above, and that COULD be stored provided you included the PK for the table A and the PK for the table B components. In other words, specify the keys for the individual members of the JOIN. Unambiguously specify where the data will be stored. If you only supply the PK for A, then it doesn't know where to store the B part. And a bad form or query design WOULD lead to omission of the table A PK.

So the next part is, could you make a form do this? Yes, if you follow the guidelines as I noted here. You must remember that for forms, there is one .Recordsource, which is in essence a SELECT query containing all the fields you need on the form. It is not that a form requires a single table, but rather that it has only one place for a .Recordsource, and that is the true limitation. ANYTHING ELSE you store from that form will be stored by manual (well... VBA) recordset operations outside of the normal operation of the form. Not impossible, but very easy to screw up.