lots of related tables causing problems for forms...

[pablavo]

Hello,

I've been working on a db that has SQL server backend tables. What's more important is that there are a lot of related tables and this is giving me some grief designing the forms.

The form Im designing has quite a few subforms. one of the subforms record source's is a table that has multiple relationships. 7 to be exact;
3 are one-one,
2 are many-one (it's a child of two tables)
2 are one to many.

So, when I enter info into the form one of the IDs become null. I suspect because of one of the relationships. I was thinking of ways around this. Perhaps making all forms unbound, so that, when a user enters all the values they could press a submit button instead and all the values would enter the SQL db at the same time. maybe that would work?

I know this is fairly vague, it's just to see if anyone has similar experiences or advice, for example, if submitting the values all at once would work?

Thanks

 

[dkinley]

No advice, just a caution on the 'all at once'. To update the downstream relationships, you will get an error if the upstream records aren't created first so be aware of the order that you do your updates.

Update latency may or may not be an issue but probably would incorporate the DoEvents between each table update to ensure the record is created prior to entering records for the related tables.

-dK

 

[georgedwilkinson]

Hello,

I've been working on a db that has SQL server backend tables. What's more important is that there are a lot of related tables and this is giving me some grief designing the forms.

The form Im designing has quite a few subforms. one of the subforms record source's is a table that has multiple relationships. 7 to be exact;
3 are one-one,
2 are many-one (it's a child of two tables)
2 are one to many.

So, when I enter info into the form one of the IDs become null. I suspect because of one of the relationships. I was thinking of ways around this. Perhaps making all forms unbound, so that, when a user enters all the values they could press a submit button instead and all the values would enter the SQL db at the same time. maybe that would work?

I know this is fairly vague, it's just to see if anyone has similar experiences or advice, for example, if submitting the values all at once would work?

Thanks

Maybe I'm showing my ignorance in thinking this should be simpler. Why do you care about the multiple relationships? If it is data in a sub-form, you should care only about the relationship(s) to/from the table in the main form. What am I missing?

 

[pablavo]

Hi, sorry for the late response and thanks for the responses to my thread. I ended up not having to work on this. I'm sure I'll come up against similar again.

I'm not sure I understand George. The Main forms record source is of a table that's just one of the relationships to the subform record source in question.

Thanks again,


Paul

 

[georgedwilkinson]

No clue. Not sure what I was thinking about.