How do I swap records between subforms?

U

unhinged

Hi, I'm using Access 2000 and trying to achieve a task that I know how
to do in FIleMaker.

I have a screen with two subforms, the one on the left shows a list of
courses that are part of a group and the subform on the right shows a
list of courses that are available to be added to the group.

What I would like to do is have the user click a button for a record in
either subform and have the record show up in the other subform.

In FileMaker, I would do this by setting the key field to a new value,
but I seem unable to do this in Access: first, when I tried setting the
key field to a NULL value, I ended up with a query where I could add
records but could not edit them, which caused Access to hang repeatedly
on the Requery command.

Now I have tried setting things up so that the query populating the
right-hand subform uses a value of zero in the WHERE clause, but I am
getting a problem where a message that no record can be found in
tGroups with the matching field "GroupID".

Can anyone point me to (preferably) the correct method for achieving my
goal and/or explain why the approach I am trying does not seem to work?

Thanks,
Daniel
 
M

Marshall Barton

unhinged said:
Hi, I'm using Access 2000 and trying to achieve a task that I know how
to do in FIleMaker.

I have a screen with two subforms, the one on the left shows a list of
courses that are part of a group and the subform on the right shows a
list of courses that are available to be added to the group.

What I would like to do is have the user click a button for a record in
either subform and have the record show up in the other subform.

In FileMaker, I would do this by setting the key field to a new value,
but I seem unable to do this in Access: first, when I tried setting the
key field to a NULL value, I ended up with a query where I could add
records but could not edit them, which caused Access to hang repeatedly
on the Requery command.

Now I have tried setting things up so that the query populating the
right-hand subform uses a value of zero in the WHERE clause, but I am
getting a problem where a message that no record can be found in
tGroups with the matching field "GroupID".
Click to expand...


I know from nothing about changing keys in FM, but the
primary key field in most databases is a complex process
because of the requirements of referential integrity.
Instead, the usual approach is to use an ordinary field
(probably a Yes/No type) to identify which records are
selected.

To do what I think you want, add a Yes/No field named
Selected to your table. Then set one subform's record
source query to something like:
SELECT * FROM table WHERE Selected = False
and the other subform's to:
SELECT * FROM table WHERE Selected = True

Then, the selection process would consist of nothing more
than setting the Selected field to True or False and using
Requery on both subforms. This might even be done by
letting the user click on the Selected check box and using
the check box's AfterUpdate event to do the requeries:
Me.Requery
Me.Parent.othersubform.Form.Requery

There's probably more details than that, but they depend on
what else you have going on. Especially important is if
your database is multi-user, in which case the Selected
field can not be in the same table as the rest of the data.
 
Top