Solved Check data from main form to subform (1 Viewer)

[lacampeona]

Hello experts,

Is it possible being on main form and tell to access to check if some value exist on subform and if the data exist prevent from user to enter this data again?
I try something with DCount and Lookup and I get lost in strings......I mean I manage to check only that if my last entered record contains that value. i need to check all the entries not only the last entered record.
I need to prevent user to enter specific value two times. all future entries can be what ever want the user but only specific value must be entered once.

Example
I am on form A and I want to enter some data that will be shown in subform datasheet view. lets say subformB

On my form A I want to enter data in field name Name.
lets say i want to enter data: ball, orange, apple,ball, kiwi, watermelon, kiwi, orange...

my problem is now: how to make that value ball can be entered only once? all other values can be enteted multiple times.

how to tell access that he has to check all the data on the subform? access must check all the records? if field name on subform contains value ball i want to trigger some message to the user that he cant enter value ball again becouse this value already contains record ID1.

i get lost in strings and ideas....i manage to check the last entered record but i need to check all the records and prevent entering special value.

Thanks in advance

 

[bob fitz]

Can you post a copy of your db

 

[lacampeona]

ok i will i need some time ...my database is very special and complex with a lot of forms and querys...i need to delete some things

 

[lacampeona]

Explanation
This is not all my database..i have a lot of more things. this is short version only to show what i mean and need.i delete a lot code
What i post before was only just a simple example what i need..now i will explain more deeper.

We have two important tables:
1. tblKolone2 ( this table is for formVnosKolone), in this table users will enter new data...
2. tblUporabaKolon ( this table will manage all the use that users will use fot the items), this table is for my frmKoloneUporabaSUB

Forms:
1.frmVnosKolone ( is for table tblKolone2), in this form user will enter name of the items and the details
2.frmKoloneUporabaSUB ( is for table tblUporabaKolon), here he will enter data for item that he will use
3.frmKoloneUporaba ( this form is my main from where user will start using the item), in tab 5 called empower...here inside is my form...frmUporeKolonePregled...this is form in datasheet view with all the data from table tblUporabaKolon
This is what i need
user is on form frmKoloneUporaba and he click the button empower...subform frmKoloneUporabaSUB opens..and he start entering data..

in field StatusKolone he can choose diferent statuses for use...
the important status that cant be choosen 2 times is (3)Sproscena...all other statuses can be choose more that one..multiple times..

4. frmUporeKolonePregled...this is form in datasheet view with all the data from table tblUporabaKolon

 

[MajP]

Private Sub StatusKoloneStanje_Enter()
  Dim criteria As String
  criteria = "[statuskolone] = '(3) Sproscena' AND [ID] = " & Me.ID
    Me.StatusKoloneStanje.InheritValueList = False
  If DCount("*", "tblUporabaKolon", criteria) > 0 Then
    'The below is key
    Me.StatusKoloneStanje.RowSource = "'(4) Neustrezna';'(5) Testitanje';'(6) Posiljanje';'(7) IzvenUporabe'"
  Else
     Me.StatusKoloneStanje.RowSource = "'(3) Sproscena';'(4) Neustrezna';'(5) Testitanje';'(6) Posiljanje';'(7) IzvenUporabe'"
  End If
End Sub

 

[MajP]

The above code is not really correct if you truly want to "inherit value list". In other words you would create the value list at the table level. Then if you updated the table it would update all pull downs in the application. To make this work with "inherit value list", the code would be more complicated. In the above if statement you would have to turn "inherit value list off" if > 0. Then use the removeitem method to remove (3). If equal 0 then turn inherit value list back on.

In my opinion "inherit value list" is waist of time, and just as easy to hard code the solution. Most people do not understand this anyways. The better solution is to make a simple table with your choices if you think the choices will ever change.

 

[lacampeona]

hellooo
thank you very much yesss that is what i need. it is first time i hear for "inherit value list". seems interesting.

i was also having in mind to make that statuses in a way that are automaticly inserted on that field...

if user choose yes that is status3 if choose 4 is another...

the code you wrote in which events i can put that? only on enter or I can i put that on click also ? or on before update?

thank you very much again

 

[MajP]

I do not know if there is a reason to call it from any other events. In fact this could cause a problem where it could blank out the selection when you need it. But if you fill the need, put it in a commons procedure and call it from any event.

Private Sub StatusKoloneStanje_Enter()
  LimitSelection
End Sub

Public Sub LimitSelection()
  Dim criteria As String
  criteria = "[statuskolone] = '(3) Sproscena' AND [ID] = " & Me.ID
    Me.StatusKoloneStanje.InheritValueList = False
  If DCount("*", "tblUporabaKolon", criteria) > 0 Then
    'The below is key
    Me.StatusKoloneStanje.RowSource = "'(4) Neustrezna';'(5) Testitanje';'(6) Posiljanje';'(7) IzvenUporabe'"
  Else
     Me.StatusKoloneStanje.RowSource = "'(3) Sproscena';'(4) Neustrezna';'(5) Testitanje';'(6) Posiljanje';'(7) IzvenUporabe'"
  End If
End Sub

So in this case you can call limitselection from other events.

"Inherit Value List" = 'yes' is the default setting of the combobox. I normally set it to "no" because I would never do it this way. If I think my value list is going to change over time, I will store the choices in a small table.

 

[lacampeona]

Thank you very much for your explanation and your help.
The list of values will never change, user will have only these 5 choises and i will disable the option that he cant enter new values.

Thank you again.
It is very good to see how you experts resolve everythink.
thank you

 

[MajP]

i will disable the option that he cant enter new values.

You can still add or change choices, but do this at the combobox rowsource instead of relying on doing this at the table.

The issue is subtle. In my code when I tried to tell it to remove choice (3) it would not work until I set "Inherit value list = NO". Because eventhough I told it to remove it, it would automatically inherit the complete list from the table. That is why this feature is confusing. It overrides the form and any code settings.

 

[lacampeona]

Ok.i see.i will take your advice. Thanks again for your explanation and help.
I will make it on combobox like you say.