Visual Basic Database

[jalbes]

I needed to increase my productivity and relieve a lot of headaches with the buildings that I lease, So I decided to make a program to do this for me, so I began to learn some VB from tutorials, etc...

But now I'm stuck!

I'm going to have a search form that will have my two Combo Boxes on it. One Combo box will list only "Addresses", and the other cboBox will list "Unit Numbers". (Each Address has its own specific Unit Number. But no unit number is the same). And I want the cboboxes to access a column within an Access '97 table with all the Address and unit numbers listed.

When I click on "find address" cmd button, a new form will pull up all the recordset information about that address. And when I click on "find Unit#" cmd button, the same form will pop up all the recordset information about that unit.

(the reason for this is... (1.) I know the Address, but not the Unit Number. and (2) I know the Unit Number, but not the Address. So when I recieve a call saying that unit 532 has a leaky toilet, I can search by unit # and get the Address of that Unit #, as well as the current tenant, previous tenant, # of bedrooms, heating type, Notes, etc... And the same with searching by address).

So how do I get the address column from my table to list on a combo box, as well as the unit# column from my table to list on a separate combo box all on the same form?

Any references or tutorials would be great as I'm just a newby.

Thanx
Jalbes

[jalbes]

I think that I will need a SQL thread somewhere in the combo box to access the database to list the specified column within the specified table. Does this sound right?

Please let me know!

[HackinCowboy]

You'll want to do something like this:

comboBox = "123 Buck Road"

sAddress = comboBox

Set recordsetAddress = dbLeases.OpenRecordset("SELECT * FROM Leases WHERE ([Address] Like "*sAddress*")")

What this will do is query your Table called Leases and gather every record where the address CONTAINS "123 Buck Road"
The new recordset is called recordsetAddress. Is this clear?

[jalbes]

I know, I'm just another annoying newby, But now I'm just dieing to get this program completed. I've been tackling every tutorial that I can find, but nothing seems to help.

If you could describe the above post in a bit more detail (as simple as it may seem to you...) it would be greatly appreciated.

I posted the same thread in another forum at vb-world and here's what Vince told me to do:
-------------------------------------------------------------------------------
Hmmm...
You have read the tutorials, so I am going to assume you are using code to populate the combo boxes.

If this is the case then do the following :

Address
1) Clear the combo boxes and the Arrays (see below)
2) Open connection (for ADO)
3) Open Recordset of required info/data
4) Loop through and add the information to an array (Information being the ID of the Address and other info you need to display on selecting) (public to the form) and also add just the Address to the combo box.

Units
As above but with the Unit number and a different Array

This gives you two arrays on the form with the IDs of Address and Units. Also you have two filled combo boxes.

Searching
Here you need to build you SQL statement to select all the columns you need returned and from the tables etc...
THEN if either or both of your combo boxes have something selected (see their listindex>-1 property) add on a Where clause and depending whether the combo box has place AddressID=" & aryAddress(comboAddress.listindex) or UnitID=" & aryUnits(comboUnits.listindex) which should place the id of the selected address or unit into the SQL statement. Then use the SQL statement to display however you are displaying the results.

Confused .. lemme know and I'll try to explain it better

Vince


__________________
MS Access (Advanced 97/Advanced 2k)
VB6 Pro (Beginner/intermediate) (Mostly Beginner)
Some Assembly lang
Some SQL


-------------------------------------------------------------------------------

Can someone decypher that for me too. Like whats an array in the first place? LOL

Thanx
Shaun

[HackinCowboy]

No prob, I'll try and explain this a little better. First thing you want to do is make a connection between your program and your database. You'll want to add a Reference to Microsoft DAO Library. You can do this by clicking Project > References. Once you've added the reference you want to create a database object to "store" the opened database. We can do all the assigning in the form load and all the declaring in a module. If you're going to be using multiple forms you might want to use a module.
Public dbLeases As Database (declare in module)
Set dbLeases = OpenDatabase(App.Path & "\Leases.mdb") (Form Load)
In those statements what we're doing is first saying that dbLeases is a Database. Then we're assigning dbLeases to an actual database called Leases.mdb. Now we have our connection to a database.
From there we want to create our recordset. A recordset is basically like a link to a Table in a database. Here's how we do this:
Public rsLeases As Recordset (declare in module)
Set rsLeases = dbLeases.OpenRecordset("NameOfTable") (Form Load)
This is basically all you need. When you want to reference any data in your database (Leases.mdb) you can use rsLeases. To get data from a specific field in a table use this: txtAddress = rsLeases("Address"). What that will do is look in the field Address and whatever value is there will be assigned to txtAddress. If you want to query data you can do it like this:
Set rsLeases = dbLeases.OpenRecordset("SQL QUERY HERE")

Well I hope this helps. These are basically all the commands you need to do what you're looking for. If you need an even better explaination let me know. If I get time I could write a sample program for ya. ~Good Luck

[jalbes]

I'm tinkering along with the description you gave me in the above post, but I'm a little confused on where everything goes. Should the loading of the database be done in the form? Like instead of starting off with...
Private Sub frmBlah()
I use
Public dbTenant As Database (declare in module)
Set dbTenant = OpenDatabase(App.Path & "\TurretDB.mdb") (Form Load)

Also, I will be having multiple forms. Only 2. So how do I set up a module.

Thanx again for all you help HackinCowboy

Shaun

[HackinCowboy]

Find what form will be loading first. In that form, go to the sub: Private Sub Form_Load() --
In that sub you'll want to Set your database and recordsets:
Set dbTenant = OpenDatabase(App.Path & "\TurretDB.mdb")
Set rsLeases = dbTenant.OpenRecordset("NameOfTable")

Module:
To add a module go to Project => Add Module
Once you've added the module you'll want to type this:
Option Explicit

Public dbTenant As Database
Public rsLeases As Recordset

What that module will do is allow you to use those variables (dbTenant & rsLeases) globally.

Hope this helps.
~HC

[jalbes]

But what command do I give my combo box so it will select all the addresses out of my Address column out of the Tenant table from my TurretDB.mdb file?

[HackinCowboy]

In the Form Load, you'll want to loop through your entire recordset and add each address. It should be something like this:

Dim I As Integer
I = 0
While Not rsTenant.EOF
Combo1.AddItem (rsTenant("Address"), i)
I = I + 1
rsTenant.moveNext
Loop

You want "I" so you have a counter. When you AddItem to a combo box you need a string and an integer. The string is what will be displayed in the box and the integer is a way to reference it.

[jalbes]

When I tried your approach using the following:
Dim I As Integer
I = 0
While Not rsTenant.EOF
CboAddress.AddItem (rsTenant("Address"), I)
I = I + 1
rsTenant.moveNext
Loop

I had an error saying something like "expected =" somewhere between ("Address"),I. I understand what you were saying by the string is what will be displayed in the box and the integer is a way to reference it. So whats wrong.

Thanx

[HackinCowboy]

Instead of CboAddress.AddItem (rsTenant("Address"), I) use this:
CboAddress.AddItem (rsTenant("Address"))
I forgot with Combo boxes you don't need an index since it automatically assigns one. So you won't need to declare "I" either.

[jalbes]

I really hate wasting your time but I just don't get this. When does the Basic part of "Visual Basic" happen. LOL

But seriously, I've been at this damn program for about a month, doing every tutorial I can find, reading every last snippet of code that I can decypher and understand. But this whole populating a combo box and making a search button is damn near impossible for me to get a grip of.

Do you happen to have a good online reference for this thing, or an existing source code that I can play around with, or anything?

You've beeen a great help HackinCowboy, I really do appreciate all of your help and specially your patience.

Maybe I'll take a course this summer... LOL.

Shaun

[HackinCowboy]

I'm gonna go ahead and write you a little sample program. I'll e-mail it to you in a little while. Hopefully you'll be able to mimic what I've coded. Don't give up though. We'll get this eventually.
~HC