Newbie Query Question

[dgreatjc]

I've tried everything and nothing works. I'm sure there's a very simple way to do this.
I have a form (Transactions - Deposits) with a subform (Transactions - Deposits Subform). In the subform I enter an amount paid (AmountSubform is the box name), and then select my Invoice Number from a drop down box (InvoiceNumber is box name). I have an Event Procedure afterupdate open a query (Transactons - Deposit Query). I then search for the InvoiceNumber I chose, and then go to the record column AmtPaid for this invoice.

Private Sub InvoiceNumber_AfterUpdate()
Dim searchrecord As Integer
Dim capitalamt As Currency

searchrecord = InvoiceNumber
capitalamt = AmtPaid
DoCmd.OpenQuery ("Transactions - Deposit Query")
DoCmd.FindRecord searchrecord, acAnywhere, True, acSearchAll
DoCmd.GoToControl "AmtPaid"

Now all I want to do is to copy the information from the subform box AmountSubform to the query column AmtPaid. Then close the query and close the main form and subform (I can do the closing part).

Any help much appreciated.

 

[jal]

It sounds to me like you are trying to look up a value in a query or table. If so, you can use DLookup to do this. Once you get the value (let's call it 'result'), you load that into a textbox.

textbox1.Value = Result

DLookup is basically a SELECT

SELECT LastName FROM Customers WHERE CustID = 55

This might return "Smith" as the Result.



Here's my notes on DLookup:


The Dlookup function looks up one value in a desired table and therefore is a SELECT that returns a single value - you will therefore neeed to provide a WHERE clause geared to return a single value (omit the word "WHERE", however. The format is this:

Dlookup(ColumnName, TableName or QueryName, WHERE clause)


For example:

Dim Result as String
Result = Dlookup("LastName", "Customers", "CustID = 55")
textbox1.Value = Result

Again, this might return "Smith".