Restricting Data Entry

[Denburt]

Ah bingo! I suppose I should have stated that you should use a text box for the Grant field. If you go to design view right click on the Grant combobox click on "Change To" then change it to a text box all should be fine.

[MNNovice]

Denburt:

Thanks. I shall try and shall keep you posted.

[MNNovice]

Denburt:

Thank you so much. It worked like charm. One question. Since my form is in datasheet format, how can I make this text box shaded in a different color so that the end users will know they cannot change the data for Grant No?

I didn't know the short cut to change a combo box to a text box. Learn something new. Thanks again.

[Denburt]

Denburt:

Thank you so much. It worked like charm. One question. Since my form is in datasheet format, how can I make this text box shaded in a different color so that the end users will know they cannot change the data for Grant No?

I didn't know the short cut to change a combo box to a text box. Learn something new. Thanks again.

Datasheets can be pretty restrictive in a lot of ways, about the only thing I could suggest for that would be to change the Grant field so that is is disabled (Enabled=no in properties) this would grey out the text in the box and not allow them to tab into it.

Glad you learned a new shortcut and I am pleased that I could be of help. Feel free to keep the questions comming.

[MNNovice]

Denburt:

Thanks for your offer of help.

A grant can have a several kinds of split ratio for funding sources from Fed and Local. It could be 80/20, 83/17 or 95/5.

Because Fed may choose to change ratios I created a table called tblSplitRatio to record any changes in future. This table has these fields
SplitRatioID - PK (auto)
FedRatio - Number
LocalRatio - Number

My question is how do I connect it to the tblGrants? If I add a GrantID as a FK that's not going to update the record in tblSplitRatio. I will prefer to enter all info relating to a grant in one table and go from there. But what I prefer and what I get to do to accomplish my goals are not the same. (LOL).

This split ratio also plays a vital role when we draw money against a grant from the Fed. For example, let's say we have an invoice from a vendor for $100 we can only draw $80 or $83 or $95 from Fed. So in my tblAPDetails, I would like this amount automatically calculated once I enter the invoiced amount of $100.

In tblAPDetails, as you know, the GrantID is tied to the FundID.

I am not sure how to go about calculating the net amount of $80 (80% of $100).

Can you give me some direction?

[Denburt]

Well if your only working with a 2 part ratio 80/20 per say and it always totals 100% then you could set a field in the tblGrants and use that for the Feds percentage your local percentage would always be the remainder. Then in the form you could use a calculated control to determine the amount that the feds would pay etc.

If you do want to use a second table for some reason then I don't see any reason why you couldn't select the Grant and store the ID in the split table then add the ratios

tblSplitRatio
SplitRatioID - PK (auto)
tblGrantID - FK
FedRatio - Number
LocalRatio - Number

[MNNovice]

Believe it or not, I was thinking the same thing. I will try the first method, ie, creating a FedRatio field in tblGrants and then have the LocalRatio automatically populated (100 - FedRatio). That will be a lot easier for me. Thanks.

I think I am getting close to be done with the Grant part. Now I have the next which is to record expenses for this grant. That's a little complicated but I need to worry one step at a time.

Thanks again. Until my next question.

[Denburt]

I did make some of the changes we talked about recently, I hope they meet your standards.

[MNNovice]

Denburt:

I calculated both fields separately and then re-do the Total field and it's working now. Nothing was wrong nowhere. For whatever reason the old total box wouldn't work. But thanks for the back up copy.

Thanks.