How to deal with a complicated table structure...

[Florianbur]

Hi you all, I am new, to this forum and to access, this is my first post here... hope to stay around for a while

Here my problem (first one):

The picture above shows my table relationships. Now I believe them all to be right, so I didn't post in the Table Forum.

The idea here is to add Information to the main table, in there included sbu, user, area, number.

Now I got two problems:

1. After those 4 Informations are entered into the Formular, the db should check wether the relation btw. le and sbu exists (n:m relation through the table in the middle). If not, it should show a MsgBox and if wished for, add the combination to the matchtable relating le and sbu.

2. The other problem ist the last part. After the 4 Informations are given, at the end we got 3 values for sure: "tier", "area", "number". With those three, the according control should be found. Each combination of the three is unique. If the combination does not exist, it should show a MsgBox again and if wished for, add the combination with a popup showing for the Controltext.

Hope this is understandable...

Now, I have no idea where even to start with this or how to put it into effect, code, comboboxes, popups, whichever...

Since I am really not getting anywhere alone, I badly need some help on this one. Would someone please be so kind and think it over for a bit... these problems shouldn't be uncommon...

 

[KenHigg]

If you're new at this, you should really start off with just a couple tables, etc. until you get a handle on the problem. It's not hard, just make a copy of this db and delete all but two tables and experiment with them. If you try to make this work, you'll be all over the place fixing bugs...

 

[Florianbur]

Well I am not *that* new...

It is like the 4th time I am building this database, the three times I did it so far I didn't include those relationships, but i fear the time has come...

 

[KenHigg]

I really feel like some of the normalization is extraneous (I know, I'm going to get bashed!). Without a clear understanding of your business process, it would pointless to speculate any further. If you feel like you need all of it then I would still build it a pc at a time until the bug surfaces...

 

[Florianbur]

Private Sub xxx_NotInList(NewData As String, Response As Integer)
    Response = acDataErrAdded
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("xxx", dbOpenDynaset)

    rs.AddNew
    rs!xxx = NewData
    rs.Update

    rs.Close: Set rs = Nothing
    Set db = Nothing
End Sub

Let me be a bit more precise. Up to now, I have basically used the following routine to update fields, with some Message Boxes in between.

The problem that I now have, is that there is no such thing as NotInList for two fields.
I also do not know when to check. I can't check if only one field is filled out, only if four are. I could check on each update of one of the four, if the other three are filled out and then run some script. But what if all four are filled out, and the user then changes one of them?
So I thought maybe about popups which a button to start which asks for the four fields, one after the other, with sbu and le being in the same popup field and the others adjusting their values...

I thought this problem to be quite comon... Maybe someone even has an example database...

 

[Florianbur]

If you feel like you need all of it then I would still build it a pc at a time until the bug surfaces...

What do you mean?

 

[KenHigg]

I gotta bug out - Chat tomorrow?...

 

[Florianbur]

Yes please, this is kind of urgent... I'll be around

 

[smercer]

1. After those 4 Informations are entered into the Formular, the db should check wether the relation btw. le and sbu exists (n:m relation through the table in the middle). If not, it should show a MsgBox and if wished for, add the combination to the matchtable relating le and sbu.

I find your problem discription is ambigus. try again without the abbreviations and we may get somewhere. your table names are also a bit confusing until you give a meaning to their existance. basicly I agree with KenHigg