Combo Box linked to two tables (1 Viewer)

[ascaife]

I have a combo box on my form called "Location" that I would like to link to two tables.

Table 1 is a local suburb/towns list
Table 2 is a countries list

I would then like to tick a box to determine whether the location is local or overseas, and have the combo box give me the correct table

Any ideas?

 

[Access2007User]

do you mean you have a combo box wherein its control source is a joined two tables?

and what do you mean about your combo box will give you the correct table. maybe you mean your combo box will give you the resulting records filtered by your tickbox.

 

[ascaife]

Sorry I'm a bit new here.

I haven't actually set the control source of the combo box as yet since I'm not sure what to do. Should I set the control source as the two joined tables? If so, how do I go about this.

And yes you're correct, I would like my combo box to give me the resulting records filtered by the tick box.

Any advice would be greatly appreciated.

 

[Galaxiom]

Is the purpose of the combo to select a location from one of the tables to save in a record of another table?

 

[ascaife]

Yes it is. My contacts table needs to contain a location field with either a town or a country value

 

[Access2007User]

i would think it this way:

TABLE 1
Fields: CountryID2 (PK) duplicates allowed - number type
Local Suburb/Towns - text type

TABLE 2
Fields: CountryID1 (PK) duplicates not allowed - number type
CountryName - text type
LocalOverseas - yesno type

Thereby in his control source of his combo box would be a joined table of table 1 and table two. So he can make the LocalOverseas as one of the criteria on his combo box control source. Thereafter, on afterupdate event of the tick box would requery his combo box.

 

[Galaxiom]

The ControlSource will be the name of the field in the Record Source table of the form.

BTW. Have you normalised the City and Country fields using IDs so you are recording a number rather than the name in the contacts table? And understand how to make the combobox show the name?


The Row Source of the combo will be the Country or City tables. Changing between them with the checkbox would require VBA to change the Row Source.

Assuming checkbox is ticked for country, in the After Update event procedure of the checkbox:

NOTE: This doesn't work because the way the If and With overlap.
See Post #10 for the right way.
(Must pay more attention to what I've typed before hitting post button)

If checkboxname Then
     With Me!Location
         .RowSource="tblCountry"
Else:  .RowSource="tblCity" 
     End With
End If
 
Me!Location.Requery

If there are more than just ID and name fields in the tables you may want to use a select query rather than just the tablename to trim down to just those fields.

 

[Galaxiom]

I was also going to suggest something like Access2007User as an alternative. However they have not explained the union query required to make it work. Also both tables would require the YesNo field to work as the row source. Moreover the union query requires the subqueries to have the same number and type of fields.

Note also in the two table technique the Country table must have every record set to yes on the LocalOverseas field and no in the City table. You would do this in the default value of the field.

If I was using this technique I would probably put all the records in one table with the Yes/no field to indicate which type of entry applied to the record. This would mean you could use a static RowSource and not require anything but a Requery of the Location combo in the After Update procedure of the checkbox.

You also need to think about how you can add new values. It is best to avoid the entries being automatically added when they don't match the current available records. Spelling mistakes end up in the list and no doubt cities and counties will get into the wrong group or table.

 

[ascaife]

Thanks for all your help guys. That code seems to be exactly what I need, but I'm doing something wrong with it. Access tells me I have an Else without If. Obviousbly I'm no VBA expert.

Private Sub Overseas_AfterUpdate()
If Overseas Then
With Me!Ctl319TermLocation
.RowSource = "tblCountriesList"
Else: .RowSource = "tblAustPostcodes"
End With
End If

Me!Ctl319TermLocation.Requery
End Sub

My checkbox name is overseas and 319TermLocation is the location field in my contacts table.

The tables have been normalised and I will need to add a select query as the row source but wanted to get this code working first.

 

[Galaxiom]

Problem with overlapping With and If. Try this:

With Me!Ctl319TermLocation
     If Overseas Then
          .RowSource = "tblCountriesList"
     Else: .RowSource = "tblAustPostcodes"
     End If
End With

 

[ascaife]

That worked an absolute treat.

Thanks so much for your help guys.