Save variable from Dlookup to field?

[NeoZakz]

Is there a way to save a variable from a Dlookup to a field in the table that is blank maybe? Or can you do a dateadd on a dlookup field?

 

[AlanS]

'for Access 97 - DAO coding is slightly different in later versions
Dim dbs as Database, rst as Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset ("MyTable")
'put Move/Seek/Find coding here to make desired record current
rst.Edit
rst!MyField = DLookup("MyOtherField", "MyOtherTable", _
[string expression with criteria])
rst.Update
set rst = Nothing
set dbs = Nothing

 

[jadeIT]

you said the coding is different in later versions of access?

i have 2002, but I also work on 2000 format dbs often. Would the above code work on either of these?

 

[NeoZakz]

I dunno about the above code but I can show you what I put in that finally got it to work in 2000.

Form_NEWTracking
Public Sub Combo119_Change()


If IsNull(Me![Combo119]) Then
txtDateDue = DateAdd("d", Me![Seq], Me![txtDateDue])
Else
'if not null
txtDateDue = DateAdd("d", Me![Seq], DLookup("[MILEDATE]", "MILE", "[MILESTONE]= '" & [Combo119] & "'"))
End If

End Sub
============================================================================================
Form_NEWTracking
Private Sub days_after_AfterUpdate()
If IsNull(Me![Combo119]) Then
txtDateDue = DateAdd("d", Me![Seq], Me![txtDateDue])
Else
'if not null
txtDateDue = DateAdd("d", Me![Seq], DLookup("[MILEDATE]", "MILE", "[MILESTONE]= '" & [Combo119] & "'"))
End If
End Sub
============================================================================================

Form_Milestones

Private Sub Date_AfterUpdate()
Dim rec As New ADODB.Recordset

Dim frm As [Form_Tracking Main]
Dim newfrm As Form_NEWTracking
Dim Seq As Long

rec.Open "SELECT * FROM allTracking WHERE GOVT='" & [Event] & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Do While Not rec.EOF
rec("Date Due") = DateAdd("d", Nz(rec("Seq")), [Date])
rec.Update
rec.MoveNext
Loop

Forms![Tracking Main].Requery
End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Must also add to the Data Library in "alt+F11"
-"Microsoft ActiveX Data Objects 2.0 Library"

2 Subforms with the main form called [Form_Tracking Main]
- Sub forms are:
-[Form_NEWTracking]
-[Form_Milestones]

For all Databases:
-Also change the Original Table to Reflect the following:
-Seq to a number field
-Govt to a text field

Dont forget to add the events to the Tracking Main field in which all the subforms lye.
-Add Events
-Afterupdate => Date - Milestones
-Change => Combo119 - NEWTracking
-Afterupdate => days_after - NEWTracking

What the bottom one does is when the date is changed in[Form_Milestones] it goes to the NEWTracking and changes the date in the date due field for any record with a matching milestone event. The top two just do a if else date add function on the form it self for changes there.

This is what I did to get it to work with my forms. Dunno if this will help you or not hope it does.

 

[AlanS]

In Access 2000, instead of DIMming dbs and rst as Database and Recordset variables, respectively, you must DIM them both as Variants. Beyond that, I'm not sure what differences are needed for Access 2000 or XP - I avoid using those versions unless absolutely necessary.

 

[Pat Hartman]

A single line of code will do it.

Me.YourField = DLookup(......)

You can put the code in the AfterUpdate event of whatever field supplies the criteria that the DLookup() needs or you can put it in the BeforeUpdate event of the Form.