opening a form using a listbox with different criteria

[icemonster]

hi.

what's the best way to open a form using a listbox? but thing is, i need it to open when the column 0 is null, it will open or filter the record using column 1?

i tried this but is not working.

Private Sub lst3month_DblClick(Cancel As Integer)
If IsNull(Me.lst3month.Column(0)) Then
   DoCmd.OpenForm "frmReviewDetails3MONTHS", , , "EMPLOYEEID = " & Me.lst3month.Column(7)
Else
   DoCmd.OpenForm "frmReviewDetails3MONTHS", , , "EMPLOYEEREVIEWID = " & Me.lst3month.Column(0)
End If
End Sub

 

[boblarson]

If you click on the listbox it will not be null, so it will never use the first option. Can you explain a bit more of what you have set up and what the different values in the listbox represent?

 

[icemonster]

oh, i thought i had it so that if the column 0 for that listbox is null, it would open something else.

basically, the two columns would be reviewid and employeeid, if the reviewid is null it would look at the employeeid (used for new employee's without reviews).

so what i really would like to do is, if column 0 is null, open using employeeid criteria, but if column 0 is not null, use review id as criteria.

 

[Beetle]

A couple questions.

1) What is your data structure? If this is some sort of employee review process then you should have, at a minimum, two tables;

tblEmployees
**********
EmployeeID
Firstname
LastName
etc.

tblReviews
***************
ReviewID
EmployeeID
ReviewDate
Comments
etc.

If an employee has no reviews then they should have no records in tblReviews. In other words, you would not have a situation where the EmployeeID field in tblReviews would be populated but the ReviewID would be Null.

2) What do you mean by "it doesn't work". Is nothing happening at all? Are you getting a Type Mismatch error? Some other error?

 

[icemonster]

ah.

but am using a query for that listbox so that it even without records, the employee's are listed hence if they don't have a record, they still have a next review date cause the next review date is calculated based on their hire date.

yeah, am getting a mismatch and now, nothing happens.

what's the best or sample of a code to start of that if the column 0 is null, it would open the form using the column 1 criteria?

 

[Beetle]

Can you give us some more info about your table structure an the data types of the fields in question?