Help with ItemData

[tcox]

The form changes roles depending on what department is selected.

There is an input combo box that I can choose personnel from different departments.
I would like the combo box to default to the selected department. people can be borrowed from a different department

The code snippet looks up the ID in the table that matches the name of the selected department.
then tries to apply that to the combo box's ItemData property. I have tried many iterations

Private Sub CBO_SelDept_AfterUpdate()
strDepartment = Me.CBO_SelDept.Column(0)
Dim DeptList As Long
DeptList = DLookup("[ID]", "Department_Personnel", "[Department] = '" & strDepartment & "'") 'works!
MsgBox DeptList 'It works!
Me.CBO_Department.ItemData (DeptList) 'Not working! :(
Me.CBO_Department.Requery
Me.CBO_ThorneID.Requery
End Sub

returns with


Compile error:
Invalid use of property


-Tevis

[June7]

Why do you have two comboboxes for department?

Why using ItemData?

Me.CBO_Department = DeptList

[ssanfu]

From HELP: "The ItemData property returns the data in the bound column for the specified row in a combo box."

In your code, if the variable "DeptList" has a value of 102365, do you really have that many rows in the combo box???

You probably don't have more than 10 departments in the combo box... so ItemData has a range of 0 to 10.

When I use the ItemData property, it is for looping through the rows of a combo box/list box.

[tcox]

I do have two combo boxes for departments, one is for the production departments(5), such as mixing, encap, powder fill, bottle line etc.. the other is for employees assigned department(10) such as warehouse, sanitation, CQ, mixing, powder fill, encap.. etc.

Most of the time, employees are assigned work from within their own department, but not always. So, I just wanted the convenience of the combo box already having the selected department for production, select the corresponding department for personnel.

My trouble is don't know what it want as an input other than a hand type value. .ItemData( variable here? )

[June7]

If the lists for each combobox are not the same, how could you set the second equal to selection from the first? This makes no sense to me. Are combobox selections limited to the listed items? Is value of combobox an ID and the text description is displayed (this is an 'alias')? If the item is not in the list and ID is the value, there will not be an 'alias' to display, even if combobox selection is not limited to list.

[tcox]

screen shot

[June7]

That doesn't answer my questions from previous post. Did some edits. Might read again.

The image really does not clarify anything for me.

[tcox]

strDepartment = Me.CBO_SelDept.Column(0)
Dim DeptList As Long
DeptList = DLookup("[ID]", "Department_Personnel", "[Department] = '" & strDepartment & "'") 'works

gets the name of the selected department and stores it in the string strDepartment. A combo box that lists departments from the Departments table.
Then I lookup the ID of the corresponding department in the Department_Personnel table and assign it to the DeptList Long Variable.

I then report that ID number in a message box to check to see if the Dlookup was correct.
DeptList is holding the ID of the corresponding department. (The ID of the entry in the Department_Personnel table that has the same name in the Department table)

I then try to use that variable (DeptList) to select the corresponding list item in the combo box.

[tcox]

that is the contents of the listboxes, the contents of the tables are the same.

[June7]

If the ID is in the list, then this should work:

Me.CBO_Department = DeptList

If you want to provide db for analysis, follow instructions at bottom of my post.

[tcox]

in very short:
1.Top combo box, department table.
2. Lookup ID of department with same name in department_personnel table
3. use that ID to preselect the value in the personnel department combo box

[tcox]

If the ID is in the list, then this should work:

Me.CBO_Department = DeptList

If you want to provide db for analysis, follow instructions at bottom of my post.

ok.

.......

[tcox]

*facepalm*
You'd think I was a German engineer trying to come up with the solution..
Simple, just assign the other combo box the same value, if the user wants something different, they will select it.

[tcox]

Private Sub CBO_SelDept_AfterUpdate()
strDepartment = Me.CBO_SelDept.Column(0)
Me.CBO_Department = Me.CBO_SelDept.Column(0)
Me.CBO_ThorneID.Requery
End Sub

simple and sweet.
I think I can blame it on the 10 hr shifts we are doing during the holidays (to get 3 day weekends!)
asleep at 11, up at 3:40, work by 5am... zzzZZZZ

[ssanfu]

Just an FYI......

You don't need to add ".Column(0)" - that is the default (the bound column).



You apparently do not have these two lines

Option Compare Database
Option Explicit

at the top of EVERY code module, because in your posted code, "strDepartment" has not been declared. While the code will execute, you can have variables misspelled and not know it. This can result in debugging hell.

There is an option in the VBA IDE to require variable declaration (TOOLS/OPTION/EDITOR tab/Require Variable Declaration check box).
Checking the box will add "Option Explicit" to a NEW module.
For existing modules (standard, form & report), you would need to add the line manually.