Edit Data using MSFlexgrid

[Guest]

I am using an MSFlexgrid to display data and I know that you can not directly
edit data in this grid, but I have seen some code to put a floating text box
on top of a cell in a grid to make it appear like you are editing data in the
grid. I have added the code to do this and it seems to work as expected
except for one major issue. The text box is displayed UNDER the grid instead
of on TOP of the grid when I try to edit a field. I have seen a ZOrder
reference in VB but I am using Access 97 and I don't have this property.
Does anyone have a suggestion on how to get a textbox to appear on TOP of the
MSFlexGrid? Thanks in advance for your help.

 

[Albert D. Kallal]

Sounds like a lot of work.

Any reason why you don't just use the built-in continues forms?

here is some screen shots:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Guest]

I actually saw some of your suggestions on another website and tried to
implement them. I am trying to mimic the last example that you show which
has a "parent" form and a "child" form. When I select a record in the
"parent" form, I need it to refresh the data in the "child" form. Do you
have an example of how to link these forms in code?

In addition, I need to edit some fields in the "parent" form.

Thanks for your help.

 

[Albert D. Kallal]

I am trying to mimic the last example that you show which
has a "parent" form and a "child" form. When I select a record in the
"parent" form, I need it to refresh the data in the "child" form. Do you
have an example of how to link these forms in code?

If you talking about the very last one, the grid on the left side actually
does not need any code. Ms-access will load, set, and maintain the record
display for you.

Assuming there is a relation between the main form, and the child data, then
you just have to set the link child/master fields in the sub-from control.
If you use the wizard, then this will all be setup for you.

No code is need.

of course, in hat example, I have a one to many, and for each of the "many",
I have another grid on the right that display multiple rows from the left
grid side (so, for example if a donation is $60 on the left side, I am able
to enter "many" records on the right side grid to "add up to" the $60.

The grid on the right side did need some code to be filled. I used the
on-current code of the left side grid:

Me.Parent.frmMyDetails.Form.Requery

So, to make that whole complex form of 2 grids (left side, right side), and
the main form part function..it took ONE LINE of code!!!

In addition, I need to edit some fields in the "parent" form.

Sure, the main part form fields are able to be editing freely. Again...no
code needed....

So, the approach here is to build a main form with the fields you need to
display and edit from the main table. To display the child records, you
build a continues form based on the child table, and then insert that into
the above form you just made. You can also use the wizard to insert this
sub-form (but, it will use a datasheet for you...and I prefer continues
sub-forms. However, either way...you don't need code, and ms-access will
fill, and maintain the child records display for you.

Note that for all cases, I did not have to build any queries that joins the
data. in fact, you don't want to build queries that join the data as that
will actually cause the fields to not be editable in the form.

So, main table = main form. Each child table = sub-form.

 

[Guest]

Thanks for all the detailed information.

I am still having an issue however. My main form is just a container for
the 2 continuous forms on the left (ParentForm) and right (ChildForm) so
these forms have no link to the main form. I populate my ParentForm with
Parent records and I traverse through the records on this form I want my
Child Form to requery with the Child records linked to the current Parent
record.

I am using the code in the Fom_Current of the ParentForm, where IssueId is a
field on the ParentForm:

lngParentId = IssueId

Me.Parent.Form.frmChildForm.RecordSource = "SELECT * from qryChild WHERE
ChildId = " & lngParentId

Me.Parent.Form.frmIssuesViewDetail.Requery

I get the error "Object doesn't support this Property or method" on the
RecordSource line but don't get an error on the Requery line.

I also tried to set the lngParentId to a public variable and set as record
source in Child Form but can't reference in property of form and not sure how
to set in code.

I'm sure there is an easy way to do this!!!!

 

[Albert D. Kallal]

I am still having an issue however. My main form is just a container for

the 2 continuous forms on the left (ParentForm) and right (ChildForm) so
these forms have no link to the main form.

Ok, above is fine. (I thought there might be some fields in the form that
are not
continues...and you need to edit them. This would suggest that the main form
is bound to some table, and then you 2 forms are dropped in.

However, it don't matter. Often I also use a main un-bound form to contain 2
sub-forms.

I am using the code in the Fom_Current of the ParentForm, where IssueId is
a
field on the ParentForm:

lngParentId = IssueId

Me.Parent.Form.frmChildForm.RecordSource = "SELECT * from qryChild
WHERE
ChildId = " & lngParentId

Me.Parent.Form.frmIssuesViewDetail.Requery

The above is close...

You need to use the "forms" object to get a reference to the reocrdsource.

Me.Parent.Form.frmChildForm.Form.RecordSource = "SELECT * from qryChild
WHERE
ChildId = " & lngParentId

Note the above "form" object use. So, use the above syntax..it should work.
You also DO NOT have to execute a requery, becasue access will do
that for you because you are setting the record source.

Further, you can actually remove all of your code, and do this with
using the link master/child settings. This approach would be
preferred if you need to actually need to add records to the child form.

The link master/child settings for the child from (right side) would look
like:

LinkMaster:
frmLeftSideFormContorlName.form!IssueID

Linkchild:
ChildID

However, if you use link child/master, you will still need
a requery in the left side on-current event.

You can use:

Me.Parent.Form.frmIssuesViewDetail.Requery


As I said, your example code will work....we just need the correct reference
to the child form "form" object...

The use of the link master/child setting is preferred if you allow adding of
records on eh right side..since then ms-access will setup and set the value
of ChildID for you. If you don't need to do this..then you can fix/modify
your existing code...and remove the re-query.

 

[Marshall Barton]

I am still having an issue however. My main form is just a container for
the 2 continuous forms on the left (ParentForm) and right (ChildForm) so
these forms have no link to the main form. I populate my ParentForm with
Parent records and I traverse through the records on this form I want my
Child Form to requery with the Child records linked to the current Parent
record.

I am using the code in the Fom_Current of the ParentForm, where IssueId is a
field on the ParentForm:

lngParentId = IssueId

Me.Parent.Form.frmChildForm.RecordSource = "SELECT * from qryChild WHERE
ChildId = " & lngParentId

Me.Parent.Form.frmIssuesViewDetail.Requery

I get the error "Object doesn't support this Property or method" on the
RecordSource line but don't get an error on the Requery line.

I also tried to set the lngParentId to a public variable and set as record
source in Child Form but can't reference in property of form and not sure how
to set in code.

You are using a redundant .Form property (Parent is a form
object so Parent.Form doesn't do anything).

As Albert said, the Link Master/Child Fields properties are
preferred over setting the record source. OTOH, I prefer to
use a slightly different arrangement.

Try adding a hidden text box (named txtLink) to the main
form. Then use ParentForm's Current event to set txtLink to
the id field:
Me.Parent.txtLink = Me.IssueId

Then set ChildForm's link master property to txtLink and the
link child property to ChildId.

In this arrangement you do not need any other code, not even
a Requery.

 

[Guest]

Thanks guys! Based on both of your emails, I was able to finally get this
code working! It looks awesome! I appreciate the help.

One last question... my original issue for this posting was about floating a
text box over an msflexgrid so it would appear that the grid was being
edited. I still have a form in my production application with a grid already
in use that must continue to be used. I was wondering if anyone knew how to
get a textbox to float ON TOP of the grid (instead of under it) so I could
make it look like I am editing the grid. I am using access 97.

 

[Albert D. Kallal]

I was wondering if anyone knew how to

get a textbox to float ON TOP of the grid (instead of under it) so I could
make it look like I am editing the grid. I am using access 97.

The only thing I can think of is that you use the format, and do a "send to
back". Place a text box on top..(perhaps try a bring to front).

Just keeping the box visible for the time being..if you can get it to
display on top..it might work, but it possible that the flex gird don't
support or respect the front/back settings of a control on a form. If the
flexgrid don't support that, then your text box will appear behind. Perahps
you cmoe up with a process where the user click on the square, and then
throw up a inputbox command, or a some type of input form...

Just try placing a regular text box contorl on the form on top of the flex
grid. If you can't figure out how to get it to dispaly on top, you likey
can't do this without restorting to some complex api, or windows code. So,
you have to have the user click (or double click) on the grid square..and
then launch some type of input screen/box whatever...

 

[Guest]

I tried the 'Bring To Front' and 'Sent To Back' but these did not work
either. I will try an input form - hopefully that will work. Thanks for all
your help. You are da bomb!

 

[Guest]

JKro,

From the 'Visual Basic Black Book' I have:

Text1.Move MSFlexGrid1.CellLeft + MSFlexGrid1.Left, _
MSFlexGrid1.CellTop + MSFlexGrid.Top, MSFlexGrid1.CellWidth, _
MSFlexGrid1.CellHeight

If you want to use the FlexGrid with an Access form, how did you set the
datasource?

I'm thinking of ising the MSFlexGrid in a form as Access grids don't allow
some functionality I need.


swas