relating list boxes and text boxes to tables

[stevieboy1985]

i would be very grateful if you could help me with my problem.

I have created a form in access that contains several text boxes and one list box. I have also made a table. I would like to choose a record from the list box and all of the data from the table to be automatically put into the specific text boxes.

Please help!
thanks

 

[wemeier]

In the AfterUpdate event of the list box, add the following coding:

Private Sub List0_AfterUpdate()
Dim db as Database
Dim rst as Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("Table0&quot

rst.FindFirst "key = " & Me.List0.ItemData(Me.List0.ListIndex)
If Not rst.NoMatch Then
Me.Text0 = rst!Field0
Me.Text1 = rst!Field1
End If

rst.Close
Set db = Nothing

End Sub

What this does is to open a recordset (rst) of the table that you want to use to populate the text boxes (Table0). Then, it uses the currently-selected listbox (List0) entry (ListIndex) as the key to finding a match on the table. If a match is found, the contents of the table record (Field0, Field1, etc.) are used to populate the text boxes (Text0, Text1, etc.).

If you wanted to speed up processing, put the two Dim statements up above any procedures in the coding behind the form. Put the two Set statements into the OnOpen event of the form and put the ending Close and Set statements into the OnClose event of the form. This will build the recordset once when the form is opened and leave the recordset open until the form is closed.

 

[Gallowgate]

Hi there,

please bear with me if this is a stupid question, but I am still getting my head 'round Access.

I am trying to produce a simple DB to record outgoing documents. The documents recipient is chosen from a combobox with a list of names. I also want to record who the document was copied to - this could be up to say 10 other names. I was trying to use a multi select listbox for this, but I have no idea how I should write these to a table.

Any help gratefully received.

Many thanks

Martin

 

[dhookom]

What are you table structures? What is the Row Source of your list box?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]