Problem with a Combo box

M

maheshchandra

Hi

I have a form linked to a subform. On the main form I put a Combo box
with 3 columns where I select Client ID, Client First name,Client Last
name and upon selecting will show Client's parent information in the
subform. Evrything works perfect except that when I open the form, the
combo box is empty and the subform i.e Client parent shows information
about the first Client.

How do I make the subform as blank.

I want the whole form to be blank when I open it and only when I select
a value from the combo box , it should pop the values related to that
particular value.

Thanks
 
K

Ken Snell \(MVP\)

What is the RecordSource of the subform's form? Is it using the combo box as
a value in the WHERE clause? Or is it returning all records fromt a
table/query, and then you use the LinkMasterFields and LinkChildFields
properties to synch to the combo box's selection?

What code do you run in the combo box's AfterUpdate event?
 
M

maheshchandra

Dear Ken

I have a combo box for the main form i.e Client ID which has the
following properties:

Row Source Type: Table / Query
Row Source: SELECT Client.ClientID, Client.ClientFName,
Client.ClientLName FROM Client;
After Update: [Event Procedure]

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = '" & Me![Combo31] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

For the subform which is Client Parent Information, has the following
properties

Link Child Fields: ClientID
Link Master Fields: ClientID

Everything works perfect. When I select a Client ID from the combo box
in the main form, it shows the parent information for that particular
client.

But when I open the form, Client ID combo box is empty, and the subform
has the records for the first client ( even though the Combo box field
is empty).

I want both the combo box and subform to be empty.

Appreciate your time and work.

Thanks

Mahesh
 
K

Ken Snell \(MVP\)

Subforms load before the main form in ACCESS, so your subform is loading
before the main form.

Appears that you are using the combo box to move to a desired record in the
main form, but I'm guessing that the main form's recordsource is a query
returning all possible records. If this is true, the main form will be at
the "first" record, and the subform will synch to that value -- which
appears to be what you see.

Assuming that the main form's recordsource is such a query, let's add a
WHERE clause to it so that it's filtered by the combo box's selection. Add
this clause to the end of the SQL statement for the main form's
recordsource:

WHERE [ClientID] = Forms!NameOfYourMainForm!Combo31


Then change your combo box's AfterUpdate event procedure to this:

Private Sub Combo31_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>






Dear Ken

I have a combo box for the main form i.e Client ID which has the
following properties:

Row Source Type: Table / Query
Row Source: SELECT Client.ClientID, Client.ClientFName,
Client.ClientLName FROM Client;
After Update: [Event Procedure]

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = '" & Me![Combo31] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

For the subform which is Client Parent Information, has the following
properties

Link Child Fields: ClientID
Link Master Fields: ClientID

Everything works perfect. When I select a Client ID from the combo box
in the main form, it shows the parent information for that particular
client.

But when I open the form, Client ID combo box is empty, and the subform
has the records for the first client ( even though the Combo box field
is empty).

I want both the combo box and subform to be empty.

Appreciate your time and work.

Thanks

Mahesh


What is the RecordSource of the subform's form? Is it using the combo box
as
a value in the WHERE clause? Or is it returning all records fromt a
table/query, and then you use the LinkMasterFields and LinkChildFields
properties to synch to the combo box's selection?

What code do you run in the combo box's AfterUpdate event?
Click to expand...
Click to expand...
 
M

maheshchandra

Hi Ken

My main form record source is a table.

Thanks
Mahesh
Subforms load before the main form in ACCESS, so your subform is loading
before the main form.

Appears that you are using the combo box to move to a desired record in the
main form, but I'm guessing that the main form's recordsource is a query
returning all possible records. If this is true, the main form will be at
the "first" record, and the subform will synch to that value -- which
appears to be what you see.

Assuming that the main form's recordsource is such a query, let's add a
WHERE clause to it so that it's filtered by the combo box's selection. Add
this clause to the end of the SQL statement for the main form's
recordsource:

WHERE [ClientID] = Forms!NameOfYourMainForm!Combo31


Then change your combo box's AfterUpdate event procedure to this:

Private Sub Combo31_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>






Dear Ken

I have a combo box for the main form i.e Client ID which has the
following properties:

Row Source Type: Table / Query
Row Source: SELECT Client.ClientID, Client.ClientFName,
Client.ClientLName FROM Client;
After Update: [Event Procedure]

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = '" & Me![Combo31] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

For the subform which is Client Parent Information, has the following
properties

Link Child Fields: ClientID
Link Master Fields: ClientID

Everything works perfect. When I select a Client ID from the combo box
in the main form, it shows the parent information for that particular
client.

But when I open the form, Client ID combo box is empty, and the subform
has the records for the first client ( even though the Combo box field
is empty).

I want both the combo box and subform to be empty.

Appreciate your time and work.

Thanks

Mahesh


What is the RecordSource of the subform's form? Is it using the combo box
as
a value in the WHERE clause? Or is it returning all records fromt a
table/query, and then you use the LinkMasterFields and LinkChildFields
properties to synch to the combo box's selection?

What code do you run in the combo box's AfterUpdate event?

--

Ken Snell
<MS ACCESS MVP>

Hi

I have a form linked to a subform. On the main form I put a Combo box
with 3 columns where I select Client ID, Client First name,Client Last
name and upon selecting will show Client's parent information in the
subform. Evrything works perfect except that when I open the form, the
combo box is empty and the subform i.e Client parent shows information
about the first Client.

How do I make the subform as blank.

I want the whole form to be blank when I open it and only when I select
a value from the combo box , it should pop the values related to that
particular value.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Create a query that is based on that table, and add the WHERE criterion that
I show. Then use that query as the RecordSource of the main form.

--

Ken Snell
<MS ACCESS MVP>

Hi Ken

My main form record source is a table.

Thanks
Mahesh
Subforms load before the main form in ACCESS, so your subform is loading
before the main form.

Appears that you are using the combo box to move to a desired record in
the
main form, but I'm guessing that the main form's recordsource is a query
returning all possible records. If this is true, the main form will be at
the "first" record, and the subform will synch to that value -- which
appears to be what you see.

Assuming that the main form's recordsource is such a query, let's add a
WHERE clause to it so that it's filtered by the combo box's selection.
Add
this clause to the end of the SQL statement for the main form's
recordsource:

WHERE [ClientID] = Forms!NameOfYourMainForm!Combo31


Then change your combo box's AfterUpdate event procedure to this:

Private Sub Combo31_AfterUpdate()
Me.Requery
End Sub

--

Ken Snell
<MS ACCESS MVP>






Dear Ken

I have a combo box for the main form i.e Client ID which has the
following properties:

Row Source Type: Table / Query
Row Source: SELECT Client.ClientID, Client.ClientFName,
Client.ClientLName FROM Client;
After Update: [Event Procedure]

Private Sub Combo31_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = '" & Me![Combo31] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

For the subform which is Client Parent Information, has the following
properties

Link Child Fields: ClientID
Link Master Fields: ClientID

Everything works perfect. When I select a Client ID from the combo box
in the main form, it shows the parent information for that particular
client.

But when I open the form, Client ID combo box is empty, and the subform
has the records for the first client ( even though the Combo box field
is empty).

I want both the combo box and subform to be empty.

Appreciate your time and work.

Thanks

Mahesh



Ken Snell (MVP) wrote:
What is the RecordSource of the subform's form? Is it using the combo
box
as
a value in the WHERE clause? Or is it returning all records fromt a
table/query, and then you use the LinkMasterFields and LinkChildFields
properties to synch to the combo box's selection?

What code do you run in the combo box's AfterUpdate event?

--

Ken Snell
<MS ACCESS MVP>

Hi

I have a form linked to a subform. On the main form I put a Combo
box
with 3 columns where I select Client ID, Client First name,Client
Last
name and upon selecting will show Client's parent information in the
subform. Evrything works perfect except that when I open the form,
the
combo box is empty and the subform i.e Client parent shows
information
about the first Client.

How do I make the subform as blank.

I want the whole form to be blank when I open it and only when I
select
a value from the combo box , it should pop the values related to
that
particular value.

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

Ask a Question

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

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

Ask a Question

Similar Threads

How 2 Set The Parameter??? 1
unbound combo box shows no value on open 2
Open main and sub form blank 1
Calculated control/combo box 1
Sync Main Form with Subform after Combo Selection 2
Recalc Combo Box - F9 2
Combo Box Problems 2
Requery subform control to refresh list based on combo box value change 1

Top