VBA code not working

[Chintsapete]

In my form I want to run a append query after update the field EmpNo if it is a new record. I'm don't know what I'm doing wrong here.

Private Sub EmpNo_AfterUpdate()

With Me.EmpNo
    If Me.NewRecord Then
    DoCmd.OpenQuery ("A2EmpNoSalaryMasterA")
    End If
    End With

End Sub

Thanks for any help

 

[BlueIshDan]

Could you explain your planned process here? I'm not seeing it.

With that said, and with my limited understanding of what is going on here. Here is my stab at it.

If Len(Me.EmpNo & vbNullString) > 0 AND Me.NewRecord Then
	DoCmd.OpenQuery ("A2EmpNoSalaryMasterA")
End If

 

[Ranman256]

After update it is not a new record, therefor
If Me.NewRecord Then
will never fire.

I think all you need is:

Private Sub EmpNo_AfterUpdate()
 DoCmd.OpenQuery ("A2EmpNoSalaryMasterA")
end sub

 

[BlueIshDan]

After update it is not a new record, therefor
If Me.NewRecord Then
will never fire.

I think all you need is:

Private Sub EmpNo_AfterUpdate()
 DoCmd.OpenQuery ("A2EmpNoSalaryMasterA")
end sub

Will it fire if the record he is currently filling is a new record???

 

[Ranman256]

here in afterupdate...it fires everytime regardless.
youd need a flag to indicate its a new rec before you get here.

 

[Chintsapete]

Could you explain your planned process here? I'm not seeing it.

With that said, and with my limited understanding of what is going on here. Here is my stab at it.

If Len(Me.EmpNo & vbNullString) > 0 AND Me.NewRecord Then
	DoCmd.OpenQuery ("A2EmpNoSalaryMasterA")
End If

This is exactly what I needed thanks. I only need to fire it when the employee number doesn't exist in the other table.
I do understand now why my code can't work too Thanks
Thanks a lot to all of you for quick response.

 

[Chintsapete]

Strangely, Dan's solution worked at first, but next day I tried it again and it didn't trigger the query. I don't understand why.
I ended up exploring a bit more along the route of Ranman. The problem with that is of course if someone mistypes the number and wants to correct it it leaves the undesired number behind.
I did come across some other way of doing it below, which should work for most scenarios except for mistyping and correcting.
I know it's almost the same as Ranman's solution, but has the nice additional feature of checking the table first.
Is there a way to somehow run an edit?
I was thinking, once the record is finished set up to lock the field for edits.

Private Sub EmpNo_AfterUpdate()
DoCmd.RunCommand acCmdRefresh
'checks if number exists
If DCount("EmpNo", "[Salaries Master]", "EmpNo=" & Forms!EmployeesDetailQ!EmpNo) > 0 Then
MsgBox "This number already in use"
Exit Sub
End If
'Adds EmpNo to the table salaries master 
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Salaries Master")

rs.AddNew
rs.Fields("EmpNo") = [Forms]![EmployeesDetailQ]![EmpNo]
rs.Update

rs.Close
Set rs = Nothing
db.Close

End Sub

Thanks for any help