Use field condition to open correct record "Newbie"

[stretch66]

Hi,

Quite new to Access and am developing an HR database for work.

Have a EmployeeIntro form with standard employee information including the Payroll Number which is the primary key. The Employee Intro Form includes a button with a link to Personal Details which holds all employee information.

I need to link the two forms so that when they are in record Joe Bloggs on the Employee Intro they can click the Personal Details Button and see Joe Bloggs records. Obviously there is some condition where the field Payroll_Number on EmployeeIntro = the field Payroll_Number on PersonalDetails but havn't done any coding for about 2yrs and never used VB so don't know the syntax.....can anyone help please.

Have included the code below for the Employee Intro Form.

Private Sub PersonalDetailsButton_Click()
On Error GoTo Err_PersonalDetailsButton_Click


Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "fmPersonalDetails"


DoCmd.OpenForm stDocName, , , stLinkCriteria


DoCmd.Close acForm, "fmEmployeeIntro"

Exit_PersonalDetailsButton_Click:
Exit Sub

Err_PersonalDetailsButton_Click:
MsgBox Err.Description
Resume Exit_PersonalDetailsButton_Click

End Sub

Thanks

 

[reclusivemonkey]

You're almost there. The strLinkCriteria needs to be a valid SQL WHERE condition, but without the where! (See F1 for help on OpenForm). So, you need to reference the field where you have the payroll number.

myPayrollNoField = Forms!frmEmployeeIntro!Payroll_Number
strLinkCriteria = "Payroll_Number = " & myPayrollNoField

Obviously placing the correct form and field names in there. I think that code above should work, I get the "s and 's mixed up sometimes...

 

[stretch66]

Hi,

Tried it but couldn't get it to work.....must be me!

Have attached my test DB if that helps.

 

[reclusivemonkey]

No DB attached. I wouldn't want to download it at work either, no offence.

What error message/wrong result are you getting? Do you have the form settings to disallow filters? Any other filter settings on that form?

 

[stretch66]

Fair enough no offence taken. Not getting any error messages just not linking to the correct record.

The name of the Payroll Number field is PayrollNumber.
Form names are:
fmEmployeeIntro which includes the button linking to the fmPersonalDetails.

Did try something else but code is as below if you can see anything blatantly wrong.

Cheeers

Private Sub PersonalDetailsButton_Click()
On Error GoTo Err_PersonalDetailsButton_Click


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "fmPersonalDetails"
myPayrollNoField = Forms!fmEmployeeIntro!PayrollNumber
strLinkCriteria = "PayrollNumber = " & myPayrollNoField

'stLinkCriteria = "[Payroll_Number]=" & Me![Payroll_Number]

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fmEmployeeIntro"

Exit_PersonalDetailsButton_Click:
Exit Sub

Err_PersonalDetailsButton_Click:
MsgBox Err.Description
Resume Exit_PersonalDetailsButton_Click

End Sub

 

[reclusivemonkey]

myPayrollNoField = Forms!fmEmployeeIntro!PayrollNumber
strLinkCriteria = "PayrollNumber = " & myPayrollNoField

My crappy syntax to blame I'm afraid...

The correct syntax should be;

strLinkCriteria = "PayrollNumber = '" & myPayrollNoField & "'"

Just to be clear, around the field its SINGLE QUOTE, DOUBLE QUOTE, AMPERSAND, FIELD NAME, AMPERSAND, DOUBLEQUOTE, SINGLEQUOTE, DOUBLEQUOTE. I tried an example, and it worked, so that should be it I think.

 

[stretch66]

Hi Rec,

Still not working. Could it be that the PayrollNumber Field on the two different forms have the same name???

Is strLinkCriteria = "PayrollNumber ......pointing to the Employee Intro Initial Form

and

'" & myPayrollNoField & "'" ...... pointing to the Record in Personal Details

It read in my code shown below:

stDocName = "fmPersonalDetails"

strLinkCriteria = "PayrollNumber = '" & PayrollNumber & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "fmEmployeeIntro"

Exit_PersonalDetailsButton_Click:
Exit Sub

 

[reclusivemonkey]

Is strLinkCriteria = "PayrollNumber ......pointing to the Employee Intro Initial Form

Nope. Your strLinkCriteria is applied to the Personal Details Form which as I understand (if I have followed correctly) follows from the Employee Intro form. As it is passed to the form as an SQL WHERE condition, supplying the field name should be enough. Make sure that your Personal Details indeed has a PayrollNumber field.

'" & myPayrollNoField & "'" ...... pointing to the Record in Personal Details

Nope, myPayrollNoField should refer to the Payroll number on your Employee intro form;

myPayrollNoField = Forms!fmEmployeeIntro!PayrollNumber

I would suggest having a look at the second form you are opening (Employee Details) and setting the filter on the form properties, to make sure that you can at least filter on this and there is nothing in the properties that is preventing you from filtering on this form.

 

[stretch66]

Hiya

Just wanted to say.....sorted it!!

Spoke to someone and they said about placing an SQL statement on the form itself. Did that and changed it a bit and it works!!!

Thanks for your help anyway.I'm sure I'll be back with more.