Sync two forms

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

Syncing two Forms

I am trying to Sync two form Via the EmployeeID (PK) from
on table to the foreign key of another.

I can link the two forms by the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Emergency"

stLinkCriteria = "[EmployeeID]=" & "" & Me!
[EmployeeID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria


When I try and do it using them forms it says that the
Key is Null but it isn't. When I fill in the tables it
links them no probs
 
Hi Joel,
I suggest that you use the form wizard to set this up and
then examine the code.

Luck
Jonathan
 
Hi Joel

In your stLinkCriteria, you have two instances of & "".

These will do nothing. You are simply appending an empty string to another
string.

If your EmployeeID field is text, then to enclose the value in quotes you
must use """". Two consecutive quote charters in a string are interpreted
as an embedded quote instead of ending the string. You can also use Chr(34)
if you prefer.
 
Hi THanks for the advice even with the wizard it sill
does tranfer the Primary key from the first table please
help

Thanks Joel
-----Original Message-----
Hi Joel,
I suggest that you use the form wizard to set this up and
then examine the code.

Luck
Jonathan
-----Original Message-----
Syncing two Forms

I am trying to Sync two form Via the EmployeeID (PK) from
on table to the foreign key of another.

I can link the two forms by the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Emergency"

stLinkCriteria = "[EmployeeID]=" & "" & Me!
[EmployeeID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria


When I try and do it using them forms it says that the
Key is Null but it isn't. When I fill in the tables it
links them no probs

.
Click to expand...
.
Click to expand...
 
HI Graham

The Primary Key is an Auto Number What I am trying to
achieve I fill in Employee form click next and it goes
to the Emergency form and allows me to fil that form with
the link established.

I really dont want to use a subform if possible as I real
don't want to pitt to much info in one form.

I am fairly new please help

Joel
-----Original Message-----
Hi Joel

In your stLinkCriteria, you have two instances of & "".

These will do nothing. You are simply appending an empty string to another
string.

If your EmployeeID field is text, then to enclose the value in quotes you
must use """". Two consecutive quote charters in a string are interpreted
as an embedded quote instead of ending the string. You can also use Chr(34)
if you prefer.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Syncing two Forms

I am trying to Sync two form Via the EmployeeID (PK) from
on table to the foreign key of another.

I can link the two forms by the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Emergency"

stLinkCriteria = "[EmployeeID]=" & "" & Me!
[EmployeeID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria


When I try and do it using them forms it says that the
Key is Null but it isn't. When I fill in the tables it
links them no probs
Click to expand...


.
Click to expand...
 
Hi again Joel

If EmployeeID is an AutoNumber (and therefore presumably a long integer in
the Emergency table) then you don't need any quotes. You should just use:
stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]

However, the addition of empty strings (& "") would make no difference, so
the problem must be somewhere else.

You say it complains that "the key is null". Do you mean that you get this
message when you try to same a new record from the Emergency form? Is
EmployeeID by any chance also the primary key of the Emergency table (or a
part of it)?

If so, then it means you are not filling in its value in the second form.
With a properly linked subform, this would happen automatically for you, but
not in a separate form.

If this is the problem, you can fix it by setting the default value of
EmployeeID as the form opens. Pass the EmployeeID from the first form in
the OpenArgs parameter:
DoCmd.OpenForm stDocName, , , stLinkCriteria _
OpenArgs := Me.EmployeeID

Now, in the Load event of the second form, set the default value:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Employee.DefaultValue = Me.OpenArgs
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

HI Graham

The Primary Key is an Auto Number What I am trying to
achieve I fill in Employee form click next and it goes
to the Emergency form and allows me to fil that form with
the link established.

I really dont want to use a subform if possible as I real > don't want to pitt to much info in one form.

I am fairly new please help

Joel
-----Original Message-----
Hi Joel

In your stLinkCriteria, you have two instances of & "".

These will do nothing. You are simply appending an empty string to another
string.

If your EmployeeID field is text, then to enclose the value in quotes you
must use """". Two consecutive quote charters in a string are interpreted
as an embedded quote instead of ending the string. You can also use Chr(34)
if you prefer.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Syncing two Forms

I am trying to Sync two form Via the EmployeeID (PK) from
on table to the foreign key of another.

I can link the two forms by the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Emergency"

stLinkCriteria = "[EmployeeID]=" & "" & Me!
[EmployeeID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria


When I try and do it using them forms it says that the
Key is Null but it isn't. When I fill in the tables it
links them no probs
Click to expand...


.
Click to expand...
Click to expand...
 
Hi there sorry about this I have tried what has been
suggested and I sill get the message below please assist
and I am truly sorry

You Cannot add or change a related record is required in
table 'Employees'
-----Original Message-----
Hi again Joel

If EmployeeID is an AutoNumber (and therefore presumably a long integer in
the Emergency table) then you don't need any quotes. You should just use:
stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]

However, the addition of empty strings (& "") would make no difference, so
the problem must be somewhere else.

You say it complains that "the key is null". Do you mean that you get this
message when you try to same a new record from the Emergency form? Is
EmployeeID by any chance also the primary key of the Emergency table (or a
part of it)?

If so, then it means you are not filling in its value in the second form.
With a properly linked subform, this would happen automatically for you, but
not in a separate form.

If this is the problem, you can fix it by setting the default value of
EmployeeID as the form opens. Pass the EmployeeID from the first form in
the OpenArgs parameter:
DoCmd.OpenForm stDocName, , , stLinkCriteria _
OpenArgs := Me.EmployeeID

Now, in the Load event of the second form, set the default value:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Employee.DefaultValue = Me.OpenArgs
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

HI Graham

The Primary Key is an Auto Number What I am trying to
achieve I fill in Employee form click next and it goes
to the Emergency form and allows me to fil that form with
the link established.

I really dont want to use a subform if possible as I
Click to expand...
real > don't want to
pitt to much info in one form.
I am fairly new please help

Joel
-----Original Message-----
Hi Joel

In your stLinkCriteria, you have two instances of & "".

These will do nothing. You are simply appending an empty string to another
string.

If your EmployeeID field is text, then to enclose the value in quotes you
must use """". Two consecutive quote charters in a string are interpreted
as an embedded quote instead of ending the string.
Click to expand...
You
can also use Chr(34)
if you prefer.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Syncing two Forms

I am trying to Sync two form Via the EmployeeID (PK) from
on table to the foreign key of another.

I can link the two forms by the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Emergency"

stLinkCriteria = "[EmployeeID]=" & "" & Me!
[EmployeeID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria


When I try and do it using them forms it says that the
Key is Null but it isn't. When I fill in the tables it
links them no probs



.
Click to expand...
Click to expand...


.
Click to expand...
 
You Cannot add or change a related record is required in
table 'Employees'
Click to expand...

This would indicate that the new record you have just created in the first
form has not yet been saved. Add the following command before the
DoCmd.OpenForm:
Me.Dirty = False

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi there sorry about this I have tried what has been
suggested and I sill get the message below please assist
and I am truly sorry

You Cannot add or change a related record is required in
table 'Employees'
-----Original Message-----
Hi again Joel

If EmployeeID is an AutoNumber (and therefore presumably a long integer in
the Emergency table) then you don't need any quotes. You should just use:
stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]

However, the addition of empty strings (& "") would make no difference, so
the problem must be somewhere else.

You say it complains that "the key is null". Do you mean that you get this
message when you try to same a new record from the Emergency form? Is
EmployeeID by any chance also the primary key of the Emergency table (or a
part of it)?

If so, then it means you are not filling in its value in the second form.
With a properly linked subform, this would happen automatically for you, but
not in a separate form.

If this is the problem, you can fix it by setting the default value of
EmployeeID as the form opens. Pass the EmployeeID from the first form in
the OpenArgs parameter:
DoCmd.OpenForm stDocName, , , stLinkCriteria _
OpenArgs := Me.EmployeeID

Now, in the Load event of the second form, set the default value:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.Employee.DefaultValue = Me.OpenArgs
End If
End Sub
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

HI Graham

The Primary Key is an Auto Number What I am trying to
achieve I fill in Employee form click next and it goes
to the Emergency form and allows me to fil that form with
the link established.

I really dont want to use a subform if possible as I
Click to expand...
real > don't want to
pitt to much info in one form.
I am fairly new please help

Joel
-----Original Message-----
Hi Joel

In your stLinkCriteria, you have two instances of & "".

These will do nothing. You are simply appending an
empty string to another
string.

If your EmployeeID field is text, then to enclose the
value in quotes you
must use """". Two consecutive quote charters in a
string are interpreted
as an embedded quote instead of ending the string. You
can also use Chr(34)
if you prefer.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
Syncing two Forms

I am trying to Sync two form Via the EmployeeID (PK)
from
on table to the foreign key of another.

I can link the two forms by the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Emergency"

stLinkCriteria = "[EmployeeID]=" & "" & Me!
[EmployeeID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria


When I try and do it using them forms it says that the
Key is Null but it isn't. When I fill in the tables it
links them no probs



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top