Assigning Text box from Combo box

[mans]

Hello,

I have a combo box set up as follows:

Do While Not rt.EOF
Combo14.AddItem rt!LastName & ", " & rt!FirstName
Text58 = rt!FirstName
Text59 = rt!Surname
rt.MoveNext
Loop

When the user clicks on the combo box above, the text boxes on that same page must also show the LastName in text1 and Firstname in text2 (separately). Basically I would like to fill in for the unknown’s below (it is a separate command on the same form):

daoDB36.openrecordset (Select Address, LastName, FirstName From Contact where contactLastName = unknown1 and contact.FirstName = unknown2)

-Unknown1 – Should show rt!LastName only from Combo14 above (in Text1)
-Unknown2 – Should show rt!FirstName only from Combo14 above (in Text2)

Thank You

 

[RalphBrown]

I'm trying to do something similar at the moment.

The best I've done so far (better suggestions would be most welcome) is:

Private Sub MyCombo_LostFocus()
DoRefresh
End Sub

Private Sub cmd_Refresh_Click()
DoRefresh
End Sub

Private Sub DoRefresh()
textbox1.Text = ""
textbox2.Text = ""
With DataControl1
.Recordset.FindFirst "Field0 = '" & MyCombo.Text & "'"
If Not .Recordset.NoMatch Then
If Not IsNull(.Recordset("Field1&quot) Then
textbox1.Text = .Recordset("Field1&quot
End If
If Not IsNull(.Recordset("Field2&quot) Then
textbox1.Text = .Recordset("Field2&quot
End If
End If
End With
End Sub

My cmd_Refresh button isn't absolutely necessary for this but I think is helpful to the user.

I hope this helps - it's not perfect in that the user has either to click on some other control to get off the combo box, or click the Refresh button, but it's the best I can come up with so far.

You could also try using the Change event with your combo box, but I have found this to be rather unreliable.

Regards,
Ralph

 

[sunaj]

I've got a solution here that are similar to RalphBrowns.
However Ralphs solution cannot handle identical first names and it also uses a datacontrol which isn't necessary.
My solution requires that you have a autonumber field in your table (which I always use a primary key anyway).
It looks like:
------------------------------------------------------------
Option Explicit
Dim conn As Connection
Dim Rst As Recordset

Private Sub Combo1_Click()
Rst.MoveFirst
Rst.Find "tblMyTable1ID=" & CStr(Combo1.ItemData(Combo1.ListIndex))
If Not Rst.EOF Then Text1.Text = Rst.Fields("MyNumberfield&quot
End Sub

Private Sub Form_Load()

Set conn = New Connection
conn.open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\tmp\test.mdb"

Set Rst = New Recordset
Rst.CursorLocation = adUseClient
Rst.open "SELECT MyTextField,tblMyTable1ID,MyNumberField FROM MyTable1;", conn, adOpenStatic, adLockOptimistic

Combo1.Clear
If Not Rst.EOF And Not Rst.BOF Then
Rst.MoveFirst
While Not Rst.EOF
Combo1.AddItem Rst.Fields("MyTextField&quot
Combo1.ItemData(Combo1.ListCount - 1) = Rst.Fields("tblMyTable1ID&quot
Rst.MoveNext
Wend
Else
'no records to show
End If
End Sub
-----------------------------------------------------------
You can replace 'MyTextField' with 'FirstName' and MyNumberField with 'LastName'.

Another (much faster) solution is to map the recordset into an array and let the index of the combobox match the array. I also have an example of that if you are interested.
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[CajunCenturion]

I may be missing something (wouldn't be the first time), but a couple of things come to mind.

I have a combo box set up as follows:

Do While Not rt.EOF
Combo14.AddItem rt!LastName & ", " & rt!FirstName
Text58 = rt!FirstName
Text59 = rt!Surname
rt.MoveNext
Loop

In this initialization loop, the assignments to text58 and text59 are unncessary, as they are constantly being overwritten within the loop. I also not that the format of the Combo14 data is Lastname Comma Firstname

When the user clicks on the combo box above, the text boxes on that same page must also show the LastName in text1 and Firstname in text2 (separately).

Private Sub Combo14_Click

Dim Names as Variant

Names = split(combo14.Text, ",&quot
daoDB36.openrecordset (Select Address, LastName, FirstName From Contact where contactLastName = '" & Names(0) & "' and contact.FirstName = '" & Names(1) & "')"
' or perhaps
text1 = names(0) ' Lastname
text2 = names(1) ' Firstname

End Sub

You'll need to apply whatever error handling is appropriate.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

sunaj
BTW, were you able to get that 2D interpolation code from the web reference?, or are you still interested in seeing the pascal algorythm? Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[mans]

Thank you all, particularily CajunCenturion, you made it very easy.

 

[sunaj]

Cajun,
Thank you for remembering the interpolation. I downloaded the manual that you pointed me towards, but the maths there are simply not detailed enough for me to put it together...
Then I had other things to do and got away from it agian. I'm still interested in having a look at the Pascal algorythm if you can dig it out
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'