RE combo boxes and pulling data from several forms

[SJBohm]

Hello,

I have a table with the following fields:

EmployeeNum
Lastname
Firstname
MI

among many others. I also have a form with a combo box and a text box. What I would like to do is:

The combo box needs to list the last name, first name, and middle initial of all the records in the table described above. Also, when I click on a single record, I would like it to display the information in the combo box in this format when it is "closed":

Lastname, Firstname MI

Also, I would like the EmployeeNum for the selected record to be displayed in the textbox.

I got as far as displaying the records in the combo box, but cannot accomplish the other two tasks. I am using Access 2000 if that makes a difference. Any help or tips would be greatly appreciated!

Thanks in advance

Steve

 

[RichD]

You could concatenate the the individual fields in the combo boxes Row Source as follows:

In the rowsource query ceate a new second column called ConCatName. In the query field enter:
ConCatName: [lastname] & ", " & [FirstName] & " " & [MI]

Make employeeNUmber the first column and in the properties box Format Column Widths as 0cm; 3cm
This way the Employee number will not be displayed.

In the combo boxes after update event enter:

txtBoxEmployeenumber = comboBOxName 'this will display the emp number hidden in the combo box.

HTH

Rich

 

[SJBohm]

Thank you very much Rich! I had been trying to do something just like your solution, but just couldn't figure it out.

I have another problem now though and cannot figure it out for the life of me. The combo box displays exactly the way I wanted, but I have not been able to actually select one of the choices from its list. I created a query and put that in the RowSource property of the combo box, so it's pulling all the data correctly. The help files installed on my machine suggested that I don't have the ControlSource property set correctly if I am unable to select a record from the list in a combo box. After several days of looking and trying things, I still cannot figure out what I need to be doing here.

Any kind of help would be *greatly* appreciated, and I can post my table/query/form designs if you need them. Thanks again!

Steve

 

[gusbrunston]

Hi:

Butting in:

What DOES your RowSource property for the combo box say?

And here's some code you could put in the After Update event in the cbo's property sheet:

Private Sub YrComboName_AfterUpdate()
    ' Find the record that matches the control.
    Dim RS As Object
    Set RS = Me.Recordset.Clone
    RS.FindFirst "[YrPrimaryKeyFieldName] = " 
          _& Str(Me![YrComboName])
    Me.Bookmark = RS.Bookmark
End Sub

Take out the underscore in "_&".

Hope this is helpful.
Gus Brunston An old PICKer, using Access2000.

 

[SJBohm]

Hi Gus Brunston,

The RowSource property of the combo box simply says "IsEmployed", which is a query I created that employs the SQL statement:

SELECT EmployeeNum, [Lastname] & ", " & [Firstname] & " " & [MI] AS ConcatName
FROM [Personnel Table]
WHERE [Personnel Table].Terminateddate Is Null
ORDER BY [Lastname] & ", " & [Firstname] & " " & [MI];

It appears like the data is getting into the list just fine, but I can't select anything. I've found a few older threads regarding not being able to select a record in a combo box, but I still haven't been able to solve this problem.

Thank you so much for any help you have to offer!

Steve

 

[gusbrunston]

Did you try the suggested code in the After Update event? Gus Brunston An old PICKer, using Access2000.

 

[SJBohm]

I apologize for not mentioning it in my last post. I did copy and paste your code into my form, as well as substituting in the name of my combo box. Still no luck. I'll play with it more tonight after work, and I'll post what I try with the results.

Thanks!

Steve

 

[gusbrunston]

Hi:
One of the important reasons for following naming conventions is that when one sees "qryIsEmployed" in the row source box he knows it is a query, and not a table or field name. Just an idea... Gus Brunston An old PICKer, using Access2000.

 

[RichD]

SJ,

I have had another look at thgis and cannot reproduce your problem, unless I set combo box Locked to Yes. - It can't be that can it?

I am also not sure if you cannot select any record or one record in particular. If the latter try deleting and re-entering.

Also I amy have missed the point Gus made about setting a bookmark. I think there is an error in one line:

Change Me.Recordset.Clone to Me.RecordsetClone

Hope some of this may help.

Rich