Problem with Dlookup?

[DLDukes]

If have a form with a 6 page tab control. The form's control source is a query that selects all records from 5 (one to one) tables. The form should allow the user to add a new record to each table in one update. <br><br>In the Form Open Event, I am updating a value in a table that holds the &quot;next id&quot; number with a RunSQL statement.<br>&nbsp;<br>I want to be able to load the updated value into a control on the form. I have tried an OpenQuery, and a Dlookup with out any luck. <br><br>The Dlookup wants me to give it some specfic criteria and I'm selecting from a one field, one record table. What would the criteria expression be?<br><br>When I do a docmd.openquery, then try and assign the value of the control to the value of the one field in the query, it tells me it can't find the table name of the query (even though I used Build).<br><br>I'm doing something stupid I'm sure, this should be simple. Any clues?<br><br>Using Access97. TIA DLDukes<br><br>

 

[JimHorton]

Suppose your table was &quot;tblNextID&quot;.&nbsp;&nbsp;You'd do this to get the new value after the runsql<br><br>dim x<br>x = dlookup(&quot;IDfield&quot;,&quot;tblNextId&quot<br><br>criteria is optional, not needed if it's one record table.<br><br>--Jim

 

[DLDukes]

Thanks, Jim. That's exactly what I was doing but in a FormsOpen event. I was able to get it to work after I cleaned up the data (the ID was coming from an RDB database and was text. I changed them all to integer) and put the Dlookup in the FormsLoad<br>event. One or both changes seemed to do the trick. I felt like I had the DLookup right, so thanks for the confirmation. DLD