OnCurrent event problem

[NeoFax99]

I am trying to use an OnCurrent event to do a dlookup to check an item.
Then show a subform based on the dlookup item. The problem is the
dlookup only returns the first items value and not the current items.
below is the code:

Private Sub Form_Current()
Dim formNo As String

Forms!frmAssett.Form3215Information.Visible = False
Forms!frmAssett.Form9Information.Visible = False
Forms!frmAssett.Form332Information.Visible = False

formNo = DLookup("FormNo", "tblAssett")

MsgBox formNo, vbInformation

If formNo = "1" Then
Forms!frmAssett.Form3215Information.Visible = False
Forms!frmAssett.Form9Information.Visible = True
Forms!frmAssett.Form332Information.Visible = False
ElseIf formNo = "2" Then
Forms!frmAssett.Form3215Information.Visible = False
Forms!frmAssett.Form9Information.Visible = False
Forms!frmAssett.Form332Information.Visible = True
ElseIf formNo = "3" Then
Forms!frmAssett.Form3215Information.Visible = True
Forms!frmAssett.Form9Information.Visible = False
Forms!frmAssett.Form332Information.Visible = False
End If

End Sub

I added in the MsgBox just to see what dlookup shows as the value.

 

[RoyVidar]

I am trying to use an OnCurrent event to do a dlookup to check an
item. Then show a subform based on the dlookup item. The problem is
the dlookup only returns the first items value and not the current
items. below is the code:

Private Sub Form_Current()
Dim formNo As String

Forms!frmAssett.Form3215Information.Visible = False
Forms!frmAssett.Form9Information.Visible = False
Forms!frmAssett.Form332Information.Visible = False

formNo = DLookup("FormNo", "tblAssett")

MsgBox formNo, vbInformation

If formNo = "1" Then
Forms!frmAssett.Form3215Information.Visible = False
Forms!frmAssett.Form9Information.Visible = True
Forms!frmAssett.Form332Information.Visible = False
ElseIf formNo = "2" Then
Forms!frmAssett.Form3215Information.Visible = False
Forms!frmAssett.Form9Information.Visible = False
Forms!frmAssett.Form332Information.Visible = True
ElseIf formNo = "3" Then
Forms!frmAssett.Form3215Information.Visible = True
Forms!frmAssett.Form9Information.Visible = False
Forms!frmAssett.Form332Information.Visible = False
End If

End Sub

I added in the MsgBox just to see what dlookup shows as the value.

You probably need something in the optional third arguement of the
DLookup function - a criterion. Typically, the primary key of the
table matched with a form control containing the current value of
it. Perhaps something like the below (amend the names) for a
numeric primary key might work?

formNo = DLookup("FormNo", "tblAssett", "MyPK = " & Me!txtMyMK)

 

[Allen Browne]

How do you identify the "current item"?
Is there a primary key field?
Could you use that field value in the DLookup so it gets the right value?

The example below assumes the primary key is a numeric field named "ID", and
that it appears in tblAssett as well.

Private Sub Form_Current()
Dim strWhere As String
Dim formNo As String

If Not Me.NewRecord Then
strWhere = "[ID] = " & Me.ID
formNo = Nz(DLookup("FormNo", "tblAssett", strWhere), vbNullString)
End If
'Debug.Print "formNo is " & formNo

Me.Form9Information.Visible = (formNo = "1")
Me.Form3215Information.Visible = (formNo = "3")
Me.Form332Information.Visible = (formNo = "2")
End Sub

If FormNo is a Number field in tblAssett (not a Text field), the code needs
to be different.

You will need to add error handling in case one of the fields you are hiding
has focus.

 

[NeoFax99]

Thank you both! I used Allens code and it worked perfectly. I guess
the DLookup requires the primary key to know exactly where in the
database it is looking up.