Insert Table from Unbound form

[Zulfi]

Hi All,

I have 2 tables Employees [EmployeeID, EmployeeName] and LeaveRecord
[EmployeeID, LeaveStart, LeaveFinish,LastLeaveFinish]

I try to insert data into leave record from a form with the following SQL:

Private Sub Command3_Click()
Dim StrSQL As String
StrSQL = "INSERT INTO Leaves ( EmployeeID, LeaveStart, LeaveFinish ) " _
& "SELECT " & Me![Combo7] & ", #" _
& Me![Text11] & "#, #" _
& Me![Text13] & "#;"
StrSQL = "UPDATE Leaves set LastLeaveFinish=LeaveFinish where EmployeeID=" &
Me.EmployeeID
DoCmd.RunSQL StrSQL
End Sub

However, if I input new record for EmployeeID already exist in LeaveRecord
table it return error (run time error 2501).

Appreciate your help.

Zulfi

 

[Arvin Meyer MVP]

EmployeeID in the LeaveRecord table needs to be a long integer, not an
autonumber, and the relationship is 1 to many.

 

[Zulfi via AccessMonster.com]

Thanks a lot.....I guess the long integer make it works.
However, I could not workout the 1 to many as I only have 2 tables with
EmployeeID is common variable between them.

Regards,

EmployeeID in the LeaveRecord table needs to be a long integer, not an
autonumber, and the relationship is 1 to many.

Hi All,

[quoted text clipped - 21 lines]

Zulfi

 

[Arvin Meyer MVP]

The reason is that 2 autonumbers cannot have a relationship if it's 1 to
many.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks a lot.....I guess the long integer make it works.
However, I could not workout the 1 to many as I only have 2 tables with
EmployeeID is common variable between them.

Regards,

EmployeeID in the LeaveRecord table needs to be a long integer, not an
autonumber, and the relationship is 1 to many.

Hi All,

[quoted text clipped - 21 lines]

Zulfi