Hello Fellas. I need some help. I am working on splitting my database as it is used on several computers and that seems the best thing to do.

Everything works except this one routine.

Sorry I don't know how to paste this in a code window so here it goes. The database (tables) are on the server and I have linked them correctly. I know this because every other aspect of the program works except for this:

I am pretty sure it has something to do with the setting but I am not sure how to fix it. I am also sure that there is probably a more efficient way of writing this sub as well. So I am thick skinned and would enjoy a critque as well from the pros out there.
---------
Dim frmEnt As Form_frmEntry
Dim rec, recEntry, recDate As Recordset
Dim db As Database
Dim reccurrentpay, rec_rev_total As Recordset

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblPay_Period")
Set frmEnt = Forms!frmEntry!
Set rec_rev_total = db.OpenRecordset("qry_rev_total")
Set reccurrentpay = db.OpenRecordset("qry_currentpay")
On Error Resume Next

'Compare the entered date with the Pay Period to determine which period we are in
rec.Index = "tblEndPayPeriod"
entereddate = frmEnt.txtDate.Value
rec.Seek "<", entereddate 'frmEnt.txtDate.Text

'Need to add 1 day to this date for the pay period
startdate = rec("tblEndPayPeriod") + 1
'Place the start and ending dates of the pay period
frmEnt.txtStart.Value = startdate
rec.MoveNext
frmEnt.txtEnd.Value = rec("tblEndPayPeriod")
rec.Close
'Subroutine checks through the Entry recordset for matches within the current
'pay period. Then adds up the pay period and enters it into the form

numrec = db.OpenRecordset("qry_rev_total").RecordCount
recordcounter = 0
rec_rev_total.MoveFirst

Do
recordcounter = recordcounter + 1
searchPilot = rec_rev_total("tblPilot").Value
searchDate = rec_rev_total("tblDate").Value

If searchPilot = frmEnt.cboPilots.Value Then
If searchDate >= frmEnt.txtStart And searchDate <= frmEnt.txtEnd Then
paytime = paytime + rec_rev_total("Sum Of tblRevenue_Hours").Value
frmEnt.txtPay_Period.Value = paytime
End If
End If
rec_rev_total.MoveNext
Loop Until recordcounter + 1 > numrec

From Help:

You can't use the Seek method on a linked table because you can't open linked tables as table-type Recordset objects.

To better critique the code, it would help if you described what you're trying to accomplish.

I have a form where my pilots enter their days worth of flying.

To start with they first select there name from a drop down and today's date is automatically filled in. After leaving the date block this code figures out what pay period we are in and then look up how much revenue flying time they have for this period. It then puts that information into the form to show the pilot and it also puts in the current start and end of the pay period (dates).

I use this code in another area as well so it is very important for me to be able to determine the current pay dates as this is how I produce my payroll.

How do I get around this short coming?

There should be a much more efficient way of accomplishing this. How are the pay period start/end dates stored? And what's the structure of the table with revenue in it? Basically, you should be able to open recordset on the dates table, using the entered date, and get the one record with the appropriate dates (if it's structured how I suspect it is). Then you should be able to get a sum of the revenue for the selected pilot for those dates in one shot, rather than looping through the table. If you could post a sample db, it would be easier to help.

Ok attached is my sample code. In it the frmEntry is the form that needs to perform the look up to see what that pilot has done during this pay period and then place that total at the bottom of the form for his viewing pleasure in addition to showing him what the pay period dates are (start and end)

Any help would be great since I really need to split this database up before someone corrupts the whole works and then I will be SOL...

Thanks,
Steve

Well, the dates table isn't structured the way I thought, but this seems to work (I created my own textboxes to test with):

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT * FROM tblPay_Period ORDER BY ID;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

rs.FindFirst "tblEndPayPeriod >= #" & Me.txtDate & "#"
Me.txtPaulEnd = rs!tblEndPayPeriod
rs.MovePrevious
Me.txtPaulStart = rs!tblEndPayPeriod + 1

strSQL = "SELECT Sum(tblEntry.tblRevenue_Hours) AS SumOftblRevenue_Hours FROM tblEntry " _
& "WHERE tblEntry.tblPilot = '" & [Forms]![frmEntry]![cboPilots] & "' AND tblEntry.tblDate Between #" _
& [Forms]![frmEntry]![txtPaulStart] & "# And #" & [Forms]![frmEntry]![txtPaulEnd] & "#"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Me.txtPaulHours = rs!SumOftblRevenue_Hours

Set rs = Nothing
Set db = Nothing

Hey Paul,

Thanks that worked like a champ and it is surprisingly faster than what I had before.

Steve