11-16-15, 17:39 #1Registered User
- Join Date
- Nov 2015
Unanswered: VBA question: code to filter based on the selection in a combo box
This is a follow up to my recent post here: http://www.dbforums.com/showthread.p...ly-to-a-record
I am now using a junction table as suggested by Sinndho. At this point I have two forms (data entry and search) and three tables: one containing my source information[tblSources], one containing the names of the 50 states[tblStates], and a junction [tblJunction] (to keep it simple). This seems to be doing the trick. In my data entry form, I have a subform arrangement similar to this one: http://www.utteraccess.com/wiki/inde...Relationships). Now when I add states to a record through my data entry form, a new record in tblJunction is populated containing the ID of the Source and the ID of the state.
Before I had the junction table, I was only allowing each record to contain one state. I was using a dynamic search form that I designed from Allen Browne's instructions (http://allenbrowne.com/ser-62.html). On the old form, I would just type in the name of the state in the unbound "State" text box, and it would return any sources where that text appeared in the State field.
Now, on my search form, I would like to have a combo box that gets its values from tblState and when the user runs the search, it returns any records that are tied to that state through tblJunction. I am using the code provided by Allen Browne on my search form, where for each unbound text box I have the following:
If Not IsNull(Me.txtSourceName) Then
strWhere = strWhere & "([SourceName] Like ""*" & Me.txtSourceName & "*"") AND "
How would I alter this code to incorporate the value in combo box?
Hopefully I am giving you all enough info to answer. I'm in over my head here, but getting there slowly with the help of the forum.
11-17-15, 12:49 #2Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
if you design the underlying tabels properly (using realtionl primciples then you can do away with this complexity.
where sourcename in ("TX", "NY","AK")
don't store different value types int he same column its lazy bad practice and will bite you int he backside
store geographical encoding in as many different columns as required (eg country, state, city, disctrict), and bear in mind if your appliciation can be potetially used outside the US non US geographical encoding can be a frightmare
bear in mind there are many places with the same name, granbted its less prevalent in the US, who can forget the hilarious reasons behind renaming Hot Springs New Mexico https://www.google.co.uk/webhp?sourc...ces+new+mexico. in theory in the US you shgouldnt' have more thna one residential area sharing the same city / settlement name, but that doesn't hold true elsewhere (eg look how many London, Manchesters, Newcastles exist)
the US postal system uses two letter codes for the state.. use em for your application. why. well its self documenting (its obvious looking that the data, if you have to share you data with others then they can immediately handle your data.
reason 2 is depending on your settigns ALASKA may not be the same as alaska or Alaska
reason 3 Alaska isn't the the same as some typo of Alaska eg Aliaska
reason 4 its good realtional db practice to have a table defining states and define a relation (and a RI link at that) between that state table and tabeles using that.. that then stops users typing in somethign not in the list so somebody could type in COL in place of Colorado and so on.I'd rather be riding on the Tiger 800 or the Norton
11-17-15, 16:06 #3Registered User
- Join Date
- Nov 2015
Healdem, thanks for the advice (here and in my other post). I'm gradually finding that tracking location is not quite as simple as I imagined it would be.