Find Form...

[OMS]

Hi All,

Basically i have a form which consists of

Officer
Line Manager
Employee_Name
Date

The user selects an Officer which then filters the line manager then they select the employee which will then show the dates in a list box which have records for that employee.

They will then select a date and click find which will then open a form. (The form is dependent on the officer)

below is the code im currently using...

Private Sub Find_Feedback_Form_Click()
On Error GoTo Err_Find_Feedback_Form_Click
    Dim stLinkCriteria As String
    Dim stDocName As String
    Dim stDate As String
 
        If Me!Combo_Officer = "Ann Ward" Then
   stDocName = "frm_acceptance_feedback"
ElseIf Me!Combo_Officer = "Carol Metcalf" Then
   stDocName = "frm_enquirys_feedback"
ElseIf Me!Combo_Officer = "Deb Ludbrook" Then
   stDocName = "frm_enquirys_feedback"
ElseIf Me!Combo_Officer = "Dharmesh Patel" Then
   stDocName = "frm_enquirys_feedback"
ElseIf Me!Combo_Officer = "Emma King" Then
   stDocName = "frm_enquirys_feedback"
ElseIf Me!Combo_Officer = "Learie Eversley" Then
   stDocName = "frm_damages_feedback"
ElseIf Me!Combo_Officer = "Liz Hutchinson" Then
   stDocName = "frm_planning_feedback"
ElseIf Me!Combo_Officer = "Nina Ohol" Then
   stDocName = "frm_quotes_feedback"
ElseIf Me!Combo_Officer = "Phil Cushen" Then
   stDocName = "frm_enquirys_feedback"
ElseIf Me!Combo_Officer = "Rebecca Coxon" Then
   stDocName = "frm_systems&strgy_feedback"
ElseIf Me!Combo_Officer = "Reshma Bodalia" Then
   stDocName = "frm_diverbilling_feedback"
End If
stLinkCriteria = "[Employee_Name]= '" & Me![cboemployee] & "' And [Date]=#" & Me![Date] & "#"
 
' Temporary debugging code; delete when correct criteria string obtained
Debug.Print stLinkCriteria
 
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
Exit_Find_Feedback_Form_Click:
    Exit Sub
Err_Find_Feedback_Form_Click:
    MsgBox Err.Description
    Resume Exit_Find_Feedback_Form_Click
 
End Sub

Currently when i click find, the form is not opening with the record. its opening blank with no date at all.

Any help is appreciated!

Regards

OMS

 

[JANR]

I can't see anything wrong with your code aside from alot of if's
My first thought is to check the value of cboemployee, add a

Debug.Print stLinkCriteria
Debug.Print me.cboemployee

now run the code and check the debugWindow to see if both stLinkCriteria and the combobox displays what you expect. Also check if there really ARE records to be displayed, an empty recordset will give you a blank form.

JR

 

[OMS]

i have rang the debug and the values it is outputting are correct

[Employee_Name]= 'Dave Lucas' And [Date]=#10/02/2009#

If i run the query there is a record created for this person on this date.

i just cant see why it wont work

 

[DCrake]

What happens if Dave Lucas gets sacked or leaves the company and Willy Ekerslike get his job. You are going to have to change your code, repeatedly.

I would be tempted to create a table of Officers, if you do not have one, and include a field which denotes the type of form to open. Then in your combo box include this as a hidden column. Then instead of using all those if statements all you will need is

stDocName = Me!Combfficer.Column where n is the column number.

Also the word Date is an Access reserved word you need to change this immediately. This is most likely the root cause of the problem.

Again systems&strgy_feedback is bad naming convention. Do not use characters like & and spaces. Access treats and ampersand as a concatenation character, such as Lastname & " " & FirstName


David