designing form for inserting records into separate tables

[Masoud]

Hello

I want to design my database for inserting records in 2 separate tables and
after make search event for searching in both tables and retrieve records.
Table 1 contains these fields:

Proj id: 2042
Field code: COM
Discipline: MG
Title: test 1
Doc Type: PR
Serial: 0002
Doc No: concatenated by Proj id+"-"+ Field code+"-"+ Discipline+"-"+ Doc
Type+"-"+ Serial and result should be some thing: 2042-COM-MG-PR-0002
Value of Doc No has been made by coding when inserting records in the form.

Table 2 contains these fields:

Projid: 2042
Doc TypeW
Unit: 114
Title:
Material Code: 1431
Serial: 0002
Doc No: Doc Type+"-"+ Projid+"-"+ Unit+"-"+ Material Code+"-"+ Serial and
result should be some thing like: DW-2042-114-1431-0002
Value of Doc No has been made by coding when inserting records in the form.
Now I want to make a form for inserting these records in the tables, how I
can do it? I mean with 1 form we can do it?
Also for searching in 2 tables and retrieving records into 1 form what I
have to do?
Thanks in advanced.

 

[BruceM via AccessMonster.com]

First of all, you should not be storing the value for DocNo. Just calculate
it on the fly, using an expression such as you have shown as the Control
Source of a text box or as a query field. However, you should be using &,
not + as the concatenation operator. The + sign may be used for special
circumstances, but it can also lead to unexpected results.

It is difficult to tell what is going on just from a listing of fields and no
description of structure and relationships, but it seems to me there should
be just one table, especially if ProjID is the primary key of both. You want
both records on the same form, but to do that you would need to combine them
in some way. If Table2 is related to Table1 by way of ProjID (one Table1
record may have several related Table2 records) you would use a form and
subform, but otherwise there does not seem to be a reason to attempt to
combine the two tables into one.