使用 DLookup 函数可以方便实现此功能,参考代码如下:
Option Compare Database
Public Function SafeDLookup(ByVal Expr As String, _
ByVal Domain As String, _
Optional Criteria As Variant) As Variant
SafeDLookup = Null
On Error Resume Next
If IsMissing(Criteria) Then
SafeDLookup = DLookup(Expr, Domain)
Else
SafeDLookup = DLookup(Expr, Domain, Criteria)
End If
End Function
Private Sub Command2_Click()
Dim varC As Variant
Dim varBID As Variant
varBID = BID.Value
varC = SafeDLookup("C", "A", "B='" & Replace(varBID, "'", "''") & "'")
If Not IsNull(varC) Then
MsgBox varBID & "已被" & varC & "借走"
End If
End Sub
假设 A 表中有如下测试数据:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/359b033b5bb5c9ea9eaf88fcdb39b6003af3b355?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
当在 BID 文本框中输入 k137 时的运行效果:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/3b87e950352ac65c85ccee6cf5f2b21192138ae0?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
追问谢谢你的回答,请问有ACCESS中vba代码吗?不是与excel连接,表也是Access中的表。
追答上面贴出的就是 Access VBA 代码(与 Excel 无关!):
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/63d0f703918fa0ec2e7daa0c289759ee3d6ddb7d?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)