How to enter data in a table using a different form

[doctorul1]

Hi,
I dont know if the subject for this post match my problem...but:

I have two linked table (t1 and t2) and two forms (f1 and f2). f2 is a subform of f1 which opening in a new window.

The f1 form fill the t1 table and the f2 subform fill the t2 table.

Example:

t1 contains the following fields: q1, q2 ..q29, q30 and q31
t2 contains the following fields: q32, q33 ... q100.

f1 contains: q1, q2 ...q29
f2 contains: q32, q33 ... q100, AND q30 and q31

Two fields of t1 (q30 and q31) must be filled using the f2 subform...

I built the forms using the acces wizard, and I selected from my form f2 the correspondent fields from t2 PLUS the two fields from t1.

The problem is: when I try to make a record I fill the data using the f1, then the f2 (the two forms are linked togheter using a macro which switch between them etc)and after I enter the data in the fields q30 and q31 I cannot go to the next record because THERE ARE some empty fields (this is the message which I received). Every field from the tables is setted to be required.

So, probably the data from the two fields q30 and q31 fill a new record different than current record..I dont know...

There is a possibility to enter the data for these two fields into the other table in the same record?

Somebody help me?
Thank you in advance.

 

[Fekri]

Hi,

First of all you have to make relationship between two tables and to fields.
for example you should have one field in each table that are same.
So when you make relationship between this tables when you made one form you can select any fields of both tables that you like and enter the data on which you like.
One things remain: before make the relation ship you should have primary key on the fields that you want to liknked.

I hop to be usefull

ali

 

[willir]

doctorul1

I guess I have to ask why you have t1.q30 and t1.q31 in the subform f2 and not in the form f1?

Next, your desgin would impact which fields are required or prevent a record from being created. Some required fields include
- primary keys
- foreign key in one table that is the primary field in another table
- In table design, "Required" set to yes
- In table design, "Indexed" set to yes / no duplicates
- In table design, "Allow zero length" set to yes for text string fields

There is a possibility to enter the data for these two fields into the other table in the same record?

A qualified yes, you can do this. You can use code, based on using the foreign key in t2 / f2 which should be the primary key in t1 / f1.

Although you have provided great info on table and field names, you have not indicated how t1 is related to t2. This has a great bearing on what to do.

the two forms are linked togheter using a macro

Typically, for Access, a form will be linked using the Master and Child link fields. This job is made easier if you pre-define your relationships with the "Relationship tool" (from database menu, "Tools" -> "Relationships", click and drag the primary key in the main table, t1, to the foreign key in the related table, t2. Make sure "Enforce referential integrity" is checked.)

This approach may negate the need for using the macro.

To set the Link Master / Child fields..
- Open the form in design mode with the "Properties" window open (from the menu, "View" -> "Properties")
- Select a text box on the main form, f1
- Then select the subform f2
- Look at the "Data" tab in the Properties window
- Look at the Link Child and Master fields in the Data tab -- they should have your linked fields.
- If not, click in the Link Master or Link Child field to open up and then select the "..." command button to the right of the selected field to open up the "Subform Field Linker"
- Using the drop down boxes, select your link fields.

This will force data in the main form, f1, to be in synch with the subform, f2.

I suspect you would benefit from reading a bit on normalization and relationships...
Fundamentals of Relational Database Design by Paul Litwin - Doc format
or
Fundamentals of Relational Database Design by Paul Litwin - HTML format

And Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Richard

 

[fneily]

When you entered your subform, the record in the main form was saved. So when you essentially reentered data in q30 and q31, it was a new record. If you must have these two fields in your subform (strange) you could do something like this: create two UNBOUND textboxes. Then on the AFTERUPDATE event of each box, put code like this:

Dim db As Database
Dim rs As Recordset
Dim strWhere As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("t1", dbOpenDynaset)
strWhere = "[t1PrimaryKey] = " & Chr(34) & Forms![MainFormName]![SubFormName].Form![fieldName on subform] & Chr(34)
rs.FindFirst strWhere
rs.Edit
rs![q30] = Forms![MainFormName]![SubformName].Form![q30]
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

And the same for q31.
If you're matching on a numeric, take the chr(34)'s out of strWhere.
Don't forget to reference the DAO library.