UserForm ListBox Trouble

[derekpax]

I have a UserForm where a person's name is selected and data is entered and stored in a database.

I want the UserForm to pre-load the data if the data has already been entered for a particular person.

After the name and corresponding data is found, I've been using:

ComboBox1.Value = ActiveCell.Value
TextBox1.Value = ActiveCell.Value

For ListBoxes I get the following error: "Could not set the Value property. Invalid property value."

The ListBox is not a multi-select ListBox

Any help would be appreciated

 

[BobRodes]

Sounds like you're using Excel and VBA. I don't know it as well as VB6, but if the Listbox works the same, there isn't a value property. A combo box is basically a list box with a text box on top of it, and what you're setting is the value of that text box when you set the combo box value property. You need to add an item to a list, or if you want to select an existing item, set the listindex property. Again, this assumes the same behavior as vb6. You might also try the Microsoft Office forum.

HTH

Bob

 

[johnwm]

See faq222-2244 paragraph 3 for the reasons to ask (and answer) questions in the right forum

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[sqit]

Lets display data first then you can start to add to database.

1. Create a folder called Contacts
2. Create an Access database called Contacts. Database has three fields Name, Street and City.
3. Put in some data: Smith, 1 Lark Street, New York Allen, 2 Oak Street, Sydney Jones, 3 Pine Street, London.
4. Use a std EXE and add a combo box named cboName, 3 text boxes named txtName, txtStrret and txtCity respectively.
5. Need to add an Adodc control to toolbox and place on form. Name it adoContacts
6. Right Click on this control then click Properties then click ADODC properties.
7. Click on record source and build record source
8. Test Connection. Click OK if all is well.
9. In Cmd Type select 1- adCmdText
10. Click on Record Source. In Command Text box type SELECT * FROM Contacts. Click OK and Apply.
11. Bind all text boxes to data source and data fields. Click on txtName and set Data Source to adoContacts and Data Field to Name. Set the other text boxed likewise to the required fields.
12. Double click on the form and enter the following code.

Option Explicit
Dim strDBName As String

Private Sub Form_Load()
adoContacts.Visible = False
Dim a As Integer
strDBName = "Provider= Microsoft.Jet.OLEDB.4.0;Data Source = " & App.Path & "\Contacts.mdb"
For a = 1 To adoContacts.Recordset.RecordCount
cboName.AddItem adoContacts.Recordset.Fields("Name").Value
adoContacts.Recordset.MoveNext
Next a
End Sub

Private Sub cboName_Click()
adoContacts.ConnectionString = strDBName
adoContacts.CommandType = adCmdText
adoContacts.RecordSource = "SELECT DISTINCT Name, Street, City FROM Contacts WHERE Name ='" & cboName.Text & "' "
adoContacts.Refresh
End Sub

All being well when you click on the down arrow on the combo box you should get the three names and the text boxes should contain the relevant details from the database.

If you want to use a list box its not much different.