Using Access 2007 with AS400 backend

[nico5038]

The form will be gray and empty when there's no data to display. :-(
I guess you have no data in some cases or your linking fields are missing or incorrevt.

Make sure that when placing the subform you accept the proposed linkfield(s).
Afterwards you can check the used fields for the link by single-clicking the subform-control and lookup the Linkage Master and Child properties underneath the Data tab.

When you want to show the subform always set the "Allow additions" to Yes and there will be an empty datainput form when there's no data.

Nic;o)

[FishVal]

Morning FishVal

Well, I have tried the subform option, but just cannot seem to make it look like a normal form. I have played around with the properties to get rid of borders and all sorts, but are still left with some remnants of the subform and it just ends up looking ugly. I must be missing something somewhere.

I will have another play this morning to see if I can edit a subform to a point where only the controls on it is visible.

Hi!

Subform control settings:
BorderStyle=Tra nsparent
SpecialEffect=F lat
BorderWidth=Hai rLine
BorderColor=0

Subform form settings:
Scroll bars=Neither
RecordSelectors =No
NavigationButto ns=No
DividingLines=N o
BorderStyle=Non e
ControlBox=No

Don't know whether all these settings are important, I just set those seemed to be worth to set and now it looks exactly like controls in the main form, no borders, no form controls, nothing that resembles subform. The only thing I'd like to point out is that I'm using Access2003.

[bloukopkoggelmander]

Right Nico

My tables did have data in them, but what I did wrong was when I created the subform, I specified my own form fields to link instead of accepting the Access suggested default.

After following your advice and changing the view for the subform to single form , I am now ablt to see the controls when changing to From View. Also , the built in Access navigation now works.

Man you might just be a God in disguise. I will try this on my main database I am working on ( only tried this on a testing database with linked tables).

I will be back a.s.a.p with the results.

[bloukopkoggelmander]

Howzit Fishval, thank you for your reply as well. I will make sure my setting correlate to yours and then come back as soon as. Thanks guys.

If this works then you all would have saved me from going completely mental. By the way, is there a maximum amount of subforms that one can use on a main form? Just asking cause I would have to do this for up to 5 or 6 tables on one main form.

[MMcCarthy]

Howzit Fishval, thank you for your reply as well. I will make sure my setting correlate to yours and then come back as soon as. Thanks guys.

If this works then you all would have saved me from going completely mental. By the way, is there a maximum amount of subforms that one can use on a main form? Just asking cause I would have to do this for up to 5 or 6 tables on one main form.

Don't know if there is a maximum but I have a form with a multitab page control and there are currently 6 subforms on it so you should be fine.

[bloukopkoggelmander]

Thanks Mary

Yip can confirm that I have put 5 subforms on the one tabbed page and it seems to work fine ecept that one of the subforms will display the first record, but when scroll on to records 2, 3, 4 etc , this subform will disappear ( there is data in the table with the refence number it uses as PK, same as the other tables). But hey at least this is getting somewhere now. ( I would still want to find out why Access is behaving as it does with AS400 though).

I want to make sure this works fine before I leave this thread

So I will be back soon.

Oh and thanks to whoever it was that changed the heading of this thread, it would make it more relevant for others in future when searching for a solution. :-)

[MMcCarthy]

Thanks Mary
Oh and thanks to whoever it was that changed the heading of this thread, it would make it more relevant for others in future when searching for a solution. :-)

I take credit for that :)

Not sure what's going on with the subform that won't navigate. But let us know how you get on.

[NeoPa]

I take credit for that :)

Yeah, she didn't change it, she just likes to take the credit :D (ROFLMAO)

[bloukopkoggelmander]

Hehehehehe..... . Hey as long as others are happy, then so am I. :-) :-) :-)

By the way, It's not easy getting subform controls positioned on a form to look like other controls. There are quite big gaps where the main form controls end and the subform controls begin. Bit ugly looking. Just hope they will accept it because it looks like I have now managed to get around my original posted problem.

[bloukopkoggelmander]

Oop sorry didn't finish my centence man..... Damn I MUST be llosing it!!!

I was going to say that it seems like I have cracked it now and I am getting around my original problem I used to have.

[nico5038]

When using a tabbed control it's advisable to make all subforms exactly the same size and have them on the same position.
I created in the past a small function to store the Top, Left, Width and Hight properties of one subform and used that for the others, but manually it's no big deal either. It's however a small and important issue as "moving" subforms are annoying for the eye.
You can remove the navigation bar on the subform and add your own buttons to save space and to "hide" the fact that it's a subform.

Nic;o).

[NeoPa]

Hehehehehe..... . Hey as long as others are happy, then so am I. :-) :-) :-)

You know I was just kidding right?
Of course Mary did it. She catches most of that sort of stuff before I even get a chance to look at it. Not that I'm complaining mind - I don't know what we'd do without her :)

[bloukopkoggelmander]

Morning All

Nico5038, yip I have removed the navigation bars from all the subforms as I do not need to use them and will only be using the main form navigation. So no need for me to create custom buttons. Also the problem I have with the subforms is that they have to slot in on certain places on the main form, but where I slot them in, the borders of the subform stops the form controls from looking like it is in sequence as it covers other controls above and below the subform when view in Form View. So now I have rather large gaps between controls where they are placed on my main form.

NeoPa, no worries man. Of course I know you were kidding. It's just good to have a bit of a joke around a laugh sometimes otherwise we would all go insane.

[bloukopkoggelmander]

Right All

First I want to thank everyone who contributed to this discussion. You all have helped me loads and your help is very much appreciated.

Second for future reference, here is what I have done to get over this problem:

First, I made sure that I set up the relationsships correctly for the linked tables within Access.

Then on my main form, I bound the form to what is in a way my 'main' form.
If I then wanted to add controls that links to different fields in other tables, I simply inserted a subform into my main form which points to the new tables and it's required fields.

I then edited the subform properties as follows to make it look like just any other form controls when viewing the main form in Form View :

Highlight the Caption Label. Set VISIBLE - NO.
Highlight subform border . Set BORDER STYLE - TRANSPARENT
Highlight subform form. Set DEFAULT VIEW - SINGLE FORM
Set BORDER STYLE - NONE
Set RECORD SELECTORS - NO
Set NAVIGATION BUTTONS - NO
Set SCROLL BARS - NEITHER

Then remove the subform Form Header/Footer by right clicking on the subform Header/Footer bar and deslect the option.

I then just position the subform controls where I need them and that's it!!

Hope this explanation is clear enough.

Thanks again All!!! : - )

[NeoPa]

Thanks for posting that Martin.
That's a perfect way to finish off a thread. A clear note of everything you did to make it work and how :)