Form/Subform sync

[Bob Quintal]

Please help. I am about to ask for something that is really a bit
of bad design, but this is how the boss wants it, and there is no
wavering.

I have a small inventory database where each record has several
fields. The boss has asked for a form with a subform. the main
form is in single form view and displays all the fields associated
with an item. The subform is in datasheet view and displays only a
few fields of each item. (TagNumber, Item, Description, and
Location).

The boss wants to be able to see several records in the subform,
then select an item, and have the main form go to that particular
record, and display all the details.

I know that I am looking at an on-click event or maybe an
on-enter, but I do not know how to code it. I hope someone else
is working this weekend.

The boss wants to use a subform to do what a listbox does.
Instead of the subform, use an unbound multi-columned listbox.
it's afterupdate event can do a findfirst on the key field for the
record in the mainform.

code would be (assuming ID is the number you want to lookup):
..
Me.RecordsetClone.FindFirst "ID= " & Me.ID
If Not Me.RecordsetClone.EOF Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

If you want to lookup a string, you would need some extra quote
marks "ID= """ & Me.Keycustomer & """"

Show it and ask if that's all right. If for some reason it's not,
you would have to put the findfirst in the subform. changing some of
the the me. s to parent.

Parent.RecordsetClone.FindFirst "ID= " & Me.ID
If Not Parent.RecordsetClone.EOF Then
Parent.Bookmark = Parent.RecordsetClone.Bookmark
End If

The problem is that the on-click for each control on that subform is
independent, so you'd have to put it in all the controls on click
events, as well as the detail on click event

Lot more work for the same functionality.

 

[Dan Robles]

Please help. I am about to ask for something that is really a bit of bad
design, but this is how the boss wants it, and there is no wavering.

I have a small inventory database where each record has several fields. The
boss has asked for a form with a subform. the main form is in single form
view and displays all the fields associated with an item. The subform is in
datasheet view and displays only a few fields of each item. (TagNumber, Item,
Description, and Location).

The boss wants to be able to see several records in the subform, then select
an item, and have the main form go to that particular record, and display all
the details.

I know that I am looking at an on-click event or maybe an on-enter, but I do
not know how to code it. I hope someone else is working this weekend.

 

[Bob Quintal]

Bob, there is no convincing the boss to use your first suggestion.
So i am bound to your other option. My VBS skills are not great,
(read: none at all) so I need a little more detail. or is this
do-able in SQL?

More help needed on this one

No, it is not doable in SQL.

Create the form and the form you will wish to use as the subform.
Set the locked property of each control on the subform the subform
to yes. set the allow additions property of the form to no.

In the subform's code module paste the following code

Private Sub Move_Parent_Record()
Parent.RecordsetClone.FindFirst "ID= " & Me.ID
If Not Parent.RecordsetClone.EOF Then
Parent.Bookmark = Parent.RecordsetClone.Bookmark
End If
End Sub

In the On Click event for each control in the detail section of the
subform, paste the following statement

CALL Move_Parent_Record()

Also add it to the detail section's on Click event.

save the form

Set the subform into the main form. Do NOT set any Link fields.

You should be off and running.

=?Utf-8?B?RGFuIFJvYmxlcw==?=

The boss wants to use a subform to do what a listbox does.
Instead of the subform, use an unbound multi-columned listbox.
it's afterupdate event can do a findfirst on the key field for
the record in the mainform.

code would be (assuming ID is the number you want to lookup):
..
Me.RecordsetClone.FindFirst "ID= " & Me.ID
If Not Me.RecordsetClone.EOF Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

If you want to lookup a string, you would need some extra quote
marks "ID= """ & Me.Keycustomer & """"

Show it and ask if that's all right. If for some reason it's not,
you would have to put the findfirst in the subform. changing some
of the the me. s to parent.

Parent.RecordsetClone.FindFirst "ID= " & Me.ID
If Not Parent.RecordsetClone.EOF Then
Parent.Bookmark = Parent.RecordsetClone.Bookmark
End If

The problem is that the on-click for each control on that subform
is independent, so you'd have to put it in all the controls on
click events, as well as the detail on click event

Lot more work for the same functionality.

 

[Dan Robles]

Bob, there is no convincing the boss to use your first suggestion. So i am
bound to your other option. My VBS skills are not great, (read: none at all)
so I need a little more detail. or is this do-able in SQL?

More help needed on this one