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

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

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

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

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

mmcdonal

Look it up at: http://wrox.books24x7.com

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

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

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

Thanks for all you help

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

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