Declaring and Using Variables with combo boxes

[Researcher]

Hello,

I have two combo boxes which I am trying to declare variables for.
These combo boxes have assigned data in them which I pull from a query.
The first cbo lists events and the 2nd lists dates of the events.

What I would like to do is select the event from the first cbo and have the 2nd cbo list match up the date with the event selected in the first cbo.
Something similar to cascading cbo's.

Also, I do not want the user to be able to randomly select an event and any dates which do not correspond. Here is my sample (poor) code I am working with...

Option Compare Database
Option Explicit

Private Sub Events_BeforeUpdate(Cancel As Integer)
Dim strname As Variant
strname = "Carlos Mencia"
Dim dteDate As Date
dteDate = #3/1/2007#
'Debug.Print Day(dteDate)
' Returns: 4
'Debug.Print Month(dteDate)
' Returns: 5
'Debug.Print Year(dteDate)
' Returns: 1999

'Dim P As String
'Dim Q As String
'Dim R As String
'Dim S As String
'Dim T As String
'Dim U As String
'Dim V As String
'Dim W As String
'Dim X As String
'Dim Y As String
'Dim Z As String
'Dim AA As String
'Dim AB As String
'Dim AC As String
'Dim AD As String
'Dim AE As String
'Dim AF As String
'Dim AG As String
'Dim AH As String
'Dim AI As String
'Dim AJ As String
'Dim AK As String
'Dim AL As String

'O = #3/1/2007#
'P = "03/15/07"
'Q = "03/22/07"
'R = "03/29/07"
'S = "04/05/07"
'T = "04/12/07"
'U = "04/19/07"
'V = "04/26/07"
'W = "05/03/07"
'X = "05/10/07"
'Y = "05/31/07"
'Z = "06/07/07"
'
'AA = "CARLOS MENCIA"
'AB = "ETTA JAMES & THE ROOTS BAND"
'AC = "CIARA WITH NE_YO"
'AD = "SAN MANUEL COMEDY JAM"
'AE = "MUAY THAI(KAENSAK VS. MEHDI ZATOUT"
'AF = "BIG & RICH WITH COWBOY TROY"
'AG = "TWO DIVAS AT THE MOVIES TOUR"
'AH = "PEPE AGUILAR"
'AI = "MIXED MARTIAL ARTS"
'AJ = "KATT WILLIAMS"
'AK = "ASLEEP AT THE WHEEL"
'AL = "VICENTE FERNANDEZ"

If Events.value = strname Then
If EventDate.value = dteDate Then
Exit Sub
End If
End If
End Sub


Any help from the experts is greatly appreciated as I have no hair left to pull..

Thank You,

John

 

[boblarson]

Why are you hard coding this? Things like this should be in a table and you access THAT with code, so changes can be made without programming changes.

 

[Researcher]

Hard Coding

Why are you hard coding this? Things like this should be in a table and you access THAT with code, so changes can be made without programming changes.

Im not really familiar with that term, and not as fluent with VBA as you might be. The data I mentioned before is created from cbo boxes that draw the data from a table (I stated query earlier).

Perhaps you can give me an example Bob of what you mean?

John

 

[Moniker]

Wow, Researcher. You're getting out of your league here, but here's what Bob means:

AA = "PersonA"
AB = "PersonB"

and so on should never happen. You've been a member since October 2006, so I'm sort of surprised you haven't read around.

Anyway, "hard coding" means just what it sounds like. You're going into the code part of things and explicitly declaring AA="PersonA" and so on. any time "PersonA" changes, you would have to find every hard-coded reference to it in your code and change it.

What Bob is suggesting is that you normalize the hell out of your structure (excuse the language). You make tables that store these things and then make the RecordSource for your ComboBoxes that table. The Northwind DB included with Access has examples of this all over the place.

There are only a few times where straight variables like that (textboxes, comboboxes, etc.) should be hard-coded. Labels, for example, are almost always some hard-coded value as looking up the display value for a label is usually overkill. Things that reference collections or objects tend to be hardcoded a lot (Dim rsMyRecords As Recordset).

Maybe this makes it clearer. Access includes a lot of "hard-coded" constants that you can use. "vbRed" is the color red. "vbCrLf" is a carriage return and a linefeed, which is used in a MsgBox to add multiple lines intentionally.

Things you might "hard-code" at some point would be the value of Pi (3.14157) as in "dblPi=3.14157". That is a constant, like the included hard-coded values. These are things that are not going to change over the course of a DB's existence.

Things that should be stored or derived are things that are variable throughout the existence of a DB (in one instance or over the entire life of the DB). For example, you would never hard-code a username as many users can access your DB. You would instead assign that to a variable and change its value as the DB users' change.

Storing things like a list of employees, almost any calculated field, a list of projects -- all of these will change with time, and assigning a static value to it (another way to say "hard-coding") defeats the purpose of programming in the first place.

Not being rude, but I have an inkling that you are missing some of the core basics of how a database works and what makes it relational. You may want to investigate the basics of design and such, and there are many resources (online, books, and otherwise) that will teach you this.