tab control form to display corresponding record

[roddy17]

hi
I have a combo box on the top of my form, on the bottom of my form i have a multiple page tab control. When a value is selected in the combo box, i am wanting the rest of the related record to be displayed in the tab control. (There are about 40 fields of information.)
Is there a way that i can just link the whole tab control to the combo box? Or do i have to link each text box individually to the combo box?
Also, there is a place in the tab control where i display the value selected in the combo box, except that the autonum is displayed instead of the text, whereas the combo box displays text only. I set the text box to reflect the combo box using this code in the control source:
=[cboInvestMandate]
thanks so much
rod

 

[gusbrunston]

Sounds like you need some stuff in the "AfterUpdate" event for your combo box...

Private Sub cboInvestMandate_AfterUpdate()
    ' Find the record that matches the control.
    Dim Rs As Object
    Set Rs = Me.Recordset.Clone
    Rs.FindFirst "[PrimaryKey] = " & Str(Me![cboInvestMandate])
    Me.Bookmark = Rs.Bookmark
    cboInvestMandate = "" 'if you want to clear the cbo
End Sub

You can display one or more columns in your combo box, then assign the value from which ever column you wish to any text box. Here's a verbatim sample from a form...

Private Sub cboPayOwner_AfterUpdate()
    Me.txtPayee = Me.cboPayOwner.Column(1)
    Me.txtPayAddress1 = Me.cboPayOwner.Column(2)
    Me.txtPayAddress2 = Me.cboPayOwner.Column(3)
    Me.txtPayCity = Me.cboPayOwner.Column(4)
    Me.txtPayState = Me.cboPayOwner.Column(5)
    Me.txtPostalCode = Me.cboPayOwner.Column(6)
    Me.txtAccountNumber = Me.cboPayOwner.Column(0)
End Sub

You have to be careful about numbering the columns...sometimes the first column seems to be numbered zero (0)...

I'm no expert, but I've struggled with the above "stuff" enough to feel pretty sure. If you need more then let's know. Best of luck.
Gus Brunston
An old PICKer
padregus@home.com

 

[gusbrunston]

Me again...
You don't have to display all the columns in the combo box...you can assign a zero width to the columns you want to hide. That's all, Folks! Gus Brunston
An old PICKer
padregus@home.com

 

[roddy17]

thanks Gus,
i went and implemented the code for the AfterUpdate as recommended. It works great for updating the Tab Control to display the current record information. thankyou vm.
I went to work with the other segment of code that you had volunteered, and i had not so much luck with it for some reason. The following is what i keyed in:
Private Sub cboInvestMandate_AfterUpdate()
'find the record that matches the control.
Dim Rs As Object
Set Rs = Me.RecordsetClone.Clone
Rs.FindFirst "[Mandate Number] = " & Str(Me![cboInvestMandate])
Me.Bookmark = Rs.Bookmark

Me.txtTabInvestMandate = Me.cboInvestMandate.Column(2)
Me.txtInvestAdvisor = Me.cboInvestMandate.Column(3)
End Sub

Unfortunately however, all i get displayed for these is their associated Autonum's instead of the name that belongs. All the rest of the fields show up properly on the form. Hoping for some more imparted wisdom.
thankyou

 

[gusbrunston]

Glad the first part worked, anyway.
Before you can use the other stuff, you have to see that your combo box is based on a table or query as described in the data section on the combo's property sheet. The RowSouceType is "table/query", and the RowSource is your table or query. You select the number of columns to be included in your combo box in the format section of the combo box's property sheet: 1, 2, 3 or more. You choose Mandate Number (autonumber), which is probably the first column as the bound column; then the next columns in order contain the information from the current record that you want displayed. I hope this isn't gobblydegook. In other words, to assign a value called cboInvestMandate.column(2), there really needs to be such a value included in your combo box structure. Try letting the wizard help you construct a combo box, looking up values in your table or query, displaying more than one column, and then look at the code it constructs.

For example the Row Source Type for the example I gave you is: Table/Query
The Row Source is: qrySelectOwners
The code for the Row Source is:

SELECT tblPayerPayee.*, tblPayerPayee.PayerPayeeID, tblPayerPayee.PPSortOnThis, *
FROM tblPayerPayee
WHERE (((tblPayerPayee.PayerPayeeID)<8000))
ORDER BY tblPayerPayee.PPSortOnThis;

I didn't write this code; it is the SQL view of the query I designed. It puts all the fields from the table tblPayerPayee into the combo box, and then I can use them in the order they appear, from left to right. I've learned a lot from the wizard.

I have found I work best with a few tables, and lots of special purpose queries.

Maybe this will help some... Gus Brunston
An old PICKer
padregus@home.com