forms and links

[Firz]

Hi to all,

I have made two forms. I have already linked the 2 forms together using a
particular field. Is there a way whereby when I click on a button in the
first form and when it opens the second form, the linked field information
will be shown? In both forms, the linked field is call "registration no:"
Both are primary keys in their own respective tables. Anyone can help??

 

[John W. Vinson]

Hi to all,

I have made two forms. I have already linked the 2 forms together using a
particular field. Is there a way whereby when I click on a button in the
first form and when it opens the second form, the linked field information
will be shown? In both forms, the linked field is call "registration no:"
Both are primary keys in their own respective tables. Anyone can help??

By far the simplest way is to make the second form a Subform of the first one,
using [Registration no:] as the master/child link field.

If this isn't acceptable for some reason, you can use VBA code in the command
button code to open the second form, setting the WhereCondition argument of
the OpenForm method. If you have code (from the wizard perhaps), please post
it if you want to do it this way.

John W. Vinson [MVP]

 

[pietlinden]

Hi to all,

I have made two forms. I have already linked the 2 forms together using a
particular field. Is there a way whereby when I click on a button in the
first form and when it opens the second form, the linked field information
will be shown? In both forms, the linked field is call "registration no:"
Both are primary keys in their own respective tables. Anyone can help??

If they're both primary keys, how do you know what record is related
to which? If you run the button wizard that opens a form to a related
record, what happens? Does it work? (Not sure I know how, since
there's no "child" record... does one table have the primary key of
the other as a foreign key?

 

[Firz]

I think it'll be more better to use the VBA code. Perhaps you can help me on
the codes. Thanks.

Hi to all,

I have made two forms. I have already linked the 2 forms together using a
particular field. Is there a way whereby when I click on a button in the
first form and when it opens the second form, the linked field information
will be shown? In both forms, the linked field is call "registration no:"
Both are primary keys in their own respective tables. Anyone can help??

By far the simplest way is to make the second form a Subform of the first one,
using [Registration no:] as the master/child link field.

If this isn't acceptable for some reason, you can use VBA code in the command
button code to open the second form, setting the WhereCondition argument of
the OpenForm method. If you have code (from the wizard perhaps), please post
it if you want to do it this way.

John W. Vinson [MVP]

 

[Firz]

Let's say that the second table do not contain any Primary key now. Is there
a way to solve this problem? Is there any VBA codes that I can use for this?

 

[pietlinden]

Let's say that the second table do not contain any Primary key now. Is there
a way to solve this problem? Is there any VBA codes that I can use for this?

The standard form of a child table is something like this:

TableName(
PrimaryKey UNIQUE,
ForeignKey LONG REFERENCES ParentTable(PrimaryKey)
)

so having the foreign key (join field in the child table) as an
autonumber makes no sense at all since it's value is derived from the
parent table.

 

[pietlinden]

The only reason to open the subform as a second form is if you
absolutely have no screen real estate left... I guess you could open
the "subform" by passing the parent's primary key in the OpenForm
command.

I think it'll be more better to use the VBA code. Perhaps you can help me on
the codes. Thanks.

Hi to all,

I have made two forms. I have already linked the 2 forms together using a
particular field. Is there a way whereby when I click on a button in the
first form and when it opens the second form, the linked field information
will be shown? In both forms, the linked field is call "registration no:"
Both are primary keys in their own respective tables. Anyone can help??

By far the simplest way is to make the second form a Subform of the first one,
using [Registration no:] as the master/child link field.

If this isn't acceptable for some reason, you can use VBA code in the command
button code to open the second form, setting the WhereCondition argument of
the OpenForm method. If you have code (from the wizard perhaps), please post
it if you want to do it this way.

John W. Vinson [MVP]

 

[John W. Vinson]

I think it'll be more better to use the VBA code. Perhaps you can help me on
the codes. Thanks.

Please explain the nature of the data in the two tables; how the information
in the tables is logically related, in the real world; and why you feel that
you need two forms. What you're proposing is FAR more difficult for both you
as the developer and for the user, and should be done only if there's a very
good reason to do so!

John W. Vinson [MVP]

 

[Firz]

I apologize if I did not explain thoroughly earlier.

In the first form, I have a field call "Registration Number" and some other
additional fields. The "Registration number" is the primary key. After
filling up the first form, there will be a button at the bottom of the form
that is link to the Second form.

When the button is clicked, the second form appears. In the second form,
there's also a field call "Registration number". This "Registration number"
field in the second form is linked to the one in the First form. What I need
to know is that whether it is possible to have the "Registration number"
field in the second form to be filled up according to the "Registration
number" stated in the first form when the user clicked on the button in the
first form.

Usually, what the user normally does is that he/she will copy the
"Registration number" in the first form and paste it in the "Registration
number" of the second form when it appears after the button is clicked.

I hope this is more clearer to you.

 

[John W. Vinson]

In the first form, I have a field call "Registration Number" and some other
additional fields. The "Registration number" is the primary key. After
filling up the first form, there will be a button at the bottom of the form
that is link to the Second form.

When the button is clicked, the second form appears. In the second form,
there's also a field call "Registration number". This "Registration number"
field in the second form is linked to the one in the First form. What I need
to know is that whether it is possible to have the "Registration number"
field in the second form to be filled up according to the "Registration
number" stated in the first form when the user clicked on the button in the
first form.

Usually, what the user normally does is that he/she will copy the
"Registration number" in the first form and paste it in the "Registration
number" of the second form when it appears after the button is clicked.

Again:

There is a very easy, no code required, intuitive and simple for the user,
tool built in to Access to do this: put the second form onto the first form as
a Subform, using [Registration number] as the master and child link field. If
you're short of screen space, you can use a Tab Control with the Table1 fields
on the first page and put the subform on the second page.

You're done. No programming, no maintenance, no hassles, the user doesn't even
need to click a button.

If you are *consciously and intentionally* rejecting this user interface in
favor of the more difficult alternative, here's some sample code you can
adapt.

Private Sub cmdOpenForm2_Click()
Dim strOpenArgs As String
Dim strCriteria As String
Dim strFormName As String
On Error GoTo Proc_Error
strFormName = "frmMyForm2"
strCriteria = "[Registration Number] = " & Me![Registration Number]
' or if Registration Number is a Text field,
' strCriteria = "[Registration Number] = '" & Me![Registration Number] & "'"
strOpenArgs = Me![Registration Number]
DoCmd.OpenForm strFormName,WhereCondition:=strCriteria,OpenArgs:=strOpenArgs
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in cmdOpenForm2_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

and in the Open event of the second form

Private Sub Form_Open(Cancel as Integer)
Const Quote as String = """"
If Me.OpenArgs & "" <> "" Then
Me![Registration Number].DefaultValue = Quote & Me.OpenArgs & Quote
End If
End Sub



John W. Vinson [MVP]

 

[Firz]

John,

Thanks for all the help. After much consideration, I think I'll be using the
1st method that you propose. Even so, I'll still consider the 2nd method as
an alternative for my next project. Again, thanks and I really appreciate it.

In the first form, I have a field call "Registration Number" and some other
additional fields. The "Registration number" is the primary key. After
filling up the first form, there will be a button at the bottom of the form
that is link to the Second form.

When the button is clicked, the second form appears. In the second form,
there's also a field call "Registration number". This "Registration number"
field in the second form is linked to the one in the First form. What I need
to know is that whether it is possible to have the "Registration number"
field in the second form to be filled up according to the "Registration
number" stated in the first form when the user clicked on the button in the
first form.

Usually, what the user normally does is that he/she will copy the
"Registration number" in the first form and paste it in the "Registration
number" of the second form when it appears after the button is clicked.

Again:

There is a very easy, no code required, intuitive and simple for the user,
tool built in to Access to do this: put the second form onto the first form as
a Subform, using [Registration number] as the master and child link field. If
you're short of screen space, you can use a Tab Control with the Table1 fields
on the first page and put the subform on the second page.

You're done. No programming, no maintenance, no hassles, the user doesn't even
need to click a button.

If you are *consciously and intentionally* rejecting this user interface in
favor of the more difficult alternative, here's some sample code you can
adapt.

Private Sub cmdOpenForm2_Click()
Dim strOpenArgs As String
Dim strCriteria As String
Dim strFormName As String
On Error GoTo Proc_Error
strFormName = "frmMyForm2"
strCriteria = "[Registration Number] = " & Me![Registration Number]
' or if Registration Number is a Text field,
' strCriteria = "[Registration Number] = '" & Me![Registration Number] & "'"
strOpenArgs = Me![Registration Number]
DoCmd.OpenForm strFormName,WhereCondition:=strCriteria,OpenArgs:=strOpenArgs
Proc_Exit: Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & " in cmdOpenForm2_Click:" _
& vbCrLf & Err.Description
Resume Proc_Exit
End Sub

and in the Open event of the second form

Private Sub Form_Open(Cancel as Integer)
Const Quote as String = """"
If Me.OpenArgs & "" <> "" Then
Me![Registration Number].DefaultValue = Quote & Me.OpenArgs & Quote
End If
End Sub



John W. Vinson [MVP]

 

[Firz]

Thanks for the advice, pietlinden but I think I've already have a solution
for this

Another matter I want to consult is related to the question I posted.
Let's say a user added an entry to table2. He/she will add "1" and the table
will capture the data. (Eg. Let's assume the field is called No of cars
owned). Is there a way whereby when the data is captured in table2, it will
also be captured in table1?

Considering 1) There's a field with a similar name in table1
2) Table1 has a primary key call "Registration number" and
is linked
with table2.

Are any VB codes required? Or is there any other simpler way?
Thanks.

 

[John W. Vinson]

Is there a way whereby when the data is captured in table2, it will
also be captured in table1?

Considering 1) There's a field with a similar name in table1
2) Table1 has a primary key call "Registration number" and
is linked
with table2.

Are any VB codes required? Or is there any other simpler way?

Yes.

DON'T STORE IT AT ALL in the second table.

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place". If you have data stored in
Table2, and a link (the registration number) to Table1, you can create a Query
joining the two tables in order to display the data from the two tables
together. Storing the data redundantly in both tables wastes space, and much
more importantly lays you open to data errors, since you could edit the fields
in Table1 and make them *different* from the "same" data in Table2.

John W. Vinson [MVP]