Results 1 to 5 of 5

Thread: findfirst

  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: findfirst

    I am trying to use findfirst to only make excel files for those offices that have records and it works for the first one it comes to that has a match but not for the second one. It doesn't matter if the first one isn't until office 5, it will work for that one and none after. Code is below (I didn't include all of it). Thanks.

    Set waitdb = CurrentDb
    Set ofcrs = waitdb.OpenRecordset("officeweek", dbOpenDynaset)
    Set dbs = Application.CurrentData

    ' asks what is current week
    week = InputBox("Please type in this weeks date, ex= 1014. Do not include spaces.", "Date needed")

    ' OFFICE NUMBER 1
    where1 = "E:\01\waitlist01_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "01"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c02 new ofc 01", where1, True
    End If

    ' OFFICE NUMBER 3
    where3 = "E:\03\waitlist03_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "03"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c03 new ofc 03", where3, True
    End If

    ' OFFICE NUMBER 4
    where4 = "E:\04\waitlist04_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "04"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c04 new ofc 04", where4, True
    End If

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: findfirst

    Originally posted by sleamiller
    I am trying to use findfirst to only make excel files for those offices that have records and it works for the first one it comes to that has a match but not for the second one. It doesn't matter if the first one isn't until office 5, it will work for that one and none after. Code is below (I didn't include all of it). Thanks.

    Set waitdb = CurrentDb
    Set ofcrs = waitdb.OpenRecordset("officeweek", dbOpenDynaset)
    Set dbs = Application.CurrentData

    ' asks what is current week
    week = InputBox("Please type in this weeks date, ex= 1014. Do not include spaces.", "Date needed")

    ' OFFICE NUMBER 1
    where1 = "E:\01\waitlist01_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "01"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c02 new ofc 01", where1, True
    End If

    ' OFFICE NUMBER 3
    where3 = "E:\03\waitlist03_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "03"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c03 new ofc 03", where3, True
    End If

    ' OFFICE NUMBER 4
    where4 = "E:\04\waitlist04_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "04"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c04 new ofc 04", where4, True
    End If
    You might want to reset the recordset to the first position before subsequent searches. rs.MoveFirst.

    Gregg

  3. #3
    Join Date
    Oct 2003
    Posts
    2

    Re: findfirst

    Originally posted by basicmek
    You might want to reset the recordset to the first position before subsequent searches. rs.MoveFirst.

    Gregg

    I did the following and it didn't work, am I putting it in the correct place?

    ' OFFICE NUMBER 3
    ofcrs.MoveFirst
    where3 = "E:\03\waitlist03_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "03"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c03 new ofc 03", where3, True
    End If

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: findfirst

    Originally posted by sleamiller
    I did the following and it didn't work, am I putting it in the correct place?

    ' OFFICE NUMBER 3
    ofcrs.MoveFirst
    where3 = "E:\03\waitlist03_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "03"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c03 new ofc 03", where3, True
    End If
    At this moment, I'm not sure what to tell you. Yeah that would be the place to put the movefirst method. If it were me, I would probably put a breakpoint in the code and follow the procedure through with F8 on the keyboard. Sometimes this helps me figure out what dumb logic step I've missed.

    I'll set this up for myself and make sure I'm not advising wrong.

    If anyone else wants to chime in, your not going to offend me.

    Hold the phone. There is something that I'm forgetting about the findfirst method. I just tried it and subsequent attempts give me the first record instead of the one I want. I'll figure it out later but in the mean time someone else I'm sure will help. If not I'll check back later.

    Gregg
    Last edited by basicmek; 10-21-03 at 15:31.

  5. #5
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: findfirst

    Originally posted by sleamiller
    I am trying to use findfirst to only make excel files for those offices that have records and it works for the first one it comes to that has a match but not for the second one. It doesn't matter if the first one isn't until office 5, it will work for that one and none after. Code is below (I didn't include all of it). Thanks.

    Set waitdb = CurrentDb
    Set ofcrs = waitdb.OpenRecordset("officeweek", dbOpenDynaset)
    Set dbs = Application.CurrentData

    ' asks what is current week
    week = InputBox("Please type in this weeks date, ex= 1014. Do not include spaces.", "Date needed")

    ' OFFICE NUMBER 1
    where1 = "E:\01\waitlist01_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "01"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c02 new ofc 01", where1, True
    End If

    ' OFFICE NUMBER 3
    where3 = "E:\03\waitlist03_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "03"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c03 new ofc 03", where3, True
    End If

    ' OFFICE NUMBER 4
    where4 = "E:\04\waitlist04_" & week & ".xls"
    ofcrs.FindFirst ofcrs![ofc] = "04"
    If ofcrs.NoMatch Then
    Else
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "c04 new ofc 04", where4, True
    End If
    Well, I figured out my problem but not yours. Worked every time without resetting the recordset to the beginning. According to the help available for the FindFirst method, the search starts at the beginning of a recordset each time and has a default direction of forward. The only thing that I do different when using FindFirst on recordsets, is to assign the search criterea to a string variable.
    You say that the syntax you are using is getting at least one return each time you try it so it must work.

    Not sure what else to tell you. You might try ADO.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •