Show / Hide Form

[Andy Roberts]

2 questions which seem too specific to get an answer from Google etc hence
the post.

I have a client form (showing tblClients) and it has a subform which shows
all the individuals who work for the client company (tblClientRep). This
works fine - as I cycle through the client records the individuals change
based on the current client.

1. To add a new individual I have a button on my client form which opens a
ClientRep form where I enter the individual's details and on that form is a
cbo box which lists all the clients on the db, so I can assign the
individual to a client company. My first question is how do I automatically
fill the client cbo on the ClientRep form? The Client Rep form is triggered
from a specific client record so I should be able to carry this through so I
don't have to manually select the client from the cbo. Is this possible?

2. When I click the button to open the Add New Individual form I have the
main client form hidden using

me.visible.=false on the click event of the button (no problem so far...)

On the Individual form once I've input the data I have a button which closes
the form and unhides the original main form using...

Private Sub Form_Close()
Forms![frmClients].Visible = True
End Sub

.... on the on close event of the individual form.

This works fine, but I've now got to the stage where I have 2 separate forms
which open the same individual form and closing it using the button will
always unhide the frmClient. My second question is can the button use code
which closes the current form and unhides whatever the previous form was
irrespective of which form it is? I can get around the problem using two
identical forms with different names but this is becoming difficult to
maintain and what if I needed a 3rd form? I'm sure the duplicate form
approach is the wrong way forward.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

 

[Dirk Goldgar]

(comments inline)

2 questions which seem too specific to get an answer from Google etc hence
the post.

I have a client form (showing tblClients) and it has a subform which shows
all the individuals who work for the client company (tblClientRep). This
works fine - as I cycle through the client records the individuals change
based on the current client.

1. To add a new individual I have a button on my client form which opens a
ClientRep form where I enter the individual's details and on that form is
a cbo box which lists all the clients on the db, so I can assign the
individual to a client company. My first question is how do I
automatically fill the client cbo on the ClientRep form? The Client Rep
form is triggered from a specific client record so I should be able to
carry this through so I don't have to manually select the client from the
cbo. Is this possible?

Yes. Your button to open the ClientRep form can do it, along these lines:

DoCmd.OpenForm "frmClientRep", DataMode:=acFormAdd
Forms!frmClientRep!cboClient.DefaultValue = """" & Me.ClientID & """"

2. When I click the button to open the Add New Individual form I have the
main client form hidden using

me.visible.=false on the click event of the button (no problem so far...)

On the Individual form once I've input the data I have a button which
closes the form and unhides the original main form using...

Private Sub Form_Close()
Forms![frmClients].Visible = True
End Sub

... on the on close event of the individual form.

This works fine, but I've now got to the stage where I have 2 separate
forms which open the same individual form and closing it using the button
will always unhide the frmClient. My second question is can the button
use code which closes the current form and unhides whatever the previous
form was irrespective of which form it is? I can get around the problem
using two identical forms with different names but this is becoming
difficult to maintain and what if I needed a 3rd form? I'm sure the
duplicate form approach is the wrong way forward.

You could either (A) hard code it to check for which of the forms is open,
or (B) you could pass the name of the form via OpenArgs when you open the
form, and the form could use that to decide which form to make visible.

Approach A
-----------------

In the Close event of frmClientRep:

Private Sub Form_Close()

If CurrentProject.AllForms("frmClients").IsLoaded Then
Forms![frmClients].Visible = True
ElseIf CurrentProject.AllForms("frmOther").IsLoaded Then
Forms![frmOther].Visible = True
End If

End Sub

Approach B
-----------------

Wherever you open frmClientRep:

DoCmd.OpenForm "frmClientRep", _
DataMode:=acFormAdd, OpenArgs:=Me.Name

Me.Visible = False

In the Close event of frmClientRep:

Private Sub Form_Close()

Dim strFormToShow As String

strFormToShow = Me.OpenArgs & vbNullString

If Len(strFormToShow) > 0 Then
Forms(strFormToShow) .Visible = True
End If

End Sub

 

[Andy Roberts]

This same individual form also needs to act as a data display form and a
data entry form, so it needs to be blank when opened from lets say formA and
not black when opened from FormB.

Can I set the DataEntry property on the on click event of the button which
opens each form?

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

 

[Andy Roberts]

Dirk

I opted for B and this works great except for one thing I didn't mention
which now seems pretty obvious.

One of the forms which calls the ClientRep form (i.e. the second form)
includes a subform which houses the button which executes the code so
opening the second form is fine but the variable being stored is the name of
the subform and not the main form so I get an error when I try to return to
the main form as this isn't the form which is stored.

Trust this makes sense.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

(comments inline)

2 questions which seem too specific to get an answer from Google etc hence
the post.

I have a client form (showing tblClients) and it has a subform which
shows all the individuals who work for the client company (tblClientRep).
This works fine - as I cycle through the client records the individuals
change based on the current client.

1. To add a new individual I have a button on my client form which opens
a ClientRep form where I enter the individual's details and on that form
is a cbo box which lists all the clients on the db, so I can assign the
individual to a client company. My first question is how do I
automatically fill the client cbo on the ClientRep form? The Client Rep
form is triggered from a specific client record so I should be able to
carry this through so I don't have to manually select the client from the
cbo. Is this possible?

Yes. Your button to open the ClientRep form can do it, along these lines:

DoCmd.OpenForm "frmClientRep", DataMode:=acFormAdd
Forms!frmClientRep!cboClient.DefaultValue = """" & Me.ClientID & """"

2. When I click the button to open the Add New Individual form I have the
main client form hidden using

me.visible.=false on the click event of the button (no problem so far...)

On the Individual form once I've input the data I have a button which
closes the form and unhides the original main form using...

Private Sub Form_Close()
Forms![frmClients].Visible = True
End Sub

... on the on close event of the individual form.

This works fine, but I've now got to the stage where I have 2 separate
forms which open the same individual form and closing it using the button
will always unhide the frmClient. My second question is can the button
use code which closes the current form and unhides whatever the previous
form was irrespective of which form it is? I can get around the problem
using two identical forms with different names but this is becoming
difficult to maintain and what if I needed a 3rd form? I'm sure the
duplicate form approach is the wrong way forward.

You could either (A) hard code it to check for which of the forms is open,
or (B) you could pass the name of the form via OpenArgs when you open the
form, and the form could use that to decide which form to make visible.

Approach A
-----------------

In the Close event of frmClientRep:

Private Sub Form_Close()

If CurrentProject.AllForms("frmClients").IsLoaded Then
Forms![frmClients].Visible = True
ElseIf CurrentProject.AllForms("frmOther").IsLoaded Then
Forms![frmOther].Visible = True
End If

End Sub

Approach B
-----------------

Wherever you open frmClientRep:

DoCmd.OpenForm "frmClientRep", _
DataMode:=acFormAdd, OpenArgs:=Me.Name

Me.Visible = False

In the Close event of frmClientRep:

Private Sub Form_Close()

Dim strFormToShow As String

strFormToShow = Me.OpenArgs & vbNullString

If Len(strFormToShow) > 0 Then
Forms(strFormToShow) .Visible = True
End If

End Sub


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk

I opted for B and this works great except for one thing I didn't mention
which now seems pretty obvious.

One of the forms which calls the ClientRep form (i.e. the second form)
includes a subform which houses the button which executes the code so
opening the second form is fine but the variable being stored is the name
of the subform and not the main form so I get an error when I try to
return to the main form as this isn't the form which is stored.

No big deal; just hard-code the name of the main form, instead of using
Me.Name :

DoCmd.OpenForm "frmClientRep", _
DataMode:=acFormAdd, OpenArgs:="MainFormName"

 

[Dirk Goldgar]

This same individual form also needs to act as a data display form and a
data entry form, so it needs to be blank when opened from lets say formA
and not black when opened from FormB.

Can I set the DataEntry property on the on click event of the button which
opens each form?

Did you see the example of this that I posted in the other subthread? In
the one case you use the DataMode:=acFormAdd argument, in the other case you
don't.

 

[Andy Roberts]

Dirk

Just realised that when the second form opens and includes the ClientID from
the first form, I cant actually add anything.

The status bar says "cannot add record(s); join key of 'tblClientRep' not in
recordset"

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

Dirk

I opted for B and this works great except for one thing I didn't mention
which now seems pretty obvious.

One of the forms which calls the ClientRep form (i.e. the second form)
includes a subform which houses the button which executes the code so
opening the second form is fine but the variable being stored is the name
of the subform and not the main form so I get an error when I try to
return to the main form as this isn't the form which is stored.

Trust this makes sense.

--
Regards

Andy
___________
Andy Roberts
Win XP Pro
Access 2007
Liverpool, UK

(comments inline)

2 questions which seem too specific to get an answer from Google etc
hence the post.

I have a client form (showing tblClients) and it has a subform which
shows all the individuals who work for the client company
(tblClientRep). This works fine - as I cycle through the client records
the individuals change based on the current client.

1. To add a new individual I have a button on my client form which opens
a ClientRep form where I enter the individual's details and on that form
is a cbo box which lists all the clients on the db, so I can assign the
individual to a client company. My first question is how do I
automatically fill the client cbo on the ClientRep form? The Client Rep
form is triggered from a specific client record so I should be able to
carry this through so I don't have to manually select the client from
the cbo. Is this possible?

Yes. Your button to open the ClientRep form can do it, along these
lines:

DoCmd.OpenForm "frmClientRep", DataMode:=acFormAdd
Forms!frmClientRep!cboClient.DefaultValue = """" & Me.ClientID & """"

2. When I click the button to open the Add New Individual form I have
the main client form hidden using

me.visible.=false on the click event of the button (no problem so
far...)

On the Individual form once I've input the data I have a button which
closes the form and unhides the original main form using...

Private Sub Form_Close()
Forms![frmClients].Visible = True
End Sub

... on the on close event of the individual form.

This works fine, but I've now got to the stage where I have 2 separate
forms which open the same individual form and closing it using the
button will always unhide the frmClient. My second question is can the
button use code which closes the current form and unhides whatever the
previous form was irrespective of which form it is? I can get around
the problem using two identical forms with different names but this is
becoming difficult to maintain and what if I needed a 3rd form? I'm
sure the duplicate form approach is the wrong way forward.

You could either (A) hard code it to check for which of the forms is
open, or (B) you could pass the name of the form via OpenArgs when you
open the form, and the form could use that to decide which form to make
visible.

Approach A
-----------------

In the Close event of frmClientRep:

Private Sub Form_Close()

If CurrentProject.AllForms("frmClients").IsLoaded Then
Forms![frmClients].Visible = True
ElseIf CurrentProject.AllForms("frmOther").IsLoaded Then
Forms![frmOther].Visible = True
End If

End Sub

Approach B
-----------------

Wherever you open frmClientRep:

DoCmd.OpenForm "frmClientRep", _
DataMode:=acFormAdd, OpenArgs:=Me.Name

Me.Visible = False

In the Close event of frmClientRep:

Private Sub Form_Close()

Dim strFormToShow As String

strFormToShow = Me.OpenArgs & vbNullString

If Len(strFormToShow) > 0 Then
Forms(strFormToShow) .Visible = True
End If

End Sub


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk

Just realised that when the second form opens and includes the ClientID
from the first form, I cant actually add anything.

The status bar says "cannot add record(s); join key of 'tblClientRep' not
in recordset"

This sounds like a flaw in the recordsource of the form. Please post the
SQL of the recordsource of tblClientRep.