Limiting Drop Down Value use

[mindbender]

Hello experts!



I have a subform in datasheet view on a main form. The subform contains a drop down field with multiple values. I am wondering if it is possible to limit the use of one of those values by account. Basically there are 5 values (contact info updated, initial DM Contact Made, sent email/left voicemail, opportunity create, Follow up call with DM) and I only want user to be able to select the value Opportunity created once per account. The others he can select as many times as he wants.

I attached a screen shot of what the form/subform looks like.

[ranman256]

Yes, the query that runs the combo box can be set depending on user. By dept or code?

In the form_ONLOAD()

select case txtDept
    Case "Acctg"
        CboFilter.rowsource = "qsAccting"
    Case "Collections"
         CboFilter.rowsource = "qsCollectg"
end select

[mindbender]

thanks for the reply. I may not have explained things as well as I could have or maybe I am misunderstanding your reply. This looks to limit the values the user sees based on criteria. What I am trying to do is only allow the user to select one of the drop down values once per account. Basically not let them enter 2 activities with the activity type of opportunity created on the same account

[orange]

In your table you can create a unique composite index from the activity type and account. This will prevent saving a duplicate record. You can include an error handler routine that checks for the duplicate condition and take appropriate action such that the user never sees the condition but is "politely" informed that that combination would create a duplicate.

There is a tutorial on composite indexes here.

Good luck.

[mindbender]

I like this idea. I do not see a spot to put the field value though. It looks like I can look at the combination of account and activity type but not account and activity type where is value is opportunity created

[mindbender]

Here is where I was going but it does not seem to work at all. Whenever I enter a value for a field not Combo18 I get this error "Procedure declaration does not match description of event or procedure having the same name" and also when I choose any value from the drop down it says it is a duplicate and then I have to close the app.

[orange]

I'm not following your last 2 posts.
Did you see the tutorial on unique composite indexes?

The unique composite index on fields activity type and account you set up within Access. When you attempt to store a duplicate record (same activity type and account) Access recognizes a violation of the unique index and will give an error. However, your error routine is checking for such an error and, instead of giving the error to the user, your routine will give a more friendly message.

I have no idea what/where combo18 fits in this???

You could post a copy of your database along with a description of what exactly you are trying to do, and instructions for how to repeat the error you are receiving.

[mindbender]

I uploaded the db. What I want to happen is if the user is trying to create an activity with an activity type of Lead Passed to Luigi, Lead Passed to Andrew F, or Lead for Earle to Work On and an activity with that Activity Type already exists for that account, pop a message saying something polite for them to stop it

[mindbender]

I had to delete a lot of stuff to get it under the size limit so I hope it all works.

Also to add to the above - for the Activity type values not listed , they can add as many as they like to each account ex Follow up with DM ect

[orange]

For your own benefit and reference.
All tables should have a Primary Key.
You should use a naming convention that does not allow embedded spaces nor special characters.
Use only alphanumeric and "_" (underscore) characters for names with Access. This will save you many syntax errors in future.

Do you have a 5-6 line overview in plain English of the business/business rules that this database is suppose to support? The database is intended to support a business, but we don't know that business nor processes.
Fix the PKs, and repost.

I don't know your experience level, but I see issues with tables and relationships. I recommend you work through this tutorial from RogersAccessLibrary to get a better understanding of concepts. You will learn more in 45 minutes by working through the tutorial that you will in 3 months of trial and error.

It also appears, based on your database, that you did not follow/understand the tutorial on unique composite indexes.

[mindbender]

I appreciate your comments - I inherited this database so I am doing the best with what I have. I will read through the attached information but as for the primary keys - The tblMain (accounts) has a primary key on it and the others do not - from what I can tell it is because there are multiple activities/contacts/ etc for each account and having a primary key on it would not all duplicate records (or more than 1 activity per account and I am not sure how to circumvent that with the existing data I have

I am relatively new to this as you can tell so any advice would be appreciated

[orange]

Yes I thought it might have been inherited. It isn't designed which is the basis of your problem.
My recommendation -- do the tutorial, then repost your concerns.
I'll help you with the database, but you need to understand some of the concepts you will experience in the tutorial. And you will need to understand what your database is suppose to do/support.

Business rules and/or a clear statement of the business is missing from your post.

First do the tutorial.
Good luck.

[mindbender]

Tutorial really showed me some things. So as a business case it would be a database to track activities by accounts. The repo would call the customer from the data provided and if necessary update the account data and/or add or update a contact and log an activity. The contact and the activities need to be tied back to the account.

some needed features - whenever the record (account and/or contact/and/or activity) is touched the last modified date under the account details section should reflect the date and time of the modification. Right now this is done by Before_Update on all the forms/subforms where [Last Modified Date] = Now() - therefore currently last modified date is a field in all of the tables.

Each account can have multiple contacts - 1-M
Each account can have multiple activities - 1-M

There is reporting in this database as well driven off of buttons that open forms and perform vba .I had to remove most of this to get the database under the size limit to attach.

The Save record button basically refreshes the screen to update the Last Modified Date field
Private Sub Save_Record_Click()
Me.Refresh
End Sub

The delete button calls a macro to do the delete

The weekly report button call the open report macro - basically opens a form where the rep enters some data and then on close runs some queries to generate the report

The Rep Report button does very similar to the weekly report button

There is vba to add a record
Private Sub Add_Record_Click()
If Me.NewRecord Then
Me!Account = Nz(DMax("Account", "tblMain"), 0) + 1
End If
End Sub


and on the activities subform to set the record to done
Private Sub Combo20_AfterUpdate()
Dim bAllow As Boolean
If Me.[Combo20] = "Done" Then
bAllow = False
Else
bAllow = True
End If
Me.AllowEdits = bAllow
Me.AllowDeletions = bAllow
End Sub


I think that is it. I will try and delete some of the data so I can attach the db in its entirety

[mindbender]

I also forgot to mention that the user shall not be allowed to enter more than 1 of the following activity types per account (all other activity types can be used as many times as they want per account:

Activity Type
Lead for Earle to Work On
Lead Passed to Luigi
Lead Passed to Jared
Lead Passed to Andrew F

[orange]

Did you build a data model (relationships window) for your own database based on what you learned in the tutorial? Can you post a jpg of your relationships window?
You will need to restructure your tables.

Also, you should use code tags when posting code. it helps readability.

For example, your

Private Sub Add_Record_Click()
If Me.NewRecord Then
Me!Account = Nz(DMax("Account", "tblMain"), 0) + 1
End If
End Sub

becomes

 
Private Sub Add_Record_Click()
If Me.NewRecord Then
   Me!Account = Nz(DMax("Account", "tblMain"), 0) + 1
End If
End Sub