Retrieving value in last record

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

How does one retrieve the value of a particular field of the last record in
a table in VBA? Any help with this will be appreciated.

Joan
 
If your form is based on a table or a query that includes the table ----

Create an unbound textbox on your form and put the following expression in its
Control Source property:

=Max([NameOfTheField])


--
PC Datasheet
A Resource for Access, Excel and Word Applications
(e-mail address removed)
www.pcdatasheet.com

· Design and basic development for new applications
· Additions, Modifications and "Fixes" for existing applications
· Mentoring for do-it-yourselfers who want guidance
· Complete application design and development
· Applications Using Palm Pilot To Collect Data And
Synchronize The Data Back To Access Or Excel
 
Andy Cole said:
Joan

One simple way to do this is to use a DMax and then a DLookup;

Assumes a Public variable declared in a global module, say a double

Public dblSMargin As Double

Dim lngID As Long

lngID = DMax("[SalesMarginID]", "[SalesMargin]")
dblSMargin = DLookup("[SMFactor]",
"[SalesMargin]","[SalesMarginID] = " & lngID)

HTH

Andy

Joan said:
John,
By last record, I mean the last record entered into the table. This
is a SalesMargin table where the database can retrieve the current
sales margin to add to the cost of products( or in other words the
'last entered'). By retrieving the last entered sales margin from
the table the user does not have to set a variable to this value
each time they open the database. What is the best way to achieve
this?
Click to expand...
Click to expand...

Note, though, that this approach only works if there is a SalesMarginID
field that can be relied on to be always ascending (so that the record
with the maximum ID is the most recent one). An ascending autonumber
would work for this, provided that (a) the database will bever be
replicated, which causes autonumbers to go random, and (b) no glitch
occurs that causes the database to start assigning negative autonumbers
(which has been known to happen). For safety's sake, you really
shouldn't make any assumptions about the value of autonumbers.

If you have a non-autonumber SalesMarginID field and you use code to
assign your own, ascending values to it, then you *can* rely on it to be
in ascending sequence and Andy's suggested approach is a good one. If
not, then the only reliable way to retrieve the most recently entered
record in the table is to have a date-time field in the table with a
default value of Now(). Then you look up the record with the maximum
value in that date-time field. Even this approach won't work if it's
possible for there to be two records created at the same moment, but my
guess from Joan's description is that that won't happen.
 
Words of wisdom Dirk.

Joan, Dirk's comments show how complex a seemingly simple request can become

Andy

Dirk Goldgar said:
Andy Cole said:
Joan

One simple way to do this is to use a DMax and then a DLookup;

Assumes a Public variable declared in a global module, say a double

Public dblSMargin As Double

Dim lngID As Long

lngID = DMax("[SalesMarginID]", "[SalesMargin]")
dblSMargin = DLookup("[SMFactor]",
"[SalesMargin]","[SalesMarginID] = " & lngID)

HTH

Andy

Joan said:
John,
By last record, I mean the last record entered into the table. This
is a SalesMargin table where the database can retrieve the current
sales margin to add to the cost of products( or in other words the
'last entered'). By retrieving the last entered sales margin from
the table the user does not have to set a variable to this value
each time they open the database. What is the best way to achieve
this?
Click to expand...
Click to expand...

Note, though, that this approach only works if there is a SalesMarginID
field that can be relied on to be always ascending (so that the record
with the maximum ID is the most recent one). An ascending autonumber
would work for this, provided that (a) the database will bever be
replicated, which causes autonumbers to go random, and (b) no glitch
occurs that causes the database to start assigning negative autonumbers
(which has been known to happen). For safety's sake, you really
shouldn't make any assumptions about the value of autonumbers.

If you have a non-autonumber SalesMarginID field and you use code to
assign your own, ascending values to it, then you *can* rely on it to be
in ascending sequence and Andy's suggested approach is a good one. If
not, then the only reliable way to retrieve the most recently entered
record in the table is to have a date-time field in the table with a
default value of Now(). Then you look up the record with the maximum
value in that date-time field. Even this approach won't work if it's
possible for there to be two records created at the same moment, but my
guess from Joan's description is that that won't happen.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
So I see! A simple request can become complex. Everything I have read from
your posts makes sense though and I am trying to follow the advice by
setting up a Margins table with the fields: ChangeDate (Default value =
Now()) to hold the date the margin was changed and is the primary key and
SalesMargin to hold the sales margin amount. But I am running into a slight
problem with syntax. Since you know what I am trying to accomplish already
would one of you mind taking a look at my code to see what the problem is?

I am using the DMax and DLookup functions that you illustrated to retrieve
the last entered value in the SalesMargin field. I am getting a syntax error
on the following DLookUp line:
curSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #" &
dteSMdate & "#")

curSalesMargin is a textbox (on the form where the salesmargin can be
changed) that displays for the user the current SalesMargin. IncreaseAmt is
my global variable and dteSMdate is a variable of date type. What is wrong
with my syntax in the above line? I do NOT get the error message on the
line IncreaseAmt = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")

The error message says the error is: ' in date in expression '[ChangeDate] =
## '

Thanks for all of your help!

Joan

Private Sub Form_Open(Cancel As Integer)
curSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")
End Sub


Private Sub Purchase_Price_AfterUpdate()
Dim dteSMdate As Date
dteSMdate = DMax("[ChangeDate]", "[Margins]")
IncreaseAmt = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")
Me.[Salesprice] = Me.[Purchase Price] + IncreaseAmt
End Sub


Andy Cole said:
Words of wisdom Dirk.

Joan, Dirk's comments show how complex a seemingly simple request can become

Andy

Dirk Goldgar said:
Andy Cole said:
Joan

One simple way to do this is to use a DMax and then a DLookup;

Assumes a Public variable declared in a global module, say a double

Public dblSMargin As Double

Dim lngID As Long

lngID = DMax("[SalesMarginID]", "[SalesMargin]")
dblSMargin = DLookup("[SMFactor]",
"[SalesMargin]","[SalesMarginID] = " & lngID)

HTH

Andy

John,
By last record, I mean the last record entered into the table. This
is a SalesMargin table where the database can retrieve the current
sales margin to add to the cost of products( or in other words the
'last entered'). By retrieving the last entered sales margin from
the table the user does not have to set a variable to this value
each time they open the database. What is the best way to achieve
this?
Click to expand...

Note, though, that this approach only works if there is a SalesMarginID
field that can be relied on to be always ascending (so that the record
with the maximum ID is the most recent one). An ascending autonumber
would work for this, provided that (a) the database will bever be
replicated, which causes autonumbers to go random, and (b) no glitch
occurs that causes the database to start assigning negative autonumbers
(which has been known to happen). For safety's sake, you really
shouldn't make any assumptions about the value of autonumbers.

If you have a non-autonumber SalesMarginID field and you use code to
assign your own, ascending values to it, then you *can* rely on it to be
in ascending sequence and Andy's suggested approach is a good one. If
not, then the only reliable way to retrieve the most recently entered
record in the table is to have a date-time field in the table with a
default value of Now(). Then you look up the record with the maximum
value in that date-time field. Even this approach won't work if it's
possible for there to be two records created at the same moment, but my
guess from Joan's description is that that won't happen.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Joan

The reason for the syntax error is because when you run the code;

curSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #" &
dteSMdate & "#")

in the Form's onOpen event, the variable dteSMdate has not been set thus the
condition clause of the DLookUp evaluates to [ChangeDate] = ##" which
results in the syntax error.

Change the Form_Open code to;

Private Sub Form_Open(Cancel As Integer)
Dim dteSMdate As Date
dteSMdate = DMax("[ChangeDate]", "[Margins]")
Me.curSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] =
#" & dteSMdate & "#")
End Sub

However, if you are likely to need to pull back the current 'SalesMargin' in
other forms in your DB (apart from the 2 you have mentioned in this post),
it would be better to write a Public Function in a global Module as follows
(I've assumed the SalesMargin is of type Double);

Public Function GetSalesMargin() As Double
Dim dteSMDate As Date
dteSMdate = DMax("[ChangeDate]", "[Margins]")
GetSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")
End Function

Then, in the form where you can change the margin, remove the Form_Open code
and just set the ControlSource property of the curSalesMargin textbox to;

=GetSalesMargin()

And change the Purchase Price AfterUpdate code to

Private Sub Purchase_Price_AfterUpdate()
Me.[Salesprice] = Me.[Purchase Price] + GetSalesMargin()
End Sub

In any other forms, just use the function to get the current SalesMargin
using methods similar to those above.

HTH

Andy

Joan said:
So I see! A simple request can become complex. Everything I have read from
your posts makes sense though and I am trying to follow the advice by
setting up a Margins table with the fields: ChangeDate (Default value =
Now()) to hold the date the margin was changed and is the primary key and
SalesMargin to hold the sales margin amount. But I am running into a slight
problem with syntax. Since you know what I am trying to accomplish already
would one of you mind taking a look at my code to see what the problem is?

I am using the DMax and DLookup functions that you illustrated to retrieve
the last entered value in the SalesMargin field. I am getting a syntax error
on the following DLookUp line:
curSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #" &
dteSMdate & "#")

curSalesMargin is a textbox (on the form where the salesmargin can be
changed) that displays for the user the current SalesMargin. IncreaseAmt is
my global variable and dteSMdate is a variable of date type. What is wrong
with my syntax in the above line? I do NOT get the error message on the
line IncreaseAmt = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")

The error message says the error is: ' in date in expression '[ChangeDate] =
## '

Thanks for all of your help!

Joan

Private Sub Form_Open(Cancel As Integer)
curSalesMargin = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")
End Sub


Private Sub Purchase_Price_AfterUpdate()
Dim dteSMdate As Date
dteSMdate = DMax("[ChangeDate]", "[Margins]")
IncreaseAmt = DLookup("[SalesMargin]", "[Margins]", "[ChangeDate] = #"
& dteSMdate & "#")
Me.[Salesprice] = Me.[Purchase Price] + IncreaseAmt
End Sub


Andy Cole said:
Words of wisdom Dirk.

Joan, Dirk's comments show how complex a seemingly simple request can become

Andy

Dirk Goldgar said:
Joan

One simple way to do this is to use a DMax and then a DLookup;

Assumes a Public variable declared in a global module, say a double

Public dblSMargin As Double

Dim lngID As Long

lngID = DMax("[SalesMarginID]", "[SalesMargin]")
dblSMargin = DLookup("[SMFactor]",
"[SalesMargin]","[SalesMarginID] = " & lngID)

HTH

Andy

John,
By last record, I mean the last record entered into the table. This
is a SalesMargin table where the database can retrieve the current
sales margin to add to the cost of products( or in other words the
'last entered'). By retrieving the last entered sales margin from
the table the user does not have to set a variable to this value
each time they open the database. What is the best way to achieve
this?

Note, though, that this approach only works if there is a SalesMarginID
field that can be relied on to be always ascending (so that the record
with the maximum ID is the most recent one). An ascending autonumber
would work for this, provided that (a) the database will bever be
replicated, which causes autonumbers to go random, and (b) no glitch
occurs that causes the database to start assigning negative autonumbers
(which has been known to happen). For safety's sake, you really
shouldn't make any assumptions about the value of autonumbers.

If you have a non-autonumber SalesMarginID field and you use code to
assign your own, ascending values to it, then you *can* rely on it to be
in ascending sequence and Andy's suggested approach is a good one. If
not, then the only reliable way to retrieve the most recently entered
record in the table is to have a date-time field in the table with a
default value of Now(). Then you look up the record with the maximum
value in that date-time field. Even this approach won't work if it's
possible for there to be two records created at the same moment, but my
guess from Joan's description is that that won't happen.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top