Text Box Lookup - There has to be a better way!

[oleBucky]

I have a form with 25 text box controls. I want the text box controls to look to table fields holding the ID number for a relationship to another table. Clearly this ID number is meaningless to virtually all users.

The only way I could get the field from the related table that means something to a user was to create a combo box with an SQL lookup, then refer to the appropriate column in the combo box with a function in the text box.

=comboLHPNOSym01.Column(1)

Finally, I turned off the visibility of the combo box to show only the text box.

I did this 25 times for the form. This worked but it was tedious, at best. To make things more tedious, the form will have a total of 5 subforms each with 25 similar text boxes. There has to be a better way; Certainly something less "awkward".

I am certain some of the Access gurus out there can, at least, offer suggestions to this Access beginner.

[jgelpi16]

Hmmmm....Would a recordset work here? You said you are getting a PK RecordID right? Why not just assign the text boxes the necessary field using a recordset....OR you may be able to use a DLookup()? I'm not understanding some of your structure very well. Could you post what code you have?

[orange]

I agree with jgelpi16 that your code would be helpful, but I also think a jpg or 2 of the form and subform would be good. Perhaps you could tell us more about the application and the purpose of the forms (and 5 subforms each with 25 boxes??)

[oleBucky]

I've attached an image of my Form-In-Progress. I thought this might be of general assistance. This db holds information necessary to play a tabletop baseball game.

The two lighter colored panels are the subforms in question. Each subform is based on a table of player hitting information. Each table as a "PlayerID" field which links the player hitting info back to the main form and the basic player data. That serves as the Child. The main form field with the label "ID Check" is named "txtHitterRecChk". It is the Master for the subforms.

Each of the 25 text box on each subform gets its Control Source from an unseen combo box on the same subform. The combo box has the following SQL code for its Row Source:

SELECT tblSymbols.SymbolID, tblSymbols.DisplaySym FROM tblSymbols;

The combo boxes, as well as the visible text boxes, are named "txtLHPNO01" and "comboLHPNO01" with the text in the name corresponding to "Against LHP" and "None-On". The visible text boxes are positioned to correspond with the dumb labels down the center of the main form, 1 thourgh 25, with final two digits in the control name changing correspondingly.

This code retrieves a symbol from a symbol table (imaginatively titled "tblSymbols") which the user can make sense of (1B, GO, K, W, etc.). The function code posted in the first thread adds another field completing what you see, i.e. 1B9 and GO6. The numbers represent a fielding position to which the type of batted ball (as represented by the text symbol) is hit. For a "K" (strikeout) or a "W" (walk), the ball is not hit and therefore no following number.

I did try Dlookup but I could not figger out the syntax. I think that method holds promise when implemented by some who knows what the h*@# they are doing. That's not me.

I hope this helps.

[oleBucky]

Perhaps you could tell us more about the application and the purpose of the forms (and 5 subforms each with 25 boxes??)

Each subform holds potential outcomes for random numbers. The first four subforms are for Against Left-Handed Pitchers with None-On (base), Against Left-Handed Pitchers with Runners-On (base), Against-Right Handed Pitchers with None-On (base) and Against Right-Handed Pitchers with Runners-On (base). For each subform the rows on the base table are unique for each hitter and contain symbol and location information of each of the 25 possible outcomes of the random number generator. The fifth subform is smaller and contains data regarding the symbols a batter "controls" on a pitcher's card. This form is in the works.

Is that all clear as mud?

[orange]

Muddy yes, but an interesting application. You must be exhausted keeping this stuff organized.

Are your player tables set up along these lines? It's my first guess at how it might be structured.

4 player stats tables LHNO, LHON, RHNO, RHON each with
playerId
+ 25 fields representing the values in the text boxes
+ some admin fields

[oleBucky]

Generally, yes. However each stats table has 25 symbol fields and 25 location fields; that is:

LHPNOSym01
LHPNOLoc01
LHPNOSym02
LHPNOLoc02 ...
LHPNOSym25
LHPNOLoc25

The Sym fields hold a SymbolID and Loc fields hold an integer from 1 to 9 representing standard scoring nomenclature for the fielding positions (1=Pitcher, 2=Catcher, etc.).

[orange]

Can you provide any sort of dumbed-down version of your database sufficient to show the Form and associated code and tables? I realize it's a unique application having potential for commercial activity, and don't mean to compromise that. However, I think working with a part of the data/form is the only way to understand it.

I'm thinking that there are 4 subForms that are similar... can they be generated via some code and procedure..

[oleBucky]

I'm thinking that there are 4 subForms that are similar... can they be generated via some code and procedure..

I agree. I think there has to be a better way than the method I came up with. It works but seems very ungainly.

Am I willing to post a version of my db? Yes. Am I able to post a version of my db? That I don't know. I am a real greenhorn to this Access and VBA thing and I don't really know how to go about it. However, I am going to be kinda out of touch this weekend. The weekend will give me some time to think as to how I might streamline the db (the current version is nearly 230 MB) to get a simpler but working version operational. I'll post again as soon as I come up with something or if I am stumped.

Thanks for your willingness and help.

[orange]

No problem. Enjoy the weekend.

[oleBucky]

I've attached a test db which, I think, illustrates the issue in question. The three subforms in "frmCardHit" show different versions of what would be one subform in my working db.

The first subform shows my combo box solution I use for data entry. I don't want to use a combo in the data viewing version of the form.

The second subform represents the interim step I could not get to work. The table "tblLHPNO" stores the batted ball symbol as a relationship to the table "tblSymbols". I could not get the text box to display the display symbol from tblSymbols, only the PK per the relationship.

The third subform shows my solution. I the real db, I would have set the visibile property to "No" for the combo boxes that drive the function in the text boxes.

I hope that is of some help.

[ajetrumpet]

dddddddddddddddddd

[jgelpi16]

dddddddddddddddddd

haha, you don't say?

[oleBucky]

dddddddddddddddddd

What the.... !?!?

Back to the subject at hand, I finally got the DLookUp to work. I got it (the syntax) right by trial and error... mucho trials and mucho errors before it worked.

The updated version of the test db is attached.

Why not just assign the text boxes the necessary field using a recordset....OR you may be able to use a DLookup()?

How might a "recordset" work?

[ssanfu]

I've been following this thread and I think you are having trouble because your table structure is not normalized. When there are field names with incrementing numbers, it usually means someone has "committed spreadsheet"; ie the Access table is "short and fat" (like a spreadsheet), instead of "tall and narrow".

I haven't got a clue as to what you are doing, but I tried to follow as best as I could. (see the mdb).

I normalized the main table - I called it tblStats - and added a couple of tables and two forms.... sorry about the colors. The form that is displayed when the mdb is opened is my brief attempt to look like the picture you posted. The other form, "frmAddStats", is how I would add entries.

Maybe it will give you some ideas to allow you to move forward...