[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help completing a program - OR is there, but AND needs to be implemented (VB6)

Posted on 2006-05-15
8
Medium Priority
?
142 Views
Last Modified: 2010-05-01
Hello,
I'm not able to finish this program, as I'm not too bright in this.
This is what it does. It has a list of locations (cellar, bathroom, garage, living room, ...) and each location has some different & same properties
(cellar and bathroom are both humid, but cellar is big and bathroom is small)
I've made a some checkboxes that need to be clicked to choose what properties you want, and then the locations should be given that have these properties (these are shown in a listbox).
The problem now is, I have made a mistake, when you choose more than 1 property, it should give only the locations that have these chosen properties in common. My own program shows all locations that have at least one of the 2 properties even if not in common.

I know I could solve this with the few properties that are in the test program, but if this should be scaled to hundred locations and 20 properties or so, I can not do it myself.

I have put the example (source and exe) of what I mean here: http://users.telenet.be/daydream/concept.zip

Any ideas, tips, code?
0
Comment
Question by:PCBOP
  • 4
  • 4
8 Comments
 

Author Comment

by:PCBOP
ID: 16681670
Project 1, 2 and 3 in the zip are the same project, but each stepup has better coding and cleaned up coding.

As you see, if you click all checkboxes you get a long list, which is false, as if you check that many properties there should not be any location that has all these properties. Only I don't know how to code this.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16685785
Hi,
I think the easiest way to do this would be to have a database as a back-end. That way, you can write a pretty simple query that will pull out all matching records. Is that an option? (it will be easily scalable)

Regards,

Rory
0
 

Author Comment

by:PCBOP
ID: 16689523
I have read some info about ADO and DataEnvironment Controll, but I'm clueless.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16689669
It's hard to be specific without knowing all the details of what you are trying to achieve, but for this example, you would want three tables in your database:
1. Locations - could just be ID, LocationName
2. Properties - ID, PropertyName
3. LocationProperties - LocationID, PropertyID
The third table is there because you have a many-to-many relationship - i.e. one property can apply to multiple locations and one location can have multiple properties.
Your query would then select everything from the LocationProperties table where the propertyID matches the checked items (you can join it to the Locations table to get the Location names rather than IDs.

Failing that, you could simply establish a Location class with 20 (or however many) properties, a Collection to hold the existing locations and iterate through the collection checking if the location items match all selected properties.

HTH

Rory
0
 

Author Comment

by:PCBOP
ID: 16689786
I had done the latter now, but am struggling with SQL commands to get the data from it.
I see that using the first method allows for quick and efforless scalability, I will try to make it that way.

The big problem for now is how do I query (use the right SQL commands), to which table, and how do I get the data.
0
 

Author Comment

by:PCBOP
ID: 16689967
What I mean is, I can access the records and read what records are what, and query the property, but I don't know how to show this in my form. I can't use a flexgrid, because the properties change, and I can't use a listbox...
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 16690002
You can't use SQL with the latter. What you can do is use a routine that:
1. Looks for the first checked property, then iterates through the collection and adds every matching location to another collection.
2. Looks through each other checked property, and for each one loops through the original locations collection and removes from the second collection any location that does not match. The items left in that second collection at the end can be added to the list.

For the database option, you will need to loop thorugh all the checkboxes and build up a string of values to retrieve, which you can then use in a SQL statement like "SELECT LocationName FROM Locations INNER JOIN LocationProperties ON Locations.ID = LocationProperties.LocationID WHERE LocationProperties.PropertyID IN (1, 4, 6, 9)"

You can then use ADO to execute this - e.g.:

Dim rst as New ADODB.Recordset
Dim cnn As New ADODB.Connection
With cnn
    .CursorLocation = adUseClient
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\databasename.mdb"
    .Open
End With
rst.Open  "SELECT LocationName FROM Locations INNER JOIN LocationProperties ON Locations.ID = LocationProperties.LocationID WHERE LocationProperties.PropertyID IN (1, 4, 6, 9)",Conn,adOpenDynamic,adLockOptimistic,adcmdText

You will also need some way of populating/maintaining this database so you will need some data entry forms too. Once you have it though, you could even create all your checkbox options at runtime using the available list of Properties.

Hopefully that gives you some ideas - it is probably worth getting hold of a good book on ADO/VB if you plan to go down this route, particularly if this is a commercial application!

HTH

Rory
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 16690028
Why can't you use a listbox?
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question