How to use two tables?

[Emma]

Hello I am trying to create a form which gathers information from two tables
and writes to the second table.

The first table holds alot of data including the Client ID, Last Name, First
Name, etc..

I created an empty second table which holds ID, Client ID, Date, Text, etc..

I want to pull the Client ID, Last Name and First Name from the first main
form which uses the first table to the second popup form which will display
Client ID, Last Name, First Name and allow the user to enter the Data and
Text etc.. which will be saved to the second table. Also I created a query
which pulls the information from the first and second tables. So it displays
the correct information if it's in the second table. However if the
information isn't in the second table a blank form comes up. Unfortunately,
I'm only able to read from the second table and can not write to it. Any
ideas how I can improve this?

 

[Jerry Whittle]

First make sure that the Client ID field is the primary key in the first
table. Next open up the Relationships window and join the two tables there.
Make sure to enable Referiential Integrity.

Next, if you haven't already done so, make a form out of the first table. On
that form place a subform with the second table. The wizard should walk you
through this process.

Now using this form you can enter and edit records from the first table;
enter records in the second table after finding the appropriate record in the
first table; or both create a new record in the first table and immediately
create a matching record in the second table.

 

[Emma]

Hi Jerry,

Yes I have done all that however I don't know how to create a new record in
the second table using the first table's Client ID and First Name and Last
Name. It's not pulling fromthe second table because the Client ID isn't there
it's in the first table, how do I bring it from the first table into the
second?

 

[Emma]

Jerry,
In the query I put Client ID as the field from table 1 and in the criteria
I put [Table2].[ClientID]
Hope this is helpful

 

[Emma]

Sorry Jerry,

One last thing, I didn't use a subform instead I created a button which
opens up the second form where I'd like the Client ID, First Name and Last
Name to open up and the user to enter the information

Thanks Emma

 

[Jerry Whittle]

Well... The names should NOT be stored in the second table. What if Mary
Jones gets married and changes her name to Mary Smith? The two tables should
only be joined by the Client ID field. If you need to see the client names
with the data from the second table, you should join those tables in a query.

Properly creating a form with a subform should automatically link the two
tables with the Client ID.

 

[Emma]

Yes the second table doesn't store the First and Last names. What I'm
thinking is the VB script should create a new record in the second table when
the form is openned with the Client ID. I'm not great with VB so there are
probably obvious errors in the following:

Option Compare Database

Private Sub OpenFormClick()
On Error GoTo Err_OpenFormClick

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "OpenForm"
If stLinkCriteria = "[Client ID]=" & Me![Client ID] Then

DoCmd.OpenForm stDocName, , , stLinkCriteria

Else
With Me
.Client_ID.DefaultValue = .Client_ID

End With
DoCmd.GoToRecord , , acNewRec
End If

Exit_OpenFormClick:
Exit Sub

Err_OpenFormClick:
MsgBox Err.Description
Resume Exit_OpenFormClick

End Sub

 

[Emma]

Hi Jerry,

Actually the first table is a query so when I go into form wizard it gives
the following error message:

"You have choosen fields from record sources which the wizard can't connect.
You may have choosen fields from a table and from a query based on that
table. If so, try choosing fields from only the table or only the query."

So I've just been choosing all the fields from the query which has table 1
and table 2 data in it.

 

[John W. Vinson]

Sorry Jerry,

One last thing, I didn't use a subform instead I created a button which
opens up the second form where I'd like the Client ID, First Name and Last
Name to open up and the user to enter the information

That's why you're having difficulty!

Do you have some strong objection to using the very simple, no-code-required,
well tested Subform approach instead of "rolling your own" custom method?

 

[Emma]

How do I create a subform in a new window?

That's why you're having difficulty!

Do you have some strong objection to using the very simple, no-code-required,
well tested Subform approach instead of "rolling your own" custom method?

 

[Emma]

Oh I see now you don't have to make a query to make a relationship.

 

[Emma]

John said "it couldn't be done"... but I did it. I created a popup window
with table one's data on the top and table two's data underneath it. I
accomplished this by making table 2 a subdatasheet of table 1. Then I created
a second form based on table one and in that form I included table 2's
information. Then I put a button on form one to connect to the popup form 2.
It really wasn't that complicated.

 

[John W. Vinson]

John said "it couldn't be done"... but I did it.

<g> Glad you got it working.

I should have been clearer - you can't (and didn't) open a Subform as a popup.
It's a separate, independent form, not a subform; it works because you went to
the extra effort to provide the code to synchronize the two forms.