0

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!

PeeHaa
  • 71,436
  • 58
  • 190
  • 262
user2744572
  • 21
  • 1
  • 9
  • This sounds like 2 recordsets would work. Why isn't the query ideal? If you select a recordset_b from a seperate table based on the location, you can populate all the text boxes you want and recordset_a that is based on the person can be editable. – Grant Nov 15 '13 at 18:04
  • That's a good point, I've been stuck in the mindset of having some kind of code running on each textbox. How exactly could I go about creating a query to populate the boxes? – user2744572 Nov 15 '13 at 18:26

2 Answers2

1

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.

Charles Naughton
  • 148
  • 1
  • 7
0

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.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117