Solved: Query From TextBox to populate other TextBoxes

[n8Mills]

Hello, all I've seen a lot of helpful code, but I'm very new to VBA so there are a number of things that I don't grasp well, so if somebody cen give me a leg-up it would be very helpful.

Right now I'm trying to populate a series of TextBoxes based on a query of another TextBox. It goes like this: Our technician enters his Notification numer (Seven digits, the entry in the table is 1010101), and hits Tab or Enter. Access will the query the table "tblSRUfromRepTech" and find the Part Number, Serial Number, and Customer associated to the Notification in the above table. I've seen similar code for ComboBoxes and Listboxes but I don't think I need anything complicated.

If somebody can help me it would be great!

Thanks, n8

[n8Mills]

I made some progress thanks to Imdabaum (Solved: Text Field Binding) but it's still a little Greek to a newb, so here goes:

In the TextBox's properties there's a Control Source field that can be populated with a DLookup which will pull values from a Table, the syntax is this:

=DLookUp("PN","ROSfake","txtNotifLRU")

where "PN" is the value to populate the TextBox, ROSfake is the table that contains "PN", and "txtNotifLRU" is the other TextBox that contains the Query data.

Now the problem is that the DLookup only seems to kick when I reload the form. Is there a command I can plop into the Private Sub txtNotifLRU_LostFocus() that will reload the form?

[n8Mills]

Further good help, thanks to Techno on the Net (techonthenet.com/access/functions/domain/dlookup.php),
which shows how to do this in the code. Somewhat more flexible it seems, and you can have your code all in one place instead of scattered across a bunch of TextBox properties. The helpful bit was this:


You could also use a form control in the DLookup function. For example:
DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)
This example would return the CustomerID field from the Orders table for the record that is currently being displayed in the Orders form (based on OrderID).


This works beautifully, except the fields remain populated after I wipe out the source data. After monkeying around a whole lot more, I stumbled upon this bit of code:

Form_SRURepairExchange.txtLRUPN.Value = Null

Correct placement was key. So the end result, functioning wonderfully is:

Private Sub txtNotifLRU_LostFocus()

Dim strValue As String
Dim iLen As Integer
Dim ROSLRUPN As String
Dim ROSLRUSN As String

On Error Resume Next
strValue = Form_SRURepairExchange.txtNotifLRU.Value
iLen = Len(strValue)

If iLen = 0 Then
Form_SRURepairExchange.txtLRUPN.Value = Null
Form_SRURepairExchange.txtLRUSN.Value = Null
Exit Sub
End If
If iLen < 7 Or iLen > 7 Then
MsgBox "A Notification must be 7 digits, """ & strValue & """ is " & CStr(iLen) & " digits."
End If

ROSLRUPN = DLookup("PN", "ROSfake", "Notif =" & Forms![SRURepairExchange]!txtNotifLRU)
Form_SRURepairExchange.txtLRUPN.Value = ROSLRUPN

ROSLRUSN = DLookup("SN", "ROSfake", "Notif =" & Forms![SRURepairExchange]!txtNotifLRU)
Form_SRURepairExchange.txtLRUSN.Value = ROSLRUSN

End Sub

...which allows a 7-digit number to be input into the said TextBox, and when this is done it will populate two other TextBoxes with the necessary Part Numbers and Serial Numbers, and if the Notification number is deleted then the Part & Serial numbers that were associated to the deleted Notification go away as well.

Despite the fact that I am the only one posting on my original question, that this was a tremendous team effort. :clap: I have used maybe four good sources in compiling this solution, so it feels like I've had personal attention on this. And while I know this could easily be misconstrued as a lonely, rambling post by a confused newb, hopefully it will help some confused newb seeker like myself. Gosh only knows that I've been working on this for two days, and had the code been available in such a condensed format, I would have gladly taken it.

n8