Table to feed form instead of lenghty code?

[Stockwell43]

Hello,

I a database that is kind of like an Auction type deal. If a user enters
their user ID and click the button, it will automatically display their
chosen private ID (or Auction ID). What I want to know is, I have 80 people
in my department and the code (only way I know how to do it) will become very
long. Can I set up a table with the User ID and Private ID and when the
button is clicked, have it read from the table and display the Private ID
that way? Below is how I am going to do it if no alternative.

Private Sub cmdStatusUpdate_Click()
Me.NewBid.Locked = False

If Me.UserID = "123" Then
Me.PrivateID = "Dman42"
ElseIf Me.UserID = "321" Then
Me.PrivateID = "apples01"
ElseIf Me.UserID = "777" Then
Me.PrivateID = "nessa1"
ElseIf Me.UserID = "888" Then
Me.PrivateID = "sweet1"
ElseIf Me.UserID = "trinity" Then
Me.PrivateID = "starbucks"
Else
MsgBox ("Incorrect UserID")
End If
End Sub

Thanks!!

 

[Douglas J. Steele]

Store the information in a table, and use DLookup. Something like

Private Sub cmdStatusUpdate_Click()
Dim varPrivateID As Variant

Me.NewBid.Locked = False
varPrivateID = DLookup("PrivateID", "MySecretTable", "UserID = '" &
Me.UserID & "'")
If IsNull(varPrivateID) = True Then
MsgBox ("Incorrect UserID")
Else
Me.PrivateID = varPrivateID
End If

End Sub

 

[Stockwell43]

Hi Doug, thanks for responding.

So I am going to add three fields to the table correct? What is the
MySecretTable representing? I will do it in the order you have it written.
Also, Which one of these fields if any are the primary key?

Thanks!!!

 

[Douglas J. Steele]

Your table only needs to have 2 fields in it: UserID and PrivateID.
"MySecretTable" is the name I chose for the table. You can change it to
whatever you want.

Since you'll looking up the PrivateID based on the UserID, I would assume
that the PK of the table would be UserID.

 

[Stockwell43]

it I got Doug, works like gem!!!!

Thank you so much for your help!!