How to syncronize a datasheet form and single form?

  • Thread starter Thread starter Sandra
  • Start date Start date
S

Sandra

At the top of a tabbed form I have a datasheet view of a
table (so that I can scroll through a complete list). At
the bottom of the tabbed form is a single entry form of
the same table (this is to enter or edit the record).

Is there a way to click (or dblclick) on a record line in
the datasheet form at the top and have the input form at
the bottom sync with the selected record?

Thanks,

Sandra G
 
Sure. Try the following:

You have 2 child forms.

The first form is the continues form you place into the exiting form.

In this sub-form that shows this datasheet view, you
place the following command to make the child forms follow this form.

me.Parent.MyDetailInfoform.Requery

In the link child/master settings for this details form, you place

linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterForm].[form].[ID]
 
Thank you so much for your reply! I'm sorry, I am really
a novice, can you be more specific, I don't understand
where to put the first statement Me.Parent....

The name of the table for both form views is Donations.
The name of the form that is in datasheet view is
frmDonationsList. The name of the form that is the
single input form is named frmDonations.

Thank you again for your help,
Sandra
 
My sorry, I actually did NOT mention where to put the requery command.

Ok, the me.Parent goes in the sub-frms on-current event.

I would just open the sub-form in design mode (no need to open the parent
form).

Then, in the forms on-current event, you add:

me.Parent.MyDetailInfoform.Requery

Note that MyDetailInfoForm is the name of the sub-form control for the child
records form.

So, we are going to put the above requery in the frmDonationsList on-current
event

me.Parent.frmDonations.Requery

Note that the name of the sub-form control DOES NOT have to be the same name
as the form (and often it is not).

So, check the name of the sub-form control used to display the frmDonations
 
Uh oh, I thought we had this really nailed, but now I
have discovered a glitch. With this setup I cannot do a
new entry in the single view form because the Child and
Master fields have been changed from the PK of the table
of donations to the continuous subform.

My thought was to have another identical single subform
for new entries, with the source and Child/Master fields
linked to the donations table, and have this form hidden
behind the other until you click the NEW button on the
visible subform. I have this accomplished, but I can't
seem to get the right action when you click the NEW
button. What I have is:

Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
Forms!DonationsNew.Visible = True
Forms!DonationsNew.SetFocus
DoCmd.GoToRecord , , acNewRec
Yada, yada, yada...

But Access tells me it cannot find the form. The control
and the form have the same name - DonationsNew. Is there
a way to do this?

Thanks,
Sandra
 
Sandra said:
Uh oh, I thought we had this really nailed, but now I
have discovered a glitch. With this setup I cannot do a
new entry in the single view form because the Child and
Master fields have been changed from the PK of the table
of donations to the continuous subform.
Click to expand...

Do you want these new records to become a "child" record of the continues
form?


Further, you user interface is going to be a bit confusing. You likely
should be displaying the contact, and have details of the contact display in
a sub-form (this is naturally how ms-access works).

As it is now, users would likely think that they can add records to the
continues form (and they can't). Worse, is when they go to add a record to
the single form, they may very well assume that this record is going to
belong to the CURRENTLY select record in the continuous sub-form. This is
going to be a source of confusing here.

You actually might be better off to turn off the navigation buttons on the
contacts form. I would make the add button LAUNCH another form (as model).
When the user is finished..they can close this form and return to your form.
(you can put some requery code in the close of this form to re-load the
sub-forms).
Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
Forms!DonationsNew.Visible = True
Forms!DonationsNew.SetFocus
DoCmd.GoToRecord , , acNewRec
Yada, yada, yada...

But Access tells me it cannot find the form.
Click to expand...

Remember, sub-forms on a form are simply controls. Each sub-form control can
have ANY form specify for it. You can have 10 sub-form controls on the SAME
form, and EACH sub-form control can be the SAME form! (that means the same
form would appear 10 times on the form as sub-forms). Now, which sub-form
are you referring to? (that is why I spent some time explain that sub-forms
are just controls on form, and the name of the control does NOT have to be
the same as the control).

So, in the above..you can go;

me.MySubFormContorl.Visible = true

Like I say...due to reasons of confusing the user...I would suggest that you
open a new form for data entry..and not try and "switch" that form on the
users (this will get confusing...since they see the details listing..and
will naturally assume a connection between the two).
 
OK, I think I follow your logic here, and this does make
sense. If I remove the bottom form (the single form)
this would also give me more room on the page to scroll
through records. Once I click on a record, I can use the
On Click event to open up the edit form, or if I click
new I would have it pop up in data entry. This still
provides some protection for my existing records because
the user has to cause an action to be performed before
the data can be edited. The reason I need to have this
second form rather than enter the data in the datasheet
view is because the second form also has 2 subforms for
entering extra data not required in all cases.

Thanks for the advice, I'll see if I can make this work!
Sandra G
 
Sandra said:
OK, I think I follow your logic here, and this does make
sense. If I remove the bottom form (the single form)
this would also give me more room on the page to scroll
through records. Once I click on a record, I can use the
On Click event to open up the edit form, or if I click
new I would have it pop up in data entry. This still
provides some protection for my existing records because
the user has to cause an action to be performed before
the data can be edited.
Click to expand...

I like the above very much (good thinking on your part).

I also think that launcing the form will make things easy for your
usres..and you get more screen space too!

You can take a look at some screen shots of forms that do the above....(note
how I have a button on the forms to launch the form to edit in single view).

You can see what I mean here:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

The code behind those buttons to launch the form is easy....

docmd.openFrom "moredetal",,,"id = " & me.id
 
Well fiddlesticks, one step forward, one step back LOL! I
still have a glitch, so I'll explain what I have now.
On the tabbed form I have just the datasheet subform,
this subform is from a query which filters for the
donation records based upon the PK in master PeoplePlaces
table. This works well because I can see more records.
This form cannot be edited. The OnClick event opens the
DonationsForm form filtered by the donation transaction
PK DID of the clicked record. This form allows for all
the edits, etc. and works just fine. However...I still
have a bit of a problem with new data.

I created a new form DonationsNew, identical to
DonationsForm, but set to DataEntry. I have a NEW button
which opens this form. I can link the first new record
to the PPID record in PeoplePlaces, but after that the
PPID is not being passed to the form. Here's what I have
in the OnClick of the NEW button:

Private Sub NewRecord_Click()
On Error GoTo Err_NewRecord_Click
MyID = Me.PPID
Dim stDocName As String
stDocName = "DonationsNew"
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Forms!DonationsNew.PPID = MyID
Exit_NewRecord_Click:
Exit Sub

Err_NewRecord_Click:
MsgBox Err.Description
Resume Exit_NewRecord_Click

End Sub

So, I guess what I need now is how to continue to pass
the PPID variable to each new record.

And one last thing will make this really functional...how
do I requery the DonationsList datasheet form after a new
record is added?

Thank you so very much. You have been a Godsend!
Sandra
 
Back
Top