opening new form in same id

[JB]

Hello.
I have a form 'Candidates' that has candidate details which you can add new
candidates or view existing.
There is a button 'Left' that opens another form where you put details if
the Candidate has left, date and reason.

I would like for that form to open showing the same candidate that is open
on the main form.

Thank you
Jen

 

[Philip Herlihy]

Hello.
I have a form 'Candidates' that has candidate details which you can add
new candidates or view existing.
There is a button 'Left' that opens another form where you put details
if the Candidate has left, date and reason.

I would like for that form to open showing the same candidate that is
open on the main form.

Thank you
Jen

You'd want a command button with the onclick event calling a procedure
using DoCmd.OpenForm

Have a look at that in Help - you would include a "Where" condition so
that the candidate in the opened form must be equal to the candidate in
the form already open.

Phil, London

 

[JB]

Hi
This is what I've got, but the CandidateId field isn't showing anyone
..........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
.................

thanks Jen

 

[Philip Herlihy]

Hi
This is what I've got, but the CandidateId field isn't showing anyone
.........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
................

thanks Jen

You'd want a command button with the onclick event calling a procedure
using DoCmd.OpenForm

Have a look at that in Help - you would include a "Where" condition so
that the candidate in the opened form must be equal to the candidate
in the form already open.

Phil, London

I can't see anything wrong in what you've done, even after creating a
simple prototype - it works for me. Here's what I tried:

I created a new database which linked to tables in an existing one where
I knew I could pick out a one-to-many relationship between two tables.

I created a simple query to join the two tables, then used the Form
Wizard to create a crude form based on that query, requesting "linked
forms" rather than a subform. This produced code based on filtering.

In the Click() function, I simply deleted the generated filtering code,
and replaced it with the equivalent of your code using DoCmd.OpenForm -
not bothering to tidy up the other related procedures.

Worked fine. So, I'd look to see if the data is the problem (we've all
puzzled over data we expected to be there but actually wasn't), and
whether the right fields are in the RecordSource for both forms.

Sadly, I have to spend the rest of this sunny London afternoon on a
hideous tax form (paper), so that's all I can contribute for now.

Good luck..

Phil

 

[Philip Herlihy]

Hi
This is what I've got, but the CandidateId field isn't showing anyone
.........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
................

thanks Jen

JB wrote:
Hello.
I have a form 'Candidates' that has candidate details which you can
add new candidates or view existing.
There is a button 'Left' that opens another form where you put
details if the Candidate has left, date and reason.

I would like for that form to open showing the same candidate that
is open on the main form.

Thank you
Jen

You'd want a command button with the onclick event calling a
procedure using DoCmd.OpenForm

Have a look at that in Help - you would include a "Where" condition
so that the candidate in the opened form must be equal to the
candidate in the form already open.

Phil, London

I can't see anything wrong in what you've done, even after creating a
simple prototype - it works for me. Here's what I tried:

I created a new database which linked to tables in an existing one where
I knew I could pick out a one-to-many relationship between two tables.

I created a simple query to join the two tables, then used the Form
Wizard to create a crude form based on that query, requesting "linked
forms" rather than a subform. This produced code based on filtering.

In the Click() function, I simply deleted the generated filtering code,
and replaced it with the equivalent of your code using DoCmd.OpenForm -
not bothering to tidy up the other related procedures.

Worked fine. So, I'd look to see if the data is the problem (we've all
puzzled over data we expected to be there but actually wasn't), and
whether the right fields are in the RecordSource for both forms.

Sadly, I have to spend the rest of this sunny London afternoon on a
hideous tax form (paper), so that's all I can contribute for now.

Good luck..

Phil

Worth adding that the automatically-generated code usefully demonstrated
that the data was as expected and also created a "where" clause for the
filter, based on the relationships defined in my back-end database.

Phil

 

[John W. Vinson]

Hi
This is what I've got, but the CandidateId field isn't showing anyone
.........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
................

As written this should show all of the records for this CandidateID which
currently exist in frmLeftDetails' Recordsource.

My guess is that there AREN'T any such records - should there be?

This would be a good deal simpler if you made frmLeftDetails a Subform of the
mainform, using CandidateID as the master/child link field. This would show
existing records and let you add new records for that candidate without any
code at all. The Subform could be on a tab page so it's not visible unless
needed.

If you'ld prefer to avoid the overhead of having a Subform (which you very
well might, especially if this feature is only used occasionally), then you'll
need to pass the CandidateID *twice*:

DoCmd.OpenForm stDocName, _
WhereCondition:=stLinkCriteria, _
OpenArgs:=Me!CandidateID

Then in the Open event of frmLeftDetails put

If Me.OpenArgs & vbNullString <> vbNullString Then
Me.CandidateID.DefaultValue = """" & Me.OpenArgs & """"
End If

 

[tina]

well, what do you mean by "showing the same candidate"?

your form Candidates is bound to a table Candidates, i assume, or to a query
based on tblCandidates. are the "left" details stored in another table? or
in the same table? if another table, is that table properly related to the
Candidate table, in the Relationships window?

and when you open the "left" window, are you expecting to see existing
records related to the candidate record in the Candidate form? or are you
only wanting to add a *new* record in the "left" form? if the latter, then
applying a WHERE clause to the "left" form's RecordSource won't get you
anywhere. what you need is to have the CandidateID automatically inserted
into the new record when you begin typing it. instead of using the WHERE
clause argument, try using the OpenForm's OpenArgs argument, as

DoCmd.OpenForm "frmLeftDetails", , , , , , Me!CandidateID

then you can add code to the "left" form's BeforeInsert event to add the
value to each new record, as

Me!CandidateID = Me.OpenArgs

whether or not you "see" the candidate depends on whether or not the
CandidateID field in the "left" form is bound to a control, and since i
assume you'd want to see a name not an ID, that control would have to be a
combobox control.

hth

Hi
This is what I've got, but the CandidateId field isn't showing anyone
.........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
................

thanks Jen

You'd want a command button with the onclick event calling a procedure
using DoCmd.OpenForm

Have a look at that in Help - you would include a "Where" condition so
that the candidate in the opened form must be equal to the candidate in
the form already open.

Phil, London

 

[JB]

WAAHHHH!! I'm SO confused!
I've tried everything, suggested, well I think I have. But I need more help
'cause it's just not sinking in.
Please hang in here with me. I'll explain what the two tables involved are
and what I'm trying to do from the beginning.

tblCandidates:

CandidateId
CandidateFirstName
CandidateLastName
tel , email etc etc.
underlying table for frmCandidates

tblAwol:

AwolId
DateLeft
ReasonLeft
CandidateId
underlying table for frmAwol
(candidateId is combo in form)

I put it in a seperate table because tblCandidates already has quite a lot
of columns.
In frmCandidate, I want a control to open frmAwol so that the user can
either
Enter the details if Candidate has left
or view the details if Candidate left and details previously entered. (the
latter not important as have a 'Gone Awol' report.) Bearing in mind a
candidate is only ever going to leave once so will only ever have one record
in tblAwol.

What I'd love is, when clicking the control to open frmAwol, instead of
using the drop down in the combo to look for the candidate, I'd like the
user to have the form open with the Candidates name ready so all they have
to do is enter the date and reason.

Is this possible?

Thanking you so much.
(btw I've changed the name from 'Left' to 'Awol' in case you were wondering,
'cause I was confusing myself with left and left-hand-side!) See what you're
dealing with....

Jen

well, what do you mean by "showing the same candidate"?

your form Candidates is bound to a table Candidates, i assume, or to a
query
based on tblCandidates. are the "left" details stored in another table? or
in the same table? if another table, is that table properly related to the
Candidate table, in the Relationships window?

and when you open the "left" window, are you expecting to see existing
records related to the candidate record in the Candidate form? or are you
only wanting to add a *new* record in the "left" form? if the latter, then
applying a WHERE clause to the "left" form's RecordSource won't get you
anywhere. what you need is to have the CandidateID automatically inserted
into the new record when you begin typing it. instead of using the WHERE
clause argument, try using the OpenForm's OpenArgs argument, as

DoCmd.OpenForm "frmLeftDetails", , , , , , Me!CandidateID

then you can add code to the "left" form's BeforeInsert event to add the
value to each new record, as

Me!CandidateID = Me.OpenArgs

whether or not you "see" the candidate depends on whether or not the
CandidateID field in the "left" form is bound to a control, and since i
assume you'd want to see a name not an ID, that control would have to be a
combobox control.

hth

Hi
This is what I've got, but the CandidateId field isn't showing anyone
.........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
................

thanks Jen

JB wrote:
Hello.
I have a form 'Candidates' that has candidate details which you can
add
new candidates or view existing.
There is a button 'Left' that opens another form where you put details
if the Candidate has left, date and reason.

I would like for that form to open showing the same candidate that is
open on the main form.

Thank you
Jen

You'd want a command button with the onclick event calling a procedure
using DoCmd.OpenForm

Have a look at that in Help - you would include a "Where" condition so
that the candidate in the opened form must be equal to the candidate in
the form already open.

Phil, London

 

[JB]

Just been tweaking and I didn't realise when I last posted, that if in
frmCandidate and go to a Candidate who has and Awol record and click to open
frmAwol, it does open in that Candidate's awol Record.
Yeay. so I'm half way there I guess.
Jen

WAAHHHH!! I'm SO confused!
I've tried everything, suggested, well I think I have. But I need more
help 'cause it's just not sinking in.
Please hang in here with me. I'll explain what the two tables involved
are and what I'm trying to do from the beginning.

tblCandidates:

CandidateId
CandidateFirstName
CandidateLastName
tel , email etc etc.
underlying table for frmCandidates

tblAwol:

AwolId
DateLeft
ReasonLeft
CandidateId
underlying table for frmAwol
(candidateId is combo in form)

I put it in a seperate table because tblCandidates already has quite a lot
of columns.
In frmCandidate, I want a control to open frmAwol so that the user can
either
Enter the details if Candidate has left
or view the details if Candidate left and details previously entered. (the
latter not important as have a 'Gone Awol' report.) Bearing in mind a
candidate is only ever going to leave once so will only ever have one
record in tblAwol.

What I'd love is, when clicking the control to open frmAwol, instead of
using the drop down in the combo to look for the candidate, I'd like the
user to have the form open with the Candidates name ready so all they have
to do is enter the date and reason.

Is this possible?

Thanking you so much.
(btw I've changed the name from 'Left' to 'Awol' in case you were
wondering, 'cause I was confusing myself with left and left-hand-side!)
See what you're dealing with....

Jen

well, what do you mean by "showing the same candidate"?

your form Candidates is bound to a table Candidates, i assume, or to a
query
based on tblCandidates. are the "left" details stored in another table?
or
in the same table? if another table, is that table properly related to
the
Candidate table, in the Relationships window?

and when you open the "left" window, are you expecting to see existing
records related to the candidate record in the Candidate form? or are you
only wanting to add a *new* record in the "left" form? if the latter,
then
applying a WHERE clause to the "left" form's RecordSource won't get you
anywhere. what you need is to have the CandidateID automatically inserted
into the new record when you begin typing it. instead of using the WHERE
clause argument, try using the OpenForm's OpenArgs argument, as

DoCmd.OpenForm "frmLeftDetails", , , , , , Me!CandidateID

then you can add code to the "left" form's BeforeInsert event to add the
value to each new record, as

Me!CandidateID = Me.OpenArgs

whether or not you "see" the candidate depends on whether or not the
CandidateID field in the "left" form is bound to a control, and since i
assume you'd want to see a name not an ID, that control would have to be
a
combobox control.

hth

Hi
This is what I've got, but the CandidateId field isn't showing anyone
.........
Private Sub cmdLeft_Click()
On Error GoTo Err_cmdLeft_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLeftDetails"

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

Exit_cmdLeft_Click:
Exit Sub

Err_cmdLeft_Click:
MsgBox Err.Description
Resume Exit_cmdLeft_Click

End Sub
................

thanks Jen



JB wrote:
Hello.
I have a form 'Candidates' that has candidate details which you can
add
new candidates or view existing.
There is a button 'Left' that opens another form where you put details
if the Candidate has left, date and reason.

I would like for that form to open showing the same candidate that is
open on the main form.

Thank you
Jen

You'd want a command button with the onclick event calling a procedure
using DoCmd.OpenForm

Have a look at that in Help - you would include a "Where" condition so
that the candidate in the opened form must be equal to the candidate
in
the form already open.

Phil, London

 

[John W. Vinson]

Just been tweaking and I didn't realise when I last posted, that if in
frmCandidate and go to a Candidate who has and Awol record and click to open
frmAwol, it does open in that Candidate's awol Record.
Yeay. so I'm half way there I guess.

You may have missed my earlier post. From it:

If you'ld prefer to avoid the overhead of having a Subform (which you very
well might, especially if this feature is only used occasionally), then you'll
need to pass the CandidateID *twice*:

DoCmd.OpenForm stDocName, _
WhereCondition:=stLinkCriteria, _
OpenArgs:=Me!CandidateID

Then in the Open event of frmLeftDetails put

If Me.OpenArgs & vbNullString <> vbNullString Then
Me.CandidateID.DefaultValue = """" & Me.OpenArgs & """"
End If

Essentially you need to pass the CandidateID from the mainform to the AWOL
form. The simplest way to do this is in the OpenArgs argument ( I prefer to
use the explicit argument names rather than tediously counting commas, but
both work; OpenArgs is the last argument to OpenForm, see the help).

The form's Open event would check to see if there is anything passed in the
OpenArgs, and if there is, use that value as the default for whatever control
contains the candidate ID.

 

[JB]

THANK YOU!!
I tried this before when you posted the first time but It didn't work, well
so I thought, but now I tried it again, but this time I made the frmAwol
property 'data entry' to True and that did it. So pleased.

Thank you so much and to all of you.
Couldn't ever do this without you.
Jen

 

[John W. Vinson]

THANK YOU!!
I tried this before when you posted the first time but It didn't work, well
so I thought, but now I tried it again, but this time I made the frmAwol
property 'data entry' to True and that did it. So pleased.

Just a warning: the Data Entry proprety means that this form will ONLY let you
add new records; it will NOT show you any existing ones. If that's what you
want, fine... but people do get tripped up on it.

 

[JB]

Thanks John, I'll make that clear to the user.
Jen