Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-29-2002, 08:24 AM   #1
NeoZakz
Junior Member
 
NeoZakz's Avatar
 
Join Date: Jul 2002
Location: Florida
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
NeoZakz
Save variable from Dlookup to field?

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?

NeoZakz is offline   Reply With Quote
Old 07-30-2002, 06:26 AM   #2
AlanS
Registered User
 
Join Date: Mar 2001
Location: Philadelphia, PA USA
Posts: 292
Thanks: 0
Thanked 0 Times in 0 Posts
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
AlanS is offline   Reply With Quote
Old 08-15-2002, 12:57 AM   #3
jadeIT
Systems Analyst
 
jadeIT's Avatar
 
Join Date: Jul 2002
Location: Australia
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
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?

__________________
~JC
jadeIT is offline   Reply With Quote
Old 08-15-2002, 03:10 AM   #4
NeoZakz
Junior Member
 
NeoZakz's Avatar
 
Join Date: Jul 2002
Location: Florida
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
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.

Last edited by NeoZakz; 08-15-2002 at 03:15 AM.
NeoZakz is offline   Reply With Quote
Old 08-15-2002, 05:21 AM   #5
AlanS
Registered User
 
Join Date: Mar 2001
Location: Philadelphia, PA USA
Posts: 292
Thanks: 0
Thanked 0 Times in 0 Posts
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.
AlanS is offline   Reply With Quote
Old 08-15-2002, 07:32 PM   #6
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
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.

Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:33 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World