Table lookup wizard question

[Mohamster]

Hello there,

I've designed a database at work to collate information about locations around the world that are contaminated by conflict and military activities. I'm struggling with the use of the lookup wizard to populate some fields in one table from another table.

The database is ultimately meant to be used to identify contaminated sites in various countries and also to be a source of data for an online interactive map. As such, it needs to hold a fair amount of information. I figured that it would be normal that some fields and tables would be connected to one another. So, for example:

Country table is looked up by the conflict table to provide the names of countries participating in a conflict. To do so I used the lookup wizard.

Similarly, the Site information table is looked up by the Site contamination event table to provide the names of contaminated sites. The Site contamination event table. Then the Site assessment table looks up the Site contamination event table to provide the name of contaminated sites. The relationship between these three table is intended so that at any given site multiple instances of contamination and their subsequent assessment can be recorded. This is where I started noticing problems. When I tried to input some fields into the Site assessment table, specifically the 'Site name', it would only display the primary and foreign keys in the drop down menu but not the 'Site name'.

So, to cut to the chase, I have two questions:

a) Is there a way around this issue?

I suspect not which leads to the broader question

b) Have I messed up by relying on the lookup wizard in my table design? This seems to be the consensus in this and most forums (I checked another thread in the 'Tables' forum here). That said, I've seen some people making a distinction between using 'Lookup tables' and 'Lookup fields within a table', but given my relative newbyness I'm struggling to see the difference!

c) If the answer to b) is yes then what approach should I take to achieve the same aim (having multiple tables that feed information to one another). Initial research seems to suggest putting lookup/combo boxes in my forms. I'm dabbling with that at the minute but so far the results haven't been as desired.

I hope that describes the problem sufficiently. I've attached a zip file with a screenshot of my relationships diagram to give an extra idea. Note that the relationship between the 'Site assessment' table and 'Site contamination event' table isn't showing up as I removed while trying to troubleshoot, but it is supposed to be one-to-many from 'Site contamination event' to 'Site assessment'.

I hope I've not rambled, if further clarification is needed please let me know.

Many thanks in advance,
Mohamed

 

[CJ_London]

best advice is don't use lookups in your table design, they cause more problems than they solve.

People can be free and easy with descriptions, you just have to work out what they mean. So I might refer to a table where you store 'static' data (such as a list of countries and their abbreviations) as a lookup table, but actually, it is just a table.

Lookup fields are usually referring to a field in a table design which is using the (not recommended) lookup facility.

Use your 'lookups' when you design your forms (the control to do this is a combo box or perhaps a list box) - you should never update your tables directly except when developing the system, you should always do it through forms and queries.

I note you are trying this but not getting the desired result so suggest you explain a bit further about this - provide as much information as you can - what is it you are getting now and why it isn't what you want, what you actually want and ideally the setting details for your combo box which should be along these lines:

Name:cboCountry
ControlSource: CountryID
rowsource: SELECT CountryID, CountryName FROM tblCountries ORDER By CountryrName
Default Column: 1
No of Columns: 2
Column Widths: 0cm,2cm

 

[Mohamster]

Thanks CJ_London, I've started revising the way the database/tables/forms are set up.

I'm going to use the Conflict table and the Country Information table as an experiment as they're the simplest and then hopefully implement similar forms for the other tables once I've got the hang of it in these two small tables.

In terms of my previous question regarding the desired result, I think I've sorted that now. through the combo wizard (Is there a way of just editing a combo box from the property sheet or am I best sticking with the wizard)?

Anyway, the details of my combo box are as follows and its purpose is to select the name of the country where a conflict occurred from the Country information table and store it the Conflict location field in the Conflict table. Here are the details for info.

Conflict form combo details:

Name: Combo17
Control Source: CountryID
Format:
Decimal Places: Auto
Visible: Yes
Datasheet Caption:
Column Count: 1
Column Widths: 2.54cm

I can't find any info in the property sheet about the target field and just had to set that up through the combo wizard.

This has now raised the question of storing multiple values. I'm under the impression that a list box is the correct way to do this, but don't want to go down another potential database design faux pas before checking first.

Also, in my first attempt to put in a list box the target field that I wanted the info to be stored in was not enabled for some reason in the final selection in the list box wizard (see attached file). To clarify, I was trying to again use the Country name field from Country information table to populate the Belligerents field in Conflict table, with the final intent of being able to select multiple countries because many conflicts have more than or even two participants!

Thanks again for your help, it's really useful and much appreciated!

 

[CJ_London]

Is there a way of just editing a combo box from the property sheet or am I best sticking with the wizard

You can use the wizard to create it and can edit all of the properties in the property sheet.

I note you did not provide the rowsource or default column which will be something like I suggested - you'll find them under the data tab of the combo properties. From the details you provided, I would expect to see column count=2 and column widths 0cm, 2.54cm

store it the Conflict location field

If this is the name of your field in the conflict table, you should be using this rather than CountryID - note- it is better not to uses spaces in field and table names, you will discover this can create all sorts of problems further down the line.

You might also want to consider renaming Combo17 to something more relevant such as cboCountry or cboConflictLocation(cbo is short for combo).

Note sure what you mean by target field - do you mean control source?

Also not sure where you are going regarding storing multiple values. Are you suggesting one conflict can be in more than one country?

 

[Mohamster]

By 'Target field' I mean the field in the Conflict table that will be the repository for the data retrieved from the Country information table. Is this the control source or the row source? I hope terminology isn't getting the better of me.

With regard to storing multiple values, it's because multiple countries can be a participant in a given conflict in a given location and I'd like to be able to represent that.

Sorry for not providing all the info you requested, I tried to find what I could from the 'property sheet'. Here goes again, for my newly created, I've attached a file showing the properties of my newly created 'Conflict location' control in the form.

I'll take on board your naming conventions, they make total sense, including avoiding spaces. With regard to the existance of a 'Country ID' field and a 'Conflict location' field, I suspect this is to do with the relationship between the two tables. I used 'CountryID' from 'Country Information' table as a foreign key in the 'Conflict' table to maintain a one-to-many relationship between them.

Hope this all makes a bit more sense now?

 

[jdraw]

Mohamster,

Here's a tutorial that may help you with table design.

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[Mohamster]

Much appreciated. Great site too, really lays things out in basic terms.

 

[CJ_London]

It does!

My only comment is that you seen to have a countryID as well in your conflict table.

With regards multiple values for countries you need another table as follows:

tblLocations
LocationsID autonumber PK
ConflictID long FK
CountryID long FK

Create a continuous or datasheet form for the table (we'll call it Locations) and change the CountryID control from a textbox to a combobox with the following properties (you can change this by right clicking on the control and selecting 'Change To'):

ControlSource: CountryID
rowsource: SELECT CountryID, CountryName FROM CountryInformation ORDER By CountryrName
Default Column: 1
No of Columns: 2
Column Widths: 0cm,2cm

and drag it to your Conflict form as a subform and resize as required- ensure the subform control name (under the Other tab) is called LocationSF (it can be anything you like but just for explanation purposes)

In the subform control LinkChild and LinkMaster properties put ConflictID

The last thing to do is a bit of vba coding. In the Conflict form On Current Event click on the three dot (...) caret and select Code Builder. The VBA screen will open with Private Sub Form_Current() just above the cursor and End Sub just below. Put the following code between these two so it looks like this:

Private Sub Form_Current()
    Me.LocationSF.Form.ConFlictID.Default=Me.ConflictID
End Sub

Since this subform will only display data relating to the conflict selected on the main form, you do not need to show the ConflictID control so this can be deleted in the subform once you are happy that everything is working.

 

[Mohamster]

Cheers CJ. That's a lot to take in, but I'm sure it'll be worth it once I've processed it all.

As for the CountryID in Conflict table I put that there because I thought it was necessary as part of the relationship between the two tables. I did it through the Relationships diagram by dragging the CountryID from 'Country Information' onto CountryID in 'Conflict'.

Before that I'd tried to use 'CountryName' as the foreign key but access was not happy

 

[Mohamster]

Just wondering, is the creation of this new tblLocations to enable a many-to-many relationship between countries and conflicts?

 

[CJ_London]

Just wondering, is the creation of this new tblLocations to enable a many-to-many relationship between countries and conflicts?

Yes - you can have many countries to one conflict and also many conflicts to one country

 

[CJ_London]

Before that I'd tried to use 'CountryName' as the foreign key but access was not happy

It won't be - you are trying to crate a link between a numeric field (countryID) and and a text field (countryname)

 

[Mohamster]

Thanks for the explanation. Definitely a steep learning curve, but I feel more on the right track now.

Hit a minor obstacle, I can't find the properties specified here in my property sheet. You can see that in the image I attached to post #5

Create a continuous or datasheet form for the table (we'll call it Locations) and change the CountryID control from a textbox to a combobox with the following properties (you can change this by right clicking on the control and selecting 'Change To'):

ControlSource: CountryID
rowsource: SELECT CountryID, CountryName FROM CountryInformation ORDER By CountryrName
Default Column: 1
No of Columns: 2
Column Widths: 0cm,2cm

 

[Mohamster]

I mean I can't find all the properties. I'm using access 2007.

 

[CJ_London]

you'll find them under the following tabs

Data - ControlSource: CountryID
Data - rowsource: SELECT CountryID, CountryName FROM CountryInformation ORDER By CountryrName
Data - Bound Column: 1 (sorry I said default before)
Format - No of Columns: 2
Format - Column Widths: 0cm,2cm

 

[Mohamster]

Thanks again. I got it to work today after a few niggles (something about not being able to find objects).

Anyway currently it's working (i.e. I can select different countries from the locations table as participants in a conflict) although when I open the Conflict form I do get the following error in the VBA window:

Run-time error '438':
Object doesn't support this property or method

When I click debug it highlights the code that you gave me. Not sure if this is problematic though as the form functions and records are being entered into tblLocations to reflect the multiple countries.

Any thoughts?

Looking to the very near future (i.e. the rest of the database), Would I be able to use the same method, with adaptations of course for the same effect in other forms (e.g. multiple contaminants at one location)?

Thanks again!

 

[CJ_London]

Do you mean this bit of code?

Private Sub Form_Current()
    Me.LocationSF.Form.ConFlictID.Default=Me.ConflictID
End Sub

It may be this is happening for new records where a ConflictID has not yet been set (so it is null). If so remove that code and put the following in the subform before update event

me.ConflictID=Parent.ConflictID

changing names if required to match your field names

Looking to the very near future (i.e. the rest of the database), Would I be able to use the same method, with adaptations of course for the same effect in other forms (e.g. multiple contaminants at one location)?

Yes