How to link forms with command button

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add tech rep"

stLinkCriteria = "[ID work details]=" & Me![ID Work details]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub
I have a command button on a form using the code above created from the
button wizard. The button works in opening a form linked by ID work details
but it opens as a new record. What I would like is to automatically have the
ID work details number loaded on the form on opening to link the information
on the tech rep form. I am new to using code so any help is appreciated.
Thanks

It sounds as though the form 'Add tech rep' is set to Data Entry - Yes.
This setting will always open it to a new record.

You can change the Data Entry property to No, but you need to carefully
consider if that will cause problems elsewhere e.g. that form is supposed to
open that way (I'm assuming this because of its name).


--
Joan Wild
Microsoft Access MVP

George McDonald wrote:
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add tech rep"

stLinkCriteria = "[ID work details]=" & Me![ID Work details]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub
I have a command button on a form using the code above created from
the button wizard. The button works in opening a form linked by ID
work details but it opens as a new record. What I would like is to
automatically have the ID work details number loaded on the form on
opening to link the information on the tech rep form. I am new to
using code so any help is appreciated. Thanks

The form 'add tech rep' is set to Data Entry - No. I am trying to log hours
and time details from a list of work done. Thanks for your input Joan. If I
can give you any other information please let me know

George

"Joan Wild" wrote:

It sounds as though the form 'Add tech rep' is set to Data Entry - Yes.
This setting will always open it to a new record.

You can change the Data Entry property to No, but you need to carefully
consider if that will cause problems elsewhere e.g. that form is supposed to
open that way (I'm assuming this because of its name).


--
Joan Wild
Microsoft Access MVP

George McDonald wrote:
Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add tech rep"

stLinkCriteria = "[ID work details]=" & Me![ID Work details]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub
I have a command button on a form using the code above created from
the button wizard. The button works in opening a form linked by ID
work details but it opens as a new record. What I would like is to
automatically have the ID work details number loaded on the form on
opening to link the information on the tech rep form. I am new to
using code so any help is appreciated. Thanks

On Tue, 31 Oct 2006 07:43:02 -0800, George McDonald wrote:

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add tech rep"

stLinkCriteria = "[ID work details]=" & Me![ID Work details]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command20_Click:
Exit Sub

Err_Command20_Click:
MsgBox Err.Description
Resume Exit_Command20_Click

End Sub
I have a command button on a form using the code above created from the
button wizard. The button works in opening a form linked by ID work details
but it opens as a new record. What I would like is to automatically have the
ID work details number loaded on the form on opening to link the information
on the tech rep form. I am new to using code so any help is appreciated.
Thanks

In addition to Joan's response, you might have code in the form's Open
or Load event that opens the form to a New Record.

For example:
RunCommand acCmdRecordsGoToNew
or..
DoCmd.GoToRecord etc....
or
DoCmd.DoMenuItem etc ....
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Just put the following code in the forms before insert event:


me![Id word details] = forms!PrevousFormNameGoesHere![id word details]

You should also make the "add tech rep" form model (so the user has to
*close* this form when done).

Note that your existing code should be ok, and if you added 5 "tec rep"
records, then you your openform command as you have will open the form with
5 records, and the users can then view/navigate the existing records...or,
add a new one.

Do note that if you use a sub-form, then you don't have to write, or use any
code at all. However, since you are opening a form, then you have to code
both the filter as you have,and also at the "insert time", you have to setup
the field used to maintain the relation.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

I have tried your suggestion and also Fredg with no luck. Can you help by
being more specific on where I would insert either code. I have entered your
code on the 'on enter' of the command button, this gives me a syntax, compile
error. Thanks for your patience.

George

"Albert D. Kallal" wrote:

Just put the following code in the forms before insert event:


me![Id word details] = forms!PrevousFormNameGoesHere![id word details]

You should also make the "add tech rep" form model (so the user has to
*close* this form when done).

Note that your existing code should be ok, and if you added 5 "tec rep"
records, then you your openform command as you have will open the form with
5 records, and the users can then view/navigate the existing records...or,
add a new one.

Do note that if you use a sub-form, then you don't have to write, or use any
code at all. However, since you are opening a form, then you have to code
both the filter as you have,and also at the "insert time", you have to setup
the field used to maintain the relation.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"George McDonald" wrote in
message ...

I have tried your suggestion and also Fredg with no luck. Can you help by
being more specific on where I would insert either code.

I have entered your
code on the 'on enter' of the command button, this gives me a syntax,
compile
error.

Ok, now why would we put the code in a different event, and different place
then suggested?

We can't just throw things at the wall, and hope they will stick like mud!!!
(computers don't work that way!!).

So, lets grab a cup a tea, take a step back, and think through what we are
doing:

We want to open a form to edit some related data. So, your first code of:

stDocName = "Add tech rep"

stLinkCriteria = "[ID work details]=" & Me![ID Work details]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The above code should open our form. The stLinkCriteria will RESTRICT THIS
FORM to the reocrds that belong (are related) to our first (main) form. Up
to this point, if you not added any reocords in "add tech rep", then when it
the form opens, it likey will not have any rocords in it. You should at this
point be able to START entering a new reocrd. (if there is exsting reocrds,
then you can view them).

So, what we are trying to accomplish here is that when you INSERT (or shall
we say add) a new reocrd, we must set up the value of the field [id work
details].

So, my suggestion was:
quote

Just put the following code in the forms before insert event:


me![Id word details] = forms!PrevousFormNameGoesHere![id word details]

Note carefully the above suggestion. Again, if we think this through, we
taking about *inserting* a new record. So, we are obviously talking about
the forms "before insert event", and that form is our "add tech rep"
form..right? (what other form would *likey* make sense here?)

So, the above code is to be entered on the "add tech rep" forms "before
insert" event.

also, since I am not a mind reader, I have:

me![Id word details] = forms!PrevousFormNameGoesHere![id word details]

You obviously have to replace !PrevousFormNameGoesHere with the name of the
main form (the one with your button code).

Also, make careful note of the other suggestions here (such as make sure you
did not accidentally set the "data entry" of this form to add child records
(add tech rep) = yes, as this will not allow you to review exiting records
(but, on the other hand, you *might* want this behaviour)

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Albert D. Kallal" wrote:

"George McDonald" wrote in
message ...

I have tried your suggestion and also Fredg with no luck. Can you help by
being more specific on where I would insert either code.

I have entered your
code on the 'on enter' of the command button, this gives me a syntax,
compile
error.

Ok, now why would we put the code in a different event, and different place
then suggested?

We can't just throw things at the wall, and hope they will stick like mud!!!
(computers don't work that way!!).

So, lets grab a cup a tea, take a step back, and think through what we are
doing:

We want to open a form to edit some related data. So, your first code of:

stDocName = "Add tech rep"

stLinkCriteria = "[ID work details]=" & Me![ID Work details]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The above code should open our form. The stLinkCriteria will RESTRICT THIS
FORM to the reocrds that belong (are related) to our first (main) form. Up
to this point, if you not added any reocords in "add tech rep", then when it
the form opens, it likey will not have any rocords in it. You should at this
point be able to START entering a new reocrd. (if there is exsting reocrds,
then you can view them).

So, what we are trying to accomplish here is that when you INSERT (or shall
we say add) a new reocrd, we must set up the value of the field [id work
details].

So, my suggestion was:
quote

Just put the following code in the forms before insert event:


me![Id word details] = forms!PrevousFormNameGoesHere![id word details]

Note carefully the above suggestion. Again, if we think this through, we
taking about *inserting* a new record. So, we are obviously talking about
the forms "before insert event", and that form is our "add tech rep"
form..right? (what other form would *likey* make sense here?)

So, the above code is to be entered on the "add tech rep" forms "before
insert" event.

also, since I am not a mind reader, I have:

me![Id word details] = forms!PrevousFormNameGoesHere![id word details]

You obviously have to replace !PrevousFormNameGoesHere with the name of the
main form (the one with your button code).

Also, make careful note of the other suggestions here (such as make sure you
did not accidentally set the "data entry" of this form to add child records
(add tech rep) = yes, as this will not allow you to review exiting records
(but, on the other hand, you *might* want this behaviour)

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



Hi Albert

Thanks for your suggestion on tea, I was getting that frustrated it was
almost something stronger I was reaching for. I have followedyourinstructions
and it nowworks perfectly. Thank you again for your patience and step by step
explanation.

George