Listbox Help

[mumbles10]

Okay... I have attached a copy of a test DB... The form frmSysEnt is setup like I would like. However, as of now the buttons don't work.

Basically what I would like to see done is the ability to move the ENT between the listboxes... my problem is that I can't figure out the code to remove the ENT from the left listbox if they are already chosen from the combo on the right...

Please help... This is the last piece of my DB that I need to finish.

 

[hbrems]

Your SQL in the left listbox would show the entitlement records that don't exist for the current system. Something like this:

SELECT e.EntitlementID, e.EntitlementName
FROM Entitlement e
WHERE NOT EXISTS
(
   SELECT se.EntitlementID
   FROM SystemEntitlement se
   WHERE se.EntitlementID = e.EntitlementID
   AND se.SystemID = [SystemID]
)

Your SQL in the right listbox would show the entitlements that are present for the current system. Something like this:

SELECT e.EntitlementID, e.EntitlementName
FROM Entitlement e
INNER JOIN SystemEntitlement se
ON e.EntitlementID = se.EntitlementID
WHERE se.SystemID = [SystemID]

You'll need to replace the [SystemID] by the value from the combobox. So you will need to add some vba code to the combobox afterupdate event that creates the above SQL statements and adds them as row source of the listboxes. Something like this:

' Declare variable
dim sqlLeft as string
dim sqlRight as string

' Create sql statement (see above)
sqlLeft = "SELECT ... "
sqlLeft = sqlLeft & "WHERE se.SystemID = " & cboSystem

sqlRight = ...

' Set listbox rowsource
lstEntitlementLeft.RowSource = sqlLeft
lstEntitlementRight.RowSource = sqlRight

Then you need to write a SystemEntitlement record when you press the add button:

' Declare variable
dim sql as string

' Create sql statement
sql = "INSERT INTO SystemEntitlement...

' Execute statement
currentproject.execute sql

' Refresh the listboxes
lstEntitlementLeft.Requery
lstEntitlementRight.Requery

And delete when you click the remove button

' Declare variable
dim sql as string

' Create sql statement
sql = "DELETE * FROM SystemEntitlement WHERE...

' Execute statement
currentproject.execute sql

Hope this give you a clue of where to start.

PS. Remove the spaces in your field names: Entitlement Name => EntitlementName.

 

[mumbles10]

This is fantastic...

question though... when you say SQL for the right and Left Listbox... are you referring to including that into the rowsource?

Or are you saying that has to go into the afterupdate of the combo?

Thanks again...

 

[hbrems]

You would only populate the listboxes after the combobox is updated. So I'd leave the listbox default rowsource empty. In the afterupdate event of your combobox build the 2 sql statements and add them as row source. Any further questions just ask.

 

[mumbles10]

You would only populate the listboxes after the combobox is updated. So I'd leave the listbox default rowsource empty. In the afterupdate event of your combobox build the 2 sql statements and add them as row source. Any further questions just ask.

hbrems... this is what I have... no errors (thank goodness) but nothing is populating into the listboxes...

Private Sub cboSysEnt_AfterUpdate()
'declare variable
Dim SQLLeft As String
Dim SQLRight As String
'Create sql statement
SQLLeft = "SELECT tblEntitlements.EntitlementsID, tblEntitlements.[EntitlementName]" & _
"FROM tblEntitlements " & _
"WHERE Not EXISTS " & _
"SELECT tblSystemEntitlements.EntitlementsID " & _
"FROM tblSystemEntitlements " & _
"WHERE tblSystemEntitlements.EntitlementID = tblEntitlements.EntitlementID " & _
"AND tblSystemEntitlements.SystemID = [SystemID]"
SQLLeft = SQLLeft & "Where tblSystemEntitlements.SystemID = " & cboSysEnt
SQLRight = "SELECT tblEntitlements.EntitlementID, tblEntitlements.[EntitlementName] " & _
"FROM tblEntitlements " & _
"INNER JOIN tblSystemEntitlement " & _
"ON tblEntitlements.EntitlementID = tblSystemEntitlements.EntitlementID " & _
"WHERE tblSystemEntitlements.SystemID = [SystemID]"
SQLRight = SQLRight & "Where tblSystemEntitlements.SystemID = " & cboSysEnt

'Set Listbox Rowsource
lboAllEnt.RowSource = SQLLeft
lboCurrentEnt.RowSource = SQLRight

End Sub

 

[hbrems]

Let me get back to you in a little while. I'll drive home first.

 

[mumbles10]

Let me get back to you in a little while. I'll drive home first.

Great thanks... I really appreciate.

 

[hbrems]

Your sql statement would look like this:

SELECT tblEntitlements.EntitlementsID, tblEntitlements.[EntitlementName]
FROM tblEntitlements
WHERE Not EXISTS
SELECT tblSystemEntitlements.EntitlementsID
FROM tblSystemEntitlements
WHERE tblSystemEntitlements.EntitlementID = tblEntitlements.EntitlementID
AND tblSystemEntitlements.SystemID = [SystemID]"
Where tblSystemEntitlements.SystemID = " & cboSysEnt

Versus this:

SELECT e.EntitlementID, e.EntitlementName
FROM Entitlement e
WHERE NOT EXISTS
(
   SELECT se.EntitlementID
   FROM SystemEntitlement se
   WHERE se.EntitlementID = e.EntitlementID
   AND se.SystemID = [SystemID]
)

Main error seems to be that you have 2 WHERE keywords. Multiple criteria should be joined by the 'AND' or 'OR' keywords. Your WHERE clause needs to look like this:

WHERE tblSystemEntitlements.EntitlementID = tblEntitlements.EntitlementID
AND tblSystemEntitlements.SystemID = " & cboSysEnt

Also to make things easier. Notice how you can give temporary names to your tables in a SELECT statement. This makes your statement easier to read. This really works in code, I didn't do it for readability on the forums. For example:

SELECT c.FirstName, c.LastName, c.Address FROM Customer c

 

[mumbles10]

Your sql statement would look like this:

SELECT tblEntitlements.EntitlementsID, tblEntitlements.[EntitlementName]
FROM tblEntitlements
WHERE Not EXISTS
SELECT tblSystemEntitlements.EntitlementsID
FROM tblSystemEntitlements
WHERE tblSystemEntitlements.EntitlementID = tblEntitlements.EntitlementID
AND tblSystemEntitlements.SystemID = [SystemID]"
Where tblSystemEntitlements.SystemID = " & cboSysEnt

Versus this:

SELECT e.EntitlementID, e.EntitlementName
FROM Entitlement e
WHERE NOT EXISTS
(
   SELECT se.EntitlementID
   FROM SystemEntitlement se
   WHERE se.EntitlementID = e.EntitlementID
   AND se.SystemID = [SystemID]
)

Main error seems to be that you have 2 WHERE keywords. Multiple criteria should be joined by the 'AND' or 'OR' keywords. Your WHERE clause needs to look like this:

WHERE tblSystemEntitlements.EntitlementID = tblEntitlements.EntitlementID
AND tblSystemEntitlements.SystemID = " & cboSysEnt

Also to make things easier. Notice how you can give temporary names to your tables in a SELECT statement. This makes your statement easier to read. This really works in code, I didn't do it for readability on the forums. For example:

SELECT c.FirstName, c.LastName, c.Address FROM Customer c

Thanks Hbrems... here is what I amended the code to:


Private Sub cboSysEnt_AfterUpdate()
'declare variable
Dim SQLLeft As String
Dim SQLRight As String
'Create sql statement
SQLLeft = "SELECT e.EntitlementsID, e.[EntitlementName]" & _
"FROM Entitlements e " & _
"WHERE Not EXISTS " & _
"SELECT se.EntitlementsID " & _
"FROM SystemEntitlements se " & _
"WHERE se.EntitlementID = e.EntitlementID " & _
"AND se.SystemID = " & cboSysEnt

SQLRight = "SELECT e.EntitlementID, e.[EntitlementName] " & _
"FROM Entitlements e " & _
"INNER JOIN SystemEntitlements se " & _
"ON e.EntitlementID = se.EntitlementID " & _
"WHERE se.SystemID = " & cboSysEnt

'Set Listbox Rowsource
lboAllEnt.RowSource = SQLLeft
lboCurrentEnt.RowSource = SQLRight

End Sub

I still can't get the listboxes to populate... any thoughts?

 

[jdraw]

I have looked at your database and made a copy.
This copy has your listboxes and datafiles, but I have played with the entitlements assigned to Barclays (SystemId 5). I modified the appearance of the frmSysEnt a little.

I have included a startup form with a toggle button. If you toggle it ON you set a flag that will show some processing steps when you click buttons on frmSysEnt. If you toggle the flag OFF you won't see the msgboxes, but the processes are the same.

You can double click the listbox entry to move the Entitlement back and forth as well.

It's mdb in Acc2000 format.

Hope it's useful.

 

[mumbles10]

I have looked at your database and made a copy.
This copy has your listboxes and datafiles, but I have played with the entitlements assigned to Barclays (SystemId 5). I modified the appearance of the frmSysEnt a little.

I have included a startup form with a toggle button. If you toggle it ON you set a flag that will show some processing steps when you click buttons on frmSysEnt. If you toggle the flag OFF you won't see the msgboxes, but the processes are the same.

You can double click the listbox entry to move the Entitlement back and forth as well.

It's mdb in Acc2000 format.

Hope it's useful.

Wow... very impressive. Thanks jdraw. The more and more I use Access, its amazing how many different ways you can do the same task.

Thanks again.

 

[jdraw]

Glad to help.

 

[mumbles10]

Glad to help.

Jack, I actually sent you a private message. Let me know if you didn't get it.

I had another question for you.

Thanks.

 

[jdraw]

Just sent a PM. Had company and didn't check AWF.

 

[jdraw]

Here's another version with a Form for Entitlements.
I didn't change the name of the database, so be careful
with too many copies.
You'll like the latest "Z" entitlements.
In my database, I added a few entitlements, removed them.
Then added 2 more and they're still there.

There is a notice about tblSampleEnt --- you must change the autonumber to number (long integer) before using the new form.

I'll be online Wednesday.