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
  #1 (permalink)  
Old January 16th, 2008, 11:56 AM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default Looping Statement

I think I need to create a looping statement that loops through the records in a form and if there is a date in the Date Complete field then I need it to modify a record to a table based on the primary key called PartID. I need it pasted to a field called LastDateServiced.

I have never used a looping statement in VBA and I have never used VBA to modify multiple records. I would appreciate any help I could get with this matter.

Thanks


Reply With Quote
  #2 (permalink)  
Old January 16th, 2008, 01:51 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

Do you have the table structure and the fields you want modified? Why would you process the whole table at one time rather than as needed for a report or other view? And then only those records?

Basically you take the PartID, then create a recordset on the table using that as the criteria.

Can you post the table(s) you want to use and modify? I am not sure from you post if you want to find a record in a table and modify it, or find a record in a table and modify all records in the table, or find a record in the table and modify a record in another table.



mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old January 17th, 2008, 09:03 AM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Let me try again.

I have two tables
Table A has field LastServiceDate
Table B has field ServiceDate

I have a continues form called ServiceNeeded which is based off of a query which uses Table B. The query shows all the records in Table B where ServiceDate is Null.

What I want to do is if someone enters values into the ServiceDate field, when the form closes I would like to take these values and put them into Table A in the LastServiceDate column based off of the primary key field called PartID.

This means I could be trying to modify any number of records including 0 at one time.

My problem is that I don’t know how to start setting up the code to do this. I know I need to create a recordset, but I cannot figure out the structure to accomplish what I am trying to do.

I would greatly appreciate any help I could get with this.
Thanks


Reply With Quote
  #4 (permalink)  
Old January 17th, 2008, 09:19 AM
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

Continuous forms are problematic for this sort of thing. What you might do is create a check box called Update, Default Value = 0, and hide it on your continuous form. Then put an AfterUpdate event on the Service Date field that checks the check box to Yes. You can do a little data validation on the event as well. Like:

Control's After Update event:

Dim dtSDate As Date

dtSDate = Me.ServiceDate

    If IsNull(dtSDate) = False Then
        Me.Update = True
    End If

Then do an Update Query, I think, where Update = True, put the ServiceDate field value to the other table.

Alternatively, you can start an update with VBA code. If you want to do that, let me know. That is easier to me since I like to code Updates, Inserts, Appends, etc.

HTH


mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #5 (permalink)  
Old January 17th, 2008, 09:24 AM
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

Sorry, create the Field Update in the table B, in case that wasn't clear.

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #6 (permalink)  
Old January 17th, 2008, 11:00 AM
Authorized User
 
Join Date: Oct 2007
Location: , , .
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I woild like to preform the update using VBA, but I am not adding records to table A I am modifying the field LastServiceDate for records that allready exist.

Thanks for all your help so far, i beleive with a little more help on the update I should be able to get it.

Thanks again

Reply With Quote
  #7 (permalink)  
Old January 17th, 2008, 12:17 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

I understand.

I would use the structure I have suggested since this will look the needed flag into the record until it can be processed.

One more question, is there ONE record in Table B and many in Table A, or vice versa? In other words, are you taking many records in Table B, finding the most recent ServiceDate, and putting it into one record in Table A, or are you taking one unique record at a time from Table B, and then updating one unique record per in Table A?

The answer has a big effect on the final code structure.

mmcdonal

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

There is one record in table A and many in table B.

Thanks for all you help

Reply With Quote
  #9 (permalink)  
Old January 17th, 2008, 02:00 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

Okay, so the way I see it, is you have a Table A on the One side of the relationship, and a Table B on the Many side, so there are many instances of Service Dates in Table B for an item in Table A, and you want to record the most recent service date in Table A every time someone schedules service in Table B. Or the latest date, for example if the user put a servicedate of 01/31/2008 in one related record and 02/01/2008 in another, 02/01/2008 would be entered in Table A for that item.

If you are then marking each recently updated record as the user inserts a new ServiceDate in Table B, and again, with multi-users this could be problematic but we can fix that, I would do this on the On Close event of the continuous form. Assuming the check boxes have been properly checked. I am also assuming local tables:

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 A first:
sSQLA = "SELECT * FROM TableA"
Set rsA = New ADODB.Recordset
rsA.Open sSQLA, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rsA.MoveFirst
'Take first PK
Do Until rsA.EOF
   iPK = rsA("PrimaryKeyFieldName")
   sSQLB = "SELECT TOP 1 FKFieldName, ServiceDate, Update FROM TableB WHERE [FKFieldName] = " & iPK & " AND [Update] <> 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

This will loop through all the records and take for each unique item in TableA, the latest service date from TableB, regardless of who entered it.

Then do this:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUPDATE_TableB_SetUpdateto_0_ALL"
DoCmd.SetWarnings True

This will reset all the Update fields to 0 so that processing runs better on subsequent runs. You need to create that Update query and then call it like this.

Then open the next form and close this one. If the main form for Table A opens, it will have the most current LastServiceDate information.

Did that work for you?


mmcdonal

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

I put your code on the OnClose Event of the form and I cannot get it to work

I had to change the name of the tables in your code
TableA actually "MachineParts"
TableB actually "MachineService"
Sorry if this caused a problem

When I run the code I get an error message of
     Run-time error '-2147217904 (80040e10)':
     No value given for one or more required parameters

When I choose to debug it highlights the yellow line rsB.Open


The code I used is


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 FKFieldName, ServiceDate, Update FROM MachineService Where [FKFieldName] =" & iPK & " AND [Update] <> 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


Thank you for all your help, I am hopping we almost have it

Thanks again


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 06:15 AM.


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