CODEWITHSUNDEEP
formsms-accesscomboboxsetfocus
user2744572
user2744572

Reputation: 21

MS-ACCESS Displaying record from one table, on a form bound to another based on combobox selection?

I need a poke in the right direction. I have a contact form that displays a persons information and I want it to also display location they work at(which is stored in another table). Now what I want to do is take that combobox with the location (which is a field in the contact form bound table), and fill in the rest of the fields from that record. (I.E. location, then have a textbox with the locations address filled). How would I go about this. This form is used to update the person as well if necessary, so a query isn't ideal. On this form, the location is not editable, only is displayed.

Any help would be great!

Upvotes: 0

Views: 2195

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8414

In the After_Update event of the textbox, you could have something like this:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset ("Select * from MyTable WHERE UCase(Location) = '" & UCase(Me.MyLocationTextbox.Text) & "'")

Me.Address1 = rec("Address1")
Me.SomeOtherField = rec("WhateverItsCalled")

Set rec = Nothing
Set db = Nothing

Obviously, the above code needs a little tweaking to suit your specific field names and textbox names, but you get the idea.

Also, I'd suggest using a Combo box because invariably someone's going to misspell something and not get a match. Or think they should get a match and not realize they're misspelling it.

Upvotes: 0

Charles Naughton
Charles Naughton

Reputation: 148

You could do this by creating a subform, and embedding it in the form. There might be other, more elegant ways to do this though.

Upvotes: 1

Related Questions