How can I make Text box go to a table, find custom text there and return it (show it for me)

[vazdajic]

Hello Everybody! Before I write anything, I'm sorry for my poor explanations of what I want. I am a beginner so please don't find it annoying from my side..

On the picture attached, I have two forms, each of them store data in separate tables:



Form "OUT_REQ" stores data in table: "Table_Outstanding_REQ"
Form "Table1" stores data in table: "Table"

I need a textbox (which is on "OUT_REQ" form) to go to"Table" and find there matching request no (in "Table" that request is saved under "REQUEST NO").

As in that table, I might have more records which have same request number and but different report number, I need my textbox to see if in report number name I have "RT".

In that case I just need the form to write for me that report number

[ranman256]

Test all controls for a possible filter then build the where clause for the report

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,4)

  'just use the filter to show data in the form
me.filter = sWhere
me.filterOn = true

'OR   
'apply the sql to the report

docmd.openreport  "rMeReport",,sWhere

[vazdajic]

Hi there.. I am a beginner at this (VBA generally and Access as well) so please, if you will have some free time, write me a sample of a code..

I was unable to understand myself how the code should look like

Thanks

[vazdajic]

Something like this:
This is what I have found on-line but I was unable to implement it:

When you design a form, you may want to display a value from a table or query other than the one that your form is bound to. For example, suppose that you have a Products form that is bound to a Products table. After you design the form, however, you decide that you want the form to display the name of the supplier contact for each product — data that resides in a table called Suppliers. The following procedure shows you how to use the DLookup function to accomplish this task. You can alter the expression in step 6 to suit your own data.
Add the text box
1. In the Navigation Pane, right-click the form that you want to change, and then click Design View on the shortcut menu.
2. On the Design tab, in the Controls group, click Text Box.
3. Locate the area in the form in which you want to add the control, and then drag the pointer on the form to create the text box.
4. Right-click the text box, and then click Properties on the shortcut menu.
5. In the property sheet, click the All tab.
6. Set the Control Source property to the following value:
=DLookup("[ContactName]","[Suppliers]","[SupplierID]=" & Forms!Products!SupplierID)

In OUT_REC form I have Text84 textbox and I want it to show me report number (REPORT NO column in table called Table) based on custom input in textbox Text3. That custom input is request number which is REQUEST NO column, also in table called Table

[ranman256]

in the button where the user clicks to open the report,
then the ON CLICK event property of that button,
in the drop box, choose event code
it will open in VB
paste the above code.

but the code must use YOUR control names, not the ones I listed.

[vazdajic]

Thank you for a solution ))