Visual Basic and Access

[jalbes]

I began this project a while ago, but I never finished it. Mainly because I'm a programming Newby, but anyway, I've decided to finish it for my wifes parents.

They rent out apartments and a few townhouses and they're always swamped with paper work, so I suggested to them to get a computer, lol, so they can merge leases, and keep upto date with lease renewals, etc...

Long storey short, they don't use the computer because they have to make a database and they don't know how. So thats where I came to the rescue, lol, and is where this project comes in. I had the "Old" version of the program working but it wasn't very practical. So I attempted a "New" version, but I'm so lost in my mess of program code, it's not funny.

they're pretty straight forward if you take a moment to download and reveiw them.

Thanx for help,
jalbes

[mosquito]

hmm, been messing around with it a bit. I'll continue later today

I hope the code gives you some clues.

A few tips though:

• Remove unused components and references
• Always destroy variables, especially recordsets and connections
• Errorhandling

[jalbes]

Did I mention That I'm a newby! lol

Thanx tho, I looked over your layout and I like it. It allows you to see the whole database which is a very nice feature, and the search by UNIT, ADDRESS, NAME, etc...

But I was hoping that we could stay on track with the layout I originally had. It's just so my inlaws can concentrate on one tennant at a time, and when they change something they won't mess up another tennants properties.

Also, the database I used in the "Turret_new" folder is the database that I'd like to use, because it allows you choose the "address #" and the "address name" seperately, thus allowing you to choose the "address #" and then it would list only the available "address names" with the same address #'s.

thanx,
jalbes

[mosquito]

Opps the database is not that big of an issue. I'll have a look at it a bit later.

[jalbes]

thanx mosquito, I appreciate your help.

jalbes

[jalbes]

mosquito, have you had the chance to take a look at it a bit more?

Let me know.

Thx,
jalbes

[mosquito]

Sorry, real busy at work the last few days, I'll have a look tonight.

[jalbes]

No problem, I don't mind waiting. Thanx so much for your help!

jalbes

[jalbes]

OK, I had a friend look over it and he made a few changes and gave it back. But it's still not quite done.

2 problems still exist:

1) on the opening form I have 2 drop menus and I want it to work so that when I select the "Street #" on one drop menu, then only the recordsets with that "Street #" appear in the "Address" combobox. (and vice versa if possible)

2) on the 2nd form is the save button. Whatever my buddy changed causes it so that when I press "Save" that it tries to add a new recordset, but all I want it to do is update the current recordset. (keep in mind that I don't need to add recordsets, I only need to update them)

If anyone can help, please let me know.
I'll post the current copy of what I have to date below.

Thx again,
Shaun

[jalbes]

I think what I need to do is query the database on what is selected in the 1st combobox. Right?!?

I took a look around the net and didn't find anything, maybe I'm looking for the wrong thing.

Am I on the right track?

A sample of what I currently have is on the previous post if you want to see what I got so far.

Thanx,
Shaun

[mosquito]

use rs.updatebatch to update the changes in your current recordset.

[jalbes]

this is what I have:


Private Sub cmdSave_Click()
Dim selcri$
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

selcri = "select * from tenant"

rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = selcri
Set rs.ActiveConnection = adoCon
rs.Open
'check if address exixts
rs.MoveFirst
While Not rs.EOF
If rs!Address = Trim(txtAddress) Then
MsgBox "Duplicate Address not allowed"
Exit Sub
End If
rs.MoveNext

Wend
'check if unit exists
rs.MoveFirst
While Not rs.EOF
If rs!unit = Trim(txtUnit) Then
MsgBox "Duplicate Unit not allowed"
Exit Sub
End If
rs.MoveNext

Wend
rs.AddNew

rs("Address") = Trim(txtAddress)
rs("streetno") = Trim(txtStreet)
rs("Unit") = Trim(txtUnit)
rs("City") = Trim(txtCity)
rs("PostalCode") = Trim(txtPostalCode)
rs("EntryDate") = dtEntry.Value
rs("FirstName") = Trim(txtFirst)
rs("LastName") = Trim(txtLast)
rs("SpouseName") = Trim(txtSpouse)
rs("Category") = Trim(txtCategory)
rs("Rent") = Trim(txtRent)
rs("Company") = Trim(txtcompany)
rs("Phone") = Trim(txtPhone)
rs("Notes") = Trim(txtNotes)

rs.Update

rs.Close
cleartext Me
End Sub


What do I change? where do I apply rs.updatebatch in there?

Thanx,
jalbes

[doctorgonzo]

Couple observations here.

First, this will not save the current recordset. The .AddNew makes it add a new record. I am assuming that this isn't what you want. If you simply want to edit the current recordset, use .Edit instead.

Second, you loop through the recordset twice to look for duplicate addresses and units. Looping through recordsets in this manner is very, very slow and should always be avoided. Instead of visiting each record and testing for duplicates, just use the .Find method.

Third, While...Wend loops aren't used very much anymore. Use Do...Loop instead.