Need help with this

[dsomers]

On my real estate management software I need to get a LeaseID from Building Number and Apartment Number for my transactions form. However when I try to get it and store it in txtLeaseID so I can use it, it doesn't do it. I've attached my db to this message. If someone could look and see what I'm doing wrong, I'd appreciate it.

I've got my db on http://briefcase.yahoo.com. User: davidinncsu2003 pass: wolfpack

Thanks!
David Somers

 

[WayneRyan]

David,

Your tblLease appears to want a TenantID and an ApartmentID
as a key.

On your form, you do have the ApartmentID. If that is OK, then
in the AfterUpdate event of the apartment combo you can put
something like: (not too sure on the names ...)

txtLeasID = Nz(DLookUp("[LeaseID]", "tblLease", "[ApartmentID] = " & Me.txtApartmentID), 0)

Wayne

 

[dsomers]

I get cannot assign a value to this object. Any ideas?

Thanks!
David Somers

 

[WayneRyan]

David,

This worked finein the AfterUpdate of your combo:

Me.txtApartmentID = cboApartment.Column(1)
Me.txtLeaseID = Nz(DLookup("[LeaseID]", "tblLease", "[ApartmentID] = " & Me.txtApartmentID), 0)

Wayne

 

[dsomers]

Is there a way to tell it to use the LeaseID that is Active? (ie. tblLease.ActiveLease = True)

Btw, I had to recreat txtLeaseID because it wasn't showing as unbound for some reason. Anyhow, it works.. Just need to get it to always go to the active lease.

Thanks!
David Somers

 

[WayneRyan]

David,

Me.txtApartmentID = cboApartment.Column(1)
Me.txtLeaseID = Nz(DLookup("[LeaseID]", _
                           "tblLease", _
                           "[ApartmentID] = " & Me.txtApartmentID " And " & _
                           "[ActiveLease] = -1"), 0)

You may want to make a test for when it = 0 (No lease)!

Wayne

 

[dsomers]

I get a syntax error with that. Any ideas?

Thanks!
David Somers

 

[WayneRyan]

David,

Maybe we lost something in the editing. I tried to make it more
readable. This worked fine:

Me.txtApartmentID = cboApartment.Column(1)
Me.txtLeaseID = Nz(DLookup("[LeaseID]", "tblLease", "[ApartmentID] = " & Me.txtApartmentID & " and ActiveLease = -1"), 0)

Wayne

 

[dsomers]

Ok, I got that in, and its storing the correct LeaseID in txtLeaseID. However, when I try to add the transaction, it says that no value is given for one or more of the parameters. Can you take a look at that and see where I'm going wrong?

Thanks!
David Somers

 

[WayneRyan]

David,

I changed your test to use the tblTransactions instead of
the query and it worked fine:

sSQL = "Select * " & _
           "From tblTransactions " & _
           "Where TransDate = #" & Me.txtTransDate & "# AND " & _
           "LeaseID = " & Me.txtLeaseID & " AND " & _
           "TransTypeID = " & Me.txtTransTypeID & " AND " & _
           "CreditAmt = " & Me.txtCreditAmt & " AND " & _
           "DebitAmt = " & Me.txtDebitAmt & ";"
    Set ADOrs = New ADODB.Recordset
    ADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

It correctly returned BOF = TRUE. I have no transaction records
in my copy.

Wayne

 

[dsomers]

Did you change both sSQL statements to the above?

Thanks!
David Somers

 

[WayneRyan]

David,

I didn't, I'm running out of time here.

I only got it to run past that first hurdle. I would change the
others.

Wayne

 

[dsomers]

I got it fixed. I had to redo the next sSQL statement and then remove the line that stores TransID.

Thanks for the help!

Thanks!
David Somers

 

[dsomers]

I have one more question regarding this. I need to be able to have the CurrAmtOutstanding be updated right after the user enters either a debit or credit amount. And have the new value show up in txtCurrAmtOutstanding.

My guess is to use the AfterUpdate of the textfields, but I'm not sure how I would code it in. Any ideas on that as well?

Thanks!
David Somers

 

[dsomers]

Oh.. forgot to mention that I changed the pass to the ftp site above. Sorry!

user: davidinncsu2003
pass: ncsuwon

Thanks!
David Somers

 

[dsomers]

Ok, I've tried putting some code in so that it will update CurrAmtOutstanding when a number is entered into txtDebitAmt or txtCreditAmt. Here's the code I'm using:

Private Sub txtCreditAmt_AfterUpdate()

Dim ADOrs As New ADODB.Recordset
Dim sSQL As String

    sSQL = "Select * From tblLease Where tblLease.LeaseID = " & Me.txtLeaseID & ";"

    Set ADOrs = New ADODB.Recordset
    ADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    With ADOrs
    
    Me.txtCurrAmtOustanding = ADOrs.Fields("CurrAmtOutstanding") - Me.txtCreditAmt
    ADOrs.Fields("CurrAmtOutstanding") = Me.txtCurrAmtOustanding
    
    End With
    
    Set ADOrs = Nothing

End Sub

Private Sub txtDebitAmt_AfterUpdate()

Dim ADOrs As New ADODB.Recordset
Dim sSQL As String

    sSQL = "Select * From tblLease Where tblLease.LeaseID = " & Me.txtLeaseID & ";"
    
    Set ADOrs = New ADODB.Recordset
    ADOrs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    With ADOrs
    
    Me.txtCurrAmtOustanding = ADOrs.Fields("CurrAmtOutstanding") + Me.txtDebitAmt
    ADOrs.Fields("CurrAmtOutstanding") = Me.txtCurrAmtOustanding
    
    End With
    
    Set ADOrs = Nothing

End Sub

When I open the form and start entering data, I get the error: Item cannot be found in the collection corresponding to the requested name or ordinal. Can somebody tell me what I'm doing wrong?

Thanks!
David Somers