whats wrong with this coding for searching table 2

[timotai]

Hi
I have a button which assigns a user a shiftpattern from an entry in a combo box. Now to do this it need to either amend an entry in my table weeklog if they are already in the table or to create a new entry. My code is supposed to look at the table, determine if their is an entry for that user or not and then take the correct path.

There is another list box which is on my form which contains a list of all the employees. They select the employee they wish to change the shift pattern of, select the shift pattern from a combo box and then click assign.

Here is my code:

Private Sub Assign_Click()
Dim emp As Variant

emp = DFirst("Employee Number", "WeekLog", [Employees])
If emp = "" Then GoTo app Else GoTo upd

app:
DoCmd.OpenQuery "setshift"
DoCmd.OpenQuery "settask"
GoTo fini
upd:

DoCmd.OpenQuery "setshift2"
DoCmd.OpenQuery "settask"

GoTo fini

fini:
End Sub

Every time I run it I get the following error:

Run-time error '5':

This action will reset the current code in break mode.

Do you want to stop the running code?


This I followed the code through and it gets stuck on the first hurdle:

emp = DFirst("Employee Number", "WeekLog", [Employees])

What is wrong with this code. i have used similar ones elsewhere in my database but never have I had this problem.

Any help is greatly appreciated.

Many Thanks

Tim

 

[JoanneM]

Hi there


first rule of coding try never to use goto

so your code should look something like


Private Sub Assign_Click()
Dim emp As Variant

emp = DFirst("Employee Number", "WeekLog", "[Employees] = " _
& Forms!frmname!cmboEmployeenumber)

select case emp
case ""
DoCmd.OpenQuery "setshift"
DoCmd.OpenQuery "settask"

case else
DoCmd.OpenQuery "setshift2"
DoCmd.OpenQuery "settask"

end select

End Sub


I have serious reservations about the dfirst as it will find any person for any date so I hope the week log table is generated weekly
Additionally how do you keep track of the number and types of shifts people have been allocated - I could see someone changing back and forth without restraint or reasoning.


Just passing thought that's all

regards


Jo