Using a form to change a query value

dgreatjc

Registered User.
Local time
Yesterday, 20:27
Joined
Oct 22, 2008
Messages
10
I'm trying to take a number entered into a form, then update another table via a query with the same value so I don't have to enter the number multiple times. 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 that opens 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"

This all works. What I can't do is copy the information from the subform (Transactions - Deposits Subform) box AmountSubform to the Query (Transactions - Deposit Query) column AmtPaid. Then close the query and close the main form and subform (I can do the closing part).

Any help much appreciated.
 
technicaly you are creating a bot script that acts based on same human logic of data manipulation :) what you miss would be copying first the variable from your form control on top of the script via :
Code:
Me![ControlNameHere].SetFocus
Me![ControlNameHere].SelText
RunCommand acCmdCopy

then on going to that control you look for rather paste copied value via :
Code:
RunCommand acCmdPaste

yet how to achieve this in a programatical conventional style :
create a dynamic query , here is an example :
Code:
Update TableNameHere Set [fieldnamehere] = Forms!FormNameHere.ControlNameHere Where [searchforfieldnamehere] = Forms!FormNameHere.SearchForControlNameHere ;
and just run it via vb script and you are done , yet do note you have to modify upper script for strings and dates (ie use Quotations ' or Sharp sign # )
 
Thank you so much for your help. Although when using your code how your wrote it, it didn't work (most likely I did something wrong...I'm not very experienced), but using the Me! with SetFocus and RunCommand acCmdPaste that you suggested, I was able to get it to work really quickly.

Dim searchrecord As Integer
Me![AmountSubform].SetFocus
RunCommand acCmdCopy
DoCmd.OpenQuery ("Transactions - Deposit Query")
DoCmd.FindRecord searchrecord, acAnywhere, True, acSearchAll
DoCmd.GoToControl "AmtPaid"
RunCommand acCmdPaste

Thanks for your help. I would have probably spent several hours if not for your help.
 

Users who are viewing this thread

Back
Top Bottom