opening a record

[Theguyinthehat]

So far I have managed to tiptoe around opening a record, but the time has come that I can't keep making references to invisible columns in a combobox selection. If I have a table JobEntry, and I want to look up a specific Lot Number with cboLotNumber, how do I open that record using after_update()?

I want to be able to bind text boxes to fields in JobEntry and have them display the values on that record. Here is my attempt:

Private Sub LotNumber_AfterUpdate()
Dim db As Object
Set db = CurrentDb
Dim rst As DAO.Recordset
Set rst = db.OpenRecordset("JobEntry")
DoCmd.FindRecord (Me.LotNumber)
End Sub

I currently get an error saying I can't find or replace here. It doesn't like my use of findrecord.

 

[gemma-the-husky]

the easiest way is to base your form on a query that includes everything you need

if some data is in 1 rable and other data in another, then join these tables visually in a query.

now you can refer a control on your form, to any field in the query without needing any code at all.

thats the best way.

if you still need to lookup something that isnt included in the query recordset, the easiest way is generally to use a dlookup

myvalue = dlookup("thevalueyouwant","thetableitsin","therowidentifier")

if you need if your form is bound to your table,

 

[teball20]

OBSERVATION:
(1) is this context for viewing or editing?
(2) if for editing, I strongly disagree with "the husky". if the form's record source is bound directly to your JobEntry table [or any updateable query] then you run the grave risk of user's inadvertently modifying the source data without integrity and validation checks in place since the control (your text boxes) are "connected" directly to the data. since I am *not* familiar with your level of risk, I will refrain from discussing the protected way
(3) if for viewing only, do you want them to have the ability to scroll through records or simply view *one* record based on the combo box selection? sounds like one from your description.
(4) if *one* [very easy]
(a) set cbo control source to sql statement "SELECT [your primary key field to display in cbo] FROM JobEntry WHERE [optional clause to filter specific records] ORDER BY [optional clause for your user's preferences];"
(b) set the cbo properties in design view appropriately [i have not memorized, but i.e., no multi-select, no add news or edits, bound column pointer to field, number of columns, etc]. just peruse through the props since most are self-explanatory.
(c) after_update of cbo, use a public module level variable and set it equal to the cbo's user's selected value
(d) for each desired piece of data from the table, create a textbox (formatted esthetically on the form with appropriate headers and colors), keep them UNBOUND!! set each controls data source to a simple sql statement with the criteria matching the variable, i.e. "SELECT [the field in question] FROM JobEntry WHERE [pk = your variable]". ensure you get the syntax correct. do this for each text box
(e) avoid DLOOKUP, DCOUNT, D[etc's], these domain functions are costly (not $$, but execution time, error on NULLS) SQLs are built to be very fast
(f) WAIT NO! sorry, even better, use a recordset
dim rs as dao.recordset
set rs = currentdb.openrecordset("SELECT * FROM JobEntry WHERE [your primary key = the cbo variable value])
if (rs.bof and rs.eof) then
rs.close
set rs = nothing
'what the heck? why no record for this pk?
GOTO error_handler for message to user
end if
rs.movefirst (hoping that there is only one match - you never know! this is a sanity check)
'now for each text box, something like:
me.txtDesiredFieldName1 = rs!SameDesiredfieldName
me.txtDesiredFieldName2 = rs!SameDesiredfieldName
etc...

clean up references when done
rs.close
set rs = nothing

something like this will be fast and reliable. ok, it's past 1:00Am, forgive any of my blunders

 

[gemma-the-husky]

ok i'll bite

i just cant believe that defining a sql statement for a single field is any different from using a dlookup statement. dlookup must surely be optimised, to generate the same syntax

since we have dlookup(field,domain,criteria) - this must generate compiled code that does

select field from domain where criteria, which is the same as the SQL

etc etc

and nz(dlookup(field,domain,criteria), "defaultvalue")

easily handles most errors.

------------
and i still believe that for most purposes if you want a form to show data from several tables, then the obvious way is to link the tables in a query, and base the form on the query. Yes, of course you may want to prevent some of the fields being updated, and sometimes the data cannot be included in the query, so you need another technique, but why complicate stuff unnecessarily?

 

[The_Doc_Man]

teball20, if you are concerned about inadvertant editing, you set the control to LOCKED and viola - no inadvertant (or any other kind of) editing.

If it is an editing form in the first place, perhaps you add a couple of control buttons that UNLOCK the locked fields, then explicitly have a COMMIT or CANCEL set of buttons. Using VBA, these wouldn't be that hard to do. That way also, if you have an update, it's because someone screwed up after clicking the UNLOCK option i.e. they KNEW they were going to be able to muck about, and they DID. So at some point, you have to stop holding the user's hand. But with an UNDO button / CANCEL button, you give them one more chance to make it right anyway.

As to the recordset vs DLOOKUP speed, I disagree. VBA code to do recordset ops is interpretive whereas DLOOKUP is in a DLL file and is compiled to machine code. DLOOKUP will be faster. The difference between a stored query that must be parametrized vs. a DLOOKUP with a selection criterion is, to my mind, very close to an even-steven decision - which in my mind ALWAYS goes to the DLOOKUP on the grounds that you track fewer queries. You cannot merely focus on the single lookup. Look at the bigger picture and realize that the more things you are forced to create as distinct objects, the more it costs to maintain the DB as a whole.

Then, using the idea you proposed of setting a module-based variable from after-update code vs. doing a monolithic DLOOKUP that you call once and you are done - invites unwanted complexity to code that should be quite simple and straightforward.

Now, if this were an UPDATE or INSERT query with a DLOOKUP and it rolled through a Myriad of records, you are quite right that DLOOKUP is the wrong answer. But on a form that waits for a person to DO something, you will never, ever in a gazillion years see the speed difference involved in running a DLOOKUP to find what you wanted.

I concur with Gemma's analysis and offer the above considerations as a deeper look at that issues raised by teball20.