Full Version: Unbound form object, based on field in form
UtterAccess Forums > Microsoft® Access > Access Forms
Olvar
Ok as some of you folks know coding is not my strong suit. I'm workin on a little project that compares a person's type and based on their time pulls a list of rates for various services up. Currently my Rate table has fields named Code, Rate1, Rate2, Rate3, Rate 4. My Client info table has a field called Type. If they are a Type 1, then I need it to display Rate1. The problem I'm running into is that there are probably 10 codes and I need to display the rates for each of those 10 codes. What I tried originally was
CODE
  
If Me.Type = 1 Then Me.9001 =
If Me.Type = 2 Then Me.9004 =

The portions after the second = is what i need to reference my Rate table and look at the record code and then pull the correct Code number (9001) and give me the correct rate for it.
The more that I look at this I'm not sure I'm on the correct approach to this but i figured it would ask anyway. Now I just need to start running to avoid Jack telling me that I need to normalize. evilgrin.gif Yes I know I need to normalize more, but I only seem to be getting there in small steps.
freakazeud
Hi,
it sounds as if you are using repeating groups in your tables. This should be avoided. Have you read up on normalization. I would suggest you do so and fix your table structures before you go on with forms/reports/queries...!
Some good reference posts (in no particular order) are:
HTH
Good luck
Jack Cowley
Yep, you read my mind... You need to normalize your Rate table...:
blRates
RateID (PK and auto)
RateDescription
Rate
I am not sure how you plan to use the data, but this should head you in the right direction...
Jack
Olvar
Yah I know my weak point is normalization. Ok here is what I've got so far taking out any propritory data, sorry for the blanks in the records. The Provider_Rate_Info form is the one that I want to update. On the right side you'll see two fields 90801 and 90804 those need to be based on the rate table. As you'll see License ABC and XYZ both have a 90801, but ABC uses Rate1 and XYZ uses Rate2. Anyway hope this clears it up a bit while i read thru some more articles on normalizing this mess o!
Jack Cowley
If I do your work for you how will you learn? Spend some time reading the articles and then see if you can sort this out yourself as I think you can...
ack
Olvar
Ok best practice question here. Am reading thru how to normalize this. Am I better off making the Rate table with
odePK,Code1, Rate1, Rate2, Rate3
CodePK,Code2, Rate1, Rate2, Rate3
or
CodePK,Code1, Rate1,
CodePK,Code1, Rate2,
CodePK,Code1, Rate3,
CodePK,Code2, Rate1,
CodePK,Code2, Rate2,
CodePK,Code2, Rate3,
Jack Cowley
Which table looks like it doesn't have 'repeating groups?
ack
Olvar
The second one, but in terms of how the data is used that layout makes absolutely no sense. I guess i can rework the continuous form where you can enter the rates to give the illusion that they are all still on one line.
The more I sit here and think about this I think the light is starting to slowly go on. Now I'm back to the point of populating the code rates. (Yes i've already cleaned up the tables in the real dbase) I'm still thinking it is going to have to be an IF, THEN statement, but what at this point I'm not sure. I think at this rate i'm going to pack it up early and go home for the first time in 5 days. That might be the source of my deadheadedness. thumbdn.gif Thanks for the info guys, maybe when I get into the office tommorrow i'll be able to wrap my brain around this a bit more and see what i'm missing in this.
Jack Cowley
Access is a relational database and is not like paper forms and this is what confuses people new to Access. That is why you have to understand normalization and also realize that your data entry and data viewing may be different from the paper forms you are used to.
Is for the If - Then solution... I don't think what is what you want, but I am not sure how you intend to use the data but my guess is that you might want to 'lookup' the rate for a given code...
Hang in there as you will get this!!!
Jack
Olvar
Well what i'm trying to achieve is something that compares their license type and fills in the appropriate rates. I didn't think that I could do that with a lookup since I have 5 different license types. I've used lookups in the past with single data types, but not sure how do even start doing this with 5 types and 10-15 different rates and codes for each type.
Jack Cowley
I am not sure how you want to use this data, but if a license can have 5 rates then you need a table something like:
blLicencesAndRates
LicensesAndRatesID (PK and auto)
LicenseID (FK)
RateID (FK)
You will need a table of rates, which I already gave you, and one for licenses and that should work for you.
Orealize that normalization can be confusing, but it appears that that you have a Many-To-Many relationship so that is the reason for the table above. Read up on Many-to-Many in Access help or search the Internet for articles and see if that is what you need in your situation.
Jack
Olvar
Will do Jack. Thanks for the info, I'll try to schedule in some research time in the next day or so to look into this. And thanks for the links Freakazoid, they definately lay normalization out a bit better than my reference books do.
Jack Cowley
You are welcome and good luck with this! I know that a relational database requires a different way of looking at data and you will get it!!!
ack
Olvar
Ok I've got an idea, but not sure if this counts as normalized or not. The various rate tables are different stored values for each Lic_Type. This is the rough outline at least, any ideas if i'm on the right track or if i'm derailed agian?
blContracts
Prov_ID (PK)
Lic_ID (FK)
Addy
City
tblLicense
Lic_ID(FK)
Lic_Type
tblRate1
90801
90804etc
tblRate2
90801
90804
Jack Cowley
Take a look at this:
blRates
RateID (PK and auto)
RateDescription (Text)
RateType (Text)
Rate (Currency)
Create this table and when it is complete type in a description that you have and I can't recall, then type in Rate1 in RateType then the amount in Rate. do this for all possible rates. Does this make sense?
Jack
Olvar
ok RateID, which was on paper, but i forgot to type in, and Rate all make sense. Now how do i make sure that the different category of contractors get the right rates. The only way i can think to make it work is use the RateType as a relation to the License type.

Type 1 gets 90801 = 80
90804 = 40

Type 2 gets 90801 = 100
90804 = 50

In the table I'm going to have this using Rate Type at the end
tblRates
1, 90801, 80, 1
2, 90804, 40, 1
3, 90801, 100, 2
4, 90806, 50, 2

You had said earlier that you didn't think using IF THEN was the best way to do this, but whenever I look at this I keep thinking of that.

Ok I'm trying to set up the relationships between the tables and keep getting No Unique index found for the referenced field of the primary table.
Edited by: Olvar on Thu Apr 27 17:19:57 EDT 2006.
Jack Cowley
tblRates shoud be a 'lookup' where you open a combo box(based on tblRates) and make a selection for the amount of money charged for that particular client, or whatever. Here is a possible scenario:
blClientRates
ClientRatesID (PK)
ClientID (FK)
RateID (FK)
RateID is a combo box based on the table tblRates. When you look at the table above you know that Joe Bloggs has a rate of $200...
Confusing, huh!
Jack
Olvar
Very confusing because I feel like i'm chasing my own tail here. confused.gif
orrect me if I'm wrong, but with this wouldn't you would be setting the rate individually for each person? In this case every person with a type 1 license has the same rate as every other type 1 for each seperate code (RateDesc). Basic form layout. If we're on the same page ignore the illustration at the bottom. From what I'm looking for, the rate should be read only, it only changes when you change the license type, and when you change that license type, the rates autopopulate to fit the correct license.
John Doe
License Type 1
90801 $80
90804 $40
etc
Jane Smith
License Type 1
90801 $80
90804 $40
etc
Joe Cool
License Type 2
90801 $100
90804 $50
Jack Cowley
Don't feel bad as I am guessing at what it is you are doing with the data. Now I see that if person has a particlar license they get a particular rate depending, I guess, one what they are doing. For 90801 they get x dollars but for 90804 they get y dollars. How about this:
blRates
RatesID (PK)
LicenseID (FK)
RateDescriptionID (FK)
Rate (The Money!)
tblRateDescriptions
RateDescriptionsID (PK)
RateDescription (90801, 90804, etc)
Now if rate descriptions don't change then you don't need the table above and you can just put RateDescription directly into tblRates.
With tblRates you can lookup the LicenseID and the RateDescription and get the rate for that Licence and RateDescription.
Are we getting close? I don't know how or where you want to use rates so this is my best guess... at the moment!!!
Jack
Olvar
Ok I'm attaching what I've got so far. If you open form Provider_Rate_Info you'll see the basic layout. Each of the three entries have a different Lic_Type and so should have a different rate. Now in tblRates I also have a Lic_Type which matches the Lic_Type in tblLicenses. How do I get those rates to show up appropriately based on the Lic_Type.
Jack Cowley
Attached is one way... Select a LicenseType in your combo and the rates will show up in the list box...
th,
Jack
Olvar
Thanks Jack that will do it. I was reading an article from Microsoft about linked combo boxes, but i so very rarely use list boxes that it never occured to me to use them to display the data. The only thing I've noticed is that when i run it on my desktop it runs fine, when i run it on the network, i get a missing dll error, but again it works.
Jack Cowley
Check the References for the copy of Access on the network and uncheck any marked "MISSING" then see if your missing dll error goes away...
Good luck and I am glad the list box will work for you. There are other ways, but this seemed the easiest...
Jack
Olvar
Unfortunately I can't change that and our IT department won't change it.
asy is always good. Ranks right up there with, "If is ain't broke, don't fix it."
Jack Cowley
Hmmm. Strange that IT won't fix a problem if they have one, but that is life in a big company I guess. As long as you can work with it I guess it has to be OK....
ack
Olvar
Ok Jack, you list boxes have been working wonders until one of our users found a way to throw a wrench into the works. On screen the information displays fine and works wonderfully, but then one of my users wanted to print off all of them, when they printed, only the first forms displays were correct and subsequent pages had the same info as the first, even though on the screen when i page to them they displayed correctly. Any idea what I need to do to make this print correctly.
Jack Cowley
Are you trying to print the form or did you create a report to print the data? If you are trying to print the form then that is not the way to go as forms are for viewing and reports are for printing. Create a report and let you users print the report(s).
ack
Olvar
How do you print an unbound field in a report, do i take all of the SQL logic behind that field and basically recreate that in the report?
Jack Cowley
I have no idea what it is you want to print, but if it is the Rates then create a query based on the necessary tables and use the query as the source for your report....
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.