Combo Box Query

  • Thread starter Thread starter Rob W
  • Start date Start date
R

Rob W

Greetings,

I have a combo box on a form based on a table with the following fields:-

ID
Name

I want the user to select via name, using the ID column as hidden.
Though Its Important I store the ID column somewhere as It needs to be
passed into a query that builds a subform.

Ive been looking at bound to try to the bound it to the ID but display the
Name only in the combo box, without any luck.

Does anyone have any suggestions please?

Thanks
Rob
 
Hi Rob,
I want the user to select via name, using the ID column as hidden.
Though Its Important I store the ID column somewhere as It needs to be
passed into a query that builds a subform.
Click to expand...

The Row Source for your combo box should look something like this:

SELECT ID, [Name] FROM MyTable ORDER BY [Name]

The combo box should have the following properties:

On Format tab
Column Count: 2
Column Widths: 0";1.5"
List Rows: 20

On Data tab
Row Source Type: Table/Query
Row Source: See above (or use a saved query)
Control Source: Specify the appropriate foreign key field to
receive the EmployeeID
Bound Column: 1


I encourage you to avoid the use of any reserved words for things that you
assign a name to in Access. It turns out that "Name" is a reserved word.
Access MVP Allen Browne has an extensive listing of reserved words, here:

http://allenbrowne.com/AppIssueBadWord.html

He also offers an application, written in Access, which you can download to
check your existing databases for the use of reserved words (and a host of
other issues as well). See the link for the "Database Issue Checker Utility"
on the above page.

Also, I would use a more descriptive field name in place of just "ID".



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
Thanks for the reply.

Those werent my actual field names, I just renamed them to make my problem
easier to read.

I now have the row source query to select TWO fields :-

SELECT tblModule.ModuleId, tblModule.ModuleName
FROM tblModule
ORDER BY tblModule.ModuleName;

On the form it shows one field, however I (assume) as Im only using the
field to be used to open up my subform and DONT want it to save ANY data
I've left it unbounded.

After the combo box is updated I run this code

Private Sub cboModuleName_AfterUpdate()

Me.frmModuleResultsSubform.Enabled = True
Me.frmModuleResultsSubform.Visible = True

End Sub

Which should open up my subform, but for whatever reason it isnt.

I then have a subform which selects all existing data for the value within
my combobox, which i assume is the Module.Id and not the ModuleName?

Hope this makes sense
Rob


Tom Wickerath said:
Hi Rob,
I want the user to select via name, using the ID column as hidden.
Though Its Important I store the ID column somewhere as It needs to be
passed into a query that builds a subform.
Click to expand...

The Row Source for your combo box should look something like this:

SELECT ID, [Name] FROM MyTable ORDER BY [Name]

The combo box should have the following properties:

On Format tab
Column Count: 2
Column Widths: 0";1.5"
List Rows: 20

On Data tab
Row Source Type: Table/Query
Row Source: See above (or use a saved query)
Control Source: Specify the appropriate foreign key field to
receive the EmployeeID
Bound Column: 1


I encourage you to avoid the use of any reserved words for things that you
assign a name to in Access. It turns out that "Name" is a reserved word.
Access MVP Allen Browne has an extensive listing of reserved words, here:

http://allenbrowne.com/AppIssueBadWord.html

He also offers an application, written in Access, which you can download
to
check your existing databases for the use of reserved words (and a host of
other issues as well). See the link for the "Database Issue Checker
Utility"
on the above page.

Also, I would use a more descriptive field name in place of just "ID".



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Rob W said:
Greetings,

I have a combo box on a form based on a table with the following fields:-

ID
Name

I want the user to select via name, using the ID column as hidden.
Though Its Important I store the ID column somewhere as It needs to be
passed into a query that builds a subform.

Ive been looking at bound to try to the bound it to the ID but display
the
Name only in the combo box, without any luck.

Does anyone have any suggestions please?

Thanks
Rob
Click to expand...
Click to expand...
 
Ive now used a text box to retrieve the hidden value in the combo box

=[cboModuleName].[Column](0)

Though still having issues launching my subform.


Tom Wickerath said:
Hi Rob,
I want the user to select via name, using the ID column as hidden.
Though Its Important I store the ID column somewhere as It needs to be
passed into a query that builds a subform.
Click to expand...

The Row Source for your combo box should look something like this:

SELECT ID, [Name] FROM MyTable ORDER BY [Name]

The combo box should have the following properties:

On Format tab
Column Count: 2
Column Widths: 0";1.5"
List Rows: 20

On Data tab
Row Source Type: Table/Query
Row Source: See above (or use a saved query)
Control Source: Specify the appropriate foreign key field to
receive the EmployeeID
Bound Column: 1


I encourage you to avoid the use of any reserved words for things that you
assign a name to in Access. It turns out that "Name" is a reserved word.
Access MVP Allen Browne has an extensive listing of reserved words, here:

http://allenbrowne.com/AppIssueBadWord.html

He also offers an application, written in Access, which you can download
to
check your existing databases for the use of reserved words (and a host of
other issues as well). See the link for the "Database Issue Checker
Utility"
on the above page.

Also, I would use a more descriptive field name in place of just "ID".



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Rob W said:
Greetings,

I have a combo box on a form based on a table with the following fields:-

ID
Name

I want the user to select via name, using the ID column as hidden.
Though Its Important I store the ID column somewhere as It needs to be
passed into a query that builds a subform.

Ive been looking at bound to try to the bound it to the ID but display
the
Name only in the combo box, without any luck.

Does anyone have any suggestions please?

Thanks
Rob
Click to expand...
Click to expand...
 
Thanks for the reply.

Those werent my actual field names, I just renamed them to make my problem
easier to read.

I now have the row source query to select TWO fields :-

SELECT tblModule.ModuleId, tblModule.ModuleName
FROM tblModule
ORDER BY tblModule.ModuleName;

On the form it shows one field, however I (assume) as Im only using the
field to be used to open up my subform and DONT want it to save ANY data
I've left it unbounded.

After the combo box is updated I run this code

Private Sub cboModuleName_AfterUpdate()

Me.frmModuleResultsSubform.Enabled = True
Me.frmModuleResultsSubform.Visible = True

End Sub

Which should open up my subform, but for whatever reason it isnt.

I then have a subform which selects all existing data for the value within
my combobox, which i assume is the Module.Id and not the ModuleName?

Hope this makes sense
Rob
Click to expand...

This code doesn't "open" the subform; it just makes it visible. The Subform is
actually opened before the mainform opens, as part of the same process!

What is the Recordsource of the subform? Is it a query referencing the combo
box? If so, what's the Bound Column of the combo, and the SQL of the query?

If not, are you using the combo box's name as the Master Link Field? If not,
what are the master and child link field properties of the subform?
 
Hi Rob,
Though still having issues launching my subform.
Click to expand...

After the combo box is updated I run this code

Private Sub cboModuleName_AfterUpdate()

Me.frmModuleResultsSubform.Enabled = True
Me.frmModuleResultsSubform.Visible = True

End Sub

Which should open up my subform, but for whatever reason it isnt.
Click to expand...

Is the ModuleID available in the recordset for the subform? It almost sounds
like you have a technique similar to the TabOnDemand for a subform, but
without the tab control. This topic is discussed in more detail in my
Multi-User Applications paper, under the sub-title "Speed up Tab controls".
This article is available here:

http://www.accessmvp.com/TWickerath/

Are the Link Master Fields and Link Child Fields properties set correctly?
Take a look at Method 3 shown in this KB article:

http://support.microsoft.com/kb/209537

Does this help?
Though still having issues launching my subform.
Click to expand...

Not sure what you mean by "issues launching my subform". Can you describe
this a bit more?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Rob W said:
Ive now used a text box to retrieve the hidden value in the combo box

=[cboModuleName].[Column](0)

Though still having issues launching my subform.
Click to expand...
 
The subform is based on a query (saved as query object) i.e the
RecordSource.

The combo box isnt bound to any table fields and the sub from using the
results from the query is bound to a single table.

The combo box value is a parameter within the query, to match data to be
displayed within the subform and referenceed via
[Forms]![frmModuleResults]![txtModuleId]

In the query design manager when I run the query without the form I expect
it for me to manually type in the value of :-
[Forms]![frmModuleResults]![txtModuleId] as the form isnt open and should be
expecting a value. Instead it just return 0 records.

When running from the form, the sub form does appear after setting it to
visible within vba code on a click even of the combobox (Which populates the
txtModuleId btw!) return 0 rows and allows me to edit data.

Hope this makes things clearer.

Rob
 
The oddity is, If i remove the WHERE clause using the form control, running
the query retrieves all results.
However running the subform from the main form retrives 0 rows yet its
source is the query that retireves results if ran under the query designer.

Confusing ..


Tom Wickerath said:
Hi Rob,
Though still having issues launching my subform.
Click to expand...

After the combo box is updated I run this code

Private Sub cboModuleName_AfterUpdate()

Me.frmModuleResultsSubform.Enabled = True
Me.frmModuleResultsSubform.Visible = True

End Sub

Which should open up my subform, but for whatever reason it isnt.
Click to expand...

Is the ModuleID available in the recordset for the subform? It almost
sounds
like you have a technique similar to the TabOnDemand for a subform, but
without the tab control. This topic is discussed in more detail in my
Multi-User Applications paper, under the sub-title "Speed up Tab
controls".
This article is available here:

http://www.accessmvp.com/TWickerath/

Are the Link Master Fields and Link Child Fields properties set correctly?
Take a look at Method 3 shown in this KB article:

http://support.microsoft.com/kb/209537

Does this help?
Though still having issues launching my subform.
Click to expand...

Not sure what you mean by "issues launching my subform". Can you describe
this a bit more?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Rob W said:
Ive now used a text box to retrieve the hidden value in the combo box

=[cboModuleName].[Column](0)

Though still having issues launching my subform.
Click to expand...
Click to expand...
 
Hi Rob,

Are you able to send me a copy of your database (or just the pertinant
objects)? You can use dummy data in place of any sensitive data. If you are
interested, send me a private e-mail message, with a valid reply-to address,
with your compacted and preferably zipped file. My e-mail address is
available at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
The subform is based on a query (saved as query object) i.e the
RecordSource.

The combo box isnt bound to any table fields and the sub from using the
results from the query is bound to a single table.

The combo box value is a parameter within the query, to match data to be
displayed within the subform and referenceed via
[Forms]![frmModuleResults]![txtModuleId]

In the query design manager when I run the query without the form I expect
it for me to manually type in the value of :-
[Forms]![frmModuleResults]![txtModuleId] as the form isnt open and should be
expecting a value. Instead it just return 0 records.

When running from the form, the sub form does appear after setting it to
visible within vba code on a click even of the combobox (Which populates the
txtModuleId btw!) return 0 rows and allows me to edit data.
Click to expand...

I see Tom has offered to look at the database. If you're willing to take him
up on his offer that would be the best way to go. I can think of several
possibilities but speculation is idle given Tom's generous offer.
 
Tom Wickerath said:
Hi Rob,

Are you able to send me a copy of your database (or just the pertinant
objects)? You can use dummy data in place of any sensitive data. If you
are
interested, send me a private e-mail message, with a valid reply-to
address,
with your compacted and preferably zipped file. My e-mail address is
available at the bottom of the contributor's page indicated below.

Please do not post your e-mail address (or mine) to a newsgroup reply.
Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Click to expand...
 
Back
Top