By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,460 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Lookup form and adding new records

P: n/a
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?
Thanks,
john
Oct 22 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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-----
Oct 22 '06 #2

P: n/a
Thanks! see inline...

"MGFoster" <me@privacy.comschreef in bericht
news:64******************@newsread4.news.pas.earth link.net...
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?


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.
I tried this and the response time is OK but the downside is that a user
can't search with wildcards this way.
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.
In this case, how can I make sure that a new record is created and how can I
copy those values to the right new record?
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 going to work on this last one.
Thanks again,
john
Oct 22 '06 #3

P: n/a
"MGFoster" <me@privacy.comschreef in bericht
news:64******************@newsread4.news.pas.earth link.net...

Great, got it working except for one thing.
' Now requery the subform so the new record will show
Forms!TableA!TableB.Form.Requery
From the lookup form I've tried:

Forms!MyMainForm!MySubForm.Form.Requery

but Access thinks MySubForm is a field.

Any thought on why this is happening?
Thanks,
john
Oct 22 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.