[Technodruid]

Hello, new to the forums, I am taking over a database and Im trying to clean up some of the Code as well as make it more user/Design friendly. In this case I am trying to get rid of about 100+ querys with a bit of VBA code, now I dont mind using queries but i think it would be more simplistic and make for less bloat using the VBA. unless there is a way to do it in SQL then i could do it there also.

Here is what I need to do.... I have tblWorkCode with User inputted data, and template2_Hold1 with Data imported from an Excel Sheet. I need to compare Resource Group, Country, Rate type and Workforce with Resource Group, Working_in _Country, Rate type and Workforce.

Based on the results tblWorkCode I need to have WLID and WID populate the same fields in template2_Hold1. If the Comparison doesn’t come back with a combo then it would prompt the user to create the entry into the tblWorkCode.

Now if it matches a combination of the 4 but the WLID and/or WID are Null then the user will be prompted to enter just the WLID or the WID (this will be based on information outside of the Information in the DB). This part I shouldn’t have much trouble coding.

I hope this makes some sense, and Thanks for any help given. Added a sample of the 2 tables and some of the code Ive been trying. The Code is in the VBA on the button form. Ill probably just put it in a module if we can get it working.

Thanks in advance for any help.

[OBP]

Technodruid, welcome to the forum, that is quite a task you given yourself.
I must admit the old adage of "If it ain't Broke don't fix it" comes to mind.
Sorry I can't open Access 2007 databases.

[Technodruid]

Thats the Problem, for what client wants, it is Broken if you want I can paste the VBA code I was trying but its not populating the tables properly anyway.

[OBP]

What isn't it doing?
By all means post the code, plus some details about what it should do to what fields.

[Technodruid]

Dim RG As String
Dim Cntry As String
Dim RT As String
Dim WF As String
Dim BC As String
Dim RG1 As String
Dim Cntry1 As String
Dim RT1 As String
Dim WF1 As String
Dim BC1 As String
Dim LRec As String
Dim TempHold As Recordset
Dim WorkCode As Recordset

Set TempHold = CurrentDb.OpenRecordset("template2_Hold1")
Set WorkCode = CurrentDb.OpenRecordset("tblWorkCode")

RG = TempHold![Resource Group]
Cntry = TempHold![Working_in_Country]
RT = TempHold![Rate Type]
WF = TempHold![Workforce]


RG1 = WorkCode![Resource Group]
Cntry1 = WorkCode![Country]
RT1 = WorkCode![Rate Type]
WF1 = WorkCode![Workforce]

Do
Loop Until RG = RG1 And Cntry = Cntry1 And RT = RT1 And WF = WF1 And TempHold!WLID <> Null And TempHold!WID <> Null
TempHold.Edit

If RG = RG1 And Cntry = Cntry1 And RT = RT1 And WF = WF1 Then TempHold!WLID = WorkCode!WLID And TempHold!WID = WorkCode!ID
TempHold.update

Its Supposed to cycle thru all the records and Update tbltemphold fields WLID and ID. If the WLID or ID
Have a Null value then it will pop up a Form for the user to manually enter, if there are records between the 2 tables that dont match I will be setting up a form that the user will beable to update Table workCode for.

[OBP]

I don't see any recordsets?

[Technodruid]

Set TempHold = CurrentDb.OpenRecordset("template2_Hold1")
Set WorkCode = CurrentDb.OpenRecordset("tblWorkCode")

[OBP]

OK, after the .update you need to .bookmark the each recordset so that it nows where it is.
You also need a .NextRecord for each one to step through the records.

[OBP]

This is part of a For next loop that adds to a table rather than updating

With rstable
.AddNew
!date = Me.Report_Date
!team = team
![Idle Code] = Code
![Agent ID] = Val(Agent)
!Agent = Name
!Duration = Timed
![Time In Code] = Val(tic)
.Update
.Bookmark = .LastModified
End With
End If
End If
If rs.EOF Then Exit For
rs.MoveNext
Next count