Access Writing to variable tables - access 2003

[silverstriip]

I am trying to write to different tables depending on a user selected variable (ie: depending on the year selected, the data would goto the correct table). Am I able to setup my fields in my forms to reference a predefined variable, then from there write the data to the correct table? Any help or suggestions would be greatly appreciated.

 

[Temmu]

i do do a little with access, however...

the best source for your answer would be to start with the microsoft forums for access.
then, go to various microsoft mvp (access) websites; the material may be dated, but i've found tons of useful info at both places.

i think your answer will involve an "if" statement in visual basic...
unfortunately, to really do a lot with access, you need to do visual basic.

 

[silverstriip]

little more detail:
this is for a database i'm using for a golf outing. i have a table w/ static information (all participant data), then 2 tables set up w/ data that is specific to the year that are linked to the participants (sponsorships and donations). i need a way to have the sponsorship and donations be year specific. whereas the participant database will just keep growing and getting updated w/ any new info.

 

[Temmu]

create the three tables on the left.
create the table on the right:
use the lookup wizard for field type and choose accordingly, 1 column from each of the other tables.

now, you can enter each participant any number of times with any number of sponsors, or the same sponsor, and the same year or different years... etc.

(reflog is golfer, backwards...)

o, and use the underscore, not the dash, to access the dash is minus. oooo that creates problems.

ur, do you know how to do a query to list the contents of the donations table so that you get all the info you want from the other tables?

 

[silverstriip]

filtering

Ok, got it all up to par. Thank you for all of the help.

The only thing I’m having an issue w/ is the filtering. i have several forms that are pop-ups. i would like to have on the main menu form a drop down to select the year they would like to work w/. This would set the filtration for all of the following forms/reports from there on out. I am not able to figure out how to carry that year variable over from form to form or form to report off the original form.

 

[Temmu]

1. using the tables as shown, there is a drop-down for year each time a record for the DONATIONS is entered. the other 3 tables, SPONSOR, PARTICIPANT, and YEAR are filled out before beginning to enter DONATIONS. this way, there are lists of sponsors, participants and years to draw from to create records for DONATIONS. again, you can have any combination of years, sponsors & participants any number of times.

2. not having access (pardon the pun) to your database, this is one way to store a variable without too much programming:
create a table like TEMPYEAR with one field, YEAR, for example. when the form opens, have its ON OPEN run a macro that deletes the contents of TEMPYEAR. have one field on the form for YEAR. have a NEXT button have a macro that saves the YEAR to TEMPYEAR YEAR and then closes the form, then opens the form for which you wish to use YEAR. upon closing that form, have the field YEAR write to whatever field in whatever table you desire, along with the other info to complete that row.

please note, my way (way nr 1, the picture and description previously posted) is so so much simpler than way nr. 2.

3. there is a third way, but that assumes you know visual basic real well.

rsvp...