Search Upon Open

[Confused87]

I've no idea what the difference between a Switchboard and a form designed to
do the same thing is - so I've used a form.

Database opens, my form automatically pops up. I have various forms the user
can access from that page, however, viewing all the data upon a click is not
useful, neither is going to one specific form everytime.

From a Command Button on a form, can I have the end product being: the user
clicks, a search function opens(for Surname), the user types inthe name, the
form opens on that record, rather than having the search button inside the
opened form.

What do I do?

I don't mind moving it all over to a Switchboard if it makes a difference.

Thanks
C

 

[Ken Sheridan]

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

If on the other hand toy want to open a form at a single person then you
have to take account of the possibility of two or more having the same name.
I'll assume for this example that none have the same first and last names,
but even that is risky (I once worked with two Maggie Taylors). The
Employees table will presumably have an EmployeeID field (e.g. an autonumber)
as its primary key to uniquely identify each employee. The combo box would
now be set up like this:

RowSource: SELECT EmployeeID, Surname & ", " & FirstName FROM Employees
ORDER BY Surname, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

As the bound column of the control, and hence its value, is now the hidden
EmployeeID column, the code for the combo box's AfterUpdate event procedure
would now be:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected employee
strCriteria = "EmployeeID = " & ctrl
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Note how in this case the value of the combo box is not wrapped in quotes
characters when building the string expression on which to filter the bound
form. This is because, unlike the Surname column, the EmployeeID column is a
number data type, not text, so is not delimited by quotes characters.

The only alteration you need to make to your current opening form is to
change the button's code so that it now opens your new frmNamesDlg dialogue
form rather than the bound form.

Ken Sheridan
Stafford, England

 

[Confused87]

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

 

[Ken Sheridan]

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

 

[Confused87]

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

 

[Ken Sheridan]

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

 

[Confused87]

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

 

[Ken Sheridan]

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

If on the other hand toy want to open a form at a single person then you
have to take account of the possibility of two or more having the same name.
I'll assume for this example that none have the same first and last names,
but even that is risky (I once worked with two Maggie Taylors). The
Employees table will presumably have an EmployeeID field (e.g. an autonumber)
as its primary key to uniquely identify each employee. The combo box would
now be set up like this:

RowSource: SELECT EmployeeID, Surname & ", " & FirstName FROM Employees
ORDER BY Surname, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

As the bound column of the control, and hence its value, is now the hidden
EmployeeID column, the code for the combo box's AfterUpdate event procedure
would now be:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected employee
strCriteria = "EmployeeID = " & ctrl
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Note how in this case the value of the combo box is not wrapped in quotes
characters when building the string expression on which to filter the bound
form. This is because, unlike the Surname column, the EmployeeID column is a
number data type, not text, so is not delimited by quotes characters.

The only alteration you need to make to your current opening form is to
change the button's code so that it now opens your new frmNamesDlg dialogue
form rather than the bound form.

Ken Sheridan
Stafford, England

:

I've no idea what the difference between a Switchboard and a form designed to
do the same thing is - so I've used a form.

Database opens, my form automatically pops up. I have various forms the user
can access from that page, however, viewing all the data upon a click is not
useful, neither is going to one specific form everytime.

 

[Confused87]

?

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

:

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

If on the other hand toy want to open a form at a single person then you
have to take account of the possibility of two or more having the same name.
I'll assume for this example that none have the same first and last names,
but even that is risky (I once worked with two Maggie Taylors). The
Employees table will presumably have an EmployeeID field (e.g. an autonumber)
as its primary key to uniquely identify each employee. The combo box would
now be set up like this:

RowSource: SELECT EmployeeID, Surname & ", " & FirstName FROM Employees
ORDER BY Surname, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

As the bound column of the control, and hence its value, is now the hidden
EmployeeID column, the code for the combo box's AfterUpdate event procedure
would now be:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected employee
strCriteria = "EmployeeID = " & ctrl
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Note how in this case the value of the combo box is not wrapped in quotes
characters when building the string expression on which to filter the bound
form. This is because, unlike the Surname column, the EmployeeID column is a
number data type, not text, so is not delimited by quotes characters.

The only alteration you need to make to your current opening form is to
change the button's code so that it now opens your new frmNamesDlg dialogue
form rather than the bound form.

Ken Sheridan
Stafford, England

:

I've no idea what the difference between a Switchboard and a form designed to
do the same thing is - so I've used a form.

 

[Ken Sheridan]

Camilla:

I either wrote my last reply with invisible ink or it was routed via the
Bermuda Triangle. I said something along these lines:

My guess is that the error occurs on the Click event of the button on the
opening form. It sounds like you might have entered code directly in the
Click event property in the control's properties sheet, not into its event
procedure. You might find the simplest solution is to delete the existing
button on the opening form and add a new one, using the control wizard to get
it to open the frmNamesDlg form. The wizard will create the code in the
click event procedure for you.

Ken Sheridan
Stafford, England

?

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla


:

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

:

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

If on the other hand toy want to open a form at a single person then you
have to take account of the possibility of two or more having the same name.
I'll assume for this example that none have the same first and last names,
but even that is risky (I once worked with two Maggie Taylors). The
Employees table will presumably have an EmployeeID field (e.g. an autonumber)
as its primary key to uniquely identify each employee. The combo box would
now be set up like this:

RowSource: SELECT EmployeeID, Surname & ", " & FirstName FROM Employees
ORDER BY Surname, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

As the bound column of the control, and hence its value, is now the hidden
EmployeeID column, the code for the combo box's AfterUpdate event procedure
would now be:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected employee
strCriteria = "EmployeeID = " & ctrl
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Note how in this case the value of the combo box is not wrapped in quotes
characters when building the string expression on which to filter the bound
form. This is because, unlike the Surname column, the EmployeeID column is a
number data type, not text, so is not delimited by quotes characters.

The only alteration you need to make to your current opening form is to
change the button's code so that it now opens your new frmNamesDlg dialogue
form rather than the bound form.

Ken Sheridan
Stafford, England

 

[Confused87]

No worries. I heard a rumour that a good possible location for a data cloud
server was Bermuda. I don’t think it is true though and probably originated
as a joke :S

Anyhow – have entered the code from point 1.5 into the Code Builder (copy
and pasted it all) of AfterUpdate event for the combobox, and the code from
2.3 (copied and pasted) straight into the box of the command button under On
Click for the event tab on a new button(for simplicity’s sake). I am now
getting this message(!):


Front Page can’t find macro ‘On Error Goto Err_Handler

DoCmd.’

The macro(or its macro group) doesn’t exist, or the macro is new but hasn’t
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro’s macro group was last saved
under.

It takes me straight to the code builder and the Err_Handler text when I
click any of the buttons on the Front Page form.

What a saga.

Camilla:

I either wrote my last reply with invisible ink or it was routed via the
Bermuda Triangle. I said something along these lines:

My guess is that the error occurs on the Click event of the button on the
opening form. It sounds like you might have entered code directly in the
Click event property in the control's properties sheet, not into its event
procedure. You might find the simplest solution is to delete the existing
button on the opening form and add a new one, using the control wizard to get
it to open the frmNamesDlg form. The wizard will create the code in the
click event procedure for you.

Ken Sheridan
Stafford, England

?

:

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla


:

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

:

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

If on the other hand toy want to open a form at a single person then you
have to take account of the possibility of two or more having the same name.
I'll assume for this example that none have the same first and last names,
but even that is risky (I once worked with two Maggie Taylors). The
Employees table will presumably have an EmployeeID field (e.g. an autonumber)
as its primary key to uniquely identify each employee. The combo box would
now be set up like this:

RowSource: SELECT EmployeeID, Surname & ", " & FirstName FROM Employees
ORDER BY Surname, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

As the bound column of the control, and hence its value, is now the hidden
EmployeeID column, the code for the combo box's AfterUpdate event procedure
would now be:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected employee

 

[Ken Sheridan]

Camilla:

The behaviour you are getting usually indicates that code has been entered
directly into the properties sheet not into the event procedure. Are you
sure you've done as follows?

Select the button in form design view and open its properties sheet if its
not already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

No worries. I heard a rumour that a good possible location for a data cloud
server was Bermuda. I don’t think it is true though and probably originated
as a joke :S

Anyhow – have entered the code from point 1.5 into the Code Builder (copy
and pasted it all) of AfterUpdate event for the combobox, and the code from
2.3 (copied and pasted) straight into the box of the command button under On
Click for the event tab on a new button(for simplicity’s sake). I am now
getting this message(!):


Front Page can’t find macro ‘On Error Goto Err_Handler

DoCmd.’

The macro(or its macro group) doesn’t exist, or the macro is new but hasn’t
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro’s macro group was last saved
under.

It takes me straight to the code builder and the Err_Handler text when I
click any of the buttons on the Front Page form.

What a saga.

Camilla:

I either wrote my last reply with invisible ink or it was routed via the
Bermuda Triangle. I said something along these lines:

My guess is that the error occurs on the Click event of the button on the
opening form. It sounds like you might have entered code directly in the
Click event property in the control's properties sheet, not into its event
procedure. You might find the simplest solution is to delete the existing
button on the opening form and add a new one, using the control wizard to get
it to open the frmNamesDlg form. The wizard will create the code in the
click event procedure for you.

Ken Sheridan
Stafford, England

?

:



:

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla


:

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

:

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "frmEmployees", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

If on the other hand toy want to open a form at a single person then you
have to take account of the possibility of two or more having the same name.
I'll assume for this example that none have the same first and last names,
but even that is risky (I once worked with two Maggie Taylors). The

 

[Confused87]

I did that Ken.

I have posteed a screen shot up here:

http://i-suck-using-access.blogspot.com/

It is a little blurry but may be of help :S

Camilla:

The behaviour you are getting usually indicates that code has been entered
directly into the properties sheet not into the event procedure. Are you
sure you've done as follows?

Select the button in form design view and open its properties sheet if its
not already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

No worries. I heard a rumour that a good possible location for a data cloud
server was Bermuda. I don’t think it is true though and probably originated
as a joke :S

Anyhow – have entered the code from point 1.5 into the Code Builder (copy
and pasted it all) of AfterUpdate event for the combobox, and the code from
2.3 (copied and pasted) straight into the box of the command button under On
Click for the event tab on a new button(for simplicity’s sake). I am now
getting this message(!):


Front Page can’t find macro ‘On Error Goto Err_Handler

DoCmd.’

The macro(or its macro group) doesn’t exist, or the macro is new but hasn’t
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro’s macro group was last saved
under.

It takes me straight to the code builder and the Err_Handler text when I
click any of the buttons on the Front Page form.

What a saga.

Camilla:

I either wrote my last reply with invisible ink or it was routed via the
Bermuda Triangle. I said something along these lines:

My guess is that the error occurs on the Click event of the button on the
opening form. It sounds like you might have entered code directly in the
Click event property in the control's properties sheet, not into its event
procedure. You might find the simplest solution is to delete the existing
button on the opening form and add a new one, using the control wizard to get
it to open the frmNamesDlg form. The wizard will create the code in the
click event procedure for you.

Ken Sheridan
Stafford, England

:

?

:



:

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla


:

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

:

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.

I'll assume for this example that you are looking up employees from an
Employees table and showing them in a form named frmEmployees. To retrieve
one or more employees of the selected name you'd firstly set up the combo box
with a RowSource property of:

SELECT DISTINCT Surname FROM Employees ORDER BY Surname;

The code for the combo box's AfterUpdate event procedure would be like this:

 

[Ken Sheridan]

Camilla:

I'm afraid I couldn't open your link. Can you mail me a copy of your file,
with empty tables, in Access 2002 or earlier format, to:

kenwsheridan<at>yahoo<dot>co<dot>uk

What you are trying to do should be a piece of cake, so I should be able to
fix it easily.

Ken Sheridan
Stafford, England

I did that Ken.

I have posteed a screen shot up here:

http://i-suck-using-access.blogspot.com/

It is a little blurry but may be of help :S

Camilla:

The behaviour you are getting usually indicates that code has been entered
directly into the properties sheet not into the event procedure. Are you
sure you've done as follows?

Select the button in form design view and open its properties sheet if its
not already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

No worries. I heard a rumour that a good possible location for a data cloud
server was Bermuda. I don’t think it is true though and probably originated
as a joke :S

Anyhow – have entered the code from point 1.5 into the Code Builder (copy
and pasted it all) of AfterUpdate event for the combobox, and the code from
2.3 (copied and pasted) straight into the box of the command button under On
Click for the event tab on a new button(for simplicity’s sake). I am now
getting this message(!):


Front Page can’t find macro ‘On Error Goto Err_Handler

DoCmd.’

The macro(or its macro group) doesn’t exist, or the macro is new but hasn’t
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro’s macro group was last saved
under.

It takes me straight to the code builder and the Err_Handler text when I
click any of the buttons on the Front Page form.

What a saga.


:

Camilla:

I either wrote my last reply with invisible ink or it was routed via the
Bermuda Triangle. I said something along these lines:

My guess is that the error occurs on the Click event of the button on the
opening form. It sounds like you might have entered code directly in the
Click event property in the control's properties sheet, not into its event
procedure. You might find the simplest solution is to delete the existing
button on the opening form and add a new one, using the control wizard to get
it to open the frmNamesDlg form. The wizard will create the code in the
click event procedure for you.

Ken Sheridan
Stafford, England

:

?

:



:

Alas there's the following message box:

"
Front Page can't find the macro 'frmNamesDlg.'

The macro(or its macro group) doesn't exist, or the macro is new but hasn't
been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument,
you must specify the name the macro's macro group was last saved under
"

The RowSource for the Combobox reads thus:
"
SELECT DISTINCT Surname FROM People3 ORDER BY Surname;
"

The Code Builder reads thus:
"
Private Sub cboNames_AfterUpdate()

End Sub

Private Sub Form_AfterUpdate()
On Error GoTo Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.Name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

End Sub
"

I Copy/Pasted exactly what you wrote so there shouldn't be any typos and I
checked the spacing at the start and end of everything I typed.

Any ideas?
Your help is very much appreciated
Camilla


:

Right, fasten your seatbelt!

1. Create the dialogue form as follows:

1.1 Create a new form in design view in the usual way.

1.2 Add a combo box to the form.

1.3 In the combo box's properties sheet change its Name property to cboNames.

1.4. Make sure the combo box's RowSourceType property is Table/Query; this
is the default, so it should already be that.

1.4 As its RowSource property enter:

SELECT DISTINCT Surname FROM People3 ORDER BY Surname;

1.5. In its AfterUpdate event procedure add the following code:

On Error Goto Err_Handler

Dim strCriteria As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

' first make sure a name has been selected
If Not IsNull(ctrl) Then
' open form filtered to selected surname
strCriteria = "Surname = """ & ctrl & """"
DoCmd.OpenForm "[Basic Profile]", WhereCondition:=strCriteria
' close this form
DoCmd.Close acForm, Me.name
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

To enter the code select the combo box in form design view and open its
properties sheet if its not already open. Then select the After Update event
property in the properties sheet. Click on the 'build' button; that's the
one on the right with 3 dots. Select 'Code Builder' in the dialogue, and
click OK. The VBA window will open at the event procedure with the first and
last lines already in place. Enter the above lines of code between these two
existing lines.

1.6 Change the properties of the new form so it doesn't show record
selectors, navigation buttons and anything else appropriate to a bound form.

1. 7 Save the new form as frmNamesDlg.

2. Open the Front page form in design view.

2.1 Select your existing command button, or add a new one.

2.2 If it’s the existing button open its Click event procedure in the same
way as described above (the event property this time is On Click in the
properties sheet) and where you see the name of the Basic Profile form in the
code change it to frmNamesDlg.

2.3 If it’s a new button open its Click event procedure and add the
following code:

On Error Goto Err_Handler

DoCmd.OpenForm "frmNamesDlg"

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

2.4 Save the Front Page form.

That's it! Provided that you've entered the code *exactly* as I've posted
it above (and assuming I've not made any typos!), when you click the button
on the Front page form what should now happen is that the dialogue form
opens. Once you select a name from the combo box's list the Basic Profile
form should open filtered to the record(s) with the surname you selected in
the combo box.

Let me know how you get on.

Ken Sheridan
Stafford, England

:

Okay,

The opening form is called "Front Page" I need a command button on it to
"Basic Profile". This is based on the table "People3" and the search criteria
needs to be "Surname"

Thankyou!
C

:

In my example Employees is the table name, frmNamesDlg is the name of the new
form you create.

Post back with the *exact* names of your table, the surname field and the
form to which the table us bound, i.e. the current form which shows the
records from the table. Then I'll be able to give you chapter and verse on
what to do.

Ken Sheridan
Stafford, England

:

I can tell this is helpful but a little too technical for me - is this what
you mean:

I create a new form divourced from my Front Page, put a combobox on it, go
to Properties, in RowSource I write

SELECT DISTINCT Surname FROM Employees ORDER BY Surname

(with the correct fields - though would Employees be the table they are all
listed in or the form that will show?)

I'm afraid that is all I understand from your post!

I don't know what >frmNamesDlg means, where I'd type or select it or how to
get to a place where I can enter >Dim strCriteria As String.....

I can tell you are being helpful even though I don't understand it and I do
appreciate it :S

Thanks
C

:

Firstly stick with your custom opening form. A switchboard form is a cheap
and cheerful way of creating an opening form via the built in wizardry, but
it does impose a degree of uniformity, while hand-crafting you own allows you
greater flexibility and to design it to your own 'house style'.

What you need to do is to create an unbound dialogue form which you'll open
from your current opening form, and from which the bound form can then be
opened after selecting the Surname. The selection can be made from a combo
box's drop down list rather than the user having to type it in; that way
typos are avoided. A user can still type the name into the combo box, in
which case it will go to the first match as they type each character.

So, first create your unbound dialogue form, frmNamesDlg say, and add a
combo box, cboNames say. Its not clear from your post whether you then want
to open the bound form at a specific person or to filter it to one or more
people with the same surname, so I'll deal with both possibilities. In
either case the code goes in the combo box's AfterUpdate event procedure.