Go To Record

[danishtune]

The record source of my employee imput form has two tables. An Employee table and a position table. The relationship is one to many with many positions to one employee. This is due to one employee code being for Vacancy.

On the form is a button which enables you to change the employee id of a position number effectively changing the employees position.

Employee pt2 is in positon 1450

Employee table has pt2, pat, thomas, salary, datestarted, etc.
Position table has 1450 , pt2, positon type , positon location, etc.

The procedure behind the change position button, asks what position you want to change to.
You enter 1101
The procedure creates a new positon 0001 and puts your id into it, pt2 and then removes the pt2 from 1450 and puts the pt2 into 1101

This is due to the linking and cascading in the tables. The tables now read

Employee table still is the same
Positon table has 1101, pt2, positon type, position location. etc.
the old positon now has the vacancy code as the employee id.

The problem is that during this procedure the form never moves to the new positon number. It remains on position 1450 until the form is requeried. Then the form goes to record 1. I need to be able to use a goto statement with a variable stored at the time the procedure asks for the new position number. Whether this new positon number is stored in the procedure or on an unbound text box on the form does not matter. I can do either. I just can't get any of the go to or find methods to work with a variable or use the value of the unbound text box to reset the form to the record with the new position number.

If I close the form and reopen it and do a find on any of the fields of the form, the record is updated and fine.
How can I go to the record programically?

 

[nrmarthi]

send ur database file.

 

[danishtune]

Here is the code behind the button. I am not able to post the whole database. Will this help?

I am looking for a search function that works with a variable to bring up a particular record on a form. It can be a variable in the code, or it can be an unlinked text box on the form itself that is populated in the code behind the form. Any ideas?


The problem is the code goes diirectly into the tables to create the new record in the position table and to update the old position to vacant and the new position to the employee id.

That means the form never moves off of the old record. I did look at bookmark, but I don't think it will work if the form does not change records. Here is the code if it will help. What I need is how to do a search in code for one of the fields of the form and have the form progress to the record based on the search.

Private Sub ChngPos_Click()
On Error GoTo Err_ChngPos_Click

'This code changes the id field of the employees current position to vacant 0009
'Then employees id into their positions.
Dim SQL1 As String
Dim SQL2 As String
Dim SQL10 As String
Dim Result As String
Dim IDNO As Variant
Dim POSNO1 As Variant
Dim NewPosNo As String
Dim SRS As String
Dim SRS2 As String

'This creates a record for the history file.

SQL10 = "INSERT INTO TBLActionHist ( ChngType, EffDate, UpdateDate, UpdatedBy, LanID, PosNo, "
SQL10 = SQL10 & "Budget, PerfPay, [Note] ) "
SQL10 = SQL10 & "Select Forms!FRMEmployee.ChngType AS ChngType, Forms!FRMEmployee.ChngEffDate AS EffDate, "
SQL10 = SQL10 & "Date() AS UpdateDate, 'pt2' AS UpdatedBy, Forms!FRMEmployee.LanID as LanID, "
SQL10 = SQL10 & "TBLPosition.PosNo AS PosNo, Forms!FRMEmployee.Budget AS Budget, "
SQL10 = SQL10 & "Forms!FRMEmployee.PerfPay as PerfPay, Forms!FRMEmployee.Note AS [Note] "
SQL10 = SQL10 & "FROM TBLPersonnel RIGHT JOIN TBLPosition ON TBLPersonnel.LanID = TBLPosition.LanID "
SQL10 = SQL10 & "WHERE (((TBLPosition.LanID)=Forms!FRMEmployee.[LanID])) "

'Create a vacant position to hold the employee lanid
SQL1 = "UPDATE TBLPosition SET TBLPosition.LanID = 'vac' "
SQL1 = SQL1 & "WHERE (((TBLPosition.PosNo)=Forms!FRMEmployee.PosNo)) "
'Enter the employee Id into the new position
SQL2 = "UPDATE TBLPosition SET TBLPosition.[LanID] = Forms!FRMEmployee.[LanID] "
SQL2 = SQL2 & "WHERE (((TBLPosition.PosNo)= Forms!FRMEmployee.[POSNO1] )) "

'Employee must be showing in the form
Result = MsgBox("IS THIS THE EMPLOYEE TO MOVE?", vbYesNo)
If Result = vbNo Then

MsgBox "Search for Employee to Change", vbOKOnly
Exit Sub
Else
'Check tables for valid vacant position
SRS2 = UCase(InputBox("Enter the new position number"))
Forms!FRMEmployee.[EmpLanID] = Forms!FRMEmployee.[LanID]
Forms!FRMEmployee.[POSNO1] = SRS2

IDNO = DLookup("[LanID]", "TBLPosition", "[TBLPosition]![PosNo] = Forms!FRMEmployee.[POSNO1] ")

If IsNull(IDNO) = True Then
MsgBox "Position Number is invalid Try Again", vbOKOnly
Exit Sub
Else
If IDNO <> "vac" Then
MsgBox "Position is filled.", vbOKOnly
Exit Sub
Else

DoCmd.SetWarnings False
Forms!FRMEmployee.ChngType = "OP"

Forms!FRMEmployee.LastPosNo = Forms!FRMEmployee.PosNo
MsgBox "Code is OK to here1", vbOKOnly
Forms!FRMEmployee.ChngEffDate = InputBox("Enter Effective Date")
MsgBox "Code is OK to here2", vbOKOnly
DoCmd.RunSQL SQL10

DoCmd.RunSQL SQL2
DoCmd.RunSQL SQL1
Forms!FRMEmployee.ChngType = "NP"
MsgBox "Enter additional Info. - Salary, Pos. End Date, Etc.", vbOKOnly
DoCmd.RunSQL SQL10
MsgBox "Code is OK to here3", vbOKOnly
Forms!FRMEmployee.FindRecord "SRS2"
MsgBox "Code is OK to here4", vbOKOnly
SRS = Forms!FRMEmployee.CurrentRecord
MsgBox "Code is OK to here5", vbOKOnly
Me.Requery
MsgBox "Code is OK to here6", vbOKOnly
DoCmd.GoToRecord , , acGoTo, SRS
MsgBox "Code is OK to here7", vbOKOnly

DoCmd.SetWarnings True

End If
End If
End If
Exit_ChngPos_Click:

Exit Sub

Err_ChngPos_Click:
MsgBox Err.DESCRIPTION

Resume Exit_ChngPos_Click

End Sub

Does this help?