john wrote:
In my form (table A) I have subform (table B (only 2 fieds: ID and App_name)
where table A -Table B are linked 1XM. To be able to add a record in the
subform I want to use a lookup form since the number of possible values is
over 300. For data consistancy I've locked the subform so that the user
can't add or change values manually without using the lookup form.
My question is: What's the best way of adding the record to table B which
the user chooses in the lookup form? Should I use an add query? or other
code?
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You might want to try a ComboBox on the App_name & see how long it takes
to load. Set the Auto Expand property to yes so the user can just type
in the app_name to find it.
Or, have your lookup form retrieve both the ID and the App_name. When
the user clicks the OK button on the lookup form (signifying that the
retrieved record is the correct record) copy the ID & the app_name to
the subform's ID & app_name controls. Then the user can decide if they
want to save that record or try something else.
Or, you could also just use an append query to save the selected record
from the lookup form, but, then you'd have to requery the subform to
show the new record. Ex:
Private Sub cmdOK_Click()
Const SQL = "INSERT INTO tableB (ID, app_name) VALUES ("
On Error GoTo err_
' Uses DAO
CurrentDB.Execute SQL & Me!ID & ",'" & Me!app_name & "')", _
dbFailOnError
' Now requery the subform so the new record will show
Forms!TableA!TableB.Form.Requery
exit_:
Exit Sub
err_:
MsgBox "Error: " & Err.Description
End Sub
Change the form names to suit.
I'm assuming that app_name is a Text field, that's why the single quotes
are around it (inside the double quotes).
You may want to beef up the error handler to do something more
meaningful to your application.
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRTu9gIechKqOuFEgEQKrTQCg3iR8ScD927vScgDf+mwGTf PY7NMAoJUI
fSXEXhpV8jLoSEnDm5RHUfCg
=5Kwb
-----END PGP SIGNATURE-----