Help with DLookUp Macro (1 Viewer)

fedebrin

Registered User.
Local time
Today, 00:35
Joined
Sep 20, 2017
Messages
59
Hello all

I have been trying to run a macro that will update a date.

The premise is that I have a Form for the table [ACTIONS] and within these actions, there are different notes --> [ACTIONNOTES]

An action will have multiple notes.

On [ACTIONS] there is [LastUpdate] date and on [ACTIONNOTES] each note will hace a [NoteDate]. Now the goal is to populate [LastUpdate] with the latest [NoteDate].

I have built a macro as follows to be used in the [ACTIONS] form:

SetValue
Item=[LastUpdate]
Expression=DLookUp("[NoteDate]","ACTIONNOTES","[ActionID]=[ActionIDb]")


The problem with this is that it gives me a random date and not necessarily the date from the latest note.

Also I am not sure if there is a way to have the Macro run in the [ACTIONNOTES] form so that I don't have to go back to the [ACTIONS] form to somehow activate this macro

Any help is greatly appreciated!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:35
Joined
Aug 30, 2003
Messages
36,118
I don't use macros, but try:

DLookUp("[NoteDate]","ACTIONNOTES","[ActionID]=" & [ActionIDb])
 

fedebrin

Registered User.
Local time
Today, 00:35
Joined
Sep 20, 2017
Messages
59
Hello, thanks for your response.

Unfortunately it does not work.

As you don't use macros that much... any other work around?
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:35
Joined
Sep 21, 2011
Messages
14,050
I think you should be using DMAX.?

Hello all

I have been trying to run a macro that will update a date.

The premise is that I have a Form for the table [ACTIONS] and within these actions, there are different notes --> [ACTIONNOTES]

An action will have multiple notes.

On [ACTIONS] there is [LastUpdate] date and on [ACTIONNOTES] each note will hace a [NoteDate]. Now the goal is to populate [LastUpdate] with the latest [NoteDate].

I have built a macro as follows to be used in the [ACTIONS] form:

SetValue
Item=[LastUpdate]
Expression=DLookUp("[NoteDate]","ACTIONNOTES","[ActionID]=[ActionIDb]")


The problem with this is that it gives me a random date and not necessarily the date from the latest note.

Also I am not sure if there is a way to have the Macro run in the [ACTIONNOTES] form so that I don't have to go back to the [ACTIONS] form to somehow activate this macro

Any help is greatly appreciated!
 

fedebrin

Registered User.
Local time
Today, 00:35
Joined
Sep 20, 2017
Messages
59
Thanks both of them work.

However found another problem but with the SetValue function - it only works to update records (or Set the Value) of another related form - if I am in a form with more than 1 subform it will not work.

Do you know a way to have SetValue set the value for a Table instead? or a similar functioning macro?

I know there was an EditRecord function on the Macros but for some reason I cannot use this function, it does not appear!

thanks again
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:35
Joined
Sep 21, 2011
Messages
14,050
I have only used macros for batch functions like running a a few queries in sequence.

If you use the Builder, you should be able to set any subform control regardless of how many are on a form.?

To update fields in a table, I expect you would have to use a query when the control is not bound.?


Thanks both of them work.

However found another problem but with the SetValue function - it only works to update records (or Set the Value) of another related form - if I am in a form with more than 1 subform it will not work.

Do you know a way to have SetValue set the value for a Table instead? or a similar functioning macro?

I know there was an EditRecord function on the Macros but for some reason I cannot use this function, it does not appear!

thanks again
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:35
Joined
Aug 30, 2003
Messages
36,118
You could also use a recordset in VBA. To my knowledge, a macro can't update a table directly, but I'm not a macro user.
 

fedebrin

Registered User.
Local time
Today, 00:35
Joined
Sep 20, 2017
Messages
59
How would you write the same as below in VBA but setting the value on a table?

DLookUp("Max([NoteDate])","ACTIONNOTES","[ActionID]=[ActionIDb]")
 

Minty

AWF VIP
Local time
Today, 07:35
Joined
Jul 26, 2013
Messages
10,355
You would use an update query, and call that from your macro.

However if you think about it you are storing redundant data, you have already stored the last notes date with the note, so why store it again, when you can simply look it up.

Additionally, if for some reason a note gets deleted or updated without you running your update query, your data is now not accurate, so in fact it's much better practice to NOT to store the information twice.
 

fedebrin

Registered User.
Local time
Today, 00:35
Joined
Sep 20, 2017
Messages
59
You could also use a recordset in VBA. To my knowledge, a macro can't update a table directly, but I'm not a macro user.

could you help me write the code for this function?

thanks!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 00:35
Joined
Aug 30, 2003
Messages
36,118
Here's my template code for a recordset:

Code:
  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ...FROM...WHERE..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  rs.Close
  set rs = nothing
  set db = nothing

Within that, use the Edit method to edit the record. Test for EOF first in case the recordset doesn't return a record. The update query is simpler.
 

Users who are viewing this thread

Top Bottom