DLookup或DMax来寻找价值 [英] DLookup Or DMax To Find Value

问题描述

我确信这很简单，我很难入门.我使用表格向客户开具发票，其中包含[Billing_Month]字段.我要完成的就是这个.当我创建新发票时，[Billing_Month]将查找最后创建的发票(将[Invoice_#]与DMax一起使用?)，并从该发票中填充[Billing_Month]

I am sure this is fairly simple put I am having trouble getting started on this. I use a Form to invoice clients which includes the field [Billing_Month]. What I'm looking to accomplish is this. When I create a new invoice, the [Billing_Month] will look to the last invoice created (use [Invoice_#] with DMax?), and populate the value from that that invoices [Billing_Month]

我曾经考虑使用:Billing_Month = DMax("Billing_Month"，"frmInvoices")，但这并不能为我提供最后一张发票，它只会查找最高Billing_Month，这是一个文本字段.

I have thought to use: Billing_Month = DMax ("Billing_Month", "frmInvoices"), but this doesn't specifically get me the last invoice, it would just look for the highest Billing_Month, which is a text field.

我曾想过使用:Billing_Month = DLookup("Billing_Month"，"frmInvoices")，但这并不能为我提供最后一张要提取的发票.

I have thought to use: Billing_Month = DLookup ("Billing_Month", "frmInvoices"), But this doesn't get me the last invoice to pull from.

推荐答案

为此，我将使用自定义函数-假设基础表名为tblInvoices:

I'd use a custom function for this - assuming the underlying table is called tblInvoices:

Function GetBillingMonthOfLatestInvoice()
  Const SQL = "SELECT TOP 1 Billing_Month FROM tblInvoices ORDER BY [Invoice_#] DESC"
  Dim RS AS DAO.Recordset
  Set RS = CurrentDb.OpenRecordset(SQL)
  If RS.EOF Then
    GetBillingMonthOfLatestInvoice = Null
  Else
    GetBillingMonthOfLatestInvoice = RS(0)
  End If
End Function

更新

上面的代码可以概括为返回其他相关字段，如下所示:

Update

The above code can be generalised to return other related fields like so:

Function GetValueForLatestInvoice(FieldToLookUp As String)
  Dim RS As DAO.Recordset, SQL As String
  SQL = "SELECT TOP 1 " + FieldToLookUp + " FROM tblInvoices ORDER BY [Invoice_#] DESC"
  Set RS = CurrentDb.OpenRecordset(SQL)
  If RS.EOF Then
    GetValueForLatestInvoice = Null
  Else
    GetValueForLatestInvoice = RS(0)
  End If
End Function

要使用该代码，请将代码复制到一个新的标准模块中，然后对于表单上的每个相关文本框，在属性"窗口中将其默认值"属性设置为如下所示:

To use, copy the code to a new standard module, then for each relevant text box on the form, set its Default Value property in the Properties window to something like this:

=GetValueForLatestInvoice("Billing_Month")

用于保存帐单月份值的文本框；对于持有帐单年度的那一年，您将使用

That would be for the text box holding the billing month value; for the one holding the billing year, you would use

=GetValueForLatestInvoice("Billing_Year")

这篇关于DLookup或DMax来寻找价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！