UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help With List Box Please...., Access 2016    
 
   
Adrian_Corn
post Aug 1 2020, 11:36 AM
Post#1



Posts: 30
Joined: 15-April 20




Evening all,

Sorry to be a pain, but I am stuck again !!

I have one form (Basically just to create a fancy background), and on it is a sub-form called Registers Sub Form1

One of the fields in my table is called "Country", what i would like to do is have either a combo-box or list box to the the side of the sub-form, to select a country from, and then access will only display that country on "Registers Sub-Form1"

I have read through various forums and the Microsoft page, and it is possible, but they all seem to go off at a tangent and talk about get focus, and various other things, which confused the [censored] out of me !!

Many Thanks in advance

Adrian

PS- on the sub-form, the field country is hidden, so to see it you need to use the sliding bar at the bottom of the form
Attached File(s)
Attached File  Test___Copy.zip ( 238.8K )Number of downloads: 2
 
Go to the top of the page
 
tina t
post Aug 1 2020, 12:01 PM
Post#2



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


QUOTE
I have one form (Basically just to create a fancy background), and on it is a sub-form called Registers Sub Form1

One of the fields in my table is called "Country", what i would like to do is have either a combo-box or list box to the the side of the sub-form, to select a country from, and then access will only display that country on "Registers Sub-Form1"

assuming that the mainform - being just for show - is unbound, the setup is actually pretty easy. presumably the RecordSource of the subform includes a foreign key field that points back to the primary key field of your "tblCountries" (by whatever name). assuming that's the case, just do the following:

1. create the listbox in the mainform, based on your tblCountries.
2. make sure the BoundColumn of the control is the primary key field of the RowSource.

while in mainform Design view, click once on the subform control to select it. set the LinkMasterFields property to the name of the listbox control. set the LinkChildFields property to the name of the related foreign key field in the subform's RecordSource.

that's it. no code needed, no fuss. the listbox control will automatically filter the subform records by country.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Adrian_Corn
post Aug 1 2020, 01:10 PM
Post#3



Posts: 30
Joined: 15-April 20



Hi Tina

when i tried linking master & Child fields, the form ran, but only displayed the first record.

I placed a list box on the main form, but i cannot find the boundcolumn of the control

sorry to be a pain...

Adrian
Go to the top of the page
 
RJD
post Aug 1 2020, 01:21 PM
Post#4


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi: tina gave you an easy way. Hi tina! wavehi.gif

But if you want a control on the main form to limit the contents on the subform, then here is a demo attached. Since the main form is unbound and there was no Master/Child relationship to the subform, I created one. I added a constant to the main form and moved the query to use with the subform (instead of what you were using) and added the constant there as well, then created the M/C link. You could probably arrange something without that, but as a personal design standard I always provide a M/C link.

Then I added a combobox (with a data source of a new table of Countries) with an AfterUpdate event to set a filter for the subform. I included the table in case you wanted to search for a country that, in actuality, is not in the data table at the time of the search. You might want to get a firm response to limiting to any possible country. I added an "All Countries" command button to remove the filter and show all records.

I also changed the subform to continuous and arranged things, and adjusted some other things as you will see, such as moving the subform record source query to a saved query (better to see and test there).

I removed the background color code. You can set the color once and be done with that - or go back to what you were using.

See if this is close to what you are trying to do.

HTH
Joe
Attached File(s)
Attached File  UA_Adrian_Test_20200801.zip ( 106.75K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Adrian_Corn
post Aug 1 2020, 03:22 PM
Post#5



Posts: 30
Joined: 15-April 20




Hi Joe,

Many Many Thanks for that, that is exactly what I was looking for, I spent days reading through web-pages and just got more and more confused.....

I take it that as I add countries to the database, I just add them to the tblcountries table ?

Again very many thanks

Regards

Adrian
Go to the top of the page
 
RJD
post Aug 1 2020, 04:10 PM
Post#6


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Adrian.

QUOTE
I take it that as I add countries to the database, I just add them to the tblcountries table ?

You should be able to add the new (or potential) countries just to the tblCountries. And you can modify the form to use a combobox to select the country for a record. See the attached revision to the solution I gave you previously. See the country control on the form, now a combobox. When you add a record, you can select the country from the combobox values. And the search combobox on the main form still works as before. So, double-duty for the Countries table. And you can add potential countries to the Countries table without affecting the contents of the records until you choose the country.

You could actually do this with Regn and Type as well, and other fields if that makes sense. If there are not too many on each list, you can add tables, add comboboxes to the form, and even add search comboboxes for them as well on the main form. And I would think that cascading comboboxes could be helpful for these two fields.

All of this should spill over into reporting as well ...

HTH
Joe
Attached File(s)
Attached File  UA_Adrian_Test_20200801_Rev1.zip ( 107.57K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Aug 1 2020, 04:20 PM
Post#7



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


hi Joe! :)

okay, here's a demo of the setup i described. it's in A2003, which you should be able to open in A2016. but i'm not sure if you'll be able to look at Design view. hmm, if not, maybe Joe could convert it to .accdb and re-post for me - if you have time, Joe? <smiles hopefully>

Attached File  ListboxSubformDemo.zip ( 13.38K )Number of downloads: 1


hth
tina

ps. since we're playing tag, Joe, i think now you're "it"! ;)

--------------------
"the wheel never stops turning"
Go to the top of the page
 
RJD
post Aug 1 2020, 04:26 PM
Post#8


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi tina ... et al ...

Here's the mdb converted to accdb (in A2010) as requested. The OP should be able to open the mdb, but just in case ...

Okay, now you're "it" again ... thumbup.gif

HTH
Joe
Attached File(s)
Attached File  ListboxSubformDemo_A2010.zip ( 22.01K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Aug 1 2020, 05:21 PM
Post#9



Posts: 6,744
Joined: 11-November 10
From: SoCal, USA


thanks for the conversion, Joe! :) and i'm about to go lie down for a nap (i'm plagued by minor injuries lately, this week it's a strained muscle in my "good" leg), so i'll concede the game to you - for today, anyway! ;) tina

ps. Adrian, good luck with your project, hon! :)

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Adrian_Corn
post Aug 2 2020, 02:28 AM
Post#10



Posts: 30
Joined: 15-April 20




Good Morning All,

Many Thanks again Joe, that's even better, with regards to adding combo-boxes to aircraft type & c/No, the data is normalised in excel first because it comes from a variety of sources all of whom have differing ways of presenting the data, so first of I have to put the data into some sort of order, look for errors & duplicates etc, so by the time I import it into access it should be pretty much correct.

Many thanks for the example Tina, i spent an hour last night looking at the design views, so I think i now have a better understanding of how this works

Again many thanks

Regards

Adrian
Go to the top of the page
 
RJD
post Aug 2 2020, 05:24 AM
Post#11


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Adrian. tina and I are happy to assist.

Continued success with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Adrian_Corn
post Aug 3 2020, 05:04 AM
Post#12



Posts: 30
Joined: 15-April 20



Duplicate Post

This post has been edited by Adrian_Corn: Aug 3 2020, 05:49 AM
Attached File(s)
Attached File  My_Database___Copy.zip ( 256.13K )Number of downloads: 1
 
Go to the top of the page
 
RJD
post Aug 3 2020, 06:17 AM
Post#13


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


Hi Adrian: You changed the Default View of the subform to Datasheet and the subform was too long for the form display (causing overlaps). Continuous allows you better control over the display of the subform. But even with the subform changed to Continuous and adjusting the size of the subform, there seemed to be a problem with the code communicating with the subform filter. So I re-created the main form and added the subform again. Now it works. I suspect some sort of corruption, but there is no way to tell.

Just in case you really do want a Datasheet view instead of Continuous, I created an example of that as well. Note the width of the controls.

I also dispensed with the Images relationship and table. I did not see a need for this, but you can add that back if it is actually used.

Looks like it works fine now. See if this is more to your liking. And note the record count control, and how it is handled differently in Datasheet vs Continuous views of the subform.

HTH
Joe
Attached File(s)
Attached File  My_Database_Copy_Rev1.zip ( 197.83K )Number of downloads: 1
Attached File  My_Database_Copy_DatasheetView_Rev1.zip ( 209.22K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Adrian_Corn
post Aug 3 2020, 07:21 AM
Post#14



Posts: 30
Joined: 15-April 20




Many Thanks again Joe, that datasheet view is exactly what I was looking for, and now everything works again !!

Everything was working fine yesterday, and then this morning when i tried to look something up it failed to go back to the all countries view

Sorry to have troubled you again

Regards

Adrian
Go to the top of the page
 
RJD
post Aug 3 2020, 10:44 AM
Post#15


UtterAccess VIP
Posts: 10,687
Joined: 25-October 10
From: Gulf South USA


No problem. You are very welcome. Glad it's working now.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 07:32 AM