Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old January 18th, 2008, 12:14 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Change the name of the field "Update" to "UpDt" and put all the field names in the SQL string in []. I used a reserved word for that field name so that may be the issue. Also, make sure the code matches your actual field names:

sSQLB = "SELECT TOP 1 [FKFieldName], [ServiceDate], [UpDt] FROM MachineService Where [FKFieldName] =" & iPK & " AND [UpDt] <> 0 Order BY ServiceDate DESC"

Is FKFieldName an actual name of a column in your table MachineService? It should be the look up column to the Primary Key in the table MachineParts.

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #12 (permalink)  
Old January 18th, 2008, 02:49 PM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I changed the Update field of UpDt and also changed FKFieldName to PartID which is the name of the primary key in MachineParts.

The code is now running, but it is not working.
It loops through all the records keeps the proper iPK count, but the code never enters the If atatement that actually updates the date.

I think the problem is in sSQLB because it never seems to pull in any records.

I am using the following code

Private Sub Form_Close()

    Dim rsA As ADODB.Recordset
    Dim rsB As ADODB.Recordset
    Dim sSQLA As String, sSQLB As String
    Dim iPK As Integer

    'Open a recordset on Table MachineParts first:
    sSQLA = "Select * FROM MachineParts"
    Set rsA = New ADODB.Recordset
    rsA.Open sSQLA, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    rsA.MoveFirst
    'Take first PK
    Do Until rsA.EOF
        iPK = rsA("PartID")
        sSQLB = "SELECT TOP 1 [PartID], [ServiceDate], [UpDt] FROM [MachineService] Where [PartID] =" & iPK & " AND [UpDt] <> 0 Order BY [ServiceDate] DESC"
    Set rsB = New ADODB.Recordset
    rsB.Open sSQLB, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        If rsB.RecordCount > 0 Then
            rsA("LastServiceDate") = rsB("ServiceDate")
            rsA.Update
        End If

    rsA.MoveNext
    Loop

    rsA.Close

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUPDATE_MachineService_SetUpdate_0_All"
    DoCmd.SetWarnings True

End Sub


I have a rough idea how to read sSQLB and what it means, but have no way of knowing if it is coded right for my application.

The name of the primary key for table MachineParts is "PartID" One to
The name of the primary key for table MachineService is "ServiceID" Many

Thanks for all your help, you have been Great

Reply With Quote
  #13 (permalink)  
Old January 18th, 2008, 03:02 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Can you create a query in the local query designer that does what you want, and then review the code and see what needs to be changed in sSQLB?

The way the looping works is that it first opens all the records in TableA, goes to the first one, takes its primary key, and then opens all the records in TableB that have that primary key as a foreign key in the proper field, that have a servicedate, and that are checked for Update. IF it finds one of those records, it should only take the latest service date, and updates the proper field in TableA.

There should be many times that it will scroll through all of TableA, an just find one record that needs updating from Table B.

A better way to do this is to create a query in the local designer that gathers all the FKs where Update = Yes, and ServiceDate <> "", and then use 3 recordsets. The first recordset would be to open Table A for updates, then open the outer loop on the query that displays all the unique FKs of TableB that have the conditions you want. Then open the 3rd recordset on TableB where the FK is in the record, and not worry about the other conditions since they were alreay met, just open Top 1. Then copy the Service Date over. THis would keep the transaction overhead down.

Anyway, see if te fix works, and if it takes too long to close the form each time, (I didn't ask how many records we are dealing with) then we can recast the code to run more quickly.


mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #14 (permalink)  
Old January 18th, 2008, 04:27 PM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for all your help. I wrote a query to take care of the update right now it works great, but there are not that many records in the table. I think it will be OK though.

Thanks for everything
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping ssaranam SQL Server 2005 2 April 17th, 2008 01:40 AM
looping mrjoka Classic ASP Basics 1 September 26th, 2007 12:21 AM
Looping deepsea007 XSLT 1 June 14th, 2007 12:13 PM
Looping Statement Solution KKeesling Visual Basic 2005 Basics 0 April 2nd, 2006 12:50 AM
Looping..? dedex C# 2 January 6th, 2005 11:24 PM



All times are GMT -4. The time now is 01:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.