Dlookup

[turks67 via AccessMonster.com]

How do I use textbox on a form to lookup a userid in a table to open another
form?

 

[Klatuu]

You can use the Before Update event of the text box to do the lookup, then
use the Where argument of the OpenForm to have the form open to the selected
ID. The reason I perfer the Before Update rather than the After Update is
because it can be canceled and you make the cursor stay in the text box.

Private Sub MyTextBox_BeforeUpdate(Cancel As Integer)
Dim varUser As Variant

If Not IsNull(Me.MyTextBox) Then
varUser = DLookup("ID", "UserTable", "ID = " & Me.MyTextBox)
If IsNull(varUser) Then
MsgBox "User " & Me.MyTextBox & "Not Found", _
vbExclamation + vbOkOnly, "Entry Error"
Me.MyTextBox.Undo
Cancel = True
Else
Docmd.OpenForm "MyOtherForm", , , "ID = " & Me.MyTextBox
End If
End With

End Sub

But, why not use a combo box? That is what they were designed to do.

 

[turks67 via AccessMonster.com]

I will try a combo box. The reason for using a textbox because I did not want
everyone to see all the userid's.

 

[turks67 via AccessMonster.com]

I'm getting a run-time error "2001" and "you canceled the previous operation".

I'm trying to see what is the problem.

 

[Douglas J. Steele]

That very misleading error message can occur if you're mistyped the name of
the table or field in your DLookup statement, but I think in this case, Dave
may have left out the quotes. (I'm assuming that ID is a text field)

varUser = DLookup("ID", "UserTable", "ID = """ & Me.MyTextBox & """")

That's three double quotes in a row before the ampersand, and four double
quotes in a row at the end.

 

[Klatuu]

I didn't include quotes because as soon as I see ID, I think Long Integer.
Maybe should have mentioned the possibility it was text.
--
Dave Hargis, Microsoft Access MVP

That very misleading error message can occur if you're mistyped the name of
the table or field in your DLookup statement, but I think in this case, Dave
may have left out the quotes. (I'm assuming that ID is a text field)

varUser = DLookup("ID", "UserTable", "ID = """ & Me.MyTextBox & """")

That's three double quotes in a row before the ampersand, and four double
quotes in a row at the end.

 

[Douglas J. Steele]

Yup, a natural assumption.

However, since the original post was "How do I use textbox on a form to
lookup a userid in a table to open another form?", I assumed that userid
meant text.

 

[Klatuu]

Reasonable, of course.