search multiple fields

[mike60smart]

Hi Greg

Not logical at all.

Why do you need to copy the same information from the Main Form to the Subform.

This is just a duplication of data.

If you have a specific Document , Date and Doc Type in the Main Record why copy this same information to the subform.

The subform should only contain data that is related to the Main (Parent) Record.

[Greg]

For example, if I have one invoice received, I have there the invoice number, the internal label of this invoice, the date of invoice etc. These are all data that are unique to this invoice and can go all into main form as a ONE Document.
However, I also have data on this same invoice that will be written in more rows (records). These are the amount of Debit and Credit that will be written to at least two or three or even more records. These records I want ALL to see below in one table while I am booking. And this is subform datasheet.

Another example if I post a bank statement, I will have one label (Document) for the whole statement and many records below for the same one document. I want all these data to be visually seen so I have control over what I am entering.

From this point of view, this seems logical to me.

Maybe I could do it through queries or otherwise in a way that I wouldn’t have the same data in two tables (duplicates). But so far, I just don’t have as much knowledge and experience in Access to see this as a big problem.

How would you do this all in other way? Would you do this all only with one main form, without the subform?

[June7]

If you want a double-entry accounting app, maybe instead of reinventing the wheel, should just buy QuickBooks. Considering the time and effort put into building Access app, QuickBooks would be a bargain.

I will state again, except for primary key, do not duplicate other fields of EntryHead into GeneralLeadger. Saving primary key of EntryHead record as foreign key into GeneralLedger will be managed by form/subform arrangement. All data from both tables is available for viewing.

Otherwise, eliminate EntryHead table.

[Greg]

Do I need to make any query in order to do what you suggest? How can I see fields of EntryHead in subform GeneralLeadger if the latter does not have these fields in source table?
Does this mean that I should compile at first one query that include all these fields from both tables and then compose a subform based on that query?

[June7]

No queries needed for forms. Build a form bound to EntryHeader table. Build a form bound to GeneralLedger table. Bind textboxes or comboboxes to fields on both forms. Drag and drop GeneralLedger form onto EntryHeader form. This is now a form/subform arrangement. Make sure subform container Master/Child Links properties are set to primary and foreign key fields. Subform will display data associated with main form record. This is conventional design for data entry and very basic Access functionality.

Do search for header record on main form.

For a report, build a query that joins tables.