Listbox Troubles (1 Viewer)

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
I am having trouble with a listbox populating. I have attached a copy of the form that is giving me issues. It has some code in the AfterUpdate of the combobox. The form in question is frmSysEnt.

Any help would be appreciated.

Also, I am rather new at programming in VBA and SQL so please be a critical as possible.

Thanks.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
Well, using

Debug.Print SQLLeft

produced this:

SELECT tblEntitlements.EntitlementsID, tblEntitlements.EntitlementNameFROM tblEntitlementsWHERE Not EXISTS SELECT tblSystemEntitlements.EntitlementsIDFROM tblSystementEntitlementsWHERE tblSystemEntitlements.EntitlementID = tblEntitlements.EntitlementIDAND tblSystemEntitlements.SystemID = Bank of America Rescap

Which clearly is missing some spaces AND has the wrong data being used for SystemID. You would need to change the bound column of the combo from 2 to 1 or refer to it using .Column(0)

And the results from the right is about the same:
SELECT tblEntitlements.EntitlementID, tblEntitlements.EntitlementNameFROM tblEntitlementsINNER JOIN tblSystemEntitlementsON tblEntitlements.EntitlementID = tblSystemEntitlements.EntitlementIDWHERE tblSystemEntitlements.SystemID = Bank of America Rescap
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
Well, using

Debug.Print SQLLeft

produced this:



Which clearly is missing some spaces AND has the wrong data being used for SystemID. You would need to change the bound column of the combo from 2 to 1 or refer to it using .Column(0)

And the results from the right is about the same:

Thanks Bob... I amended the SQL and changed the bound column. Still nothing populates the listbox.
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
Well, using

Debug.Print SQLLeft

produced this:



Which clearly is missing some spaces AND has the wrong data being used for SystemID. You would need to change the bound column of the combo from 2 to 1 or refer to it using .Column(0)

And the results from the right is about the same:

Or if you have an easier way of doing this... I would be all ears.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
Okay, your SQL was a bit off and the listboxes didn't have their RowSource Type set to Table/Query. I THINK I got it to come out the way you wanted it and now you will have to work on the code to add the entitlement to the system using your buttons to move between listboxes (an append/update query or delete query for removing would be what you will need).

See attached.
 

Attachments

  • BPSv2_RevBL_29Mar2011.zip
    37 KB · Views: 87

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
Okay, your SQL was a bit off and the listboxes didn't have their RowSource Type set to Table/Query. I THINK I got it to come out the way you wanted it and now you will have to work on the code to add the entitlement to the system using your buttons to move between listboxes (an append/update query or delete query for removing would be what you will need).

See attached.

Bob,

Thank you very much... I think I am almost there... it looks like its doubling up some Entitlements on the left. Again, I am no advanced person in VBA or SQL... but it looks like its saying to populate the LEFT table with all Entitlements not in the combo selection. That is true that I want it to do that, however, each system can have the same entitlements.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
The query SHOULD have selected all that do not have the system ID from the combo.

What are some examples which are doubling up, if I can ask, so I can look at it and see if it is doing it to me.
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
The query SHOULD have selected all that do not have the system ID from the combo.

What are some examples which are doubling up, if I can ask, so I can look at it and see if it is doing it to me.

Yeah... the 2nd combo selection... the 2nd value on the right listbox... Image Access. It shows up in the left listbox. Multiple times. I know many systems have that same entitlement. It seems like it showing all the entitlements in tblSystemEntitlements not tblEntitlements.
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
The query SHOULD have selected all that do not have the system ID from the combo.

What are some examples which are doubling up, if I can ask, so I can look at it and see if it is doing it to me.


Again, thanks for taking the time.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
Okay, I got it working (but to make it easier I went with two saved queries for the left listbox. I created one which pulls the EntitlementID's for the combo selection and then we use that to exclude in the second query. Hopefully you can see what I did. Also go here if you want a more thorough explanation (the example in that has to do with using a table to exclude but it works with a query too because you can basically use a query anywhere you use a table).
 

Attachments

  • BPSv2_Rev2BL_29Mar2011.zip
    36.5 KB · Views: 88

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
Okay, I got it working (but to make it easier I went with two saved queries for the left listbox. I created one which pulls the EntitlementID's for the combo selection and then we use that to exclude in the second query. Hopefully you can see what I did. Also go here if you want a more thorough explanation (the example in that has to do with using a table to exclude but it works with a query too because you can basically use a query anywhere you use a table).

Bob it works great... Last question... if I were to add an Entitlement to the actual underlying table... how would I update the listbox?

I tested it and it didn't update.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
You should be able to, in the other form's After Update event use

Forms!sysEnt.lboAllEnt.Requery
Forms!sysEnt.lboCurrentEnt.Requery
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
You should be able to, in the other form's After Update event use

Forms!sysEnt.lboAllEnt.Requery
Forms!sysEnt.lboCurrentEnt.Requery


Thanks... but it keeps saying can't find form frmSysEnt.

I'm stumped.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
Sorry, I think your error should be telling you it can't find form sysEnt because I left off the frm part. Check your code you used and if you just copied and pasted you would need to add the frm part of the name frmsysEnt.
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
Sorry, I think your error should be telling you it can't find form sysEnt because I left off the frm part. Check your code you used and if you just copied and pasted you would need to add the frm part of the name frmsysEnt.


I was one step ahead... (for once). I added the correct form name and it didn't work.
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
Do you have a new form in there? You didn't have one for entitlements before when you posted this. All you had was the frmSysEnt and the frmSystems one.
 

mumbles10

Registered User.
Local time
Today, 18:46
Joined
Feb 18, 2011
Messages
66
I was one step ahead... (for once). I added the correct form name and it didn't work.


Yeah... its bound to the tblEntitlements. the form just has the EntitlementID (which is an Autonumber) and the Entitlement Name... I was just trying to create a new record from it. Should I be doing something different?
 

boblarson

Smeghead
Local time
Today, 15:46
Joined
Jan 12, 2001
Messages
32,059
 

Attachments

  • mumbles10qrychanges.png
    mumbles10qrychanges.png
    25.9 KB · Views: 158

Users who are viewing this thread

Top Bottom