Add Record based on value of another

[alex]

I have a subform that opens in datasheet view. I would like users to
be able to add a new record based on the value (a single value) of an
existing one!

For example (I’ve already created a custom popup menu)…
The user right clicks a record under the field > StoreName…a menu pops
up with an AddRow command…user clicks on AddRow…a new record is
automatically added based on the value where the user right clicked.

I’ve tried assigning a macro to the custom command, but cannot get the
code to work.

The subform has a many-to-one relationship to the main form.

I ask this question because I’ve locked down the record on the many
side of the relationship (on the subform). I don’t want the user to
be able to arbitrarily add a new record (we all know what will happen)…
if they do not type a new record exactly like one in the main table,
they’ll get an referential integrity error (rightfully so). I assume
an easy way around this is to limit any new record to a preexisting
record already in the subform; a record assigned in the mainform.

I may have confused some…My mainform contains the subform and the
problem I mentioned above does not exist. What I do, however, is
allow the user to open the subform independently if he/she wishes
(it’s a space issue and seems to work just fine).

Any help would be appreciated.
alex

 

[John W. Vinson]

I ask this question because I’ve locked down the record on the many
side of the relationship (on the subform). I don’t want the user to
be able to arbitrarily add a new record (we all know what will happen)…
if they do not type a new record exactly like one in the main table,
they’ll get an referential integrity error (rightfully so). I assume
an easy way around this is to limit any new record to a preexisting
record already in the subform; a record assigned in the mainform.

I may have confused some…My mainform contains the subform and the
problem I mentioned above does not exist. What I do, however, is
allow the user to open the subform independently if he/she wishes
(it’s a space issue and seems to work just fine).

You can't have it both ways: a Subform is in a Subform Control on a mainform.
A form that you pop up independently is NOT a subform.

To resolve space issues, consider putting the Subform on one page of a Tab
Control. You can put mainform textboxes, combos, etc. on the first page of a
two-page tab control, and a Subform on the second page; this gives you the
master/child link field feature, synchronizes the forms, ensures integrity,
etc. without sacrificing screen space.

If you have good reason to use a popup form, then you'll need a fair bit of
VBA code. You can pass the Primary Key value from the mainform in the OpenArgs
argument of the OpenForm method, and use the child form's Open event to set
the DefaultValue property of the control bound to the foreign key field; that
control could be set with Enabled = No, Locked = Yes to keep the user from
changing it.

 

[alex]

You can't have it both ways: a Subform is in a Subform Control on a mainform.
A form that you pop up independently is NOT a subform.

To resolve space issues, consider putting the Subform on one page of a Tab
Control. You can put mainform textboxes, combos, etc. on the first page of a
two-page tab control, and a Subform on the second page; this gives you the
master/child link field feature, synchronizes the forms, ensures integrity,
etc. without sacrificing screen space.

If you have good reason to use a popup form, then you'll need a fair bit of
VBA code. You can pass the Primary Key value from the mainform in the OpenArgs
argument of the OpenForm method, and use the child form's Open event to set
the DefaultValue property of the control bound to the foreign key field; that
control could be set with Enabled = No, Locked = Yes to keep the userfrom
changing it.

Hi John,

I apologize for the confusion: I'm not looking to use a pop up form.
I have a pop up menu...the kind that appears when you right click. I
created a custom one and added simple things like cut and paste.

All that I'm looking to do is add another command (to the pop up menu)
that will add a new record (which is simple)and provide a value to
that record based on a preexisting value (cannot figure that part
out).

alex

 

[John W. Vinson]

I apologize for the confusion: I'm not looking to use a pop up form.
I have a pop up menu...the kind that appears when you right click. I
created a custom one and added simple things like cut and paste.

All that I'm looking to do is add another command (to the pop up menu)
that will add a new record (which is simple)and provide a value to
that record based on a preexisting value (cannot figure that part
out).

Please post your code. We can't see it from here.

I'd expect that you could use DLookUp() to find a value in a previous record,
but knowing nothing about the structure of your database it's impossible to be
specific!

 

[alex]

Please post your code. We can't see it from here.

I'd expect that you could use DLookUp() to find a value in a previous record,
but knowing nothing about the structure of your database it's impossible to be
specific!

John,

I don't have any code associated with the task. I'll do some research
on DLookUp() and see if I can provide more information.

Thanks,
alex