Using Variables in a Form to Reference Table Field Names

[buckwheat_311]

I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record.
I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:

Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.

'------Code I'm Currently Working On-------
Dim intX As Integer
Dim EventVal As Integer
Dim strX As String

intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal <> intX And intX < 6
intX = intX + 1
Loop

strX = str(intX)
Dim E_Name As String
Event_Name = [Event strX ]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]

'-----Old Code That I'm trying to simplify-------
If Event_List = "Event 1" Then
Event_Name = [Event 1]
PMT = [PMT 1]
Receipt = [Receipt 1]
Payment_Type = [Payment Type 1]
PIF = [Paid In Full 1]
Camp_Site = [Camp Site 1]
Cabin_Number = [Cabin Number 1]
ElseIf Event_List = "Event 2" Then
Event_Name = [Event 2]
PMT = [PMT 2]
Receipt = [Receipt 2]
Payment_Type = [Payment Type 2]
PIF = [Paid In Full 2]
Camp_Site = [Camp Site 2]
Cabin_Number = [Cabin Number 2]
ElseIf Event_List = "Event 3" Then
Event_Name = [Event 3]
PMT = [PMT 3]
Receipt = [Receipt 3]
Payment_Type = [Payment Type 3]
PIF = [Paid In Full 3]
Camp_Site = [Camp Site 3]
Cabin_Number = [Cabin Number 3]

PLEASE HELP!!!!

 

[raskew]

Hi -

Does your book talk about normalization? If so, please read up on it. What you're in the process of creating is a real ugly spreadsheet-type solution, totally denormalized.

Consider splitting your data into two tables:

tblEvents:
-EventID (autonumber, key field)
-EventName (text)

tblEventDetails:
-ID (autonumber, key field)
-EventID (number, FK)
-PMT (?)
-Receipt (?)
-PIF (yes/no)
-CampSite (?)
-CabinNumber(?)

Create a relationship based on EventID

I've attached a very small and simplistic example database (written in A97) demonstrating how you'd use a form with a combo box to select a desired event and populate a form with the data pertaining to the selected event.

HTH - Bob

 

[buckwheat_311]

I have searched normalization (my book doesn't contain info on it) and did some reading on the subject, and it sounds like I have already started doing that. I am doing an internship at a Girl Scout office working with some of the databases. When I arrived, they were using (what seemed like 800) many different Excel spredsheets to do what I have now on one Access table and form. They don't plan on really using the table for entering or looking at the information, as that's what the form is for. However, I have been trying to keep all the information on one table so that it will be easiest for them to run queries/reports after I leave.
That's all.
Thank you for your help.

 

[buckwheat_311]

Futher Help Needed

I was finally able to look at your attached file and, may I say, WOW. You did some cool things. Unfortunately, I am not using my combo box to list all of the event names (because there are over 70 events), it only contains "Event 1" "Event 2" ..."Event 6". I want it to display the details for what that particular person's first event was when I click on "Event 1" and to display the details for the third event when "Event 3" is clicked. Note that the event 1 for one person will not (most of the time) be the same as the event 1 for the next person. How may I go about this?

Some other questions:
How did you get the tblEventsdetails to stack in the tblEvents table?

Is it possible to use a variable as part of a field location, as in the following code?

Dim intX As Integer
Dim EventVal As Integer
Dim strX As String
intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal <> intX And intX < 6
intX = intX + 1
Loop

strX = str(intX)
Dim E_Name As String

Event_Name = [Event strX]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]

Thanks for the help!