Combo Box - to Exclude data insert in table (1 Viewer)

[crovella]

Good morning everyone,
I have to combine groups to account,
then when account is a group in next time, list or combobox,
this group mustn't to be in that list.
what is the solution :banghead:? SQL or VB
I append file

Thank you
Paolo

 

[Uncle Gizmo]

Good morning everyone,
I have to combine groups to account,
then when account is a group in next time, list or combobox,
this group mustn't to be in that list.
what is the solution :banghead:? SQL or VB
I append file

Thank you
Paolo

The ID fields of your tables are set-up as Text, should be auto numbers.

 

[crovella]

I can see none has a solution

 

[crovella]

I wrote down a solution but I don't know if it's right way.
It's working, but when will there be many records ?
what do you think about this?
thank you very much

On Error GoTo ErrComando5_Click
Dim sqlList As String
Dim strID, stMin, stMax As String
Dim ii
stMin = (DMin("[IDGroups]", "tblAutorisation", "[IDAccount]=Forms![frmAbbinamenti]![cboAccount]"))
stMax = DMax("[IDGroups]", "tblAutorisation", "[IDAccount]=Forms![frmAbbinamenti]![cboAccount]")

For ii = stMin To stMax
strID = DLookup("[IDGroups]", "tblAutorisation", "[IDAccount]=Forms![frmAbbinamenti]![cboAccount] And [IDGroups]=" & ii) & " And (ID)<>" & strID & "0"
Next ii

If IsNull(DMin("[IDGroups]", "tblAutorisation", "[IDAccount]=Forms![frmAbbinamenti]![cboAccount]")) Then
sqlList = "SELECT ID, Sigla, Description FROM tblGroups;"
Else
sqlList = "SELECT ID, Sigla, Description FROM tblGroups WHERE ((ID)<>" & strID & ");"
End If

Me.lstGroups.RowSource = sqlList
Me.Refresh

 

[Mihail]

I tweaked a bit your DB (New names for IDs and I added relationships between your tables).
Just I don't understand what you are trying to do.
Can you explain IN WORDS ?
Something like this: When I click on button (ButtonName) I like to see.... ?????
Better is to insert for now some labels with explanations in your form.

 

[crovella]

sorry if my english is bad

I would like to create groups with accounts, because I want to delimitate records to Groups

I made a function, what do you think



Function AccountGroups()
On Error GoTo ErrorHandler

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim varIDGruppo As Variant
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAccountPrivilegi", dbOpenDynaset, dbAppendOnly)
'make sure a selection has been made
If Me.lsbGruppi.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Groups"
Exit Function
End If

'add selected value(s) to table
Set ctl = Me.lsbGruppi
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!IDGruppoSub = ctl.ItemData(varItem)
rs!IDAccount = Me.txtAccount
rs.Update
Next varItem

DoCmd.RunSQL "UPDATE tblAccountPrivilegi INNER JOIN tblGruppoSub ON tblAccountPrivilegi.IDGruppoSub = tblGruppoSub.ID SET tblAccountPrivilegi.IDGruppo = [tblGruppoSub].[IDGruppo];"
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Function
ErrorHandler:
Select Case err
Case Else
'MsgBox err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select


Me.Refresh
DoCmd.RefreshRecord
DoCmd.RunCommand acCmdRefresh + acCmdSynchronizeNow
End Function

 

[Mihail]

Still out of border of my understanding.
I understand very well your English (and I'm proud ).
But still I don't understand what you are looking for.

I'd asked you to explain the problem in human words.
Instead you show me a procedure in VBA language.

You ask me what I think.
I think nothing.
Your question is like this:
I know to swim. What you think ?
I think that this can be OK if you swim in a lake but can be very dangerous if you swim in Nil river.

So, for the last time:
What you have and what you are looking for ? In humans words.