Linked Table Fields in Forms

[Hey Lucy]

I am so confused and need help! Creating a properties listing database for a real estate client.

Have tblPUC (properties under contract) and another tblAttorneys. In tblPUC there is a lookup field called ClosingAtty which is looked up from the tblAttorneys in order to create a drop down list on the frmPUC. (In other words, client wants a drop down list to choose the closing attorney from on the frmPUC). No problem there, but here IS the problem:

When using the frmPUC, client wants name of atty's firm and contact info of selected atty to show on the frmPUC (info from the frmAtty). While it is simple to choose an atty from the drop down list, how can I have the form display the name of the firm that the atty works for on the frmPUC?

In addition, on the frmAtty, I have created a field to concatenate the contact info data from each of the fields in the tblAtty. This field will be hidden in the frmAtty and has been created for the sole purpose of displaying on the frmPUC.

I cannot figure out how to get the firm name and the concatenated field from the frmAtty to display on the frmPUC. And in the end, these two things have to be connected on the frmPUC to the atty field.

Does any of this make sense and can anybody help me? I don't know VB so that won't help. I don't want to do a subform because it's a one-to-many, one atty to many properties.

I have been trying to write an IF statement, but can't figure out what my conditions would be. Arrrrrgggghhhh!

:banghead:

All I know about Access I've taught myself, so go easy on this fumblin', bumblin' idiot! Thanks!

 

[John Big Booty]

So long as the items you wish to populate your text boxes form part of the Row Source of your combo, you can use the following to populate your Text Boxes (unbound);

=YourComboName.Column([B][COLOR="Red"]X[/COLOR][/B])

Where X represents the column, that hold the data you wish to appear in the text box. Remember that the columns in a combo (or a list box for that matter) are numbered from zero on up. The first column, which is often and hidden and bound to the form's record source, is column zero, the second column one and so on.

The attached sample demonstrates the method.

 

[John Big Booty]

Sorry here's the sample :banghead:

 

[Hey Lucy]

Thanks, but I don't think that's the answer I'm looking for. I am attaching a sample db to show you what I mean. Please open the sample before continuing so you can see how everything is set up.

On the frmPUC, you will see there is a ClosingAtty field that is a drop-down. This comes from the tblAttys.

On the frmPUC I also need it to show this calculated field from frmAttys:

AttyContact

So, on the frmPUC, if the closing attorney is, let's say, Ford Jones, the person making the entry would use the drop-down to select Ford Jones. Then I need underneath that for it to show the ClosingAtty field BASED on Ford Jones' info.

The ClosingAtty field is a calculated field on the frmAttys.

Can this even be done? Or is there a workaround. Maybe I just didn't understand your instructions, but I am grateful for the response.

 

[John Big Booty]

There is absolutely no reason you can't use the method demonstrated in my sample.

All you need to do is open your form frmPUC in design view and edit the Row Source for your Combo ClosingAtty. You can do that by clicking the ellipsis button (...) to the right of that field on Data Tab. This will open the Combo's row source as a query builder grid. In the first blank field construct the same contact details, in the Field row, you have on the form frmAttys all the fields are available to you in the table tblAttys. This new field will show in the grid as Expr1:, now simply close the query builder grid, don't forget to save changes when prompted.

You can now use the following as the Control Source for an unbound text box to display that contact information on the form;

=[ClosingAtty].[column](2)

You will also need to edit the Combo's Column Count to 3 and add an additional Column Width;

 

[John Big Booty]

I wasn't going to do this initially but here you go

 

[Hey Lucy]

I want to thank you so much for your help! I guess it was a communication problem...I didn't understand what you were saying until I saw the actual coding. ! I appreciate you writing the code for me. That helped a lot, but with me, I have to see it, understand it, know why I'm doing it and what my results are going to be. Now, thanks to you, I understand all that and can move forward and will be using this structure in the future for other things!!!! Thank you so much!

 

[Hey Lucy]

May I ask another question? I also need to get the Wiring Instructions field on the frmPUC. I've tried adding the Wiring Instructions to the query and then setting the column to 3, but I'm not having success, so I apparently still don't know what I'm doing. Can I write multiple expressions in the query? And how? Thanks for your patience and kindness.

 

[John Big Booty]

Did you save the SQL when prompted, adjust the number of columns and the column widths?

 

[Hey Lucy]

I did that, but there's something I'm missing because I can't get the data to show in the field.

I can add the wiring instructions to that same field on the form where we added the contact information, but that's not ideal. I needed the wiring instructions to show in a separate field from the contact info field. Hmmm...possible?

 

[John Big Booty]

What was wrong with what I did in the sample in post #9

 

[Hey Lucy]

What was wrong with what I did in the sample in post #9

Thanks John...It DID work...I made a typing error!

 

[John Big Booty]