automate some field values?

[Guest]

I have a table containing all company spend with a form to add new records.
There are a lot of regular monthly payments which involves repetitive
monthly input. I would like to be able to choose the supplier and then have
the add new record form presented with some fields automatically filled in
with the values from the previous (latest) record for that supplier.

I can't get my head around the steps I need to achieve this - can anyone
help with a broad methodology or example I can use? I should say I'm not
great on VB - any help much appreciated.

Sheila

 

[Guest]

Sheila,
You shouldn't need too much code, if any. You might look into using the
DLast function to get the previous record and use the expression in the
Default Value property for each control. To make sure DLast finds the last
record, create a query that orders and filters the records and point DLast to
this query.

Barry

 

[Guest]

Sheila,
You shouldn't need too much code, if any. You might look into using the
DLast function to get the previous record and use the expression in the
Default Value property for each control. To make sure DLast finds the last
record, create a query that orders and filters the records and point DLast to
this query.

Barry

 

[Guest]

Thanks Barry - I can see the logic of this and have set the Supplier as a
combo box at the top of the form. I then have a query which uses the value
from that field as it's criteria and have set the Default Value for the first
field (Claimant) I want to set as follows:
=DLast("[Claimant]","[Q_Add Remittance Info]")
where the Query uses the criteria from the Supplier field in the form
This does not seem to work - I've tried setting the After Update property of
the Supplier field to open the Query (which does show the correct records)
and also not opening the query - what am I missing, any ideas.

Sheila

 

[JK]

Sheila,

Your syntax as you have it will give you the *last [Claimant] * in your
query *not* the last amount for your current [Claimant],This may well be the
one in the code you are just adding if it the form has been update during
entry!!!

I'm afraid that if you want to insert a pervious payment for a supplier in
the current record you will have to do it in VB. You cannot do it in the
properties sheet because when you are adding a new record, until you enter
the supplier there is no supplier in your current record to search for
his/her last payment. In property sheet you will get nothing (null), wrong
value or an error, depending how you set it.

Furthest Dlast will only work if your query is sorted by date, my suggestion
is to use the DMax function on the YrPayDate, in which case you don't have
to worry about the sorting. Assuming that you selecting the claimant by a
Combo Box use the [event procedure] of the After Update *of the field*

Private Sub YourCombo_AfterUpdate()

Dim Last_ID as Long

Last_ID=Dmax("[yrPayDate]","Q_Add Remittance Info","[CaimantID]=" &
Me.ClaimatID _
& " AND [YrPayment_ID]<>" & Nz(Me.YrPayment_ID,0))

Me.yrAmount.DefaultValue=DLookup("[yrAmount]","Q_Add Remittance
Info","[YrRemittance_ID=" _
& Me.yrRemittance_ID)

End Sub

Regards/JK


[QUOTE="Sheila D"]
Thanks Barry - I can see the logic of this and have set the Supplier as a
combo box at the top of the form. I then have a query which uses the value
from that field as it's criteria and have set the Default Value for the
first
field (Claimant) I want to set as follows:
=DLast("[Claimant]","[Q_Add Remittance Info]")
where the Query uses the criteria from the Supplier field in the form
This does not seem to work - I've tried setting the After Update property
of
the Supplier field to open the Query (which does show the correct records)
and also not opening the query - what am I missing, any ideas.

Sheila

Sheila,
You shouldn't need too much code, if any. You might look into using the
DLast function to get the previous record and use the expression in the
Default Value property for each control. To make sure DLast finds the
last
record, create a query that orders and filters the records and point
DLast to
this query.

Barry

[/QUOTE]

 

[Guest]

Remove the equal sign in front of DLast when you put it in the Default Value
property.

You shouldn't need to open a select query to get data from it. Any
expression will see it as a table.

Barry

Thanks Barry - I can see the logic of this and have set the Supplier as a
combo box at the top of the form. I then have a query which uses the value
from that field as it's criteria and have set the Default Value for the first
field (Claimant) I want to set as follows:
=DLast("[Claimant]","[Q_Add Remittance Info]")
where the Query uses the criteria from the Supplier field in the form
This does not seem to work - I've tried setting the After Update property of
the Supplier field to open the Query (which does show the correct records)
and also not opening the query - what am I missing, any ideas.

Sheila

Sheila,
You shouldn't need too much code, if any. You might look into using the
DLast function to get the previous record and use the expression in the
Default Value property for each control. To make sure DLast finds the last
record, create a query that orders and filters the records and point DLast to
this query.

Barry

 

[Guest]

Sheila:

My inclination would be to establish a Recordset which includes just the
last row entered for the client in question. You can then get the default
values for the fields from the recordset in one operation rather than making
separate DLast function calls. The code would go in the new record form's
Open event. The SupplierID value would be taken from the combo box on the
first form. The last payment would be identified by the date of the payment
which I'm assuming to be a unique date/time value per customer, this being
the only reliable way of determining the last payment as a table is a set and
consequently has no intrinsic order. The code would go something like this:

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strCriteria As String

strCriteria = "SupplierID = " & Forms!YourFirstForm!cboSupplier

' establish recordset containing supplier's latest payment
strSQL = "SELECT SupplierID, Claimant, " & _
SomeOtherField, YetAnotherfield " & _
"FROM Payments WHERE " & strCriteria & _
" AND PaymentDate = " & _
"(SELECT MAX(PaymentDate) " & _
"FROM Payments WHERE " & strCriteria & ")"

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

' if recordset not empty set default values of
' controls on form to values from recordset
With rst
If Not .EOF then
Me.SupplierID.DefaultValue = """" & .Fields("SupplierID") & """"
Me.Claimant.DefaultValue = """" & .Fields("Claimant") & """"
Me.SomeOtherField.DefaultValue = """" &
..Fields("SomeOtherField") & """"
Me.YetAnotherField.DefaultValue = """" &
..Fields("YetAnotherField") & """"
End If
End With

Set rst = Nothing

Note that the DefaultValue property is a string expression regardless of the
field's data type and is therefore wrapped in literal quotes. Often this is
not essential, but can be crucial in some circumstances where you might not
expect it to be so, so its prudent to do it regardless.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks very much for all help - got it working fine now!