Combo box / Subform Question

[James C.]

I have a combo box that displays two columns from the main data table that it
queries

EmpID & EmpName (I have it showing both because not everyone knows the ID)

When the selection is made I have it update the Subform below with all the
Emp Details. My problem is that it will work when I only have the combo box
show the EmpID but when I have it show both it no longer works. It obviously
doesn't work because the combo box result no longer equals the field in my
table. Can someone help me figure out a way to do this. Thanks in advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Jeff Boyce]

James

I'm not sure I'm following ... "when I have it show both" ... a combobox
will display only one selection, unless you mean while you have it dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That seems
like an extra step, when you could have the main form display the record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[James C.]

Hi Jeff,

Sorry for the confusion. First the combo box and record detail are all on
the same form. Second what I mean by "when I have it show both" is this

When you click on the control and go into properties you can have it show
two columns from my table. The combo box would show something like this

-- 221723 | Bob Smith

Below this combo box is my bound form which has the following fields

EmpID
EmpName
Salary
StartDate
EndDate
etc.

The code that I use says to take me to the first record where the EmpID &
Combo Box result is the same. Again, if I have the combo box return only the
EmpID field this works perfect but when I have it return both the EmpID & the
EmpName it doesn't

James

I'm not sure I'm following ... "when I have it show both" ... a combobox
will display only one selection, unless you mean while you have it dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That seems
like an extra step, when you could have the main form display the record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a combo box that displays two columns from the main data table that
it
queries

EmpID & EmpName (I have it showing both because not everyone knows the ID)

When the selection is made I have it update the Subform below with all the
Emp Details. My problem is that it will work when I only have the combo
box
show the EmpID but when I have it show both it no longer works. It
obviously
doesn't work because the combo box result no longer equals the field in my
table. Can someone help me figure out a way to do this. Thanks in advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Jeff Boyce]

James

Sorry, still a little sub-caffeinated this morning. I believe comboboxes
can only show a single field at a time. When I drop down a combobox, it can
(temporarily) display as many fields as I've told it have Width > 0.

What is the SQL statement you use to 'feed' the combobox?

What is the SQL statement you use to 'feed' the form?

By the way, that expression you provided tells Access to look for an [EmpID]
that is equal to the "TEXT" version of what's in [EmpCombo]. I assume that
the "bound" field in your combobox is a long integer. Those two don't match
up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Sorry for the confusion. First the combo box and record detail are all on
the same form. Second what I mean by "when I have it show both" is this

When you click on the control and go into properties you can have it show
two columns from my table. The combo box would show something like this

-- 221723 | Bob Smith

Below this combo box is my bound form which has the following fields

EmpID
EmpName
Salary
StartDate
EndDate
etc.

The code that I use says to take me to the first record where the EmpID &
Combo Box result is the same. Again, if I have the combo box return only
the
EmpID field this works perfect but when I have it return both the EmpID &
the
EmpName it doesn't

James

I'm not sure I'm following ... "when I have it show both" ... a combobox
will display only one selection, unless you mean while you have it
dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That seems
like an extra step, when you could have the main form display the record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a combo box that displays two columns from the main data table
that
it
queries

EmpID & EmpName (I have it showing both because not everyone knows the
ID)

When the selection is made I have it update the Subform below with all
the
Emp Details. My problem is that it will work when I only have the combo
box
show the EmpID but when I have it show both it no longer works. It
obviously
doesn't work because the combo box result no longer equals the field in
my
table. Can someone help me figure out a way to do this. Thanks in
advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[James C.]

For the combo box:

Under Data Tab in Properties-
Row Source Type = Table/Query
Row Source =
SELECT [01 Contractor_Data].[EmpID], [01 Contractor_Data].[EmpName] FROM [01
Contractor_Data] ORDER BY [EmpID];

Under Format Tab in Properties-
Column Count = 2 (this is what expands the combo box to show both columns)

To feed the form I use the following (I think this may be the problem, I
must be linking my combo box to my bound form incorrectly)
Event Procedure in AfterUpdate

Private Sub EmpCombo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

James

Sorry, still a little sub-caffeinated this morning. I believe comboboxes
can only show a single field at a time. When I drop down a combobox, it can
(temporarily) display as many fields as I've told it have Width > 0.

What is the SQL statement you use to 'feed' the combobox?

What is the SQL statement you use to 'feed' the form?

By the way, that expression you provided tells Access to look for an [EmpID]
that is equal to the "TEXT" version of what's in [EmpCombo]. I assume that
the "bound" field in your combobox is a long integer. Those two don't match
up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Sorry for the confusion. First the combo box and record detail are all on
the same form. Second what I mean by "when I have it show both" is this

When you click on the control and go into properties you can have it show
two columns from my table. The combo box would show something like this

-- 221723 | Bob Smith

Below this combo box is my bound form which has the following fields

EmpID
EmpName
Salary
StartDate
EndDate
etc.

The code that I use says to take me to the first record where the EmpID &
Combo Box result is the same. Again, if I have the combo box return only
the
EmpID field this works perfect but when I have it return both the EmpID &
the
EmpName it doesn't

James

I'm not sure I'm following ... "when I have it show both" ... a combobox
will display only one selection, unless you mean while you have it
dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That seems
like an extra step, when you could have the main form display the record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a combo box that displays two columns from the main data table
that
it
queries

EmpID & EmpName (I have it showing both because not everyone knows the
ID)

When the selection is made I have it update the Subform below with all
the
Emp Details. My problem is that it will work when I only have the combo
box
show the EmpID but when I have it show both it no longer works. It
obviously
doesn't work because the combo box result no longer equals the field in
my
table. Can someone help me figure out a way to do this. Thanks in
advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Jeff Boyce]

James

Perhaps another newsgroup reader with experience using the .Recordset.Clone
can assist.

I've always used a parameterized query that looks at the form's combobox
value to find the correct record to display.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

For the combo box:

Under Data Tab in Properties-
Row Source Type = Table/Query
Row Source =
SELECT [01 Contractor_Data].[EmpID], [01 Contractor_Data].[EmpName] FROM
[01
Contractor_Data] ORDER BY [EmpID];

Under Format Tab in Properties-
Column Count = 2 (this is what expands the combo box to show both columns)

To feed the form I use the following (I think this may be the problem, I
must be linking my combo box to my bound form incorrectly)
Event Procedure in AfterUpdate

Private Sub EmpCombo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

James

Sorry, still a little sub-caffeinated this morning. I believe comboboxes
can only show a single field at a time. When I drop down a combobox, it
can
(temporarily) display as many fields as I've told it have Width > 0.

What is the SQL statement you use to 'feed' the combobox?

What is the SQL statement you use to 'feed' the form?

By the way, that expression you provided tells Access to look for an
[EmpID]
that is equal to the "TEXT" version of what's in [EmpCombo]. I assume
that
the "bound" field in your combobox is a long integer. Those two don't
match
up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Sorry for the confusion. First the combo box and record detail are all
on
the same form. Second what I mean by "when I have it show both" is this

When you click on the control and go into properties you can have it
show
two columns from my table. The combo box would show something like this

-- 221723 | Bob Smith

Below this combo box is my bound form which has the following fields

EmpID
EmpName
Salary
StartDate
EndDate
etc.

The code that I use says to take me to the first record where the EmpID
&
Combo Box result is the same. Again, if I have the combo box return
only
the
EmpID field this works perfect but when I have it return both the EmpID
&
the
EmpName it doesn't

:

James

I'm not sure I'm following ... "when I have it show both" ... a
combobox
will display only one selection, unless you mean while you have it
dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That
seems
like an extra step, when you could have the main form display the
record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a combo box that displays two columns from the main data table
that
it
queries

EmpID & EmpName (I have it showing both because not everyone knows
the
ID)

When the selection is made I have it update the Subform below with
all
the
Emp Details. My problem is that it will work when I only have the
combo
box
show the EmpID but when I have it show both it no longer works. It
obviously
doesn't work because the combo box result no longer equals the field
in
my
table. Can someone help me figure out a way to do this. Thanks in
advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[James C.]

Jeff,

By no means am i stuck on the .Recordset.Clone method. How do you use a
paremeter query to accomplish your task? I am assuming you set the criteria
in the paremeter query to your combo box (ie. Forms!FormName!ComboBoxName)

What code would you then put in your AfterUpdate event to get it to update
your form?

Thanks again for the help.

James

Perhaps another newsgroup reader with experience using the .Recordset.Clone
can assist.

I've always used a parameterized query that looks at the form's combobox
value to find the correct record to display.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

For the combo box:

Under Data Tab in Properties-
Row Source Type = Table/Query
Row Source =
SELECT [01 Contractor_Data].[EmpID], [01 Contractor_Data].[EmpName] FROM
[01
Contractor_Data] ORDER BY [EmpID];

Under Format Tab in Properties-
Column Count = 2 (this is what expands the combo box to show both columns)

To feed the form I use the following (I think this may be the problem, I
must be linking my combo box to my bound form incorrectly)
Event Procedure in AfterUpdate

Private Sub EmpCombo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

James

Sorry, still a little sub-caffeinated this morning. I believe comboboxes
can only show a single field at a time. When I drop down a combobox, it
can
(temporarily) display as many fields as I've told it have Width > 0.

What is the SQL statement you use to 'feed' the combobox?

What is the SQL statement you use to 'feed' the form?

By the way, that expression you provided tells Access to look for an
[EmpID]
that is equal to the "TEXT" version of what's in [EmpCombo]. I assume
that
the "bound" field in your combobox is a long integer. Those two don't
match
up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Sorry for the confusion. First the combo box and record detail are all
on
the same form. Second what I mean by "when I have it show both" is this

When you click on the control and go into properties you can have it
show
two columns from my table. The combo box would show something like this

-- 221723 | Bob Smith

Below this combo box is my bound form which has the following fields

EmpID
EmpName
Salary
StartDate
EndDate
etc.

The code that I use says to take me to the first record where the EmpID
&
Combo Box result is the same. Again, if I have the combo box return
only
the
EmpID field this works perfect but when I have it return both the EmpID
&
the
EmpName it doesn't

:

James

I'm not sure I'm following ... "when I have it show both" ... a
combobox
will display only one selection, unless you mean while you have it
dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That
seems
like an extra step, when you could have the main form display the
record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a combo box that displays two columns from the main data table
that
it
queries

EmpID & EmpName (I have it showing both because not everyone knows
the
ID)

When the selection is made I have it update the Subform below with
all
the
Emp Details. My problem is that it will work when I only have the
combo
box
show the EmpID but when I have it show both it no longer works. It
obviously
doesn't work because the combo box result no longer equals the field
in
my
table. Can someone help me figure out a way to do this. Thanks in
advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Jeff Boyce]

James

In my combobox's AfterUpdate event I add:

Me.Requery

When the form first opens, no record is displayed because the query that
feeds the form looks at the combobox, sees nothing and gets "that" record
for display. After the combobox selection is made, the AfterUpdate requery
triggers, the query sees the selected record and that one gets displayed on
the form.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

By no means am i stuck on the .Recordset.Clone method. How do you use a
paremeter query to accomplish your task? I am assuming you set the
criteria
in the paremeter query to your combo box (ie. Forms!FormName!ComboBoxName)

What code would you then put in your AfterUpdate event to get it to update
your form?

Thanks again for the help.

James

Perhaps another newsgroup reader with experience using the
.Recordset.Clone
can assist.

I've always used a parameterized query that looks at the form's combobox
value to find the correct record to display.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

For the combo box:

Under Data Tab in Properties-
Row Source Type = Table/Query
Row Source =
SELECT [01 Contractor_Data].[EmpID], [01 Contractor_Data].[EmpName]
FROM
[01
Contractor_Data] ORDER BY [EmpID];

Under Format Tab in Properties-
Column Count = 2 (this is what expands the combo box to show both
columns)

To feed the form I use the following (I think this may be the problem,
I
must be linking my combo box to my bound form incorrectly)
Event Procedure in AfterUpdate

Private Sub EmpCombo_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub


:

James

Sorry, still a little sub-caffeinated this morning. I believe
comboboxes
can only show a single field at a time. When I drop down a combobox,
it
can
(temporarily) display as many fields as I've told it have Width > 0.

What is the SQL statement you use to 'feed' the combobox?

What is the SQL statement you use to 'feed' the form?

By the way, that expression you provided tells Access to look for an
[EmpID]
that is equal to the "TEXT" version of what's in [EmpCombo]. I assume
that
the "bound" field in your combobox is a long integer. Those two don't
match
up.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Sorry for the confusion. First the combo box and record detail are
all
on
the same form. Second what I mean by "when I have it show both" is
this

When you click on the control and go into properties you can have it
show
two columns from my table. The combo box would show something like
this

-- 221723 | Bob Smith

Below this combo box is my bound form which has the following fields

EmpID
EmpName
Salary
StartDate
EndDate
etc.

The code that I use says to take me to the first record where the
EmpID
&
Combo Box result is the same. Again, if I have the combo box return
only
the
EmpID field this works perfect but when I have it return both the
EmpID
&
the
EmpName it doesn't

:

James

I'm not sure I'm following ... "when I have it show both" ... a
combobox
will display only one selection, unless you mean while you have it
dropped
down. Which field (ID or Name) is the "bound" field?

By the way, why use a subform to display the record selected? That
seems
like an extra step, when you could have the main form display the
record
selected.

Regards

Jeff Boyce
Microsoft Office/Access MVP


I have a combo box that displays two columns from the main data
table
that
it
queries

EmpID & EmpName (I have it showing both because not everyone
knows
the
ID)

When the selection is made I have it update the Subform below
with
all
the
Emp Details. My problem is that it will work when I only have the
combo
box
show the EmpID but when I have it show both it no longer works.
It
obviously
doesn't work because the combo box result no longer equals the
field
in
my
table. Can someone help me figure out a way to do this. Thanks in
advance

Here is the code that I use:
Private Sub EmpCombo_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmpID]= '" & Me![EmpCombo] & "'"
Me.Bookmark = rs.Bookmark
End Sub