Primary Key Error

[sullyman]

I have been using a Primary key (numerical eg. 002, 003 etc. from Employees in a test database) and all is working fine.

I now want to use the company system employee number eg. b002, b003 as the primary key but when i changed the records and used this new PK with combos etc., access declares an invalid column.

Any ideas? when i switch back to just numerical it is fine

 

[boblarson]

Don't mess with the primary key. It is for the system use only anyway. You can ADD that extra field if you want but try to remember that Primary Keys are ONLY really there to identify a unqiue row of data for the system. Let IT manage its own keys and you manage the stuff you want to see.

 

[sullyman]

Thanks Bob but i have a dilemma. I am currently creating an access front end for SQL

All was going well but to use this system better and gain more company data already generated, i need to use Employee ID's (eg. b004, b005) from another SQL database as the other database already has more data which i could use.

I have got the import working today where it will import all employee names and IDs from the other Database. I hope to run this every evening so if new employees or existing employees leave, this will be updated automatically from the import.

If i just use a numeric PK, this won't work as the import first deletes all records, and then imports in the new uptodate records. So if an existing employee leaves, i will be a number short which will effect all existing records for remaining employees.

As i'm a novice, maybe i am not seeing an easy way around the above. Any suggestions would be great.

 

[dcb]

Where do you get the invalid column err?

 

[sullyman]

I have four combos working correctly to show employees in a subform but when i get to the last combo Employees, it shows the correct individuals in the dropdown but when i click on the employee i get the following error:

Run Time Error '207'
Invalid Column Name: b008

Note: boo8 is the employee id value and not the column name

If i change the Employeeid values to just numerical e.g. 001, 002 etc., it works fine

 

[dcb]

what is your after update code?
Is the box bound?

 

[sullyman]

Hi DCB - Here is the code for all combos which update the Units Subform. I did change my Table name in SQL and am wondering would that have anything to do with it. But then i'm stumped as using a numerical value for employeeid works great but if i want to use a value like b004 as the employeeid, it will not show the employee. The SQL Field is varchar(50)

Private Sub CboYear_AfterUpdate()
Me.CboUnit.RowSource = "SELECT DISTINCT LK_Safety_History.UnitID, Unit.Location " _
& "FROM LK_Safety_History INNER JOIN Unit ON LK_Safety_History.UnitID = Unit.UnitID " _
& "WHERE [LK_Safety_History].[YearID] = " & Me.CboYear & _
" ORDER BY Unit.Location"
Me.CboUnit = Me.CboUnit.ItemData(0)
CboUnit = Null
CboDiscipline = Null
CboEmployee = Null

Me.Units.Form.RecordSource = "SELECT DISTINCT LK_Safety_History.Safety_History_ID, LK_Safety_History.YearID, LK_Safety_History.EHS_CourseID, LK_Safety_History.Expiry, LK_Safety_History.Renewal, LK_Safety_History.EmployeeID, LK_Safety_History.UnitID, LK_Safety_History.DisciplineID, LK_Safety_History.Completed FROM LK_Safety_History " _
& "WHERE LK_Safety_History.[YearID] = " & Me.CboYear _
& " ORDER BY LK_Safety_History.YearID"

End Sub

Private Sub CboUnit_AfterUpdate()
Me.CboDiscipline.RowSource = "SELECT DISTINCT LK_Safety_History.DisciplineID, Discipline.Discipline " _
& "FROM LK_Safety_History INNER JOIN Discipline ON LK_Safety_History.DisciplineID = Discipline.DisciplineID " _
& "WHERE [LK_Safety_History].[YearID] = " & Me.CboYear _
& " AND [LK_Safety_History].[UnitID] = " & Me.CboUnit _
& " ORDER BY Discipline.Discipline"
Me.CboDiscipline = Me.CboDiscipline.ItemData(0)
CboDiscipline = Null
CboEmployee = Null

Me.Units.Form.RecordSource = "SELECT DISTINCT LK_Safety_History.Safety_History_ID, LK_Safety_History.YearID, LK_Safety_History.EHS_CourseID, LK_Safety_History.Expiry, LK_Safety_History.Renewal, LK_Safety_History.EmployeeID, LK_Safety_History.UnitID, LK_Safety_History.DisciplineID, LK_Safety_History.Completed FROM LK_Safety_History " _
& "WHERE LK_Safety_History.[YearID] = " & Me.CboYear _
& " AND [LK_Safety_History].[UnitID] = " & Me.CboUnit _
& " ORDER BY LK_Safety_History.UnitID"

End Sub

Private Sub CboDiscipline_AfterUpdate()
Me.CboEmployee.RowSource = "SELECT DISTINCT LK_Safety_History.EmployeeID, Employees.Employee " _
& "FROM LK_Safety_History INNER JOIN Employees ON LK_Safety_History.EmployeeID = Employees.EmployeeID " _
& "WHERE [LK_Safety_History].[YearID] = " & Me.CboYear _
& " AND [LK_Safety_History].[UnitID] = " & Me.CboUnit _
& " AND [LK_Safety_History].[DisciplineID] = " & Me.CboDiscipline _
& " ORDER BY Employees.Employee"
Me.CboEmployee = Me.CboEmployee.ItemData(0)
CboEmployee = Null


Me.Units.Form.RecordSource = "SELECT DISTINCT LK_Safety_History.Safety_History_ID, LK_Safety_History.YearID, LK_Safety_History.EHS_CourseID, LK_Safety_History.Expiry, LK_Safety_History.Renewal, LK_Safety_History.EmployeeID, LK_Safety_History.UnitID, LK_Safety_History.DisciplineID, LK_Safety_History.Completed FROM LK_Safety_History " _
& "WHERE LK_Safety_History.[YearID] = " & Me.CboYear _
& " AND [LK_Safety_History].[UnitID] = " & Me.CboUnit _
& " AND [LK_Safety_History].[DisciplineID] = " & Me.CboDiscipline _
& " ORDER BY LK_Safety_History.DisciplineID"

End Sub

Private Sub CboEmployee_AfterUpdate()

Me.Units.Form.RecordSource = "SELECT DISTINCT LK_Safety_History.Safety_History_ID, LK_Safety_History.YearID, LK_Safety_History.EHS_CourseID, LK_Safety_History.Expiry, LK_Safety_History.Renewal, LK_Safety_History.EmployeeID, LK_Safety_History.UnitID, LK_Safety_History.DisciplineID, LK_Safety_History.Completed FROM LK_Safety_History " _
& "WHERE LK_Safety_History.[YearID] = " & Me.CboYear _
& " AND [LK_Safety_History].[UnitID] = " & Me.CboUnit _
& " AND [LK_Safety_History].[DisciplineID] = " & Me.CboDiscipline _
& " AND [LK_Safety_History].[EmployeeID] = " & Me.CboEmployee _
& " ORDER BY LK_Safety_History.EmployeeID"

End Sub

 

[dcb]

Ya ya - (I think I see it)

[LK_Safety_History].[EmployeeID] is VarChar(50)

Therefore your query would currently look like
[LK_Safety_History].[EmployeeID] = b005

It should be [LK_Safety_History].[EmployeeID] = 'b005' as in all sql text querys

try (off the top of my head)
AND [LK_Safety_History].[EmployeeID] = '" & Me.CboEmployee _
& "' ORDER

The reason you are getting the Invalid Column Name: b008 is the driver is passing the string as "b008" which seen as a column name

 

[gemma-the-husky]

i wouldnt delete records

i would import the new data then

a) update existing records for any changes and then
b) insert any new records.

that way you can keep your existing numeric key
you just need to add a new externalid field, to help with the above processes

 

[sullyman]

Hi guys - thanks for feedback. Will try same and let you know how i get on. Gemma, how can i import the records and update the ones already there if necessary and add/delete other records if necessary. Any link online you can point me to. I'm using a job to do the import at mo in Server Management studio

 

[sullyman]

DCB - You are a genius. That is fantastic. Worked like a charm.

 

[gemma-the-husky]

Hi guys - thanks for feedback. Will try same and let you know how i get on. Gemma, how can i import the records and update the ones already there if necessary and add/delete other records if necessary. Any link online you can point me to. I'm using a job to do the import at mo in Server Management studio

generally you can do all this with a series of queries

you have your existing table
you have a newly imported data table

match them together with appropriate queries to update items/add new ones