Creating a Textbox Control that will Cross Reference a Table

[MitchR]

Hi All;
I have a form that contains a textbox control called CPUServicetag (Serial number). My users enter in the CPUServicetag manually. My question is this ... How do I go about creating an expression in a separate textbox control with in the control source that would cross reference the manually entered CPUServicetag to a Table called RMA which contains two fields: Serial_num (CPUServicetag) and RMA# , and display in this separate textbox control the RMA# associated to the CPUServicetag which is the same as Serial_num. This is something I have never dealt with before and need some assistance

[NeoPa]

You can use the DLookup() function. Give me a short while and I'll knock up some (non-specific) VBA code to put in your form. If you were to come back with the correct name of the controls on your form where the data will be shown and the types of the two fields used, then I could tailor it more precisely.

NB. Access doesn't like using a '#' char in a control name - it will be converted automatically in related event names.

[NeoPa]

Hi All;
I have a form that contains a textbox control called CPUServicetag (Serial number). My users enter in the CPUServicetag manually. My question is this ... How do I go about creating an expression in a separate textbox control with in the control source that would cross reference the manually entered CPUServicetag to a Table called RMA which contains two fields: Serial_num (CPUServicetag) and RMA# , and display in this separate textbox control the RMA# associated to the CPUServicetag which is the same as Serial_num. This is something I have never dealt with before and need some assistance

I've assumed you will change your [RMA#] field name to simply [RMA], and, for now, have assumed that the Serial_num field is alpha-numeric.

Private Sub CPUServicetag_AfterUpdate()
    Dim strRMA As String
 
    If Me!CPUServicetag > "" Then
        strRMA = "='" & DLookup("RMA", "RMA", _
                                "Serial_num='" & Me!CPUServicetag & "'") & "'"
    Else
        strRMA = ""
    End If
    Me!RMA.ControlSource = strRMA
End Sub

[MitchR]

Thank You!! ... Thank You!! ... Thank You!! .... This resolved my issue.....Also I took your advice and changed my column name from RMA# to RMA

[NeoPa]

Happy to help Mitch :)

[MitchR]

If I wanted to pass the varRMA to a text box field"RMA" in my table"MonitorRe turn" : ? ? ? Would this be the correct method ?

 varRma = "RMA", "MonitorReturn"

[NeoPa]

I'm afraid not.
I get that you have a table=[MonitorReturn], but tables don't have TextBox fields. Are you talking about a form perhaps? And which way are you moving the data - your example looks as if you want to set varRMS (a VBA variable) from somewhere else rather move its value into your TextBox.

[MitchR]

I want to pass the RMA number that I was able to ascertain from the code we discussed earlier in this thread and deposit the RMA number in to a RMA column in the table MonitorReturn. I can make the RMA number appear in my Form Text Box "RMA" now I want to record it in my table.

[NeoPa]

Sorry, I just read the last post quickly. I was rushing to catch up with all the updated threads since my last visit.
What you need is for the control (RMA) on your form to be bound to the underlying table. I assume the form is bound to the MonitorReturn table?

[MitchR]

Yes you are correct sir ! The Form is bound to the table ... I need the RMA result to deposit the RMA result into the table in a column(RMA) in Table (MonitorReturn)