(could be some sort of DLookup but I'm not to sure)

[lars7]

Hi There,
I have a staff table "tblstaff" where the staff have a code number "Code" and then 4 fields are updated,"JobFamily","SubJobFamily","Spine" and "Band", according to the code and the information is then sent to payroll for the staff to be regraded. But as this table is updated every week (Imported Excel Sheet) with more staff that can have the same code the user will have to go through the same process again and again. They have asked me is it possible for the information in these 4 fields to be automatically (could be some sort of DLookup but I'm not to sure) updated where the code number has already been regraded.
As always any help would be gratefully received.

 

[PHV]

Replace this:
"AFCCode ='" & rec!AFCCode & "',, " & _
with this:
"AFCCode ='" & rec!AFCCode & "' " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Remember REMOVE the 2 commas after
"AFCCode ='" & rec!AFCCode & "',, "
should be
"AFCCode ='" & rec!AFCCode & "', "

Make sure ALL variables are populating

why no NZ(), with "AFCCode ='" & rec!AFCCode & "', "???

 

[Zion7]

...you did say ALL the fields are text, right?

 

[lars7]

Hi All,
Sorry about missing the 2 commas but I was rushing at the end of my working day and forgot about that. I tried first PHV'S and then the following and got the dreaded "syntax error in update query":

Private Sub btn4_Click()
On Error GoTo btn4_Err
Dim strSQL As String
Dim rec As New AdODB.Recordset

DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tom\My Documents\truststaffupdate.xls ", True, ""

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic


Do Until rec.EOF

CurrentProject.Connection.Execute _
"Update tbltruststaff SET " & _
"JobFamily ='" & Nz(rec!Jobfamily, "NA") & "', " & _
"SubJobFamily='" & Nz(rec!Subjobfamily, "NA") & "', " & _
"PostDescriptor ='" & Nz(rec!PostDescriptor, "NA") & "', " & _
"spine ='" & Nz(rec!spine, "NA") & "', " & _
"Band ='" & Nz(rec!band, "NA") & "', " & _
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "', " & _
"WHERE code ='" & rec!code & "'"
rec.MoveNext
Loop

rec.Close: Set rec = Nothing

Yes Zion everything is text in both "importedStaff" and "tbltruststaff". Is there anything else I can tell you about the reading from the debugging? Do I have this right here in that: the code will look up the fields in the "tbltruststaff" and put values next to the same code(if there) in the coresponding fields in the ImportedStaff table.
Thanks for your patience guys.

 

[PHV]

Again, replace this:
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "', " & _
with this:
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "' " & _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Is rec!Code populating?

Yes it will update, ONLY IF Records exist, that match rec!Code.

I hate to complicate things, but maybe you should try,


....
Dim varCode As Variant , SQL As String

Do Until rec.EOF

varCode = DLookUp("pkTrustStaffID","tbltruststaff", _
"Code = '" & rec!Code & "'")


IF IsNull(varCode) Then

SQL = _
"Update tbltruststaff SET " & _
"JobFamily ='" & Nz(rec!Jobfamily, "NA") & "', " & _
"SubJobFamily='" & Nz(rec!Subjobfamily, "NA") & "', " & _
"PostDescriptor ='" & Nz(rec!PostDescriptor, "NA") & "', " & _
"spine ='" & Nz(rec!spine, "NA") & "', " & _
"Band ='" & Nz(rec!band, "NA") & "', " & _
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "', " & _
"WHERE code ='" & rec!code & "'"

Else
SQL = "INSERT INTO tbltruststaff(" & _
"JobFamily,SubJobFamily,PostDescriptor," & _
"Spine,Band,AFCCode) VALUES('" & _
Nz(rec!Jobfamily, "NA") & "','" & _
Nz(rec!SubJobfamily, "NA") & "','" & _
Nz(rec!PostDescriptor, "NA") & "','" & _
Nz(rec!spine, "NA") & "','" & _
Nz(rec!Band, "NA") & "','" & _
Nz(rec!AFCCode, "NA") & "'"
End IF

CurrentProject.Connection.Execute SQL

rec.MoveNext
Loop

 

[Zion7]

Thank-you PHV, I thought i got the gist,
your first post.

...UNBELIEVABLE!!!

 

[PHV]

If Update AND Insert should be done, you may consider this:
Private Sub btn4_Click()
DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tom\My Documents\truststaffupdate.xls ", True
CurrentProject.Connection.Execute _
"UPDATE tbltruststaff AS A RIGHT JOIN ImportedStaff AS I ON A.code=I.code SET" _
& " A.code=I.code,A.JobFamily=I.JobFamily,A.SubJobFamily=I.SubJobFamily" _
& ",A.PostDescriptor=I.PostDescriptor,A.spine=I.spine" _
& ",A.Band=I.band,A.AFCCode=I.AFCCode;", , 129
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

lars7, if you decide to use my suggestion,
remove the last comma, as PHV pointed,
in the update staement, and my logic is off
with th if statement,
run update if varcode is not null,
or run insert if varcode is null.

PHV's is worth a good look.

 

[lars7]

Hi there,
I was getting a bit confused with all the copying and pasting so I have "frmtest" now with 2 command buttons with your latest codes.

PHV's,

I tried this but it is importing the sheet then nothing with no readings from it during debugging

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tom\My Documents\truststaffupdate.xls ", True
CurrentProject.Connection.Execute _
"UPDATE tbltruststaff AS A RIGHT JOIN ImportedStaff AS I ON A.code=I.code SET" _
& " A.code=I.code,A.JobFamily=I.JobFamily,A.SubJobFamily=I.SubJobFamily" _
& ",A.PostDescriptor=I.PostDescriptor,A.spine=I.spine" _
& ",A.Band=I.band,A.AFCCode=I.AFCCode;", , 129
End Sub

Zion7,

I am getting an error message saying object required and the same above all the code during debugging.

Private Sub Command1_Click()
Dim varCode As Variant, SQL As String

Do Until rec.EOF

varCode = DLookup("pkTrustStaffID", "tbltruststaff", _
"Code = '" & rec!code & "'")


If IsNull(varCode) Then

SQL = _
"Update tbltruststaff SET " & _
"JobFamily ='" & Nz(rec!Jobfamily, "NA") & "', " & _
"SubJobFamily='" & Nz(rec!SubJobfamily, "NA") & "', " & _
"PostDescriptor ='" & Nz(rec!PostDescriptor, "NA") & "', " & _
"spine ='" & Nz(rec!spine, "NA") & "', " & _
"Band ='" & Nz(rec!Band, "NA") & "', " & _
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "'" & _
"WHERE code ='" & rec!code & "'"

Else
SQL = "INSERT INTO tbltruststaff(" & _
"JobFamily,SubJobFamily,PostDescriptor," & _
"Spine,Band,AFCCode) VALUES('" & _
Nz(rec!Jobfamily, "NA") & "','" & _
Nz(rec!SubJobfamily, "NA") & "','" & _
Nz(rec!PostDescriptor, "NA") & "','" & _
Nz(rec!spine, "NA") & "','" & _
Nz(rec!Band, "NA") & "','" & _
Nz(rec!AFCCode, "NA") & "'"
End If

CurrentProject.Connection.Execute SQL

rec.MoveNext
Loop
End Sub

Sorry for my confusing I think i will be able to keep track of any changes better now.

 

[lars7]

Hi,
Zion7,

I added more of your code to the new command button and I have a message "you canceled the previous operation" with this.

Private Sub Command1_Click()
Dim varCode As Variant, SQL As String
Dim rec As New AdODB.Recordset

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic

Do Until rec.EOF

varCode = DLookup("pkTrustStaffID", "tbltruststaff", _
"Code = '" & rec!code & "'")


If Not IsNull(varCode) Then

SQL = _
"Update tbltruststaff SET " & _
"JobFamily ='" & Nz(rec!Jobfamily, "NA") & "'" & _
"SubJobFamily='" & Nz(rec!SubJobfamily, "NA") & "'" & _
"PostDescriptor ='" & Nz(rec!PostDescriptor, "NA") & "'" & _
"spine ='" & Nz(rec!spine, "NA") & "'" & _
"Band ='" & Nz(rec!Band, "NA") & "'" & _
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "'" & _
"WHERE code ='" & rec!code & "'"

Else: IsNull (varCode)

SQL = "INSERT INTO tbltruststaff(" & _
"JobFamily,SubJobFamily,PostDescriptor," & _
"Spine,Band,AFCCode) VALUES('" & _
Nz(rec!Jobfamily, "NA") & "''" & _
Nz(rec!SubJobfamily, "NA") & "''" & _
Nz(rec!PostDescriptor, "NA") & "''" & _
Nz(rec!spine, "NA") & "''" & _
Nz(rec!Band, "NA") & "''" & _
Nz(rec!AFCCode, "NA") & ""
End If

CurrentProject.Connection.Execute SQL

rec.MoveNext
Loop
rec.Close: Set rec = Nothing
End Sub



Debugging Info.

"Code = '" & rec!code & "'") <---Giving me a code

Do Until rec.EOF <--- False

If Not IsNull(varCode) Then <--- Empty

Nz(rec!Jobfamily, "NA") & "'" & <--- Null

CurrentProject.Connection.Execute SQL <--- SQL=""

 

[Zion7]

This is failing, because you didn't asdd the corect field,

varCode = DLookup("pkTrustStaffID", "tbltruststaff", _
"Code = '" & rec!code & "'")

this "pkTrustStaffID" should be replaced,
with your primary key, in tbltruststaff

"That is causing the "...Cancelled previous oper.." error,
which is why varCode is empty, and all other errant values.

 

[lars7]

Hi Zion7,
I don't have a primary key in "tbltruststaff" all I have is the code that they giving to identify which new job description they are assigned. Normally Pay Number would be the Primary key but because staff have had many jobs since this process began (01/10/04) the one pay number could have up to 4 job descriptions. Having a Primary Key my database would be a dream to my. Is there an other way around this.

 

[Zion7]

yes, any other field that is guaranteed to be populated,
with your given criteria.
Primary key's are the safest bet, but when not an option...

 

[lars7]

Hi,
I have been away for a couple of days but sadly this problem is still here. I am still getting the syntax error but the debugging info has changed. Here is the code:

Private Sub Command1_Click()
Dim varCode As Variant, SQL As String
Dim rec As New AdODB.Recordset

DoCmd.TransferSpreadsheet acImport, 8, "ImportedStaff", "C:\Documents and Settings\tom\My Documents\truststaffupdate.xls ", True

rec.Open "ImportedStaff", _
CurrentProject.Connection, adOpenForwardOnly, adLockPessimistic

Do Until rec.EOF

varCode = DLookup("code", "tbltruststaff", _
"Code = '" & rec!code & "'")


If Not IsNull(varCode) Then

SQL = _
"Update tbltruststaff SET " & _
"JobFamily ='" & Nz(rec!Jobfamily, "NA") & "'" & _
"SubJobFamily='" & Nz(rec!SubJobfamily, "NA") & "'" & _
"PostDescriptor ='" & Nz(rec!PostDescriptor, "NA") & "'" & _
"spine ='" & Nz(rec!spine, "NA") & "'" & _
"Band ='" & Nz(rec!Band, "NA") & "'" & _
"AFCCode ='" & Nz(rec!AFCCode, "NA") & "'" & _
"WHERE code ='" & rec!code & "'"

Else: IsNull (varCode)

SQL = "INSERT INTO tbltruststaff(" & _
"JobFamily,SubJobFamily,PostDescriptor," & _
"Spine,Band,AFCCode) VALUES('" & _
Nz(rec!Jobfamily, "NA") & "''" & _
Nz(rec!SubJobfamily, "NA") & "''" & _
Nz(rec!PostDescriptor, "NA") & "''" & _
Nz(rec!spine, "NA") & "''" & _
Nz(rec!Band, "NA") & "''" & _
Nz(rec!AFCCode, "NA") & ""
End If

CurrentProject.Connection.Execute SQL

rec.MoveNext
Loop
rec.Close: Set rec = Nothing
End Sub


Debugging Info.

"Code = '" & rec!code & "'") <---Giving me a code (N2025)

Do Until rec.EOF <--- False

If Not IsNull(varCode) Then <--- Giving me a code (N2025)

Nz(rec!Jobfamily, "NA") & "'" & <--- Null as is all fields.

CurrentProject.Connection.Execute SQL <--- Giving me the SQL code.

The code that the (varcode) and "code" field are displaying during debugging is N2025, that code in "tbltruststaff" is attached to only one person and the fields are null as their info has not yet been sent to payroll, there are many other code in the "ImportedStaff" table that do have info attached in "tbltruststaff" why does this code always show up? I Hope this new Information sheds some light on what is going on.

 

[lars7]

Hi there,
Looks like this one has bit the dust. I will inform the user that she will have to do it manualy, thanks for the time and effort as always.

Tom.

 

[Zion7]

it's looping thru each record,
why wouldn't it show up.
Why is the Nz() not working???

Are the other recordss working, either appending or updating?
If so, maybe right some error code.

On error got to xxx
....

xx:
Exit Sub
xxx:
If err = ..... Then'whatever the syntax err number is
Resume Next 'continue to next line/record
Else
MsgBox err & VbCrLf & Error&
Resume xx
End If
End Sub

 

[lars7]

For the record.

Hi there,
Looks like I have a solution to this one.

I have created a new table "tblUpdateDescriptors" which I will append from "tbltruststaff" with I record of the code with the 6 Fields attached, after the user has updated the information, this should be unique in "tblUpdateDescriptors". Then I will update all the records in "tbltruststaff" that have the code but have arrived at a later date and did not get the 6 fields updated and now can be sent to payroll with the next batch.


Private Sub btn4_Click()
On Error GoTo btn4_Err
Dim strSQL2 As String
Dim strSQL As String
Set db = CurrentDb

'Import Excel spreadsheet to "tbltruststaff"

'DoCmd.TransferSpreadsheet acImport, 8, "tbltruststaff", "\\hcimax\users\hosiem\truststaffupdate.xls", True, ""
DoCmd.Requery "lst2"
Call DeleteInput

'Append "tblupdateDescriptors" with code and attached information.

strSQL2 = "INSERT INTO tblupdateDescriptors ( Code, JobFamily, SubJobFamily, PostDescriptor, AFCCode, Spine, [Band] ) " & _
"SELECT DISTINCT A.Code, A.JobFamily, A.SubJobFamily, A.PostDescriptor, A.AFCCode, A.Spine, A.[Band] " & _
"FROM tbltruststaff AS A LEFT JOIN tblupdateDescriptors AS B ON A.Code = B.Code " & _
"WHERE A.JobFamily<>'' AND B.Code Is Null"

CurrentDb.Execute strSQL2, dbFailOnError

'Update "tbltruststaff" with code and attached information.

strSQL = "UPDATE tbltruststaff RIGHT JOIN tblupdateDescriptors ON tbltruststaff.Code = tblupdateDescriptors.Code SET tbltruststaff.JobFamily = tblupdatedescriptors.jobfamily, tbltruststaff.SubJobFamily = tblupdatedescriptors.Subjobfamily, tbltruststaff.PostDescriptor = tblupdatedescriptors.PostDescriptor, tbltruststaff.AFCCode = tblupdatedescriptors.AFCCode, tbltruststaff.Spine = tblupdatedescriptors.Spine, tbltruststaff.[Band] = tblupdatedescriptors.[Band]" & _
"WHERE (((tbltruststaff.JobFamily) Is Null))"

CurrentDb.Execute strSQL, dbFailOnError

Set db = Nothing
btn4_Exit:
Exit Sub
btn4_Err:
MsgBox Error$
Resume btn4_Exit
End Sub

Thanks to all who gave there time to this problem