Save variable from Dlookup to field? (1 Viewer)

NeoZakz

Registered User.
Local time
Today, 00:01
Joined
Jul 22, 2002
Messages
12
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

Registered User.
Local time
Today, 00:01
Joined
Mar 23, 2001
Messages
292
'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

Systems Analyst
Local time
Today, 13:31
Joined
Jul 16, 2002
Messages
50
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

Registered User.
Local time
Today, 00:01
Joined
Jul 22, 2002
Messages
12
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.
 
Last edited:

AlanS

Registered User.
Local time
Today, 00:01
Joined
Mar 23, 2001
Messages
292
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

Super Moderator
Staff member
Local time
Today, 00:01
Joined
Feb 19, 2002
Messages
29,027
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom