Using a List Box to Select Data in a Subform

[Guest]

Ok first off I'm a novice, just taken a couple courses so this is probably a
newbie question.

I have a button on a form that when pressed runs a query that asks for a
zipcode and then displays the name of all the area's associated with that
zipcode in a list box. I then have a subform on the form that is for phone
numbers of the area. I want whatever is selected in the listbox to
automatically change whats in the subform to the info for the selected area.

Ex. Click Jacksonville and the telephone numbers for that area are displayed
in the subform.

This seems so simple but I've been having a heck of a time trying to find
info and know I'll be kicking myself after I hear the answer. Thanks.

 

[Allen Browne]

If you want to match the selected value in the list box to one of the fields
in your subform:

1. Open the main form in design view.

2. Right-click the edge of the subform control, and choose Properties.
The properties box opens, and its title bar indicates it is showing the
propertie of the subform control.

3. An the Data tab of the Properties box, set the LinkMasterFields property
to the name of your list box, and hte LinkChildFields property to the name
of the matching control in the subform.

 

[Guest]

That worked good thanks, I am having another problem now though.

The "selector" function of the listbox works but I had to put a textbox on
the form that I just turned the visible value for off so that it would link
properly. That is fine but whenever I open the form it wants to go to the
first record so it is overwritting the first record (usually, sometimes it
opens on others). So I add:

Private Sub Form_Load()
If Not Me.NewRecord Then
DoCmd.GoToRecord , , acLast
End If
End Sub

And add a primary key that is guarenteed to be the last one all the time and
just use it as a fake data holder that gets overwritten each time the list
box is clicked. The problem with what I tried above is that it will not
update correctly unless I click off into the subform and then click another
selection in the main form each time I want to navigate through the search's.

I need help finding a solution to this,
Thanks in advance.

 

[Allen Browne]

If you want the form to open to a new blank record, you could set its Data
Entry property to Yes, or open it in Add mode (if you OpenForm
programmatically). Alternatively, you could move to the new record with:
Private Sub Form_Load()
If Not Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
End Sub
That will not overwrite the last record.

I'm not clear how/why/when you are overwriting values in the last record, or
attempting to add a new record, but in general you don't want to be
adding/modifying fields unless you really want the record. The source of the
problem might be programmatically modifying the values when you don't want
them modified.

If the list box is just for getting a match in the subform, it might be best
to use an unbound list box?

 

[Guest]

This one is fairly lengthy, includes many details about my project.

When I make the listbox unbound it doesn't link properly, the going to new
field won't work either because it wants to create a new record every time.

The database I have is only a few forms/tables page's so I'll put the jist
of it up here, its overall goal is to search for what police centers dispatch
for a searched for zipcode, display the results in a listbox, then view
details about whatever center is selected:

Table Name: tblInfo
Fields: ID(PK), Name, State, PolicePrimary, PoliceSecondary, FirePrimary

Table Name: tblZipCode (No PK)
Fields: ID, ZipCode

The two ID's are linked one to many with cascade update on, I will have
multiple Zipcodes for each ID

Query Name: qryZipCode (select query)
Has the Name field from Info (visible) and the Zipcode field from Zipcode
(doesn't show), I do a [enter zipcode] in the critera field.

Form Name: frmMain
Record Source is tblInfo, there is a button that runs qryZipCode and places
the results in a listbox on the form. Code here: (mixed and matched through
experimenting sure its got unneeded stuff in it probably)

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "qryZipCode"
ListBox.RowSource = stDocName

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub

Form Name: Info
Record Source is tblInfo and its linked to the frmMain through the Name
field. Ok, now whatever is in the name textbox I placed so that the "click
navigate" feature would work (located on frmMain) will have its info show up
in the subform. Thats the result I want, thats all I'm trying to do with this
database, the problem with the name change is all thats holding it back.

So say I have the command to go to a new record in the load command, what
will happen is whatever is located in the textbox that is on tblInfo so that
the linking will work will want to write to a new record upon closing (it
won't let it because there is no primary key for it)

Now if I have my go to last record feature loaded in the form it will go to
the last record "zzzzz" upon loading and I figured I could just have "zzzzz"
take the name change over and over. The only problem is it wants to go to the
last record to much, I have to click around a couple times to lose/gain focus
in the right order in order to view information about a name if it was the
name that "zzzzz" took.

If I try to switch the listbox to unbound it doesn't see anything to link
with in the subform and it won't navigate the details by clicking in the
listbox.

Thanks for the help.

 

[Allen Browne]

Michael, there's a raft of issues here.

Firstly, if you have a field named "Name", Access is very likely to get
confused. Most objects in Access have a Name property, inclusing forms. When
you try to refer to the Name field in the form, it will probably think you
mean the name of the form. Best to change this to PersonName, or ClientName
or something that is not ambiguous.

Next, it is unclear what the purpose of the list box is. If it's for
displaying the zip codes applicable to the ID in the main form, the it would
make sense to use an unbound list box. If it is to store the actualy zip
code for the record in the main form, then it would make sense to use a
bound list box. Either way, if there is no record selected in the main form
(e.g. when you go to a new record), surely the list box should not show
anything.

So, what is the goal here? You've described what you are doing, but I don't
understand what you are trying to achieve. We started out with telephone
number area codes, and now we are talking zip codes. For display? To select
a code and store?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This one is fairly lengthy, includes many details about my project.

When I make the listbox unbound it doesn't link properly, the going to new
field won't work either because it wants to create a new record every
time.

The database I have is only a few forms/tables page's so I'll put the jist
of it up here, its overall goal is to search for what police centers
dispatch
for a searched for zipcode, display the results in a listbox, then view
details about whatever center is selected:

Table Name: tblInfo
Fields: ID(PK), Name, State, PolicePrimary, PoliceSecondary, FirePrimary

Table Name: tblZipCode (No PK)
Fields: ID, ZipCode

The two ID's are linked one to many with cascade update on, I will have
multiple Zipcodes for each ID

Query Name: qryZipCode (select query)
Has the Name field from Info (visible) and the Zipcode field from Zipcode
(doesn't show), I do a [enter zipcode] in the critera field.

Form Name: frmMain
Record Source is tblInfo, there is a button that runs qryZipCode and
places
the results in a listbox on the form. Code here: (mixed and matched
through
experimenting sure its got unneeded stuff in it probably)

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "qryZipCode"
ListBox.RowSource = stDocName

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub

Form Name: Info
Record Source is tblInfo and its linked to the frmMain through the Name
field. Ok, now whatever is in the name textbox I placed so that the "click
navigate" feature would work (located on frmMain) will have its info show
up
in the subform. Thats the result I want, thats all I'm trying to do with
this
database, the problem with the name change is all thats holding it back.

So say I have the command to go to a new record in the load command, what
will happen is whatever is located in the textbox that is on tblInfo so
that
the linking will work will want to write to a new record upon closing (it
won't let it because there is no primary key for it)

Now if I have my go to last record feature loaded in the form it will go
to
the last record "zzzzz" upon loading and I figured I could just have
"zzzzz"
take the name change over and over. The only problem is it wants to go to
the
last record to much, I have to click around a couple times to lose/gain
focus
in the right order in order to view information about a name if it was the
name that "zzzzz" took.

If I try to switch the listbox to unbound it doesn't see anything to link
with in the subform and it won't navigate the details by clicking in the
listbox.

Thanks for the help.

 

[Guest]

The name field could have been a few issues I was having and wondering why
stuff wasn't working, (I'm not able to get to my stuff until tommarow so
can't check til then)

The purpose of the list box is to display the actual name of the dispatch
center that the zip code is searched for, for example, 11111 is entered, all
areas that cover the 11111 zip code will be listed in the list box. The
listbox will then be used as the form of navigation for the subform.

Lets say I have this data in tblInfo:
ID Name
AAA ------Anaheim County
HAM ------Hamilton County
JKS -------Jackson City

And this info is in tblZipCode
ID ZipCode
AAA--------11111
AAA--------22222
HAM--------11111
HAM--------33333
JKS---------22222
JKS---------44444

The user enters 11111 for the Search:

List Box Displays:
Anaheim County
Hamilton County

If Anaheim County is clicked in the list box, the subform displays the
information for Anaheim County.

This is the only form of navigation I want, I've turned off all the record
navigators, in the main and sub form.

The goal is this:
When I get a new customer, all I do is input the zipcode they are located in
into the message box, every center that covers the zipcode is then populated
into the listbox by the name of the center, I can then click on the names in
the listbox and have the information quickly and easily displayed so I can
quickly choose the proper center.

No data will ever be altered from the form, nor will the user ever be able
to get to it (I'm just going to turn off everything in the startup options
and if they get anything new shift click to get in and manually input the
data myself, which is secure enough for what I'm doing.).

Thank you again for taking the time to continue helping, your a great help.

Michael, there's a raft of issues here.

Firstly, if you have a field named "Name", Access is very likely to get
confused. Most objects in Access have a Name property, inclusing forms. When
you try to refer to the Name field in the form, it will probably think you
mean the name of the form. Best to change this to PersonName, or ClientName
or something that is not ambiguous.

Next, it is unclear what the purpose of the list box is. If it's for
displaying the zip codes applicable to the ID in the main form, the it would
make sense to use an unbound list box. If it is to store the actualy zip
code for the record in the main form, then it would make sense to use a
bound list box. Either way, if there is no record selected in the main form
(e.g. when you go to a new record), surely the list box should not show
anything.

So, what is the goal here? You've described what you are doing, but I don't
understand what you are trying to achieve. We started out with telephone
number area codes, and now we are talking zip codes. For display? To select
a code and store?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This one is fairly lengthy, includes many details about my project.

When I make the listbox unbound it doesn't link properly, the going to new
field won't work either because it wants to create a new record every
time.

The database I have is only a few forms/tables page's so I'll put the jist
of it up here, its overall goal is to search for what police centers
dispatch
for a searched for zipcode, display the results in a listbox, then view
details about whatever center is selected:

Table Name: tblInfo
Fields: ID(PK), Name, State, PolicePrimary, PoliceSecondary, FirePrimary

Table Name: tblZipCode (No PK)
Fields: ID, ZipCode

The two ID's are linked one to many with cascade update on, I will have
multiple Zipcodes for each ID

Query Name: qryZipCode (select query)
Has the Name field from Info (visible) and the Zipcode field from Zipcode
(doesn't show), I do a [enter zipcode] in the critera field.

Form Name: frmMain
Record Source is tblInfo, there is a button that runs qryZipCode and
places
the results in a listbox on the form. Code here: (mixed and matched
through
experimenting sure its got unneeded stuff in it probably)

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "qryZipCode"
ListBox.RowSource = stDocName

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub

Form Name: Info
Record Source is tblInfo and its linked to the frmMain through the Name
field. Ok, now whatever is in the name textbox I placed so that the "click
navigate" feature would work (located on frmMain) will have its info show
up
in the subform. Thats the result I want, thats all I'm trying to do with
this
database, the problem with the name change is all thats holding it back.

So say I have the command to go to a new record in the load command, what
will happen is whatever is located in the textbox that is on tblInfo so
that
the linking will work will want to write to a new record upon closing (it
won't let it because there is no primary key for it)

Now if I have my go to last record feature loaded in the form it will go
to
the last record "zzzzz" upon loading and I figured I could just have
"zzzzz"
take the name change over and over. The only problem is it wants to go to
the
last record to much, I have to click around a couple times to lose/gain
focus
in the right order in order to view information about a name if it was the
name that "zzzzz" took.

If I try to switch the listbox to unbound it doesn't see anything to link
with in the subform and it won't navigate the details by clicking in the
listbox.

Thanks for the help.

 

[Allen Browne]

The main form, then, is unbound. It is there just to hold the listbox and
subform.

The list box on the main form is unbound. It is there just to filter the
subform.

Presumably the BoundColumn property of the list box is 1, so it refers to
the ID column.

If that's the way it is set up, you can just put the name of the listbox
into the subform's LinkMasterFields property, and the name of the matching
field from the subform ("ID", I think?) into the subform's LinkChildFields
property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The name field could have been a few issues I was having and wondering why
stuff wasn't working, (I'm not able to get to my stuff until tommarow so
can't check til then)

The purpose of the list box is to display the actual name of the dispatch
center that the zip code is searched for, for example, 11111 is entered,
all
areas that cover the 11111 zip code will be listed in the list box. The
listbox will then be used as the form of navigation for the subform.

Lets say I have this data in tblInfo:
ID Name
AAA ------Anaheim County
HAM ------Hamilton County
JKS -------Jackson City

And this info is in tblZipCode
ID ZipCode
AAA--------11111
AAA--------22222
HAM--------11111
HAM--------33333
JKS---------22222
JKS---------44444

The user enters 11111 for the Search:

List Box Displays:
Anaheim County
Hamilton County

If Anaheim County is clicked in the list box, the subform displays the
information for Anaheim County.

This is the only form of navigation I want, I've turned off all the record
navigators, in the main and sub form.

The goal is this:
When I get a new customer, all I do is input the zipcode they are located
in
into the message box, every center that covers the zipcode is then
populated
into the listbox by the name of the center, I can then click on the names
in
the listbox and have the information quickly and easily displayed so I can
quickly choose the proper center.

No data will ever be altered from the form, nor will the user ever be able
to get to it (I'm just going to turn off everything in the startup options
and if they get anything new shift click to get in and manually input the
data myself, which is secure enough for what I'm doing.).

Thank you again for taking the time to continue helping, your a great
help.

Michael, there's a raft of issues here.

Firstly, if you have a field named "Name", Access is very likely to get
confused. Most objects in Access have a Name property, inclusing forms.
When
you try to refer to the Name field in the form, it will probably think
you
mean the name of the form. Best to change this to PersonName, or
ClientName
or something that is not ambiguous.

Next, it is unclear what the purpose of the list box is. If it's for
displaying the zip codes applicable to the ID in the main form, the it
would
make sense to use an unbound list box. If it is to store the actualy zip
code for the record in the main form, then it would make sense to use a
bound list box. Either way, if there is no record selected in the main
form
(e.g. when you go to a new record), surely the list box should not show
anything.

So, what is the goal here? You've described what you are doing, but I
don't
understand what you are trying to achieve. We started out with telephone
number area codes, and now we are talking zip codes. For display? To
select
a code and store?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This one is fairly lengthy, includes many details about my project.

When I make the listbox unbound it doesn't link properly, the going to
new
field won't work either because it wants to create a new record every
time.

The database I have is only a few forms/tables page's so I'll put the
jist
of it up here, its overall goal is to search for what police centers
dispatch
for a searched for zipcode, display the results in a listbox, then view
details about whatever center is selected:

Table Name: tblInfo
Fields: ID(PK), Name, State, PolicePrimary, PoliceSecondary,
FirePrimary

Table Name: tblZipCode (No PK)
Fields: ID, ZipCode

The two ID's are linked one to many with cascade update on, I will have
multiple Zipcodes for each ID

Query Name: qryZipCode (select query)
Has the Name field from Info (visible) and the Zipcode field from
Zipcode
(doesn't show), I do a [enter zipcode] in the critera field.

Form Name: frmMain
Record Source is tblInfo, there is a button that runs qryZipCode and
places
the results in a listbox on the form. Code here: (mixed and matched
through
experimenting sure its got unneeded stuff in it probably)

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "qryZipCode"
ListBox.RowSource = stDocName

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub

Form Name: Info
Record Source is tblInfo and its linked to the frmMain through the Name
field. Ok, now whatever is in the name textbox I placed so that the
"click
navigate" feature would work (located on frmMain) will have its info
show
up
in the subform. Thats the result I want, thats all I'm trying to do
with
this
database, the problem with the name change is all thats holding it
back.

So say I have the command to go to a new record in the load command,
what
will happen is whatever is located in the textbox that is on tblInfo so
that
the linking will work will want to write to a new record upon closing
(it
won't let it because there is no primary key for it)

Now if I have my go to last record feature loaded in the form it will
go
to
the last record "zzzzz" upon loading and I figured I could just have
"zzzzz"
take the name change over and over. The only problem is it wants to go
to
the
last record to much, I have to click around a couple times to lose/gain
focus
in the right order in order to view information about a name if it was
the
name that "zzzzz" took.

If I try to switch the listbox to unbound it doesn't see anything to
link
with in the subform and it won't navigate the details by clicking in
the
listbox.

Thanks for the help.

 

[Guest]

That did it. Just had to add the ID to the query in the second slot have the
bound column be 2 and set a control source to the main form (zipcode) and
link it like you said.

You have been a God send, thanks so much for all the help!!

The main form, then, is unbound. It is there just to hold the listbox and
subform.

The list box on the main form is unbound. It is there just to filter the
subform.

Presumably the BoundColumn property of the list box is 1, so it refers to
the ID column.

If that's the way it is set up, you can just put the name of the listbox
into the subform's LinkMasterFields property, and the name of the matching
field from the subform ("ID", I think?) into the subform's LinkChildFields
property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The name field could have been a few issues I was having and wondering why
stuff wasn't working, (I'm not able to get to my stuff until tommarow so
can't check til then)

The purpose of the list box is to display the actual name of the dispatch
center that the zip code is searched for, for example, 11111 is entered,
all
areas that cover the 11111 zip code will be listed in the list box. The
listbox will then be used as the form of navigation for the subform.

Lets say I have this data in tblInfo:
ID Name
AAA ------Anaheim County
HAM ------Hamilton County
JKS -------Jackson City

And this info is in tblZipCode
ID ZipCode
AAA--------11111
AAA--------22222
HAM--------11111
HAM--------33333
JKS---------22222
JKS---------44444

The user enters 11111 for the Search:

List Box Displays:
Anaheim County
Hamilton County

If Anaheim County is clicked in the list box, the subform displays the
information for Anaheim County.

This is the only form of navigation I want, I've turned off all the record
navigators, in the main and sub form.

The goal is this:
When I get a new customer, all I do is input the zipcode they are located
in
into the message box, every center that covers the zipcode is then
populated
into the listbox by the name of the center, I can then click on the names
in
the listbox and have the information quickly and easily displayed so I can
quickly choose the proper center.

No data will ever be altered from the form, nor will the user ever be able
to get to it (I'm just going to turn off everything in the startup options
and if they get anything new shift click to get in and manually input the
data myself, which is secure enough for what I'm doing.).

Thank you again for taking the time to continue helping, your a great
help.

Michael, there's a raft of issues here.

Firstly, if you have a field named "Name", Access is very likely to get
confused. Most objects in Access have a Name property, inclusing forms.
When
you try to refer to the Name field in the form, it will probably think
you
mean the name of the form. Best to change this to PersonName, or
ClientName
or something that is not ambiguous.

Next, it is unclear what the purpose of the list box is. If it's for
displaying the zip codes applicable to the ID in the main form, the it
would
make sense to use an unbound list box. If it is to store the actualy zip
code for the record in the main form, then it would make sense to use a
bound list box. Either way, if there is no record selected in the main
form
(e.g. when you go to a new record), surely the list box should not show
anything.

So, what is the goal here? You've described what you are doing, but I
don't
understand what you are trying to achieve. We started out with telephone
number area codes, and now we are talking zip codes. For display? To
select
a code and store?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This one is fairly lengthy, includes many details about my project.

When I make the listbox unbound it doesn't link properly, the going to
new
field won't work either because it wants to create a new record every
time.

The database I have is only a few forms/tables page's so I'll put the
jist
of it up here, its overall goal is to search for what police centers
dispatch
for a searched for zipcode, display the results in a listbox, then view
details about whatever center is selected:

Table Name: tblInfo
Fields: ID(PK), Name, State, PolicePrimary, PoliceSecondary,
FirePrimary

Table Name: tblZipCode (No PK)
Fields: ID, ZipCode

The two ID's are linked one to many with cascade update on, I will have
multiple Zipcodes for each ID

Query Name: qryZipCode (select query)
Has the Name field from Info (visible) and the Zipcode field from
Zipcode
(doesn't show), I do a [enter zipcode] in the critera field.

Form Name: frmMain
Record Source is tblInfo, there is a button that runs qryZipCode and
places
the results in a listbox on the form. Code here: (mixed and matched
through
experimenting sure its got unneeded stuff in it probably)

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

Dim stDocName As String

stDocName = "qryZipCode"
ListBox.RowSource = stDocName

Exit_Command9_Click:
Exit Sub

Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click
End Sub

Form Name: Info
Record Source is tblInfo and its linked to the frmMain through the Name
field. Ok, now whatever is in the name textbox I placed so that the
"click
navigate" feature would work (located on frmMain) will have its info
show
up
in the subform. Thats the result I want, thats all I'm trying to do
with
this
database, the problem with the name change is all thats holding it
back.

So say I have the command to go to a new record in the load command,
what
will happen is whatever is located in the textbox that is on tblInfo so
that
the linking will work will want to write to a new record upon closing
(it
won't let it because there is no primary key for it)

Now if I have my go to last record feature loaded in the form it will
go
to
the last record "zzzzz" upon loading and I figured I could just have
"zzzzz"
take the name change over and over. The only problem is it wants to go
to
the
last record to much, I have to click around a couple times to lose/gain
focus
in the right order in order to view information about a name if it was
the
name that "zzzzz" took.

If I try to switch the listbox to unbound it doesn't see anything to
link
with in the subform and it won't navigate the details by clicking in
the
listbox.

Thanks for the help.

 

[Guest]

Michael,

I have been trying to get a query to run based on a button on my Data Access
Page. Could you tell me how you set that up? I've been trying for a while
and just can't figure it out.

Zach