Full Version: drop down 000-00
UtterAccess Forums > Microsoft® Access > Access Forms
calebm12
i have tblyears
yearid (pk)
year
The year is formatted like 0000-00. meaning i am entering years like 2006-07
Othen have a tblmemberyears
memberyearid (pk)
memberid
yearid
i then had a frmpersonalinfo in which i have a subform for years. the problem is in the drop down menu the year is being displayed as 200607, instead of 2006-07
how can i fix this?
fkegley
You're going to have to develop a query that fetches the data for the combo box. In the query put the format that you want for the year field.
calebm12
i have a query that fetches the data for the combo box, it is based on tblyears. how do i enter a format into the query.
freakazeud
Hi,
I'm not seeing a whole lot of purpose of your table set up as you have it?
Why can't you put this all in one table e.g.:
blMembers
MemberID PK/Autonumber
FirstName
LastName
MemberSince
Unless you have people who are not members, which would require an extra people table to link to a member table I don't see a reason to have this over three tables. If your year table stores possible years and month...you should NOT be doing that anyway, since that are calculated values and can just be calculated at runtime.
HTH
Good luck
calebm12
because there are events associate with year. as in example. in 1998 a member could elect to pay for insurance, while another year they might not.
o my tblmemberyears
is actually
memberyearsid
memberid
yearid
insuance
photo
does that make sense.
freakazeud
But why would you need a table for years? This could just be a field which holds the year value rather then linking to it, or?
calebm12
aha. i guess it could be. put then it would be a lookup right, in which i enter the values for the drop down. i thought using a table would be easier in case in the future the admin wants to add year options. plus i am using yearid in other subtables in my database.
your thoughts.
And how in the world do i format a query into 0000-00. i dont understand why if my table has the data like that then when i run a query on that table it wouldnt fill the drop down with the same format.
freakazeud
No you wouldn't use lookups at all, but rather populate the combo with a query which creates this data for you or with code which populates the combo at runtime. These values are calculations and should not be stored up to some certain time to create some sort of lookup option.
HTH
Good luck
calebm12
i understand. that is all very new to me. like i said "brand new here". i didnt even know that was an option. what are the drawback in keeping it as is.
ScottGem
Lets go back a bit. You said: "because there are events associate with year. as in example. in 1998 a member could elect to pay for insurance, while another year they might not."

This says to me you need an "events" table":

tblMemberEvents
MemberEventID (PK Autonumber)
MembershipID (FK)
EventDate
EventDuration
EventID (FK)

In this way you enter an event for a member with its duration. So a member purchased insurance on 5/8/98 for 12 months. Again in 99 and 2000, but dropped it for 2001. So you would have 3 records, one for each year.

One more point. I'm assuming that 2006-07 means that the term spans 2 calendar years. That's why I used Date and duration. That's better than using a text field to display 2006-07.
freakazeud
That you store redundant data in a redundant table, which breaks normalization rules!
calebm12
ok. the years are recording years of participation. and there are two things that a member can elect during this year of participation. they can buy the one dollar insurance and they can allow their image to be used in advertising. they register every year. someyears they might pay some they might not. i also need to know what specific years they were in the program.
Have i gone completly wrong.
freakazeud
No,
not completly...just made it a little more difficult then needed.
So for what I understand you would need three tables e.g.:
blMembers
MemberID PK/Autonumber
FirstName
LastName
MemberSince
...
tblInsurance
InsuranceID PK/Autonumber
Insurance
Price
...
tblMembersParticipating
ParticipatingID PK/Autonumber
MemberID FK/Number
InsuranceID FK/Number
Duration
Image
...
Does that make sense or are we missing something?
HTH
Good luck
calebm12
i have 3 tables now related to what we are talking about. they are
blpersonalinfo
memberid
lastname
firstname
address
gender
dob
etc
tblMemberyears
memberyearsid
memberid
yearid
insurance
tblyears
yearsid
years
Owouldnt need your tblinsurance. because selecting onedollar from the insurance box tells me that they have insurance, as well as what level they bought. no selection, no insurance. your insurance table is like my year table. or?
this has got be confused...here i was thinking i was normal
would be glad to zip it up and show it to yall.
freakazeud
No...tblyears is redundant...whereas tblinsurance should hold ALL possible options a user could be to have as insurance?
Do you not have information regarding insurance rather then just a dollar amount?
HTH
Good luck
ScottGem
No, your years table is unneccesary. If there is only the one type of insurance then you only need 2 tables. On table for members and one table to the years they partiicpate. That latter table should indicate the date and duration.
calebm12
no, there is no other information associated with insurance.
Just if they have it or not.
freakazeud
ok...so then you just need two tables as Scott has indicated.
freakazeud
Then...for your combo problem...I will give you a sample of how to populate it with a duration of years/month!
Put this on the on load event of the form where you display your combobox:
im YourVariable As Integer
YourVariable = 0
Do Until YourVariable > 15
Me.Yourcombo.AddItem (Format(DateAdd("m", YourVariable, Date()),"yyyy-mm"))
YourVariable = YourVariable + 1
Loop
This would populate your combo with 15 month of future months in the format you want!
If you want to give the user control of how much further the combo should advance then give them a control in which they can set the value which you want the loop to increase to.
HTH
Good luck
calebm12
alrighty, i will try and give it a shot. thanks.
freakazeud
You're welcome.
Good luck
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.