Access - modify code to select ID & surname

[Gregof1976]

Welcome

The code below filters the list box depending on the selected option 1, 2, 3, 4, 5
Unfortunately, after filtration in the list box is visible only EmployeeID, and I would like to see surname as well.

WybPrac.RowSource = "Select Prac.IDemploy" & _

I tried to modify the code in this way, but unfortunately it does not work (

WybPrac.RowSource = "Select Prac.IDemploy, Prac.Surname" & _


Can anyone hint on how to modify the code below?

Private Sub grpFirma_AfterUpdate()
On Error Resume Next
Dim strFirm As String
Select Case grpFirm.Value
Case 1
strFirm = "Ne"
Case 2
strFirm = "La"
Case 3
strFirm = "Re"
Case 4
strFirm = "In"
Case 5
strFirm = "Al"
End Select
WybPrac.RowSource = "Select Prac.IDemploy " & _
"FROM Prac " & _
"WHERE Prac.Firm = '" & strFirm & "' " & _
"ORDER BY Prac.Surname;"
End Sub

 

[vbaInet]

I tried to modify the code in this way, but unfortunately it does not work (

WybPrac.RowSource = "Select Prac.IDemploy, Prac.Surname" & _

You got it right but you need to change these properties:

Column Count - 2
Column Widths - 0cm; 2cm

2cm can be anything you like but it's best you use the Width of the control.

 

[Gregof1976]

I applied your advice and it works.

Once more thanks for your help

 

[vbaInet]

Happy to hear Greg

 

[Gregof1976]

What needs to be modify in code to make filter base on the two conditions come from the separate option group?

For example:

I have mark such condition:
- company

Case 1
strFirm = "Ne"

- department

Case X (from the second option group)

strDep = "Co"

In a result I would like to get only specific records for the 'company "AND" department'.

Thanks in advance for the tips.

 

[CBrighton]

Something like this should do it:

Private Sub grpFirma_AfterUpdate()
On Error Resume Next
Dim strFirm As String
Dim strDept as String
Dim strFilter as String
 
strFirm = null
strDept = null
strFilter = ""
 
Select Case grpFirm.Value
Case 1
strFirm = "Ne"
Case 2
strFirm = "La"
Case 3
strFirm = "Re"
Case 4
strFirm = "In"
Case 5
strFirm = "Al"
End Select
 
Select Case grpDept.Value
Case 1
strFirm = "Ne"
Case 2
strFirm = "La"
Case 3
strFirm = "Re"
Case 4
strFirm = "In"
Case 5
strFirm = "Al"
End Select
 
'Add company to strFilter
 
If not IsNull(strFirm) then
    strFilter = strFilter &  "Prac.Firm = '" & strFirm & "' AND "
end if
 
'Add department to strFilter
 
If not IsNull(strDept) then
    strFilter = strFilter &  "Prac.Dept = '" & strDept & "' AND "
end if
 
'Remove trailing "AND " from strFilter, will only fire if at least one option group has a selection
 
If Len(strFilter)>0 then
    strFilter = left(strFilter,len(strFilter)-4)
End if
 
WybPrac.RowSource = "Select Prac.IDemploy " & _
"FROM Prac " & _
"WHERE " & strFilter & _
"ORDER BY Prac.Surname;"
End Sub

 

[Gregof1976]

Looks perfect

The code working properly but what should I do to get for the selection option case1/case1 all recods from the data base.


CBrighton once more thanks for your support

 

[Gregof1976]

Looks perfect

The code working properly but what should I do to get for the selection option case1/case1 all recods from the data base.


CBrighton once more thanks for your support

I was not enough precise in my previous post.

In case when the first option for the second grup is marked I get filter only for the specific Firm (this option would be kind of "All" for the specific case from the first group selection).

 

[CBrighton]

Change

If not IsNull(strDept) then
    strFilter = strFilter &  "Prac.Dept = '" & strDept & "' AND "
end if

To

If not IsNull(strDept) and strDept <> "All" then
    strFilter = strFilter &  "Prac.Dept = '" & strDept & "' AND "
end if

This assumes that selecting "All"as the dept it won't add it to the filter string.

 

[Gregof1976]

Change

If not IsNull(strDept) then
    strFilter = strFilter &  "Prac.Dept = '" & strDept & "' AND "
end if

To

If not IsNull(strDept) and strDept <> "All" then
    strFilter = strFilter &  "Prac.Dept = '" & strDept & "' AND "
end if

This assumes that selecting "All"as the dept it won't add it to the filter string.

CBrighton code works perfect. Once more thanks for your valuable support.

 

[Gregof1976]

I would like to add another funcionality to database but code doesn't work properly.

Expected result is filtering combobox through TextField, simmilar like it is in the enclosed example (Form 'FrmWprPrac').

I started change the code for 'FrmWprPrac2' but unfortunatelly it doesn't work.

Any tips are welcome.

Kind Regards
Grzegorz