Search results

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...

Can you point out where I am going wrong, as originally stated I tried both docmd.runSQL and docmd.openQuery. I have now tried seperating the 2 parts in order to update the JobCode value on the Correct PAFID record I still am doing something wrong, any ideas? Option Compare Database...

Im getting a 3075 error now: What this is meant to do is UPDATE the PAFTable and SET PAFTable.JobCode = TMC_PDT_Details.Code on the record WHERE PAFTable.PAFID = PAFForm.PAFID FROM TMC_PDT_Details WHERE TMC_PDT_Details.Job Title = PAFForm.JobTitle AND TMC_PDT_Details.Division Code =...

Hi, I am a total newbie with access databases, litterally 2nd day. I am trying to populate a textbox and the related control source table field with a select criteria. Basically on the form you select a Job Title from a combobox then enter a division and I am trying to get it to select the...

Ok, I think I have done this now.... I first tried an append query but I got some validation error. So I wrote a delete query and then used a macro to run the delete query then the append query, it seems to work. But, would it be better done with just an update query, and sorry for the...

Hi, I am relaerning access again and haven't used in for a few years, I'm a currently trying to build a payroll queries database to log and manage queries. I have a database view of our staff database, but cannot just make table links as I don't have access. I have set the datbase view up...