How can I populate a text box with info from...

[hankshad]

How can i populate a text box in a form with related data from a table when a combolist is used from that table?

for example NSN and model are on a table.
the combobox lists the NSN.
when choosing a NSN from the combobox I would like the text
box below it to fill with the appropriate entry from the same record on that table.

this is probably a stupid question, but I had to ask it anyway.

 

[danvlas]

My way (not necessarily the best way):

I make the control names on the form to match the field names in the table/view/stored procedure.

The stored procedures look like

Create Procedure "ProcName"
(@ID int)
As
Select * From TableName Where IDField = @ID

I have a function in a module:

Function fGetRst(Conn As adodb.Connection, ProcName As String, CallingForm As Form, KeyValue As Long) As adodb.Recordset

Dim oCmd As New adodb.Command
Dim fld As adodb.Field
Dim ctr As Control
With oCmd
    .ActiveConnection = Conn
    .CommandText = ProcName
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@id", adInteger, adParamInput, , KeyValue)
    Set fGetRst = .Execute
End With
Set oCmd = Nothing

For Each fld In fGetRst.Fields
    For Each ctr In CallingForm.Controls
        If fld.Name = ctr.Name Then
            If fGetRst.EOF Then
                ctr = Null
            Else
                ctr = fld.Value
            End If
        End If
    Next
Next

End Function

When I need the form to be populated:

Set rst = fGetRst(CurrentProject.Connection, "sp_GetWhateverData", Me.Form, Me("NameOfIDControl")

'if necessary, kill the recordset
Set rst=nothing

To have it flexible, I always have an Identity field as a primary key, and the stored procedures have always the "@ID" parameter.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[hankshad]

thanks, i appreciate the response
i couldnt get it to work for some reason, but i figured out another way to do it.

i set the nsn field on the main table as the many and the corresponding field on the other table as the one in a relationship; enforced referential integrity and cascade update related fields. in the form i put the model from the main table and the corresponding field from the sub table.
i set nsn from main table as a combobox with its source to be the values of nsn from the other table.

then in the code i put this:

Private Sub NSN_AfterUpdate()
Me.hank_Model = Me.nsnmodel_Model
End Sub

then make model from nsnmodel invisible.

by the way hank was the name of the main table and nsnmodel was the name of the other table.

but thanks just the same for trying to help me out