Pass linked field data from form to form

S

Scott A

I have created a form used for recording equipment data.
To this form, I have added a command button that launches
a form to schedule service for a piece of equipment.

What I can't get it to do is pass the equipment ID number
(the primary key in the table used to create the first
form) to the service form (where the equipment ID is a
foreign key).

Any suggestions?

Thanks,
Scott
 
S

Sandra Daigle

Hi Scott,

Are you trying to use EquipmentId to start a new record or to filter a form
based on equipmentid? If you want to pass EquipmentId in order to start a
new record, you would probably want to use the OpenArgs parmameter of
docmd.openform. Your calling form can put anything into this parameter and
that (or those) values are available to the called form once it is opened.
If you look at Northwinds, the Add button passes the SupplierId value to the
Products form using this parameter (last one in the list of parameters
below)

DoCmd.OpenForm strDocName, , , , acAdd, , Me!SupplierID

Then in the called form (Products) the following code gets that value and
puts it into the new record after the Product Name is entered:

Private Sub ProductName_AfterUpdate()
' If OpenArgs property isn't null, set SupplierID to value of form's
OpenArgs
' property. OpenArgs will have a value if Products form is opened by
clicking
' AddProducts command button on Suppliers form.
If IsNull(Forms!Products.OpenArgs) Then
Exit Sub
Else
Me!SupplierID = Forms!Products.OpenArgs
End If
End Sub

Personally, I would do it in the BeforeInsert Event of the called form and I
would structure the code a bit differently. Mainly because I would only want
this code to run for new records.

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.SupplierID = Me.OpenArgs
End If
End Sub

If you want to use EquipmentId to filter existing records, look at the
WhereCondition parameter of docmd.openform:

docmd.OpenForm "frmMyForm",,,"Equipmentid=" & me.equipmentid

or

docmd.OpenForm "frmMyForm", WhereCondition:="Equipmentid=" & me.equipmentid
 
S

Scott A

I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?
-----Original Message-----
Hi Scott,

Are you trying to use EquipmentId to start a new record or to filter a form
based on equipmentid? If you want to pass EquipmentId in order to start a
new record, you would probably want to use the OpenArgs parmameter of
docmd.openform. Your calling form can put anything into this parameter and
that (or those) values are available to the called form once it is opened.
If you look at Northwinds, the Add button passes the SupplierId value to the
Products form using this parameter (last one in the list of parameters
below)

DoCmd.OpenForm strDocName, , , , acAdd, , Me! SupplierID

Then in the called form (Products) the following code gets that value and
puts it into the new record after the Product Name is entered:

Private Sub ProductName_AfterUpdate()
' If OpenArgs property isn't null, set SupplierID to value of form's
OpenArgs
' property. OpenArgs will have a value if Products form is opened by
clicking
' AddProducts command button on Suppliers form.
If IsNull(Forms!Products.OpenArgs) Then
Exit Sub
Else
Me!SupplierID = Forms!Products.OpenArgs
End If
End Sub

Personally, I would do it in the BeforeInsert Event of the called form and I
would structure the code a bit differently. Mainly because I would only want
this code to run for new records.

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.SupplierID = Me.OpenArgs
End If
End Sub

If you want to use EquipmentId to filter existing records, look at the
WhereCondition parameter of docmd.openform:

docmd.OpenForm "frmMyForm",,,"Equipmentid=" & me.equipmentid

or

docmd.OpenForm "frmMyForm",
Click to expand...
WhereCondition:="Equipmentid=" & me.equipmentid
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
I have created a form used for recording equipment data.
To this form, I have added a command button that launches
a form to schedule service for a piece of equipment.

What I can't get it to do is pass the equipment ID number
(the primary key in the table used to create the first
form) to the service form (where the equipment ID is a
foreign key).

Any suggestions?

Thanks,
Scott
Click to expand...

.
Click to expand...
 
S

Sandra Daigle

Hi Scott,

The code you currently have is only going to filter the second form based on
EqId. You need to use a combination of OpenArgs in the calling form and a
BeforeInsert event in the called form (subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event of 'subfrmServiceSchedule' you would have
something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you reference it would
be different if it is a subform.
 
S

Scott A

Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second form based on
EqId. You need to use a combination of OpenArgs in the calling form and a
BeforeInsert event in the called form (subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event of 'subfrmServiceSchedule' you would have
something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you reference it would
be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?
Click to expand...

.
Click to expand...
 
S

Sandra Daigle

Why of course there is hope for you! You found the right place to ask the
questions, now we just have to try to provide the answers. :)

Your scenario is perfectly reasonable - though it might be easier to
accomplish this with a simple form/subform, linked on EqID.

If you do want to stick with the second standalone form, I would make a
couple of recommendations - first change the name to drop the 'sub' since
that implies that it is a subform, rather than a related or linked form.
Note that the forms themselves do not necessarily have a connection but your
application, via the command button, is linking them.

The code I gave you should be sufficient since it opens the form in add mode
(preventing the user from viewing any other records) and it automatically
fills in the foreign key field (Eqid) as soon as the user begins to insert a
new record. Note that the BeforeInsert event occurs when the user types the
first character in a new record, but before the record is actually created
(from Access Help).


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second form
based on EqId. You need to use a combination of OpenArgs in the
calling form and a BeforeInsert event in the called form
(subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event of 'subfrmServiceSchedule' you would
have something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you reference
it would be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?
Click to expand...

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

Sandra Daigle

Hi Scott,

Is EqID a text field? I should have noticed that in stLinkCriteria you have
it quote wrapped so I am guessing that it is - change the OpenArgs parameter
to include the quotes and see what happens -

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.EqID & "'"

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Unfortunately, I can't get the code to work. Here's what
I've got for the OnClick event in the parent form:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

stLinkCriteria = "[EqID]=" & "'" & Me![EqID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, OpenArgs:=Me.EqID

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

I didn't think there would be any problem keeping the link
criteria - thought it might be of some use, but could just
be junk code at this point.

Here's what I have on the child form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

I can get all the way to the end of a record in the child
form, save the new record, and nothing appears in the EqID
field.

If it makes any difference, I'm using Access 2000. Maybe
should have said that up front...
-----Original Message-----
Why of course there is hope for you! You found the right place to
ask the questions, now we just have to try to provide the answers.
:)

Your scenario is perfectly reasonable - though it might be easier to
accomplish this with a simple form/subform, linked on EqID.

If you do want to stick with the second standalone form, I would
make a couple of recommendations - first change the name to drop the
'sub' since that implies that it is a subform, rather than a related
or linked form. Note that the forms themselves do not necessarily
have a connection but your application, via the command button, is
linking them.

The code I gave you should be sufficient since it opens the form in
add mode (preventing the user from viewing any other records) and it
automatically fills in the foreign key field (Eqid) as soon as the
user begins to insert a new record. Note that the BeforeInsert event
occurs when the user types the first character in a new record, but
before the record is actually created (from Access Help).


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second form
based on EqId. You need to use a combination of OpenArgs in the
calling form and a BeforeInsert event in the called form
(subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event of 'subfrmServiceSchedule' you would
have something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this
form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you reference
it would be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?

.
Click to expand...

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

Scott A

Nice catch! I knew there must be something I was
missing. I am having to use a text type for the
equipment Id to accomodate some business rules.

Thank you for all your help!

Scott
-----Original Message-----
Hi Scott,

Is EqID a text field? I should have noticed that in stLinkCriteria you have
it quote wrapped so I am guessing that it is - change the OpenArgs parameter
to include the quotes and see what happens -

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.EqID & "'"

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Unfortunately, I can't get the code to work. Here's what
I've got for the OnClick event in the parent form:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

stLinkCriteria = "[EqID]=" & "'" & Me![EqID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, OpenArgs:=Me.EqID

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

I didn't think there would be any problem keeping the link
criteria - thought it might be of some use, but could just
be junk code at this point.

Here's what I have on the child form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

I can get all the way to the end of a record in the child
form, save the new record, and nothing appears in the EqID
field.

If it makes any difference, I'm using Access 2000. Maybe
should have said that up front...
-----Original Message-----
Why of course there is hope for you! You found the right place to
ask the questions, now we just have to try to provide the answers.
:)

Your scenario is perfectly reasonable - though it might be easier to
accomplish this with a simple form/subform, linked on EqID.

If you do want to stick with the second standalone form, I would
make a couple of recommendations - first change the name to drop the
'sub' since that implies that it is a subform, rather than a related
or linked form. Note that the forms themselves do not necessarily
have a connection but your application, via the command button, is
linking them.

The code I gave you should be sufficient since it opens the form in
add mode (preventing the user from viewing any other records) and it
automatically fills in the foreign key field (Eqid) as soon as the
user begins to insert a new record. Note that the BeforeInsert event
occurs when the user types the first character in a new record, but
before the record is actually created (from Access Help).


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott A wrote:
Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second form
based on EqId. You need to use a combination of OpenArgs in the
calling form and a BeforeInsert event in the called form
(subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event
Click to expand...
of 'subfrmServiceSchedule' you would
have something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this
form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you reference
it would be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?

.

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

.
Click to expand...
 
S

Sandra Daigle

You're welcome - good luck with the rest of your project!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Nice catch! I knew there must be something I was
missing. I am having to use a text type for the
equipment Id to accomodate some business rules.

Thank you for all your help!

Scott
-----Original Message-----
Hi Scott,

Is EqID a text field? I should have noticed that in stLinkCriteria
you have it quote wrapped so I am guessing that it is - change the
OpenArgs parameter to include the quotes and see what happens -

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.EqID & "'"

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Unfortunately, I can't get the code to work. Here's what
I've got for the OnClick event in the parent form:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

stLinkCriteria = "[EqID]=" & "'" & Me![EqID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, OpenArgs:=Me.EqID

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

I didn't think there would be any problem keeping the link
criteria - thought it might be of some use, but could just
be junk code at this point.

Here's what I have on the child form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

I can get all the way to the end of a record in the child
form, save the new record, and nothing appears in the EqID
field.

If it makes any difference, I'm using Access 2000. Maybe
should have said that up front...
-----Original Message-----
Why of course there is hope for you! You found the right place to
ask the questions, now we just have to try to provide the answers.
:)

Your scenario is perfectly reasonable - though it might be easier
to accomplish this with a simple form/subform, linked on EqID.

If you do want to stick with the second standalone form, I would
make a couple of recommendations - first change the name to drop
the 'sub' since that implies that it is a subform, rather than a
related or linked form. Note that the forms themselves do not
necessarily have a connection but your application, via the
command button, is linking them.

The code I gave you should be sufficient since it opens the form in
add mode (preventing the user from viewing any other records) and
it automatically fills in the foreign key field (Eqid) as soon as
the user begins to insert a new record. Note that the BeforeInsert
event occurs when the user types the first character in a new
record, but before the record is actually created (from Access
Help).


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second
form based on EqId. You need to use a combination of OpenArgs in
the calling form and a BeforeInsert event in the called form
(subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event
of 'subfrmServiceSchedule' you would
have something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this
form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you
reference it would be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?

.

.
Click to expand...

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

Scott A

I'm now passing the value to the new form, but it includes
a pair of quotes around the EqID - which prevents the new
record from being saved! Here's what I have for code in
the two forms at this point:

Equipment form, OnClick event:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.[EqID]
& "'"

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

End Sub

Service Schedule form, Before Insert event:
Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

Were are the extra quotes coming from?
-----Original Message-----
You're welcome - good luck with the rest of your project!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Nice catch! I knew there must be something I was
missing. I am having to use a text type for the
equipment Id to accomodate some business rules.

Thank you for all your help!

Scott
-----Original Message-----
Hi Scott,

Is EqID a text field? I should have noticed that in stLinkCriteria
you have it quote wrapped so I am guessing that it is - change the
OpenArgs parameter to include the quotes and see what happens -

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.EqID & "'"

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott A wrote:
Unfortunately, I can't get the code to work. Here's what
I've got for the OnClick event in the parent form:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

stLinkCriteria = "[EqID]=" & "'" & Me![EqID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, OpenArgs:=Me.EqID

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

I didn't think there would be any problem keeping the link
criteria - thought it might be of some use, but could just
be junk code at this point.

Here's what I have on the child form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

I can get all the way to the end of a record in the child
form, save the new record, and nothing appears in the EqID
field.

If it makes any difference, I'm using Access 2000. Maybe
should have said that up front...
-----Original Message-----
Why of course there is hope for you! You found the right place to
ask the questions, now we just have to try to provide the answers.
:)

Your scenario is perfectly reasonable - though it might be easier
to accomplish this with a simple form/subform, linked on EqID.

If you do want to stick with the second standalone form, I would
make a couple of recommendations - first change the name to drop
the 'sub' since that implies that it is a subform, rather than a
related or linked form. Note that the forms themselves do not
necessarily have a connection but your application, via the
command button, is linking them.

The code I gave you should be sufficient since it opens the form in
add mode (preventing the user from viewing any other records) and
it automatically fills in the foreign key field (Eqid) as soon as
the user begins to insert a new record. Note that the BeforeInsert
event occurs when the user types the first character in a new
record, but before the record is actually created (from Access
Help).


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second
form based on EqId. You need to use a combination of OpenArgs in
the calling form and a BeforeInsert event in the called form
(subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event
of 'subfrmServiceSchedule' you would
have something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this
form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you
reference it would be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?

.

.

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

Sandra Daigle

Hi Scott,

The extra quotes are showing up because I told you to put them there - now
I'm wondering why since I *know* better! :-D

Before we added the superflous quotes, you said that all was working fine
except that EqId wasn't getting a value in the BeforeInsert event - correct?
I wonder if the 'frmServiceScheduleA' was already open when you clicked your
command button to run this code - if so, OpenArgs in frmServiceScheduleA
would still be the value that was passed (if any) when it was originally
opened. This is important to know when using OpenArgs. Do you think that was
the original probem?

Sandra Daigle
Microsoft Access MVP

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
I'm now passing the value to the new form, but it includes
a pair of quotes around the EqID - which prevents the new
record from being saved! Here's what I have for code in
the two forms at this point:

Equipment form, OnClick event:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.[EqID]
& "'"

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

End Sub

Service Schedule form, Before Insert event:
Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

Were are the extra quotes coming from?
-----Original Message-----
You're welcome - good luck with the rest of your project!

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Scott said:
Nice catch! I knew there must be something I was
missing. I am having to use a text type for the
equipment Id to accomodate some business rules.

Thank you for all your help!

Scott
-----Original Message-----
Hi Scott,

Is EqID a text field? I should have noticed that in stLinkCriteria
you have it quote wrapped so I am guessing that it is - change the
OpenArgs parameter to include the quotes and see what happens -

DoCmd.OpenForm stDocName, OpenArgs:="'" & Me.EqID & "'"

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
Unfortunately, I can't get the code to work. Here's what
I've got for the OnClick event in the parent form:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmServiceScheduleA"

stLinkCriteria = "[EqID]=" & "'" & Me![EqID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm stDocName, OpenArgs:=Me.EqID

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

I didn't think there would be any problem keeping the link
criteria - thought it might be of some use, but could just
be junk code at this point.

Here's what I have on the child form's BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel As Integer)

If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If

End Sub

I can get all the way to the end of a record in the child
form, save the new record, and nothing appears in the EqID
field.

If it makes any difference, I'm using Access 2000. Maybe
should have said that up front...
-----Original Message-----
Why of course there is hope for you! You found the right place to
ask the questions, now we just have to try to provide the
answers. :)

Your scenario is perfectly reasonable - though it might be easier
to accomplish this with a simple form/subform, linked on EqID.

If you do want to stick with the second standalone form, I would
make a couple of recommendations - first change the name to drop
the 'sub' since that implies that it is a subform, rather than a
related or linked form. Note that the forms themselves do not
necessarily have a connection but your application, via the
command button, is linking them.

The code I gave you should be sufficient since it opens the form
in add mode (preventing the user from viewing any other records)
and it automatically fills in the foreign key field (Eqid) as
soon as the user begins to insert a new record. Note that the
BeforeInsert event occurs when the user types the first
character in a new record, but before the record is actually
created (from Access Help).


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
Is it a form, or is it a subform? Tough question!

It is a standalone form that I have linked to an existing
form using one of the Wizards... When setting it up, I
did ask it to basically filter the information displayed
on the child based on the record displayed in the parent.
I just named it 'sub' to differentiate it from the other
forms in the database.....

I'm still not sure I'm going about this the right way - it
seems this should be so much easier to do, especially
since I've already got relationships established between
these two tables, etc, bla bla.. Should I be basing the
second form on a query that already includes the EqID?

My intention is to provide a pop-up form that allows the
user to add service records ONLY to the piece of equipment
displayed in the parent form, which I'm guessing means
that I want to filter the second form based on the EqID of
the record displayed AND pass the value to the child form.

Is there any hope for me?
-----Original Message-----
Hi Scott,

The code you currently have is only going to filter the second
form based on EqId. You need to use a combination of OpenArgs
in the calling form and a BeforeInsert event in the called form
(subfrmServiceSchedule).

Modify your click event to be something like this:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click
Dim stDocName As String
stDocName = "subfrmServiceSchedule"
DoCmd.OpenForm stDocName,,,,,,me.eqid
' or more succintly:
' DoCmd.OpenForm stDocName,openargs:=me.eqid

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click

Then in the BeforeInsert event
of 'subfrmServiceSchedule' you would
have something like the following:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.EqID = Me.OpenArgs
End If
End Sub

One thing that needs clarification - is this
form 'subfrmServiceSchedule'
really a subform or is it a standalone form? The way you
reference it would be different if it is a subform.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this
newsgroup.


Scott A wrote:
I'm trying to start a new record - I'd like to
automatically pass the Equipment ID to the new form, just
because it's not relevant to the user and I don't want
them to have to type it in themselves.

If I were to implement this code (the first one you've
provided), I assume I need to modify the existing OnClick
event procedure. Here's what is in there already:

Private Sub cmdServiceSchedule_Click()
On Error GoTo Err_cmdServiceSchedule_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "subfrmServiceSchedule"

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

Exit_cmdServiceSchedule_Click:
Exit Sub

Err_cmdServiceSchedule_Click:
MsgBox Err.Description
Resume Exit_cmdServiceSchedule_Click


I assume that the existing event describes the linked
properties of the two forms, and was also assuming that
this code would pass the data between the forms. If this
is not the case, where do I insert the acAdd statement?

.

.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DATA FROM RECORDS ON SUBFORM 19
Auto-populating fields in a form 1
Report Preview inside a Form 2
Must Fill in a Field 4
Stuck on form design and appropiate control to use, Need Help Plea 2
Form to update table and detect changes 4
apply form filter on open form 1
One form with many questions 4

Top