Recent content by mcinnes01

Hi, I am trying to join to tables using the Employee ID field. Both are views from other databases (previously never linked) one holds the value as number and the other as a text string. I have tried using the to_number function but keep getting the error "JOIN expression not supported."...

AUTH is my access table TMC_POS_MPO is a view therefore not updatable just readable

the table is called AUTH which is in my queries e.g.: UPDATE [AUTH] INNER JOIN TMC_POS_MPO ON AUTH.[Manager Job] = TMC_POS_MPO.[POS_NUMBER] SET AUTH.[Manager ID] = [TMC_POS_MPO].[EMP_ID];

Hi, I have made 6 queries and created a sub, but I get an error when I try to run any of there queries. "3073 - Operation must be an updatable query" This is my code, the error occurs even if I run the queries on their own. Sub authUP() DoCmd.SetWarnings 0 DoCmd.OpenQuery "maup"...

is it possible to make this work in one update query, obivously in its current state it doesn't work but I was wondering if it can be altered? UPDATE [AUTH] INNER JOIN [TMC_POS_MPO] ON [AUTH].[Manager Job]= [TMC_POS_MPO].[POS_NUMBER] SET [AUTH].[Manager ID] = [TMC_POS_MPO].[EMP_ID] UPDATE...

Hi thanks for the reply, so basically if I want to keep things the way I have them I would need 6 seperate update queries? I am really rubbish at update queries, could you explain how I go about doing this for 1 of the six columns e.g. manager as per my example? Thanks again Andy

Hi, I am trying to make an update query that updates multiple fields from another table. Basically it holds the relational data for staff using job codes and in my database I also hold the employee number for each of the 6 relationships per job. Every job has 6 relationships: Manager...

Ok so I have the filter working on for job title now, so if I select administrator from combo52 it displays all variations of administrator Administrator Administrator AA Administrator AB etc etc This is the Query: SELECT TMC_PSDET.TITLE, TMC_PSDET.DIVISION, TMC_PSDET.CONTRACT_TYPE...

Hi, I am quite new with access and I am trying to filter a subform. The idea is that multiple combo boxes will apply filters to the subform, then the user will select from the subform and this choice will be used on the form. Unfortunately I am not quite there yet... I have the subform...

Hi Dave, Thanks for the explination there, one question would this still apply if I am only using alphas. E.g. My ID codes will be unique in the format as follows, the idea is that I have a 6 digit field in our HR system for job codes. The jobs are very unique and hold alot of data. E.g. not...

Hi, Thanks for all the suggestions I will see if I can work it in to my code to shorten it a little. This is what I have so far and it seems to work well, I don't know if you can see any problems in it? One question regarding the use of an auto number as a reference point... I understand...

Ok so I have got a little further and can now handle when the previous last letter is a "Z". This also now creates the next new record if there are no gaps, but my problem now is how do I turn it in to a function that will add a record to the table whether its filling a gap or adding a totally...

I have got a little bit further, this is my testing so far. Option Compare Database Sub alphaID() Dim x Dim rs1 As Recordset Dim Val1 As String, Val2 As String Set rs1 = CurrentDb.OpenRecordset("SELECT [Division ID] FROM [DIVISION] ORDER BY [Division ID]") Do While Not rs1.EOF Debug.Print...

Hi, I am trying to write a function to automatically assign an ID like the way autonumber works, but with alphas. I want a 2 digit alpha assigned and I want to reuse and deleted like as if you compact the database everytime a record is removed. The Table in this example is called division...

Worked it out.... Private Sub Div_AfterUpdate() Dim rs As Recordset Dim SQLstr1 As String Dim JCode As String Dim Job As String, Division As String On Error GoTo Textit Job = JobTitle Division = Div SQLstr1 = "SELECT {Code} AS J1 FROM [TMC_PDT_DETAILS] WHERE [Job Title] = """ & Job & """ AND...