Need VBA code for Update Table Record, Need help?

[furnitureheaven]

Hi,
I got stuck in the update table record. Actually I have a parent form with subform (child form) attached with two tables (Obviously one is Parent table and other is child table).

Fields in Parent Table:-
RefNo
…..
…..
Revwdate (Depend on Child table Revdate against Type)

Fields in Child Table:-
RefNo
Revdate
Type (Could be C or A or Null)
…..

I want in Event Action (Don’t know what event it would be), It should check the “Type” Field in Child table and if its found Type “A”, it should update Parent table “Revwdate” field with Child table “Revdate” (if there are multiple A types, should update with first A) of that appropriate Ref No.
e.g
Update parent table Set Revwdate=(select Revdate from Child table Where Type =First A)

And if it found Type C then it should update Revwdate with Child table “Revdate(Last Record)” of that appropriate Ref No.

e.g
Update parent table Set Revwdate=(select Revdate from Child table Where Type =Last C)

And if there is no Type then should update with first Revdate in child table of that appropriate Ref No.

This is really impossible for me to work it out, so I hope somebody can help me on this.

For detail view I have attached a sample project.

Thanks.

 

[HiTechCoach]

Calculated data should not be stored. This violated the rules of normalization.

This means that you should not be storing the “Revdate” in the parent table but only in the child tbale. You should have a method to look it up the date as needed. This will also makes sure that you also get the correct information each since it is calculated every time.

In you case, I would probably create a user defined function where you pass the primary key and it would to return the “Revdate”

 

[HiTechCoach]

Here is a fuciton to get what you want:

Option Compare Database
Option Explicit


Public Function GetReviewDate(pRefNo As String) As Variant


Dim RevDate As Date


RevDate = 0

' first check for type A

 RevDate = Nz(DMin("RevDate", "ChildTable", "[Type] = 'A' and RefNo ='" & pRefNo & "'"), 0)

If RevDate = 0 Then
' Check for Type C
RevDate = Nz(DMax("RevDate", "ChildTable", "[Type] = 'C' and RefNo ='" & pRefNo & "'"), 0)


End If
If RevDate = 0 Then
' Check for not types
RevDate = Nz(DMax("RevDate", "ChildTable", "[Type] Is Null and RefNo ='" & pRefNo & "'"), 0)


End If

If RevDate > 0 Then
  GetReviewDate = RevDate
End If

End Function

I am not sure if I got the logic correct. It was not real clear on exactly how to calculate the date. You may need to adjust it.

I have added this to your example to get your started.

 

[furnitureheaven]

I am not sure if I got the logic correct. It was not real clear on exactly how to calculate the date. You may need to adjust it.

I have added this to your example to get your started.

Sorry for late responce, and Thanks for the soluation, you got pretty much the logic.

Its shows the right date in the Main Form, but it pick it from the child table.

I want it to pick the date from the child table and update the parent table column, and then display it from the parent table.

So your half code is good, but its not updating the parent table "RevwDate" column.

Your code is just show the date according to the logic, and i also want to update the parent table by using the above logic.

I hope i have clear the logic, if you sitll not clear please ask me.

Much appericated.