Code Problem

[anon47]

The code below gives no error. But it don't update the record like it is designed to? Any help I have used this type of code before adn its worked great.


Set rst = CurrentDb.OpenRecordset("SELECT * FROM [CreditFile] Where [CreditFile].[ProjectID]=" & Me.[ProjectID])
If Not IsNull(rst.[ProjectID]) Then
rst.Edit
rst![LastPaymentDate].Value = Me.PaymentDate ' Text Field
rst![LastPaymentAmount].Value = Me.PaymentAmount " Text Field
rst![CurrentBalance].Value = Me.BalanceDue 'Text Field
rst.Update
rst.Close
End If

 

[SkipVought]

hi,

"...But it don't update the record like it is designed to?..."

What is the intent?

What is ACTUALLY happening that is NOT the intent?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[anon47]

Its not editing the information when the code is ran on click. I am baffled and have been running the code in multi configs to see if it was maybe a field issue but nothing works.

 

[anon47]

It is designed to open the recorded and find the one that matches the id then change the value you as indecated. Thanks

 

[SkipVought]

Does the Select return the row you are looking for?

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[randy700]

If you copied your code correctly, change this...

rst![LastPaymentAmount].Value = Me.PaymentAmount " Text Field

to this...

rst![LastPaymentAmount].Value = Me.PaymentAmount [COLOR=red]'[/color] Text Field

Randy

 

[tigersbh]

Is [CreditFile].[ProjectID] defined as a numeric field, or text? If text, change:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [CreditFile] Where [CreditFile].[ProjectID]=" & Me.[ProjectID])

to

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [CreditFile] Where [CreditFile].[ProjectID]=""" & Me.[ProjectID]) &

 

[tigersbh]

Correction:

Change it to:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [CreditFile] Where [CreditFile].[ProjectID]=""" & Me.[ProjectID] & """)

 

[TheAceMan1]

How are ya anon47 . . .

. . . and this:

[blue]   Dim db As DAO.Recordset, rst As DAO.Recordset, SQL As String
   Dim Msg As String, Style As Integer, Title As String

   Set db = CurrentDb
   SQL = "SELECT * " & _
         "FROM CreditFile " & _
         "Where (ProjectID = " & Me.ProjectID & ");"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If rst.BOF Then
      Msg = "Record not found matching ProjectID " & Me!ProjectID & "!"
      Style = vbInformation + vbOKOnly
      Title = "Can't Find Record! . . ."
      MsgBox Msg, Style, Title
   Else
      With rst
         .Edit
         !LastPaymentDate = Me.PaymentDate
         !LastPaymentAmount = Me.PaymentAmount
         !CurrentBalance = Me.BalanceDue
         .Update
      End With
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[anon47]

Thanks guys Project ID is a num field

 

[anon47]

Ok guys this is what works thanks again

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [CreditFile] Where [CreditFile].[ProjectID]=" & Me.[ProjectID])
If rst.BOF Then
End
Else
rst.Edit
rst![LastPaymentDate].Value = Me.PaymentDate
rst![LastPaymentAmount].Value = Me.PaymentAmount
rst![CurrentBalance].Value = Me.BalanceDue
rst.Update
rst.Close
End If