Restricting Data Entry

[MNNovice]

Denburt:

Yes things are much clearer now. Following your instructions I was able to create the tblGrantAPExp. However, I must have done something incorrect. As I try to select a Fund number on this form (frmGrantAPExp) , I can see the Fund number but the GrantID is displayed as ID and NOT a grant number. Therefore, it's not easy to select which fund I want. Because eg, Fund 702 has more than one Grant number associated with it. If I can only see the GrantID how do I pick the correct Fund No 702?

2. In the form frmGrantAPExp, I need to have both the grant number and fund number displayed. Is there a way to do this? Because I no longer have GrantID or FundID available on this form rather GrantFundID.

Thanks.

[MNNovice]

Birdman:

I understand your cautionary notes. Thank you.

However, I don't think I understand what you meant when you said,

To create a relationship between a Grant and a Fund, I would have my user select a Grant from one combobox, and a Fund from a combobox. Now user presses a cmdbutton captioned "Create Relationship", and VBA code does the rest. If the link already exists, the cmdbutton would be captioned "Delete Relationship"

Can you elaborate on this? I am not clear on the location of the combo boxes and command button you are referring to. Also, I don't know VBA codes. Thanks.

[OldBirdman]

Without VBA coding I'm lost so I'll let someone else pick up that question.

To elaborate on my comments -
I believe that users should work in forms, not in the tables themselves. This way the programmer has control over what is allowed, by whom, and when. This is my concept of the division between programmer/developer and the end user.

So, as I see it, you will need 2 forms, one for updating tblFunds and the other for updating tblGrants. Update here is to add records or modify (edit) records. You seem to have that OK, based on your last post.

Now I would have a 3rd form for creating the links between Grants and Funds. For the moment, I'll have 3 controls on this form. I'll call them cboFunds, cboGrants, and cmdAction.

cboFunds will have a query for Control Source. This query, as a minimum, would have tblFunds.FundID and tblFunds.FundNo , with an 'ORDER BY' appropriate to this use. ORDER BY could be a date field, descending, the FundNo ascending, or whatever. The first column, containing the FundID, would be hidden, but cboFunds would be bound to column 1.

cboGrants would be set up the same as cboFunds.

Between these 2 controls would be cmdAction, a command button. It would have a caption of "Create Association". When pressed, it would -
1) Check that both comboboxes had a selected item
2) Create a new record for tblFundGrants
3) Set both comboboxes to initial state
4) Notify user of success or failure
a) One or both comboboxes not selected
b) Duplicate Association - correct selected
c) New Association created
d)

Denbert or FishVal- can you help with a non-VBA solution here.

[MNNovice]

Birdman:

Thank you for your detailed elaboration. It makes sense. But without knowing VBA it's of no use for me. I do appreciate your time and effort.

I will wait for Denburt's response.

Thanks again.

[Denburt]

As I try to select a Fund number on this form (frmGrantAPExp) , I can see the Fund number but the GrantID is displayed as ID and NOT a grant number.

Ok in the field that you are using to select the Fund/Grant your rowsource should be a query you can add all three tables to this query and use the fields needed to display the Grant number or description etc. making sure that the ID used for tracking purposes is from the tblGrantFund (usually the first field. the field should be bound to that column and you can set that column size to 0 so it isn't seen.

2. In the form frmGrantAPExp, I need to have both the grant number and fund number displayed. Is there a way to do this? Because I no longer have GrantID or FundID available on this form rather GrantFundID.

On the form add a field for the Grant number and reference the Fund Field Column 2 or whatever column it is in. The controlsource for the field Grant would be. =cboFund.column (2) and it would not be editable. Note:the columns start with 0 as a reference

I usually use a light shade of yellow for all of my non editable fields and make a note somewhere on the form.

[Denburt]

Now I would have a 3rd form for creating the links between Grants and Funds. For the moment, I'll have 3 controls on this form. I'll call them cboFunds, cboGrants, and cmdAction.

Leave out the button you just need the two fields, the forms recordsource would be tblFundGrants and each combo box would have a controlsource for the item in question. I would probably use Continuous forms or set it to datasheet so I can have a better view of what is already associated with what. Make sure your first column is using the ID in question column size 0 etc. and you should be all set

[MNNovice]

Denburt:

Thanks again.

Ok in the field that you are using to select the Fund/Grant your rowsource should be a query you can add all three tables to this query and use the fields needed to display the Grant number or description etc. making sure that the ID used for tracking purposes is from the tblGrantFund (usually the first field. the field should be bound to that column and you can set that column size to 0 so it isn't seen.

Aha! now I know I need to do a query first and then use it in the control source (instead of using tblGrantFund) of the fromGrantAPExp.

I shall try now and keep you posted. Many many many thanks.

[Denburt]

;) My pleasure! (did I hit 20 char yet?) lol

[Denburt]

Oh and thanks again for the help Fish and Birdman I know that my communication skills aren't always as good as they could be.

[MNNovice]

Denburt:

I succeeded but partially.

Ok in the field that you are using to select the Fund/Grant your rowsource should be a query you can add all three tables to this query and use the fields needed to display the Grant number or description etc. making sure that the ID used for tracking purposes is from the tblGrantFund (usually the first field. the field should be bound to that column and you can set that column size to 0 so it isn't seen.

I got this part working. On the form frmGrantAPExp cboFund is linked to qryGrantFund. I can see the fund list along with the associated Grant numbers. Really cooooooool.

But when I added cboFund.Column( 2) in the control source of cboGrant on the same form. I get no data. I expected to get cboGrant automatically populated as soon as data selected for cboFund.

qryGrantFund has these fields
GrantFundID
GrantNo (this is a text field)
FundNo (text)

Column count 3 and column widths are 0";1";1";
What am I doing wrong?

You are a good teacher. I find your communication rather easy to understand. Thanks again.

ps: I have the form in datasheet view.

[OldBirdman]

Thanks Denburt for taking over. When I first started with Access, I had real problems with a mix of VBA and Macros, and elected to go with VBA. Now I can't do anything without it.

[Denburt]

Yeah BirdMan I do the same thing I used to be real bad but now I try to take a step back and see if I can simplify things before I start my VBA typing, in the long run it helps save me some time (usually). I once worked with a guy and he said (jokingly) that if I needed to add 2+2 I would sit down and write a routine for it. Your quite welcome BTW.

Ok MNNovice

But when I added cboFund.Column( 2) in the control source of cboGrant on the same form. I get no data. I expected to get cboGrant automatically populated as soon as data selected for cboFund.

qryGrantFund has these fields
GrantFundID
GrantNo (this is a text field)
FundNo (text)

well if grant is the second column then a slight change is in order however you should have something or did you leave off the = sign?

The ID field is column(0)
The next column (grant I think) would be:
=cboFund.Column (1)

Also thank you for the nice comments.

[MNNovice]

No I didn't forget the equal sign and it's still not working. Thanks.

[Denburt]

Well according to everything I posted it should be working. I just verified it on this end. Check the field names and look everything over carefully and if it still looks o.K. to you then (if it's not to big or sensitive in nature) you could upload it and I can look it over for you.

[MNNovice]

Denburt:

Many thanks. Attached is the DB. Please remove it from this site once you find out where I goofed up. I would like to learn from my mistakes. If you keep a copy for yourself, that will be okay. It perhaps will help me with future questions as I build it.

Thanks.