Display flag corresponding to selection in combo box

[AGI]

Hi there,

This is my first post on the forum . I'm trying to create an access database to make an inventory of my model trains.

I have a main entry form (frmTrain) where I enter all sorts of info regarding e.g. a locomotive. This info is then stored in a table (tblTrain).

In the main entry form, I've put a combo box (cmbCountries) linked to a query (qryCountries) which queries the country codes from a table (tblCountries) that has three fields:

ID (autonumber)
CountryCode (short text) (containing the country codes UK, FR, DE, ...)
FlagFile (short text) (containing the name of the flag picture, e.g. UK.png)

The flags are stored as *.png files in a folder \Flags that is in the same folder as the database file. I have chosen this approach instead of putting the flag pictures in an OLE field in tblCountries because I'd like to avoid being stuck to *.bmp files (don't support transparency). I'd also like to avoid having to mention the complete file path in the field FlagFile

I created a form (frmCountries) to easily add countries to tblCountries as needed.

Now back to the main entry form. The selection made in cmbCountries is stored in the field 'Countries' in tblTrain. When a country is selected in cmbCountries, I'd like that the corresponding flag is displayed next to the combo box.

I found an example on the web where an image field was used to display the flag, let's say with the following code:

Private Sub cmbCountries_Change()
Me.ImageFieldName.Picture = Me.cmbCountries.Column(2)
End Sub

Private Sub Form_Load()
Me.ImageFieldName.Picture = Me.cmbCountries.Column(2)
End Sub

and where the combo box had as row source (not using qryCountries):

SELECT tblCountries.ID, tblCountries.CountryCode, [Application].[CurrentProject].[path] & "\Flags\" & [FlagFile] AS Expr1 FROM tblCountries ORDER BY tblCountries.

;

The problem with this example is that, if you select in frmTrain e.g. UK, the UK flag is then displayed across all records in frmTrain. So the image field is not the appropriate field to display the flag in frmTrain and I guess an unbound/bound (?) object frame should rather be used. :banghead:

I'd be very grateful for any help on how to display correctly the flag picture for every individual record in frmTrain corresponding to the country chosen in cmbCountries.

 

[CJ_London]

try

Me.ImageFieldName.ControlSource = Me.cmbCountries.Column(2)

 

[AGI]

Thanks.

When I try that, I get the following error:

Compile error:

Method or data member not found

I guess I need to enter the Countrol Source for the image field? But what? Referring to 'Countries' in tblTrain gives the same error.

 

[CJ_London]

if countries is a lookup field in your trains table then you need a different approach. Please clarify

 

[AGI]

Countries is not a lookup field.

However, when looking in tblTrain, I noticed that, instead of the CountryCode, the ID is stored in the Countries field, so I guess the problem lies there ?

How do I make cmbCountries store the CountryCode in the Countries field in tblTrain?

 

[CJ_London]

I'm getting a bit confused, please can you show some example data for a few records and clarify if your form is a continuous or single form, has a subform etc

 

[AGI]

I wanted to send a link to the database that I've uploaded on Dropbox (db file exceeds the limit of the forum), but the forum doesn't allow me :

To be able to post links or images your post count must be 10 or greater. You currently have 3 posts.

Perhaps the following was a bit confusing in my original post: at first I had set up cmbCountries linked to qryCountries querying the country codes from tblCountries. However, using the example I found on the web, cmbCountries gets its values directly from tblCountries.

 

[CJ_London]

if you zip your db, you can upload it to this site - use the advanced editor and you will find the 'manage links' button towards the bottom

 

[AGI]

I zipped it, but it's still 29 MB (there's pictures and manuals in it) and the forum limit for zip files seems to be 2 MB.

 

[AGI]

I'll try and make a slimmed down version tonight, which only contains the relevant tables, forms, fields, ... for this question.

 

[CJ_London]

also, don't forget to compact it

 

[AGI]

OK, I trimmed it. When opening the database, clicking on the locomotive picture takes you to the form with the Country combo box. As you can see, the UK flag is shown in both records even though 'BE' is selected in the second record.

Thanks!

 

[CJ_London]

OK fixed it - you were assigning a picture to an unbound control and not refreshing it in the oncurrent event (you were using the load event)

As advised before, you needed to set the controlsource, not the picture - (which then does not need refreshing) - although you need to hide it if a country is not specified - which you do in the afterupdate event, not the change event.

Recommend you read up on the order of events and when they are triggered. This link will get you started, but googling something like 'access order of events' will come up with plenty of links

https://support.office.com/en-ca/ar...-objects-e76fbbfe-6180-4a52-8787-ce86553682f9

I've attached the corrected db with the corrected code and control. Your table design is pretty good, but not sure of the benefit of the 'era' table unless you are going to be adding additional data - you could just use a value list instead of a rowsource in your combobox

 

[AGI]

Marvelous!

Thank you so much for your time and your help

I definitely have to make myself familiar with the order of events; thanks for the link.

Regarding the Era table, a value list would indeed suffice for my purposes. However, when the database is finished, I'll post it on the web so that other people can use it as well and some may want to add sub eras (IIIb, IVa, ...). So, I think I'll also create an entry form 0_Era to allow people to easily do that.

Thanks again!