Hi,

I have a form which when opened, stores the logged in user ID from a login form and appends this to the table linked to the form. A user can only enter a record once for each date, but each date can be entered by more than one person (hope that makes sense).

The problem I have is this: I have set up a combo box to search for a record entered by a user so that they can amend an entry if need be. I only want users to be able to amend data that has previously been entered by themselves (identified by the logged in user ID), but when I try to search by date (and linked user ID) I just access the first record which could've been entered by any user.

I hope that makes sense? Any help would be much appreciated.

Andy

Hi Andy,

Your WHERE condition should look something like:

WHERE UserID = Forms!FormName.ComboboxName AND EntryDate = Forms!FormName.TextboxName

That's the idea anyway. You'll have to adapt it to your situation.

Hope that helps...

Hi Andy

You can restrict the entries in the Combo to show only records that have been entered by the currently logged in user. If you're still using the method we talked about earlier, then a User Defined Function would get the UserID to use to restrict the entries. The Combos rowsource would look like



The UDF would be along the lines of



You can then use the combo to let the current logged in user select/view/edit only th entries made by themselves

Hi Alan,

Yes I'm still using the method we talked about earlier but I'm struggling to get a grip on what I should be doing with your information.

I've managed to put all the other controls on my form and limited the input of data how I want it, but I'm just not sure where to insert the UDF.

Apologies for being such a novice at this.

Andy

Hi Andy,

No need to apologize. We've all been there before. Just stick with Alan, and I'm sure he will guide you to the right solution. Good luck!

Hi Andy

You can put the UDF in the same module you used for the Global variable if you want. The combo should have 2 columns, bound column set to 1, column widths set to 0, 3. You should then just see the dates of records entered by the current logged in user. Selecting one of the dates can then display the record that was entered on that date

Hi Alan,

Sorry about this, but everything I've tried this morning just fails to work.

I defined the UDF in the module that was used for the Global Variable, and also changed the row source of the combo. However, when clicking on the combo, it asks for the User ID to be entered in a parameter box.

I've attached a copy of what I'm trying to do if you would be so kind to point out where I'm going wrong?

Thanks again for all your help.

Andy

Hi Andy

Well, the good news is that you just need to change the rowsource of the combo to



which is pretty much as I had it above

Now, the bad news. I can see you've added lots of new fields to your table. Unfortunately, the table is now what's known as a flat file and contains a lot of repeating groups, meaning it's totally de-normalised. All of the fields you have with the same name but numbers/letters after them need to be in their own related tables, as do all the managers/support/screening/complaints etc etc fields.

The basis of a successful relational database is getting the structure correct before moving onto anything else. It's sort of like getting the foundations for a house right before you start adding the different floors. You can guarentee that a building with dodgy foundations will come tumbling down sooner rather than later, and that's pretty much the state your table structure is in at the moment.

I'd strongly recommend you take a couple of steps back and redesign your tables before you go any further. it will save you lots of headaches and problems as you develop your db if you do. It's probably no consolation to you at the moment, but trust me, you're not on your own. It's something we all do when first starting out designing databases

I've included some links below for you that should give you the info you need to restructure. Have a read through then come up with your new structure which you can post in the Tables and rels. forum for someone to take a look at for you to make sure you're on the right track.

Good luck with your project

• Database planning tutorial Part I
• Database planning tutorial Part II
• argeedblu's FAQ on Design Strategies
• Noah's FAQ on Normalization
• Jerry's FAQ on Normalization
• GroverParkGeorge's PDF on Normalization
• A Discussion on Storing Calculated Values and Audit Trails
• Jerry’s FAQ on Fully Qualified Naming
• Jerry's FAQ on Glossary
• Danny's FAQ on Reserved Words
• Autonumbers - What They Are / Are Not
• Primary Keys - Natural vs Surrogate
• Naming Conventions - Hungarian Notation
• Crystal's ACCESS BASICS
• Database Answers (free models)
• MSKB 283878: Description of the database normalization basics
• The Evils of Lookup Fields in Tables

Hi Alan,

Sorry it's taken so long to reply to you. Thank you for all the great advice. I have taken this on board and indeed taken a backwards step and redesigned the tables in my database project so that data is more logically organised. Thanks for pointing me in the right direction.

On the note of only allowing a user to search for a record that they have entered, this still does not seem to want to work as it only finds the first record entered on that date, regardless of who entered it.

Not sure whether you can help?

Thanks again.

Andy

Hi Andy

Not sure where you are with it now ? Have you changed table or field names anywhere during your re-structure - which would also mean the SQL of the rowsource of the combo would need changing to reflect any name changes?

I've just downloaded your demo from 12/1 attached to your post above and tried it again, and making the changes to the combo's RowSource - ie SELECT RecID, EnteredDate FROM EfficiencyMaster WHERE UserID = lngLoggedInUserID() - works fine for me, only showing the dates in the combo that were entered by the logged in user.

If you can upload your current version (again zipped to less than 500k and no sensitive data) I'll take another look for you............

Hi Alan,

The version that I'm with at the moment is tool large to upload so I've stripped one down and attached it with a bare minimum number of fields.

You'll see from the data entered that there are entries for UserID 1 & 2 on 2 dates. However, when selecting a date from the combo, it just brings up the first of the 2 regardless of who is logged in?

Any guidance would be much appreciated.

Andy

Hi Andy

The problem was that you were searching for a date which is the value returned by the second column of the combo, but the bound column of the combo is set to 1 meaning the value of the combo is actually the RecID and not the date. That's how it's supposed to be by the way - it's much more efficient using the primary key to search for records rather than the date.

Anyway, what all that means is if you change the name of your search combo to cboSearch (instead of just Search), delete the code you have in the After Update event of the combo and replace it with



you should be good to go.

Also, don't know whether or not you've finished your restructure, but you still have a lot of repeating groups in your table that need to be moved to their own related table(s)