Validate and fill in textbox

[dcollins]

Help! On my form, when users enter an SID, I want to first verify that the SID exists in another table (tblSISAR) and second, to fill in the name (from tblSISAR) associated with that SID. tblSISAR has 25,000-40,000 records in it so I don't think a combo box is feasible. I've tried dlookup in both beforeupdate and afterupdate on the SID field but can't make it work. With a table that large though, I'm not sure dlookup is my best option, especially since eventually I will split the database. Does anyone know how I should handle this?

 

[WayneRyan]

D,

I'd normally be tempted to use a recordset to do
this, but with that large of a table, you can
make an update query.

The UpdateTo is:

Forms![YourForm]![TheName]

and the criteria is:

Forms![YourForm]![SID]

Then you can use the BeforeUpdate event of your
form to execute the query.

Wayne

 

[dcollins]

I'm sorry, I'm confused. I tried that but it did not work. I'm not sure I would want an update query since my goal isn't to update the table. My main goal is to verify that the SID number exists in this seperate, large table. At this point I just want the name displayed on the form (not saved to the database). If the SID does not exist I want to notify the user and make them reenter a valid number. I've attached my database, it starts by clicking frmMainMenu. tblSISAR is the table that would normally be 25,000-40,000 records (I've put bogus info in and greatly shortened it). Thanks for your help.

 

[Mike375]

I would make a table from your big table and the new table would only have the fields you need and that way it will open quickly.

Make a form for the new table. Then a simple macro to open that form for matching record to form where SID (what is SID?) and a SetValue action to transfer the name to an unbound text box on your main entry form.

Mike

 

[dcollins]

Thanks for the insight. I used a subform and it seems to be working. Don't know if there are any performance hits associated with doing it this way but after hours of fighting with this I'm just happy it is finally doing what I want! (SID by the way is Student ID)