Form On Current question

[Billy B]

On a tabbed control of the form I am working with, I have want a label to
display the store information from the store table where the StoreID = the
number found in the txtPurchased field of the same tab control. The data
shows up alright for the first record but does not update when I move to the
next record. What do I need to do? Thank you.

Private Sub Form_Current()

'store the StoreID of the plant record for
'use in DLookup statements below
Dim intStoreNum As Integer

'create variables to hold store information
Dim strStoreNme As String, strAddress As String, strCity As String
Dim strState As String, strZip As String, strPhone As String
Dim strFax As String, strEmail As String

'get the vendors number from the label on the tab control
intStoreNum = Me.txtPurchased.Value

strStoreNme = DLookup("StoreName", "tblStore", "StoreID=" & intStoreNum)
strAddress = DLookup("Address", "tblStore", "StoreID=" & intStoreNum)
strCity = DLookup("City", "tblStore", "StoreID=" & intStoreNum)
strState = DLookup("State", "tblStore", "StoreID=" & intStoreNum)
strZip = DLookup("ZipCode", "tblStore", "StoreID=" & intStoreNum)
strPhone = DLookup("Phone", "tblStore", "StoreID=" & intStoreNum)
strFax = DLookup("Fax", "tblStore", "StoreID=" & intStoreNum)
strEmail = DLookup("Email", "tblStore", "StoreID=" & intStoreNum)

Me.lblFullRetailInfo.Caption = strStoreNme & vbNewLine _
& strAddress & vbNewLine _
& strCity & "," & strState & " " & strZip & vbNewLine _
& "Phone: " & strPhone & vbNewLine _
& "Fax: " & strFax & vbNewLine _
& "E-mail: " & strEmail
End Sub

 

[ruralguy via AccessMonster.com]

Why not join the tblStore table to the RecordSource of your form in a query
so the fields are available without using DLookup's?

 

[Billy B]

I'll do that. Thank you.

Why not join the tblStore table to the RecordSource of your form in a query
so the fields are available without using DLookup's?