9 Forms using only the one Table

[rdsandy]

Hi,

I am going to start off and apologise for the length of this post!

I have a form with 9 subforms on. Currently, each subform has its own table. Each of the tables have exactly the same field layout (ie: same fields, but slightly different names for whichever table it is). Here is an example of what I mean:

One table is called ProjectCommercial(PC), another called ProjectTechnical (PT). In these two tables (plus the other 7 tables), 7 fields are identical in each table, and 2 fields are nearly identical (in PT theres a PTID field and a PT field - field with same name as table, and in PC theres a PCID field and a PC field).

I want to normalise this so that there is only one table, and the 9 subforms use this one table. I made a table called BrainstormingTable, and have a TableID field, which will serve to say if its a PT, or PC etc. Part of the reason I want to normalise it is there is a lot of code which is really unnecessary if there is only one table.

Ive made a couple of subforms (placed on a new form) and set them to BrainstormingTable, but I don't know how to add in a row and make the field TableID equal to 1 for example if the subform name is PT. I have named the subform BSPT (stands for BrainstormingProjectTechnical) for the PT part and BSPC for the PC part, with the record source to BrainstormingTable. I tried this code but it doesn't work:

Private Sub BrainstormingTableDescription_AfterUpdate()
    If Me.Name = "BSPT" Then
        MsgBox "BSPT"
    ElseIf Me.Name = "BSPC" Then
        MsgBox "BSPC"
    End If
End Sub
 

where BrainstormingTableDescription is the description field in BrainstormingTable, similar to PT in the PT table, or PC in the PC table etc.

What I noticed is that Me.Name is returning BrainstormingTable, which is the name of the subform when it is opened as a normal form (if that makes sense). What I want is to basically check if the subform name on the main form is equal to BSPT, or BSPC etc and if so then set the TableID field to the correct number (I currently have a msgbox for testing purposes).

Many thanks in advance,

Andrew

[puppydogbuddy]

Andrew,
It sounds to me like you forgot to set up the Master/Child Links on TableID in the subform control. If you do that, Access will manage the links for you. To set up the links, click on the subform control, which is on the border between the main form and the subform....then invoke the property sheet, go to master link and click the builder button with 3 dots on the right. Ditto for the child link.

As for the code, are you trying to look up the value in your table after it has been entered on the subform?...please clarify.

[rdsandy]

Andrew,
It sounds to me like you forgot to set up the Master/Child Links on TableID in the subform control. If you do that, Access will manage the links for you. To set up the links, click on the subform control, which is on the border between the main form and the subform....then invoke the property sheet, go to master link and click the builder button with 3 dots on the right. Ditto for the child link.

As for the code, are you trying to look up the value in your table after it has been entered on the subform?...please clarify.

Sorry I didn't get back to this, I put this on the side for a while to do other things.

I did forget to put the links in, and have done that. What I was asking before was how to set the tableID field to a number for which ever subform I was in, so if the record was going into ProjectTechnical, TableID would be set to 1, if the record was in ProjectCommercial then TableID would be set to 2 etc. I forgot that you could set the default value on the text box of the subform, as opposed to the default value in the actual table, which is what I have done.

One question regarding this site though, how do you put email notifications on so that when someone replies to the post I am notified? At the moment on my settings, I have under Edit Options, Default Thread Subscription Mode set to "Instant Notification", but under List Subscriptions, each of my threads/threads I posted in are set to "None" for notifications. How do I change them, and also how do I have new threads in the future set to instant notifcation?

Thanks

[rdsandy]

I fixed all the current threads to instant notification (by selecting them all and setting them to instant notification from the drop down box at the bottom), but will new threads have notifcations on do you know?

[puppydogbuddy]

I fixed all the current threads to instant notification (by selecting them all and setting them to instant notification from the drop down box at the bottom), but will new threads have notifcations on do you know?

It should if you signed in and changed your email settings, checking the setting for immediate notification.