Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 25th, 2006, 10:20 AM
Registered User
 
Join Date: Apr 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default listbox problem

hi i am working in the audit database and i am stuck in one point, in my database i have three tables as follows.
1. audit with fields audit_id,audit_title etc (in this table i register the new audit for the company)
2.standards with fields standard_id, standard (this is the static table with predefined standards like national, clinical, non-clinical etc)
3.audit_standards with fields audstandid, audit_it , standard_id (this is the table in wich i store the standards against order selected by the user. there can be more than one standards against one order.
for this i have list box bounded to my standard table. user selects one or more standard from the list and the values are inserted in standard_detail table against that audit. until here it works fine.

the problem is with the updating of the audit when user reopen that audit. i can't find the way how to populate my list with contents of the standard table (ie with all standards) and highlight those standards already selected by the user which are in audit_standard table, i mean if list is loaded the standards is audit_standard shold already be selected in the list and the user shold be able to deselect them or add the new standard against the audit.

kindest regards
ali

 
Old April 26th, 2006, 07:21 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It might be easier you use a series of checkboxes than a list, however you should be able to do it as follows:

Populate the list with the standards (set recordsource = tblStandards)
Set the form's On Current event to code that creates a query showing the standards associated with the audit
Turn that query into a recordsource, loop through it and set the standards as selected within the list box
Set the after update event (might have to be a different event, not 100% sure) of the list box to code that executes an update on the table depending on what standard was selected (append query) or deselected (delete query)

Personally I prefer checkboxes, much easier to design around.

 
Old April 26th, 2006, 09:44 AM
Registered User
 
Join Date: Apr 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for reply kindler
actually i am new with access and vba can you help me how to use the series of check boxes. i am working on unbound forms the reason is that i have to work with almost 17 tables in one form so i can't bind it. in the form ther are many options user has to select. there are two scenarios
1. user should be able to select only one option (for this i am using option group with check boxes)
2. user should be able to select more than one options (for this i am using multi select list box unfortunatily couldn't find the way to use series of check boxes)
this is working fine user can register the audit. but the problem is with the retreivel of data to update as i mentioned in my previous post.

i have tried you sujjestion as well but if i create new query on form current event and turn the query into record source to list box it replaces the previous recordsourse (tblstandard). if you can please send me a bit of code for this. or guide me how to use serious of check boxes.
kindest regards
ali

 
Old April 26th, 2006, 10:59 AM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Instead of using the option group, create multiple check box controls separate from each other. That gets around the problem of the user only being able to select one at a time.

Also, for the listbox, I did not mean that you should turn that query into the listbox's record source, I actually used the wrong word. I meant to say recordset. Upon going to write a code sample for you and brushing up on how bloody useless MS's listbox is.. I'm inclined to say it can't be done that way since there doesn't seem to be any method or alterable property to automate selection of the right items.

 
Old April 27th, 2006, 09:58 AM
Registered User
 
Join Date: Apr 2006
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks for your help kindler , yes you are right, there is no way to select the right items in the list for these type of scenarios, i will work on check boxes now or i will provide the user with two lists from which he would be able to copy the data from one list to second list. any how bundle of thanks for your kind help.
Ali





Similar Threads
Thread Thread Starter Forum Replies Last Post
Listbox Problem Brendan Bartley Access 4 November 26th, 2007 01:34 PM
ListBox problem aliirfan84 ASP.NET 2.0 Professional 5 May 28th, 2007 08:39 AM
ListBox Problem spacy ASP.NET 1.x and 2.0 Application Design 2 October 18th, 2004 05:45 AM
A ListBox problem csc820203 C# 6 June 15th, 2004 06:45 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.