Binding the Text box value

[abhiutd]

Hi,
I have created a form that has a textbox field bound to some interger ID of a field in the table but there is another table that has the corresponding Name field for the ID and thats what i want to display in the textbox not the ID. It seems obvious i would have to use join and somehow associate the ControlSource of the text box to it but i'm not able to do it. I tried clicking on the ControlSource ellipse button of the text box but dont know how the expression builder exactly works.
Please help. Excuse me if it was really easy as i am very new to Access.

Thank You.

 

[maxmangion]

Hi,

I'm not sure if you can do it on a textbox, but if you convert your textbox to a combo box you can use the following:

SELECT tablename.IDFieldname, tablename.FieldnameToShow FROM tablename;

then set the column count of your combo to 2 and the width to 0cm;2.5cm

 

[dkinley]

txtFormControl = DLookup("Name", "OtherTable", "[OtherTableID] = [ThisTableID]")

or

txtFormControl = DLookup("Name", "OtherTable", "[OtherTableID] = " & Me!txtThisTableIDFormControl)

-dK

 

[maxmangion]

dkinley,

your suggestion would work if it was an unbound textbox. In his scenario the textbox is bounded to a field, so if he had to change the control source to your dlookup suggestion he will unbound the textbox from his field.

 

[dkinley]

As yes, I reread ... true.

Can I resuggest? =]

Set integer based bound control visibility property to False on the form and use the second DLookup function on a visible control.

That is, if the user/reader is not required to manipulate this control but only observe it for display purposes only.

-dK

 

[abhiutd]

Hi,

I'm not sure if you can do it on a textbox, but if you convert your textbox to a combo box you can use the following:

SELECT tablename.IDFieldname, tablename.FieldnameToShow FROM tablename;

then set the column count of your combo to 2 and the width to 0cm;2.5cm

Hello max*, I dont think it would make a lot sense to make it a combobox as it is a one value field. Hmm i didn't think it would not be possible doing that. I hope someone knows a way to do it.
Thanks.

 

[maxmangion]

Hi,

i didn't say it wasn't possible, but i only knew how to do it via a combo, so hopefully someone else will come up with the solution

 

[abhiutd]

As yes, I reread ... true.

Can I resuggest? =]

Set integer based bound control visibility property to False on the form and use the second DLookup function on a visible control.

That is, if the user/reader is not required to manipulate this control but only observe it for display purposes only.

-dK

So now you suggest having two text box controls on the form? Can you pld explain more on that.

Thanks.

 

[dkinley]

Clarification ...

Let's call "textbox field bound to some interger ID of a field in the table" by the name of txtIntegerTextBox and call "corresponding Name field for the ID" by the name of txtNameTextBox.

If your user's do not need to manipulate or interact with txtIntegerTextBox then in properties, set Visible to 'No'.

In txtNameTextBox, set the Control Source of the data properties to

= DLookup("Name", "OtherTable", "[OtherTableID] = " & Me!txtIntegerTextBox )

Note: txtIntegerTextBox is the bound control that holds the linking ID information.

Also, for the data properties of txtNameTextBox, set the Locked property to 'Yes'.

The outcome should be that txtNameTextBox is using the information in txtIntegerTextBox to provide a name for the user to read. If the integer value in txtIntegerTextBox does not concern the user, then it is hidden from their view. Last, txtNameTextBox is locked from user manipulation rendering it read-only.

-dK

 

[abhiutd]

Clarification ...

Let's call "textbox field bound to some interger ID of a field in the table" by the name of txtIntegerTextBox and call "corresponding Name field for the ID" by the name of txtNameTextBox.

If your user's do not need to manipulate or interact with txtIntegerTextBox then in properties, set Visible to 'No'.

In txtNameTextBox, set the Control Source of the data properties to

= DLookup("Name", "OtherTable", "[OtherTableID] = " & Me!txtIntegerTextBox )

Note: txtThisTableIDFormControl is the bound control that holds the linking ID information.

Also, for the data properties of txtNameTextBox, set the Locked property to 'Yes'.

The outcome should be that txtNameTextBox is using the information in txtIntegerTextBox to provide a name for the user to read. If the integer value in txtIntegerTextBox does not concern the user, then it is hidden from their view. Last, txtNameTextBox is locked from user manipulation rendering it read-only.

-dK

As per your comments txtThisTableIDFormControl is same as txtIntegerTextBox ?

 

[dkinley]

Correct. My apologies, I just caught that and edited that post for clarification while you were posting.

-dK

 

[abhiutd]

sorry to say but it doesn't seem to work. The value in the text box says "#Name?"
this is my expr
=DLookUp("ClientName","tblClient","[ClientID]=" & Me!txtClientID)

txtClientID is Name of the text box bound to the ID value.

Any suggestions?

 

[dkinley]

Might be having problems with the Me! reference (doing this from memory without testing). Try this ...


=DLookUp("ClientName","tblClient","[ClientID]=" & [Forms]![ThisFormName]![txtClientID])

-dK

 

[abhiutd]

Might be having problems with the Me! reference (doing this from memory without testing). Try this ...


=DLookUp("ClientName","tblClient","[ClientID]=" & [Forms]![ThisFormName]![txtClientID])

-dK

Genius! This one works although i would like to mention that you can see a lag before text appears in the textbox. Probably the DLookup function is intensive.

Thank You.

 

[dkinley]

Unfortunately there is that lag - I think it stems that the form itself will load, everything else will load and then it performs the DLookup. Correct in the assumption that DLookup is intensive (at least from what I read). It will begin searching from record 1 until it finds the first match and then report back so if you have 10k records and the one you want is #9,950 you might see an increased lag.

Now that I think about it, there might be a shortcut so that all data is presented closer together.

In the form properties, create an On Load event. In the code area of the function, put in ...

txtNameTextBox = DLookUp("ClientName","tblClient","[ClientID]=" & Me!txtClientID)

Note: I changed the reference here because it may work in VB (or is that only for loading a variable?) If not, use the reference that works with the text box. I am splitting hairs only because I can't remember which referencing works faster to lessen any lag.

Loading the text boxes before presentation should lessen the lag. This should not work on the On Open event because no data has been loaded yet (and you need it loaded to do the lookup).

More .02,

-dK

 

[maxmangion]

Hi,

When you started the thread i thought that you wanted to enter data in your textbox and not just display information. In such circumstance if you include your lookup tables inside your query and add the fields from the lookup tables you will achieve the same effect as the dlookup but without the lagging.

If you go to the sample databases of this forum there is a sample by pat hartman about many to many relationships and in that database you can see how it can be done.