RE: Display Value In Form's Textbox

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

This should help you. I created a form with 4 text boxes, you enter the
Account Number and Currency into the 1st 2 and once you have done that and
have exited from them and both have entries in them, the corresponding values
are placed into the other pair of text boxes. The 'catch' here is that it
doesn't do anything until you exit the text entry text boxes, so put a dummy
button next to/below them with appropriate wording on it like [Do It],
[Retrieve Name/Instructions] or whatever. The button doesn't have to have
any code associated with it, it just is there to get them to click on it to
make sure they have exited from both of the other text boxes.

Since the code for both input text boxes is the same, I wrote the functional
code once and it is called from eiach of the input text boxes' _Exit
routines.

Private Sub txtAccountNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'don't do anything until there are entries in Account Number and Currency
If Me!txtAccountNumber.Text <> "" And Me!txtCurrency.Text <> "" Then
RetrieveData Me!txtAccountNumber.Text, Me!txtCurrency.Text
End If

End Sub

Private Sub txtCurrency_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'don't do anything until there are entries in Account Number and Currency
If Me!txtAccountNumber.Text <> "" And Me!txtCurrency.Text <> "" Then
RetrieveData Me!txtAccountNumber.Text, Me!txtCurrency.Text
End If

End Sub

Private Sub RetrieveData(anAccountNumber As String, someCurrency As String)

Dim lastRow As Long
Dim LC As Long ' loop/offset counter

'find last used row in column A
lastRow = Range("A" & Rows.Count).End(xlUp).Row
'look at all rows, including row 1 to end of data in column A
For LC = 0 To lastRow - 1
'if the acct# matches AND currency matches
If Range("A1").Offset(LC, 0) = anAccountNumber And _
Range("A1").Offset(LC, 2) = someCurrency Then
'then get account name into txtAccountName on form, and
Me!txtAccountName.Text = Range("A1").Offset(LC, 1).Value
'get instructions into txtInstructions on form
Me!txtInstructions.Text = Range("A1").Offset(LC, 3).Value
Exit For ' found match, quit looking
End If
Next

End Sub


"franciz" wrote:

I have create a Form, I want to look up the correspondence values in column B
(AccountName), D ( SI ) in sheet 1 and display these values in the matching
textboxes of the form which are; Account Name & Standing Instruction details,
after I input the account no ( Col A ) and the currency ( Col C ) into the
form.

I have create a button on the worksheet to load the form.

Is this possible with Excel's marco? If yes, would the experts pls assist.

Thanks in advance

cheers, franciz
.

Relevant Pages

  • RE: Display Value In Forms Textbox
    ... are placed into the other pair of text boxes. ... Private Sub txtAccountNumber_Exit ... 'don't do anything until there are entries in Account Number and Currency ...
    (microsoft.public.excel.programming)
  • Re: Making a search form
    ... It has combo boxes of different things you can search by account number, ... private sub currentform_click ... where form2 is the form you want to open when the button is clicked. ...
    (microsoft.public.access.formscoding)
  • RE: Hide textboxes if checkbox selected
    ... What are the Option Values for the check boxes in Frame0? ... Private Sub Currency_AfterUpdate ... DoCmd.GoToControl "Currency" ...
    (microsoft.public.access.forms)
  • Re: Unbound Refresh Problem
    ... Put code in the current event to sync the text boxes with the combo each ... Private Sub Form_Current ... Dim stDocName As String ... You can use the after update event for cboOldJob to open a form filtered ...
    (microsoft.public.access.forms)
  • RE: creating multiple pop up calendars in a form???
    ... I created combox boxes because I was following some instructions that I found ... box to get the calendar. ... Set ctl = Screen.ActiveControl ... Private Sub Date_Shipped_BeforeUpdate ...
    (microsoft.public.access.forms)