Passing records and changing fields for images

[oleBucky]

I am a novice with Access and VBA so please bear with me.



I have a form in an Access 2003 db in which I want to pass two selections (a record and a one of two fields in the selected record) to a subform or an object in the form which will display an image based on the two selections.

The basic information is a table (tblPlayers) with information from baseball tabletop game and includes players, teams, and card images. Each player is a record and each record has two card image fields (actually a text field with the path to externally stored images), one for hitting and one for pitching as well as a field for the player’s team. So far, my main form, called frmVisitor, queries tblPlayers for a team then has 10 combo boxes to allow the selection of nine hitters and one pitcher from the queried team. This works fine.

On the image display issue, I adapted the Microsoft Support kb 285820 solution into a form, dubbed frmVisImage, to show images. On a standalone basis, that works too. After extensive work, my novicity (I make up words too often) in working with VBA code has precluded my ability to connect the option group on frmVisitor to the record control on frmVisImage, let alone, get the frmVisitor to change the image path field on frmVisImage.

I’ve included a screen capture of my current form (the one that is not communicating) to hopefully those who know more than I better understand where I am and where I want to be. I would GREATLY appreciate ANY help you all might offer.

[RuralGuy]

I think I'm looking at a Form with a SubForm on it. Maybe this will help: http://www.mvps.org/access/forms/frm0031.htm

[oleBucky]

Looks interesting. I will see if I can make it work. Thanks so much.

[RuralGuy]

Let us know how you make out.

[oleBucky]

It appears my ability to adapt code to my evil plan has hit a wall. The wall is my total lack of knowledge of VBA syntax. I purchased a copy of “VBA for Dummies” and it was of some basic help however I am still unable to get where I need to be.

Using guidance from the website suggested above and the option group (selectVisCard) as described in the thread’s first post, I’ve tried a myriad of syntax combinations to attempt to pass the PlayerID selected in a combo box from frmVisitor to the PlayerID text box in the subform frmVisImage without success. For example:

Private Sub selectVisCard_AfterUpdate()

Select Case [selectVisCard].Value

Case 1
Dim intPlayerID As Integer
intPlayerID = Me.VisHit01Combo.Value
Me!frmVisImage.Form!PlayerID.Value = intPlayerID

End Select

End Sub

As YOU ALL can probably tell by looking (I obviously can't), this did not make it through the compiler. The debugger stopped on the Me! line so I know enough to know the first problem is there. However, I am not knowledgable enough to know what questions to ask to debug the problem. A couple of facts may come to bear. The combo box (VisHit01Combo) has two columns. The first is the AutoNumber for PlayerID and the second is the player's name. In the combo bos format, I hid the first column by giving it a width of 0". Can someone help?

As a sidebar, I read the “10 Commandments” on web page in the thread and, lamentably, as you can tell from the code sample, I am a sinner. I repent all my sins. However, it was not within my programming skill to correct them all without butchering my db.

[RuralGuy]

Removing Lookup Fields is easy and painless: http://www.btabdevelopment.com/ts/removelookups
Having ComboBoxes (Lookup's) on a form is just fine and preferred, just not at the table level. Removing these from a table does not effect an existing form in any way and therefore "painless".

[oleBucky]

Thanks for that. Any suggestions on how to pass the PlayerID from the main form to the subform with the option group?

[RuralGuy]

I would try:

Case 1
frmVisImage.Form.PlayerID = Me.VisHit01Combo

[oleBucky]

I'm going to try it now. I will let you know. Thanks again.

[oleBucky]

OK, now I am really going to show my ignorance. The subform control (text box) was not "PlayerID", but ""txtImageID" with "PlayerID" (the Autonumber from my table) as a control source. That acknowledged, I tried:

 
Case 1
frmVisImage.Form.txtImageID = Me.VisHit01Combo

This generated an error:

Run-time error '-2147352567 (80020009)':
You can't assign a value to this object.

Clicking the "Help" button on the error message brings up a blank Microsoft Access Help window. Kinda the way I feelin' now.

Is this a basic problem with the construction of my root table?

[RuralGuy]

Okay, you need to go about this differently. What do you have as the LinkChild/MasterFields properties of the SubFormControl?

[oleBucky]

I think you've hit on at least one of my problems. After a thorough search, I am unable to find any property that refers to LinkChild/MasterFields in either my main form (frmVisitor) or my subform (frmVisImage). I also looked at the table (tblPlayers) which is the record source for both frmVisitor and frmVisImage. No luck there. Where might I find such a beast?

One observations: When I open frmVisitor in design view, the only way I can see the subform in design view is to open it in a new window. Whether this has any bearing on anything... I don't know.

[RuralGuy]

Forms are displayed on other forms by means of a SubFormControl. It is this SubFormControl that has the LinkChild/MasterFields properties. Selecting it is more of a visual issue but it involves clicking on the upper left side of the SubFormControl. Good luck.

[oleBucky]

Found it with the help of a YouTube video tutorial.

The Link Child Fields is set to PlayerID
The Link Master Fields is set to PlayerID

[RuralGuy]

Create an invisible TextBox on your MainForm named txtPlayerID. Now set your LinkMasterField to point to this TextBox (Me.txtPlayerID). Now change your Case statements to:
Case 1
Me.txtPlayerID = Me.VisHit01Combo
...and see what happens.